QuestDB Platform¶

Tags intermediate guide questdb sql-platform self-hosted timeseries

QuestDB is a high-performance open-source time-series database optimized for fast ingestion and SQL queries. It supports the PostgreSQL wire protocol (port 8812) for query execution and a REST API (port 9000) for efficient bulk data import. BenchBox connects via psycopg2 over the PG wire protocol and uses the REST API /imp endpoint for high-throughput CSV ingestion. SQL translation targets the postgres dialect via SQLGlot.

QuestDB is designed for time-series and event-driven workloads, with features like designated timestamps, automatic partitioning by time, and the SYMBOL type for low-cardinality string columns. It delivers competitive ingestion throughput against InfluxDB, TimescaleDB, and ClickHouse on time-series workloads.

Features¶

PostgreSQL wire protocol - Standard PG wire protocol via psycopg2 (port 8812)
REST API CSV import - High-throughput bulk data loading via /imp endpoint (port 9000)
Full TPC-H support - All 22 queries with row count validation
PostgreSQL dialect - SQL translation via SQLGlot postgres dialect
Time-series optimized - Designated timestamps, partitioning by time, SYMBOL type
Autocommit mode - PG wire protocol operates in autocommit mode (required by QuestDB)
TPC file normalization - Automatic handling of pipe-delimited .tbl files with trailing delimiter removal
Fallback loading - REST API import with automatic fallback to COPY via PG wire protocol
TLS support - Optional HTTPS for REST API endpoints via --questdb-use-tls

Quick Start¶

# Install psycopg2 dependency
uv add psycopg2-binary

# Or install via the QuestDB extra
uv add benchbox --extra questdb

# Configure connection (QuestDB must be running)
export QUESTDB_HOST=localhost

# Run TPC-H benchmark
benchbox run --platform questdb --benchmark tpch --scale 0.01

Docker Quick Start¶

# Start QuestDB with Docker (all ports exposed)
docker run -p 9000:9000 -p 8812:8812 -p 9009:9009 -p 9003:9003 \
    questdb/questdb:latest

# Verify PG wire protocol connectivity
psql -h 127.0.0.1 -p 8812 -U admin -d qdb -c "SELECT 1"

# Verify REST API
curl http://localhost:9000/exec?query=SELECT%201

# Run benchmark
benchbox run --platform questdb --benchmark tpch --scale 0.01

QuestDB Ports¶

Port	Protocol	Purpose
9000	HTTP	REST API for CSV import (`/imp`) and queries (`/exec`)
8812	TCP	PostgreSQL wire protocol for SQL queries
9009	TCP	InfluxDB Line Protocol (ILP) for high-throughput ingestion
9003	HTTP	Health check and metrics endpoint

Configuration Options¶

Option	CLI Argument	Environment Variable	Default	Description
`host`	`--questdb-host`	`QUESTDB_HOST`	`localhost`	QuestDB server hostname
`pg_port`	`--questdb-pg-port`	`QUESTDB_PG_PORT`	`8812`	PostgreSQL wire protocol port
`http_port`	`--questdb-http-port`	`QUESTDB_HTTP_PORT`	`9000`	REST API HTTP port
`username`	`--questdb-username`	-	`admin`	QuestDB username
`password`	`--questdb-password`	-	`quest`	QuestDB password
`database`	`--questdb-database`	-	`qdb`	QuestDB database name
`use_tls`	`--questdb-use-tls`	-	`false`	Use HTTPS for REST API endpoints
`connect_timeout`	-	-	`10`	Connection timeout in seconds

Data Loading¶

BenchBox loads data into QuestDB using the REST API /imp endpoint for bulk CSV import. If the REST API is unavailable, the adapter falls back to COPY via the PG wire protocol.

Loading Process¶

Schema creation - Tables are created via PG wire protocol with QuestDB-compatible DDL
Foreign key removal - FK constraints are stripped (QuestDB does not support foreign keys)
DROP TABLE adaptation - IF EXISTS is added to DROP TABLE statements for idempotent schema creation
TPC file normalization - Pipe-delimited .tbl files are streamed through a normalizer that removes trailing delimiters
REST API import - CSV data is uploaded to the /imp endpoint with table name, delimiter, and durability settings
Fallback to COPY - If REST API import fails, data is loaded via COPY FROM STDIN over PG wire protocol

REST API Import (`/imp`)¶

The primary data loading mechanism uses QuestDB’s REST API:

Endpoint: http://<host>:<http_port>/imp
Method: HTTP POST with multipart file upload
Parameters: name (table), delimiter, overwrite, durable
Format: CSV with configurable delimiter (auto-detected for TPC .tbl files)
Throughput: Significantly higher than row-by-row INSERT for bulk datasets

COPY via PG Wire Protocol (Fallback)¶

When the REST API is unavailable, the adapter uses psycopg2’s copy_expert:

COPY "table_name" FROM STDIN WITH (FORMAT csv, DELIMITER '|')

InfluxDB Line Protocol (ILP)¶

QuestDB also supports the InfluxDB Line Protocol on port 9009 for high-throughput time-series ingestion. While BenchBox uses the REST API for benchmark data loading, ILP is available for custom ingestion workflows:

Port: 9009 (TCP)
Protocol: InfluxDB Line Protocol
Throughput: Millions of rows per second for time-series data
Use case: Real-time streaming ingestion

Usage Examples¶

Basic Benchmarks¶

# TPC-H at scale factor 0.01 (quick test)
benchbox run --platform questdb --benchmark tpch --scale 0.01

# TPC-H at scale factor 1
benchbox run --platform questdb --benchmark tpch --scale 1.0

# Run specific queries only
benchbox run --platform questdb --benchmark tpch --queries Q1,Q6,Q17

Environment Variable Configuration¶

export QUESTDB_HOST=questdb.example.com
export QUESTDB_PG_PORT=8812
export QUESTDB_HTTP_PORT=9000

benchbox run --platform questdb --benchmark tpch --scale 1.0

CLI Argument Configuration¶

benchbox run --platform questdb --benchmark tpch --scale 1.0 \
    --questdb-host questdb.example.com \
    --questdb-pg-port 8812 \
    --questdb-http-port 9000 \
    --questdb-username admin \
    --questdb-password quest

TLS-Enabled REST API¶

benchbox run --platform questdb --benchmark tpch --scale 1.0 \
    --questdb-host questdb.example.com \
    --questdb-use-tls

Dry Run (Preview)¶

# Preview execution plan without running
benchbox run --platform questdb --benchmark tpch --scale 1.0 --dry-run ./preview

Architecture¶

Adapter Structure¶

The QuestDB adapter is a single-module adapter:

Module	Class	Responsibility
`questdb.py`	`QuestDBAdapter`	Connection, schema, loading, query execution

Connection Model¶

BenchBox CLI
    |
    v
QuestDBAdapter
    |
    +-- PG Wire Protocol (psycopg2) --> QuestDB (port 8812)
    |       - Schema DDL (CREATE TABLE, DROP TABLE)
    |       - Query execution (SELECT, EXPLAIN)
    |       - COPY FROM STDIN fallback loading
    |       - Autocommit mode required
    |
    +-- REST API (requests) --> QuestDB (port 9000)
            - /imp endpoint for bulk CSV import
            - /exec endpoint for row count verification
            - Optional TLS (HTTPS) support

Platform Information¶

At runtime, BenchBox captures platform metadata:

{
    "platform_type": "questdb",
    "platform_name": "QuestDB",
    "host": "localhost",
    "pg_port": 8812,
    "http_port": 9000,
    "dialect": "postgres",
    "configuration": {
        "database": "qdb"
    },
    "version": "7.x.x"
}

Single Database Model¶

QuestDB uses a single database per instance. The adapter sets skip_database_management = True because:

There is no CREATE DATABASE or DROP DATABASE support
The database parameter (default: qdb) identifies the instance, not a user-created database
check_database_exists() tests connectivity rather than database presence

Time-Series Considerations¶

Designated Timestamps¶

QuestDB tables can have a designated timestamp column that enables time-based partitioning and optimized time-range queries:

CREATE TABLE sensor_data (
    timestamp TIMESTAMP,
    device_id SYMBOL,
    temperature DOUBLE,
    humidity DOUBLE
) TIMESTAMP(timestamp) PARTITION BY DAY;

While TPC-H tables do not use designated timestamps, the adapter supports them for custom benchmarks and time-series workloads.

Partitioning¶

QuestDB supports automatic time-based partitioning:

PARTITION BY NONE - No partitioning (default)
PARTITION BY HOUR - Hourly partitions
PARTITION BY DAY - Daily partitions
PARTITION BY MONTH - Monthly partitions
PARTITION BY YEAR - Yearly partitions

Partitioning is specified at table creation time and cannot be changed after the fact.

SYMBOL Type¶

QuestDB’s SYMBOL type is optimized for low-cardinality string columns (e.g., status codes, country codes, device IDs). SYMBOLs are stored as integers internally with a symbol table for deduplication, providing significant storage and query performance benefits over VARCHAR for repeated string values.

Constraints¶

QuestDB has a minimal constraint model:

No foreign keys - The adapter automatically strips FK constraints from DDL
No traditional primary keys - Uniqueness is not enforced at the database level
No ALTER TABLE ADD CONSTRAINT - Constraints must be defined at table creation time

Troubleshooting¶

Connection Refused¶

Error: Failed to connect to QuestDB

Solutions:

Verify QuestDB is running and accessible on the configured host
Check that port 8812 (PG wire protocol) is open and not blocked by a firewall
For Docker deployments, ensure port mapping is correct (-p 8812:8812)
Test connectivity directly: psql -h <host> -p 8812 -U admin -d qdb -c "SELECT 1"

Missing psycopg2 Dependency¶

Error: Missing dependencies for questdb platform: psycopg2

Solutions:

Install psycopg2: uv add psycopg2-binary
Or install the QuestDB extra: uv add benchbox --extra questdb

REST API Import Failures¶

Warning: REST API import failed, falling back to COPY

Solutions:

Verify the REST API is accessible on the HTTP port (default: 9000)
Check that requests is installed: uv add requests
Test the REST API directly: curl http://<host>:9000/exec?query=SELECT%201
If using TLS, ensure --questdb-use-tls is set and certificates are valid

Schema Creation Failures¶

Error: critical CREATE TABLE statement(s) failed

Solutions:

Check QuestDB server logs for detailed error messages
Verify the QuestDB user has sufficient privileges
Ensure sufficient disk space on the QuestDB server
Review the translated DDL for QuestDB-incompatible syntax (e.g., unsupported column types)

Autocommit Mode Errors¶

QuestDB requires autocommit mode on the PG wire protocol. The adapter sets conn.autocommit = True automatically. If you encounter transaction-related errors:

Ensure you are not wrapping queries in explicit BEGIN/COMMIT blocks
QuestDB does not support multi-statement transactions via PG wire protocol

Slow Data Loading¶

Solutions:

Ensure the REST API port (9000) is accessible for bulk CSV import
If falling back to COPY, check network latency between BenchBox host and QuestDB
For large datasets, consider increasing the QuestDB server’s memory allocation
Check disk I/O on the QuestDB server during loading

QuestDB Platform¶

Features¶

Quick Start¶

Docker Quick Start¶

QuestDB Ports¶

Configuration Options¶

Data Loading¶

Loading Process¶

REST API Import (/imp)¶

COPY via PG Wire Protocol (Fallback)¶

InfluxDB Line Protocol (ILP)¶

Usage Examples¶

Basic Benchmarks¶

Environment Variable Configuration¶

CLI Argument Configuration¶

TLS-Enabled REST API¶

Dry Run (Preview)¶

Architecture¶

Adapter Structure¶

Connection Model¶

Platform Information¶

Single Database Model¶

Time-Series Considerations¶

Designated Timestamps¶

Partitioning¶

SYMBOL Type¶

Constraints¶

Troubleshooting¶

Connection Refused¶

Missing psycopg2 Dependency¶

REST API Import Failures¶

Schema Creation Failures¶

Autocommit Mode Errors¶

Slow Data Loading¶

See Also¶

REST API Import (`/imp`)¶