A billion operations in the database in 0.3 seconds. Do not compare OLTP with OLAP, but QuestDB with PostgreSQL

What affects the choice of database

• real-time transaction processing or online analytic processing
  
• vertically or horizontally scalable
• in the case of a distributed base - data consistency / availability / separation resistance (CAP theorem)
• a specific data scheme and restrictions in the database or storage that does not require a data scheme
  
• data model - key-value, hierarchical, graph, document or relational
• processing logic as close to data as possible or all processing in the application
• work mainly in RAM or with a disk subsystem
• universal solution or specialized
• we use the existing expertise on a database that is not particularly suitable for the requirements of the project or we develop a new one in suitable but not familiar training, “blood and sweat” (the same applies not only to development, but also to operation)
  
• built-in or in another process / network
• hipster or retrograde

• “Alien” query language
• the only native API for working with the database, which will complicate the transition to other databases (time, team effort and project budget were spent)
• unavailability of drivers for other platforms / languages ​​/ operating systems
• lack of source codes, descriptions of the data format on the disk (or a ban on reverse engineering licenses, especially Oracle with the buggy Coherence)
• license cost growth year by year
• own ecosystem and difficulty finding specialists
• , ,

QuestDB

• An attempt to get the most out of the hardware for performing analytical queries - vectorization of aggregation functions, working with data through memory mapped files
• SQL as the language of DML queries and DDL operations for managing the database structure
• support for join tables specific to time series DB
• support for window and aggregation functions in SQL
• the ability to embed a database in an application on the JVM
• JVM, ServiceLoader
• Influx DB line protocol (ILP) UDP Telegraf. «What makes QuestDB faster than InfluxDB»
• PostgreSql 11 PostgreSQL: JDBC, ODBC psql
  
• web - REST endpoint , SQL json
  
• ,
  
• zero-GC API, .
  
• ( )
  
• 64 Windows, Linux, OSX, ARM Linux FreeBSD
  
• , open source,

SIMD operations

Running docker database image

docker pull questdb/questdb

docker run --rm -it -p 9000:9000 -p 8812:8812 questdb/questdb

Jdbc access

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import static org.assertj.core.api.Assertions.*;

public class QuestDbDriverTest {

    @Test
    void containerIsUpTestByJdbcInvocation() throws Exception {
        try (Connection connection = DriverManager.getConnection("jdbc:tc:questdb:///?user=admin&password=quest")){
            try (Statement statement = connection.createStatement()){
                try (ResultSet resultSet = statement.executeQuery("select 42 from long_sequence(1)")){
                    resultSet.next();
                    assertThat(resultSet.getInt(1)).isEqualTo(42);
                }
            }
        }
    }
}

import io.questdb.ServerMain;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.io.TempDir;

import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class QuestDbJdbcTest {
    @Test
    void embeddedServerStartTest(@TempDir Path tempDir) throws Exception{
        ServerMain.main(new String[]{"-d", tempDir.toString()});
        try (DriverManager.getConnection("jdbc:postgresql://localhost:8812/", "admin", "quest")){
            try (Statement statement = connection.createStatement()){
                try (ResultSet resultSet = statement.executeQuery("select 42 from long_sequence(1)")){
                    resultSet.next();
                    assertThat(resultSet.getInt(1)).isEqualTo(42);
                }
            }
        }
    }
}

Embedding in java application

import io.questdb.cairo.CairoEngine;
import io.questdb.cairo.DefaultCairoConfiguration;
import io.questdb.griffin.CompiledQuery;
import io.questdb.griffin.SqlCompiler;
import io.questdb.griffin.SqlExecutionContextImpl;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.io.TempDir;

import java.nio.file.Path;

public class TruncateExecuteTest {
    @Test
    void truncate(@TempDir Path tempDir) throws Exception{
        SqlExecutionContextImpl executionContext = new SqlExecutionContextImpl();
        DefaultCairoConfiguration configuration = new DefaultCairoConfiguration(tempDir.toAbsolutePath().toString());
        try (CairoEngine engine = new CairoEngine(configuration)) {
            try (SqlCompiler compiler = new SqlCompiler(engine)) {
                CompiledQuery createTable = compiler.compile("create table tr_table(id long,name string)", executionContext);
                compiler.compile("truncate table tr_table", executionContext);
            }
        }
    }
}

Web console

Do you need another database?