Analytics

What Analytics does

Analytics (binary querypilot-analytics) is QueryPilot's analytics server. It embeds DuckDB, exposes it over the PostgreSQL wire protocol, and keeps it continuously loaded with your operational data via CDC replication from an upstream PostgreSQL or MySQL database: a snapshot of the configured tables first, then a live change stream. Paired with the Router, this is the HTAP shape: analytical reads run on DuckDB while writes and point lookups stay on the source database. Typical uses are dashboards, reporting endpoints, and aggregate-heavy queries that would otherwise scan the primary.

Configuration keys and defaults are in the Analytics configuration reference.

Setup

Analytics loads querypilot-analytics.toml from --config / -c, the QUERYPILOT_ANALYTICS_CONFIG environment variable, or the current directory. A minimal replicating setup from PostgreSQL:

[server]
bind = "127.0.0.1:5433"        # PostgreSQL wire protocol
api_bind = "127.0.0.1:8080"    # HTTP management API

[duckdb]
database_path = "/var/lib/sqp-duckdb/analytics.duckdb"
memory_limit = "8GB"
threads = 4

[replicator]
enabled = true
source_type = "postgresql"
source_url = "postgres://replicator:<password>@pg-host:5432/mydb"
pg_publication = "sqp_publication"
pg_slot_name = "querypilot_app_slot"
tables = ["users", "orders", "products"]
snapshot_on_startup = true

[metrics]
enabled = true

Upstream requirements for PostgreSQL: wal_level = logical, sufficient max_replication_slots and max_wal_senders, and a user with replication privileges. The publication (pg_publication, default sqp_publication) is created automatically if missing.

Three startup behaviors are worth internalizing:

• A missing config file is not an error; the server boots on full defaults. Always confirm with querypilot-analytics --check -c <path>.
• Unknown keys are ignored outside [replicator], so typos in [server] or [duckdb] vanish silently. [replicator] rejects unknown keys.
• Non-loopback binds are refused for server.bind and server.api_bind unless you pass --unsafe-bind-all (or set QUERYPILOT_ANALYTICS_UNSAFE_BIND_ALL=true; see environment variables). This guard exists because the wire frontend performs no password verification: any client that can reach the port can query the data. Keep both binds on loopback or a private network and let the Router, which does authenticate clients, be the front door. The guard does not cover metrics.bind.

Validate and start:

querypilot-analytics --check -c querypilot-analytics.toml
querypilot-analytics -c querypilot-analytics.toml

Snapshotting

With snapshot_on_startup = true (default), a fresh server first copies the configured tables in full, then switches to streaming. Snapshots write into staging tables and swap them in atomically, so a partially loaded table is never visible; staging tables orphaned by a crash are reaped at the next boot, and a detected half-completed swap refuses to boot rather than serve inconsistent data (both behaviors have config escape hatches).

Per-table snapshot progress is checkpointed inside DuckDB in the _sqp_snapshot_state table (status, position, rows done, last primary key per table), so an interrupted snapshot resumes from the last committed batch instead of restarting the table.

For PostgreSQL sources, snapshot_mode picks the read path:

• auto (default): fresh snapshots use CTID-partitioned parallel COPY BINARY, with ctid_workers_per_table workers per table (default 4) and max_parallel_snapshot tables at a time (default 2). This is the fastest path but not resumable mid-table; interrupted snapshots resume on the paginated path.
• paginated: keyset-paginated SELECTs in snapshot_batch_size batches (default 100000 rows), checkpointing _sqp_snapshot_state after each batch. Fully crash-resumable at some cost in throughput.

Peak connection usage against the source during snapshots is max_parallel_snapshot * (ctid_workers_per_table + 1); size the source's max_connections and the replication user's limits for it.

MySQL sources always snapshot via paginated SELECTs under START TRANSACTION WITH CONSISTENT SNAPSHOT, recording the GTID/binlog position the stream will start from.

The write side is controlled by snapshot_strategy:

• appender (default): rows stream straight into DuckDB through dedicated snapshot writer threads (snapshot_writer_count, default 2; the second writer roughly doubles ingest throughput, with little gain beyond it).
• parquet / parquet_streaming (advanced): readers spool the table to Parquet files on scratch disk, then DuckDB bulk-ingests them. Materially faster on storage with slow fsync, at the cost of transient scratch usage around 2x the source table size (parquet_streaming bounds peak scratch to a small constant by rotating files and ingesting into a staging table). The pipeline is defensive by default: a pre-flight check refuses to start without enough scratch space (parquet_min_scratch_gib, 0 auto-computes 2x the source relation size), the snapshot aborts if free space falls below a safety margin mid-run, and ingested row counts are verified against read counts (parquet_strict_row_reconciliation). Reader and writer counts auto-size from the vCPU count when left at 0; if you tune them, do not set writers above the vCPU count.

Long snapshots log progress lines (rows done, throughput, ETA) every 10 seconds by default; GET /api/status and GET /api/snapshot/:table expose the same data over HTTP.

CDC replication

After the snapshot, the replicator streams changes and applies them to DuckDB. INSERTs are buffered and flushed in batches (bounded by both a row count and a time limit); UPDATEs and DELETEs flush the buffer first, then apply, so ordering is preserved. Each flush commits data together with the replication position in one DuckDB transaction, so a crash at any point resumes exactly where the committed state left off.

The stream position (LSN for PostgreSQL, GTID set or binlog file/position for MySQL) is checkpointed inside DuckDB in the single-row _sqp_checkpoint table: persisted on every batch flush, periodically during streaming, and on graceful shutdown. On startup, a present checkpoint means resume streaming from there; an absent one triggers a full snapshot.

PostgreSQL (GA path)

The replicator uses logical replication: it creates the publication if missing and manages a replication slot (pg_slot_name; auto-generated when unset). The slot name is stable across restarts: on resume the existing slot is reused, and a fresh start (no checkpoint) drops and recreates it, so crashes do not leak an ever-growing family of orphaned slots.

Analytics does not emit heartbeat traffic, so on a near-idle source database the slot's confirmed position advances slowly; budget WAL retention accordingly.

MySQL (preview)

Streaming uses the binlog protocol with GTID mode recommended (use_gtid = true, the default). server_id must be unique within the source's replication topology, and gtid_set optionally pins an explicit starting point instead of the current position at first connect. Set source_type = "mysql" and a mysql:// source_url.

Schema changes and type mapping

DDL support on the stream is limited to ALTER TABLE ... ADD COLUMN; column constraints (NOT NULL, DEFAULT) are dropped in translation because DuckDB does not support them in ADD COLUMN, and a warning is logged. DROP/MODIFY/RENAME COLUMN are not replicated; plan such migrations as re-snapshot events (remove and re-add the table via the API, or restart with a clean database file).

Type mapping is automatic and mostly faithful. The edges worth knowing: unsigned MySQL integers promote to the next wider signed DuckDB type (BIGINT UNSIGNED becomes HUGEINT), MySQL TIME maps to INTERVAL, JSON/ENUM/SET are stored as text, and on the way out to PostgreSQL clients HUGEINT, UBIGINT, and DECIMAL are rendered as text because the wire protocol has no native 128-bit types.

Querying it

The PostgreSQL frontend supports simple and extended (prepared-statement) protocols, with per-connection statement caching, and has been exercised with psql, psycopg2, asyncpg, JDBC, node-postgres, lib/pq, and tokio-postgres. There is no TLS on this listener; see Security.

psql -h 127.0.0.1 -p 5433 -c "SELECT count(*) FROM orders"

To serve it through the Router, add Analytics as a backend in a pool with a protocol override, so even MySQL clients can query it transparently:

[pools.analytics]
protocol = "postgres"          # the pool speaks PG wire even if clients speak MySQL
backends = [{ host = "127.0.0.1", port = 5433 }]

[[routing.pattern_rules]]
name = "reports_to_analytics"
type = "regex"
pattern = "(?i)FROM analytics_"
pool = "analytics"

Where the analytics SQL dialect differs from the source dialect, use the Router's translation rules to rewrite queries on the way in, for example MySQL JSON_OVERLAPS(col, '[1,2]') into DuckDB list_has_any(...). See the Router guide and the HTAP analytics tutorial. Leave raw_passthrough off on any pool fronting Analytics.

Internally, DuckDB writes are single-writer: one dedicated worker thread owns the read-write connection, while read-only statements (SELECT, SHOW, EXPLAIN) outside transactions fan out to a pool of read-only connections (read_pool_size, default 4). Statements inside an explicit transaction always go to the writer so they see uncommitted state. Client queries are bounded by query_timeout_secs (effective default 30 s); snapshot ingest gets its own much larger budget (snapshot_ingest_timeout_secs, default 1 h).

HTTP management API

Served on api_bind. The high-traffic endpoints:

POST /api/tables is the way to grow the replicated set without a restart: it registers the table, snapshots it while the change stream keeps running, and returns when the snapshot completes. The full surface, including config status and reload, is in the analytics API reference.

Tuning

The [duckdb] section sets the engine's resource envelope: memory_limit (e.g. "8GB"), threads, and timezone for TIMESTAMPTZ operations, which must match the source PostgreSQL server's timezone for consistent date extraction. Size read_pool_size against concurrent analytical clients; every read connection shares the same memory_limit.

Observability

With [metrics] enabled = true, Prometheus series are served under sqp_duckdb_*: query counts, latency, and errors by statement type (sqp_duckdb_queries_total, sqp_duckdb_query_duration_seconds), connection gauges, database size and memory (sqp_duckdb_database_size_bytes, sqp_duckdb_memory_bytes), reader-pool activity, and replication health (sqp_duckdb_replication_lag_seconds, sqp_duckdb_tables_replicated). Alert on sqp_duckdb_replication_lag_seconds growing without bound; GET /api/status is the quickest human-readable view of snapshot progress and stream position. See the metrics reference.