QuestDB (questdb/questdb)

QuestDB

https://github.com/questdb/questdb
Admin
QuestDB is an open-source time-series database offering fast ingestion and low-latency SQL queries,...

Tokens:44,140
Snippets:325
Trust Score:8
Update:4 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# QuestDB - High-Performance Time-Series Database

QuestDB is an open-source time-series database designed for high-performance ingestion and low-latency SQL queries. Built primarily in zero-GC Java with native C/C++ libraries for performance-critical operations, QuestDB features column-oriented storage, SIMD-accelerated vector execution, and specialized time-series SQL extensions like SAMPLE BY, LATEST ON, and ASOF JOIN. The database supports multi-tier storage (WAL to native columnar to Parquet on object storage) and provides multiple interfaces including REST API, PostgreSQL wire protocol, and InfluxDB Line Protocol.

The core architecture consists of the Cairo storage engine for table readers/writers and columnar storage, the Griffin SQL engine for parsing, compilation, and execution, and the Cutlass network layer handling HTTP, PostgreSQL wire protocol (PGwire), and InfluxDB Line Protocol (ILP). QuestDB excels at handling financial market data, sensor/telemetry data with high cardinality, and real-time dashboards requiring low-latency queries.

## Building and Running QuestDB

Build QuestDB from source with Maven, optionally including the web console and native binaries.

```bash
# Prerequisites: Java 11+ (64-bit), Maven 3, JAVA_HOME set

# Build JAR without tests (fastest)
mvn clean package -DskipTests

# Build with web console
mvn clean package -DskipTests -P build-web-console

# Build with web console and native binaries
mvn clean package -DskipTests -P build-web-console,build-binaries

# Run QuestDB server after building
mkdir /path/to/data
java -p core/target/questdb-9.3.2-SNAPSHOT.jar \
     -m io.questdb/io.questdb.ServerMain \
     -d /path/to/data

# Web console available at http://localhost:9000
# PostgreSQL wire protocol on port 8812
# InfluxDB Line Protocol on port 9009
```

## Running Tests

Execute JUnit tests for specific classes or methods during development.

```bash
# Run all tests
mvn test

# Run a specific test class
mvn -Dtest=LineTcpReceiverTest test

# Run a specific test method
mvn -Dtest=SqlCompilerImplTest#testSelectAll test
```

## Building Native C/C++ Libraries

Build native libraries for SIMD operations and platform-specific optimizations.

```bash
cd core
cmake -B build/release -DCMAKE_BUILD_TYPE=Release
cmake --build build/release --config Release
# Artifacts output to: core/src/main/resources/io/questdb/bin/
```

## Java Client - Basic Data Ingestion (TCP)

The Sender API provides high-throughput data ingestion via InfluxDB Line Protocol over TCP. The client buffers data locally and automatically flushes when the buffer is full.

```java
import io.questdb.client.Sender;

public class BasicTcpIngestion {
    public static void main(String[] args) {
        // TCP transport on default ILP port 9009
        try (Sender sender = Sender.fromConfig("tcp::addr=localhost:9009;")) {
            // Insert trade data - symbols must come before other columns
            sender.table("trades")
                    .symbol("symbol", "ETH-USD")
                    .symbol("side", "sell")
                    .doubleColumn("price", 2615.54)
                    .doubleColumn("amount", 0.00044)
                    .atNow();  // Server assigns timestamp

            sender.table("trades")
                    .symbol("symbol", "BTC-USD")
                    .symbol("side", "buy")
                    .doubleColumn("price", 39269.98)
                    .doubleColumn("amount", 0.001)
                    .atNow();

            // Data automatically flushed when buffer fills or sender closes
        }
    }
}
```

## Java Client - HTTP Transport with Auto-Flush

HTTP transport provides stronger transactional guarantees and better error feedback compared to TCP. Configure auto-flush behavior for optimal batching.

```java
import io.questdb.client.Sender;

public class HttpIngestion {
    public static void main(String[] args) {
        // HTTP transport with custom auto-flush settings
        // auto_flush_rows=100000 - flush after 100K rows
        // auto_flush_interval=5000 - flush every 5 seconds minimum
        // retry_timeout=20000 - retry recoverable errors for 20 seconds
        try (Sender sender = Sender.fromConfig(
                "http::addr=localhost:9000;" +
                "auto_flush_rows=100000;" +
                "auto_flush_interval=5000;" +
                "retry_timeout=20000;")) {

            for (int i = 0; i < 1_000_000; i++) {
                sender.table("sensor_data")
                        .symbol("device_id", "sensor_" + (i % 100))
                        .symbol("location", "building_A")
                        .doubleColumn("temperature", 20.0 + Math.random() * 10)
                        .doubleColumn("humidity", 40.0 + Math.random() * 30)
                        .longColumn("reading_id", i)
                        .atNow();
            }
            // Explicit flush before close ensures all data is sent
            sender.flush();
        }
    }
}
```

## Java Client - HTTPS with TLS and Authentication

Configure TLS encryption and authentication for secure production deployments.

```java
import io.questdb.client.Sender;

public class SecureHttpsIngestion {
    public static void main(String[] args) {
        // HTTPS with authentication token
        // tls_verify=unsafe_off only for testing with self-signed certs
        try (Sender sender = Sender.fromConfig(
                "https::addr=questdb.example.com:9000;" +
                "token=YOUR_AUTH_TOKEN;" +
                "tls_verify=on;")) {

            sender.table("secure_trades")
                    .symbol("symbol", "ETH-USD")
                    .doubleColumn("price", 2615.54)
                    .atNow();
        }

        // TCP with authentication (for legacy servers)
        try (Sender sender = Sender.fromConfig(
                "tcp::addr=localhost:9009;" +
                "user=testUser1;" +
                "token=GwBXoGG5c6NoUTLXnzMxw_uNiVa8PKobzx5EiuylMW0;")) {

            sender.table("authenticated_data")
                    .symbol("source", "secure_client")
                    .longColumn("value", 42)
                    .atNow();
        }
    }
}
```

## Java Client - Builder API

Use the builder API for programmatic configuration with type safety.

```java
import io.questdb.client.Sender;
import io.questdb.client.Sender.Transport;
import java.time.Instant;
import java.time.temporal.ChronoUnit;

public class BuilderApiExample {
    public static void main(String[] args) {
        // HTTP transport with builder
        try (Sender sender = Sender.builder(Transport.HTTP)
                .address("localhost:9000")
                .bufferCapacity(128 * 1024)        // 128KB initial buffer
                .maxBufferCapacity(100 * 1024 * 1024) // 100MB max
                .autoFlushRows(50000)
                .autoFlushIntervalMillis(1000)
                .retryTimeoutMillis(30000)
                .build()) {

            // Insert with explicit timestamp
            sender.table("events")
                    .symbol("event_type", "user_login")
                    .stringColumn("user_id", "user_12345")
                    .longColumn("session_duration_ms", 3600000)
                    .timestampColumn("login_time", Instant.now().minus(1, ChronoUnit.HOURS))
                    .at(Instant.now());  // Explicit designated timestamp
        }

        // TCP transport with TLS
        try (Sender sender = Sender.builder(Transport.TCP)
                .address("localhost:9009")
                .enableTls()
                .advancedTls().disableCertificateValidation() // Testing only!
                .bufferCapacity(64 * 1024)
                .build()) {

            sender.table("tls_data")
                    .symbol("source", "secure")
                    .longColumn("value", 100)
                    .atNow();
        }
    }
}
```

## Java Client - Environment Variable Configuration

Load client configuration from environment variables for cloud deployments.

```java
import io.questdb.client.Sender;

public class EnvConfigExample {
    public static void main(String[] args) {
        // Set environment variable:
        // export QDB_CLIENT_CONF="http::addr=localhost:9000;auto_flush_rows=100;"

        try (Sender sender = Sender.fromEnv()) {
            for (int i = 0; i < 1000; i++) {
                sender.table("env_configured_table")
                        .longColumn("value", i)
                        .atNow();
            }
        }
    }
}
```

## Embedded Server Usage

Embed QuestDB directly in your Java application for testing or single-process deployments.

```java
import io.questdb.ServerMain;
import io.questdb.cairo.CairoEngine;
import io.questdb.griffin.SqlCompiler;
import io.questdb.griffin.SqlExecutionContext;
import io.questdb.cairo.sql.RecordCursor;
import io.questdb.cairo.sql.RecordCursorFactory;

import java.util.HashMap;
import java.util.Map;

public class EmbeddedServerExample {
    public static void main(String[] args) throws Exception {
        String dataDir = "/tmp/questdb-data";

        // Configure server with custom settings
        Map<String, String> env = new HashMap<>();
        env.put("QDB_HTTP_BIND_TO", "0.0.0.0:9000");
        env.put("QDB_LINE_TCP_NET_BIND_TO", "0.0.0.0:9009");
        env.put("QDB_PG_NET_BIND_TO", "0.0.0.0:8812");

        try (ServerMain server = ServerMain.create(dataDir, env)) {
            server.start(false);  // false = don't wait for shutdown signal

            CairoEngine engine = server.getEngine();

            // Execute SQL directly via the engine
            try (SqlCompiler compiler = engine.getSqlCompiler()) {
                SqlExecutionContext ctx = engine.newSqlExecutionContext();

                // Create table
                compiler.compile(
                    "CREATE TABLE IF NOT EXISTS measurements (" +
                    "  ts TIMESTAMP, " +
                    "  device SYMBOL, " +
                    "  temperature DOUBLE, " +
                    "  humidity DOUBLE" +
                    ") TIMESTAMP(ts) PARTITION BY DAY WAL;", ctx);

                // Insert data
                compiler.compile(
                    "INSERT INTO measurements VALUES " +
                    "(now(), 'sensor_1', 23.5, 65.2), " +
                    "(now(), 'sensor_2', 24.1, 62.8);", ctx);

                // Query with time-series extensions
                try (RecordCursorFactory factory = compiler.compile(
                        "SELECT device, avg(temperature), avg(humidity) " +
                        "FROM measurements " +
                        "WHERE ts > dateadd('h', -1, now()) " +
                        "SAMPLE BY 10m;", ctx).getRecordCursorFactory()) {

                    try (RecordCursor cursor = factory.getCursor(ctx)) {
                        // Process results...
                    }
                }
            }

            // Server continues running until closed
            Thread.sleep(60000);
        }
    }
}
```

## Time-Series SQL Extensions

QuestDB provides specialized SQL extensions for time-series data analysis including SAMPLE BY for time-based aggregation, LATEST ON for retrieving the most recent values, and ASOF JOIN for point-in-time lookups.

```sql
-- SAMPLE BY: Time-based aggregation
-- Aggregate sensor readings into 1-hour buckets
SELECT
    ts,
    device,
    avg(temperature) as avg_temp,
    max(temperature) as max_temp,
    min(temperature) as min_temp,
    count() as reading_count
FROM sensor_data
WHERE ts > dateadd('d', -7, now())
SAMPLE BY 1h
ALIGN TO CALENDAR;

-- LATEST ON: Get most recent value per partition
-- Get the latest reading for each device
SELECT * FROM sensor_data
LATEST ON ts PARTITION BY device;

-- ASOF JOIN: Point-in-time join for time-series
-- Match trades with the most recent quote at trade time
SELECT
    t.ts as trade_time,
    t.symbol,
    t.price as trade_price,
    t.quantity,
    q.bid,
    q.ask,
    t.price - q.bid as spread_to_bid
FROM trades t
ASOF JOIN quotes q ON (t.symbol = q.symbol);

-- Window functions with time ranges
SELECT
    ts,
    symbol,
    price,
    avg(price) OVER (
        PARTITION BY symbol
        ORDER BY ts
        RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
    ) as moving_avg_5m
FROM trades;
```

## REST API - SQL Query Execution

Execute SQL queries via the REST API endpoint on port 9000.

```bash
# Execute a SELECT query (returns JSON)
curl -G "http://localhost:9000/exec" \
  --data-urlencode "query=SELECT * FROM trades LIMIT 10"

# Response format:
# {
#   "query": "SELECT * FROM trades LIMIT 10",
#   "columns": [
#     {"name": "ts", "type": "TIMESTAMP"},
#     {"name": "symbol", "type": "SYMBOL"},
#     {"name": "price", "type": "DOUBLE"}
#   ],
#   "dataset": [
#     ["2024-01-15T10:30:00.000000Z", "ETH-USD", 2615.54],
#     ...
#   ],
#   "count": 10
# }

# Execute DDL statement
curl -G "http://localhost:9000/exec" \
  --data-urlencode "query=CREATE TABLE IF NOT EXISTS logs (ts TIMESTAMP, level SYMBOL, message STRING) TIMESTAMP(ts) PARTITION BY DAY"

# Query with limit and pagination
curl -G "http://localhost:9000/exec" \
  --data-urlencode "query=SELECT * FROM trades WHERE symbol='BTC-USD'" \
  --data-urlencode "limit=100,200"

# Export query results as CSV
curl -G "http://localhost:9000/exp" \
  --data-urlencode "query=SELECT * FROM trades" \
  -o trades.csv
```

## REST API - CSV Import

Import CSV data directly into QuestDB tables.

```bash
# Import CSV file into a new table (auto-creates table)
curl -F data=@trades.csv "http://localhost:9000/imp?name=trades_imported"

# Import with specific timestamp format and column
curl -F data=@sensor_data.csv \
  "http://localhost:9000/imp?name=sensors&timestamp=reading_time&partitionBy=DAY"

# Import with schema override
curl -F data=@data.csv \
  -F schema='[{"name":"id","type":"LONG"},{"name":"value","type":"DOUBLE"}]' \
  "http://localhost:9000/imp?name=my_table"

# Response:
# {
#   "status": "OK",
#   "location": "trades_imported",
#   "rowsRejected": 0,
#   "rowsImported": 1000000,
#   "header": true,
#   "columns": [...]
# }
```

## PostgreSQL Wire Protocol (PGwire)

Connect using any PostgreSQL client library on port 8812 (default credentials: admin/quest).

```python
# Python with psycopg2
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=8812,
    user="admin",
    password="quest",
    database="qdb"
)

cursor = conn.cursor()

# Create WAL-enabled table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS metrics (
        ts TIMESTAMP,
        host SYMBOL,
        cpu_usage DOUBLE,
        mem_usage DOUBLE
    ) TIMESTAMP(ts) PARTITION BY DAY WAL
    DEDUP UPSERT KEYS(ts, host)
""")

# Insert with prepared statement
cursor.execute(
    "INSERT INTO metrics VALUES ($1, $2, $3, $4)",
    ("2024-01-15T10:30:00Z", "server1", 45.5, 78.2)
)

# Query with time-series functions
cursor.execute("""
    SELECT
        host,
        avg(cpu_usage) as avg_cpu,
        max(mem_usage) as peak_mem
    FROM metrics
    WHERE ts > dateadd('h', -24, now())
    SAMPLE BY 1h
    ALIGN TO CALENDAR
""")

for row in cursor.fetchall():
    print(row)

conn.close()
```

## InfluxDB Line Protocol (ILP) - Raw Protocol

Send data directly using InfluxDB Line Protocol format over TCP.

```bash
# Line protocol format: measurement,tag1=val1,tag2=val2 field1=value1,field2=value2 timestamp_ns

# Send via netcat (timestamp in nanoseconds)
echo "trades,symbol=ETH-USD,side=buy price=2615.54,amount=0.5 1705312200000000000" | nc localhost 9009

# Multiple lines
cat << EOF | nc localhost 9009
sensor_data,device=sensor_001,location=building_a temperature=23.5,humidity=65.2
sensor_data,device=sensor_002,location=building_a temperature=24.1,humidity=62.8
sensor_data,device=sensor_001,location=building_b temperature=22.0,humidity=70.1
EOF

# Using curl with HTTP ILP endpoint (QuestDB 7.0+)
curl -X POST "http://localhost:9000/write" \
  -d "trades,symbol=BTC-USD,side=sell price=39269.98,amount=0.001"
```

## Configuration Properties

Key server configuration properties set via environment variables or server.conf file.

```properties
# server.conf - Key configuration options

# HTTP server
http.bind.to=0.0.0.0:9000
http.min.net.bind.to=0.0.0.0:9003
http.connection.timeout=300000

# PostgreSQL wire protocol
pg.net.bind.to=0.0.0.0:8812
pg.connection.pool.capacity=64
pg.password=quest
pg.user=admin

# InfluxDB Line Protocol (TCP)
line.tcp.net.bind.to=0.0.0.0:9009
line.tcp.auth.db.path=/path/to/auth.txt
line.tcp.default.partition.by=DAY

# Cairo storage engine
cairo.root=/var/lib/questdb/db
cairo.commit.mode=nosync
cairo.max.file.name.length=127
cairo.sql.jit.mode=on

# WAL (Write-Ahead Log)
cairo.wal.enabled.default=true
cairo.wal.purge.interval=30000

# Query execution
cairo.sql.parallel.filter.enabled=true
cairo.sql.parallel.groupby.enabled=true
cairo.page.frame.shard.count=4

# Shared workers
shared.worker.count=4
shared.worker.affinity=1,2,3,4
```

## Summary

QuestDB serves as a high-performance time-series database optimized for real-time analytics, financial market data processing, IoT sensor data collection, and observability workloads. The Java Sender client provides the primary ingestion API with support for both HTTP (recommended for stronger transactional guarantees) and TCP transports, automatic batching, retry logic, and TLS encryption. For querying, applications can use the REST API for ad-hoc queries, PostgreSQL wire protocol for compatibility with existing tools and ORMs, or embed the engine directly for single-process deployments.

Integration patterns typically involve streaming data via the Sender client or ILP protocol, querying with time-series SQL extensions (SAMPLE BY for aggregation, LATEST ON for current state, ASOF JOIN for point-in-time analysis), and visualizing results through Grafana using the native QuestDB plugin. The WAL (Write-Ahead Log) mode enables concurrent writes with deduplication support, making QuestDB suitable for high-throughput scenarios where data may arrive out-of-order or require exactly-once semantics. Production deployments should enable authentication, configure appropriate partition strategies based on query patterns, and tune worker thread counts for the target hardware.