Readyset Docs

Complete reference for querypilot-analytics.toml, the configuration file for the QueryPilot Analytics.

Overview

Analytics (binary querypilot-analytics) is the DuckDB-backed analytics component. It is configured by querypilot-analytics.toml, located from, in order of precedence: the --config / -c flag, the QUERYPILOT_ANALYTICS_CONFIG environment variable, or querypilot-analytics.toml in the current working directory.

Timeouts in this file are plain integer seconds (query_timeout_secs = 30), not duration strings. Run querypilot-analytics --check -c <path> to validate a file without serving traffic.

Unlike the Router, Analytics ignores unknown config keys everywhere except inside [replicator], and it boots on full defaults if the file at the config path does not exist (no error, no warning). A misspelled key or a mistyped --config path therefore looks like a healthy server with none of your settings applied. Double-check key spelling and paths, and use --check to confirm the file you intend is the file being read.

MySQL CDC replication is in preview. The MySQL-specific [replicator] keys below are functional, but PostgreSQL is the supported GA v1 path.

[server]

Listener addresses. By default both binds refuse non-loopback addresses; to bind 0.0.0.0 you must pass --unsafe-bind-all or set QUERYPILOT_ANALYTICS_UNSAFE_BIND_ALL (see environment variables).

`bind`

PostgreSQL wire-protocol listener. This is where the Router's analytics pool, or any PostgreSQL client, connects.

Type: socket address Default: "127.0.0.1:5433"

`api_bind`

HTTP management API listener. See the Analytics API reference. The shipped deployment configs move this to port 18080.

Type: socket address Default: "127.0.0.1:8080"

[server]
bind = "127.0.0.1:5433"
api_bind = "127.0.0.1:8080"

[duckdb]

DuckDB engine settings.

`database_path`

DuckDB database file, or ":memory:" for an in-memory database. Use a persistent path in production so replicated data survives restarts. In the shipped container image, /var/lib/sqp-duckdb is the only directory prepared and writable by the runtime user; keep in-container database paths under it (or mount a volume there).

Type: string Default: ":memory:"

`memory_limit`

DuckDB memory cap, for example "4GB". Unset lets DuckDB decide. Set this in memory-constrained deployments; an unbounded DuckDB can drive the process over container limits during large queries or replay.

Type: string Default: unset

`threads`

DuckDB thread count. Unset lets DuckDB decide.

Type: integer Default: unset

`default_schema`

Default schema for unqualified table names; sets DuckDB search_path when set.

Type: string Default: unset

`read_pool_size`

Number of read-only connections for parallel client query execution. 0 disables the read pool, sending all queries through the single writer. CDC writes always use the main writer.

Type: integer Default: 4

`snapshot_writer_count`

Dedicated snapshot-ingest writer threads. During multi-table snapshots, tables are assigned to writers round-robin, enabling concurrent WAL writes. 0 disables dedicated writers and routes snapshot writes through the main worker.

Type: integer Default: 2

`timezone`

Session timezone for TIMESTAMPTZ operations, for example "Europe/Berlin". Should match the source PostgreSQL server's timezone for consistent date extraction. Requires DuckDB's ICU extension, which is installed automatically at startup. Unset uses DuckDB's default (UTC).

Type: string Default: unset

`query_timeout_secs`

Per-query timeout in seconds for client queries on the PostgreSQL wire endpoint. The snapshot bulk-ingest path uses snapshot_ingest_timeout_secs instead, so this can stay at an interactive value.

Type: integer (seconds) Default: unset (effective 30)

`snapshot_ingest_timeout_secs`

Timeout in seconds for a single snapshot bulk-ingest operation. Large tables on slow storage can legitimately run for tens of minutes.

Type: integer (seconds) Default: unset (effective 3600)

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

[logging]

`level`

Log filter. Unlike the Router's closed level list, this is a freeform tracing directive: "info" and "sqp_duckdb_core=debug,info" are both valid. Must parse as a tracing filter at boot. This is the only key reloadable at runtime via POST /api/config/reload.

Type: string (tracing filter) Default: "info"

`format`

Human-readable or structured JSON output.

Type: string, one of "pretty", "json" Default: "pretty"

[metrics]

`enabled`

Serve Prometheus metrics. See the metrics reference.

Type: bool Default: false

`bind`

Metrics listen address. Note that this bind is not covered by the --unsafe-bind-all loopback guard: a config can put metrics on 0.0.0.0 without the flag, so treat this address with the same care as the [server] binds.

Type: socket address Default: "127.0.0.1:9090"

[replicator]

CDC replication from an upstream MySQL or PostgreSQL database into DuckDB. This is the one Analytics section with strict key checking: unknown keys here fail at parse time. See Analytics and the HTAP tutorial.

`enabled`

Turn replication on.

Type: bool Default: false

`source_type`

Upstream database flavor.

Type: string, one of "mysql", "postgresql" Default: "mysql"

`source_url`

Connection URL for the source database, for example postgres://<user>:<password>@<host>:5432/<db> or mysql://<user>:<password>@<host>:3306/<db>.

Type: string Default: unset

`server_id`

MySQL only. Replication server ID presented to MySQL; must be unique in the replication topology.

Type: integer Default: 4294967240

`use_gtid`

MySQL only. Use GTID-based replication instead of binlog file and position. Recommended for production because it handles failover better.

Type: bool Default: true

`gtid_set`

MySQL only. Explicit GTID starting point, in the form uuid:interval[,uuid:interval,...]. Unset starts from the current server position.

Type: string Default: unset

`pg_publication`

PostgreSQL only. Publication name for logical replication; created automatically if it does not exist.

Type: string Default: "sqp_publication"

`pg_slot_name`

PostgreSQL only. Replication slot name. Unset auto-generates a name of the form sqp_slot_{random}.

Type: string Default: unset

`tables`

Tables to replicate; the database comes from source_url. An empty list processes all events, which is intended for testing.

Type: array of strings Default: []

`snapshot_on_startup`

Snapshot existing data before streaming changes.

Type: bool Default: true

`snapshot_batch_size`

Rows per paginated SELECT batch. After each batch, the last primary key is checkpointed, so this also controls how much work is lost on a crash. Larger batches mean fewer round-trips and higher throughput; each batch is held in memory before insert.

Type: integer Default: 100000

`max_parallel_snapshot`

Tables snapshotted concurrently. Each table gets its own source connection and transaction.

Type: integer Default: 2

`ctid_workers_per_table`

PostgreSQL only. CTID COPY workers per table for parallel snapshots. Peak PostgreSQL connections are max_parallel_snapshot * (ctid_workers_per_table + 1); with defaults that is 10 connections. MySQL snapshots ignore this setting.

Type: integer Default: 4

`snapshot_mode`

"auto" uses CTID-parallel COPY BINARY for fresh PostgreSQL snapshots and falls back to paginated for resume after a crash. "paginated" always uses the crash-resumable paginated path. MySQL always uses paginated mode regardless of this setting.

Type: string, one of "auto", "paginated" Default: "auto"

`snapshot_strategy`

Bulk-load strategy for fresh PostgreSQL CTID-parallel snapshots. "appender" uses the per-row DuckDB Appender API. "parquet" spools data to Parquet files on scratch disk (transiently about twice the source table size), then bulk-ingests them; it is faster on slow-fsync storage. "parquet_streaming" is a streaming variant that rotates bounded files into a staging table and swaps it in atomically, keeping peak scratch usage a small constant. See Advanced snapshot tuning.

Type: string, one of "appender", "parquet", "parquet_streaming" Default: "appender"

`parquet_readers`

Parquet strategies only. Parallel PostgreSQL COPY BINARY readers. 0 auto-detects as min(16, max(2, vcpu * 4)); readers are network-bound and benefit from oversubscribing vCPU.

Type: integer Default: 0 (auto)

`parquet_writers`

Parquet strategies only. Parquet writer threads. 0 auto-detects as max(1, vcpu). Writers are CPU and disk-sync bound; do not set this above the vCPU count.

Type: integer Default: 0 (auto)

`parquet_compression`

Parquet codec. Snappy is the calibrated default; zstd trades CPU for tighter compression; "none" trades disk for CPU.

Type: string, one of "snappy", "zstd", "none" Default: "snappy"

`parquet_min_scratch_gib`

Scratch-disk pre-flight floor in GiB for parquet snapshots. 0 auto-computes the floor as twice the total source relation size and checks free space at startup, refusing to start below it. A positive value sets an explicit floor without the source round-trip. -1 skips the pre-flight entirely (a warning is logged, and running out of disk then surfaces as a runtime failure mid-snapshot).

Type: integer Default: 0 (auto)

`snapshot_progress_log_interval_ms`

Interval in milliseconds between snapshot-progress log lines and Prometheus gauge updates (rows done, estimated total, throughput, ETA, phase). 0 disables progress logging.

Type: integer (milliseconds) Default: 10000

[replicator]
enabled = true
source_type = "postgresql"
source_url = "postgres://<user>:<password>@<host>:5432/<db>"
pg_publication = "sqp_publication"
tables = ["users", "orders", "products"]
snapshot_on_startup = true
max_parallel_snapshot = 2

Advanced snapshot tuning

These keys tune the parquet_streaming snapshot pipeline. appender is the default and baseline strategy, unchanged from prior releases; parquet_streaming is the newer opt-in path for bounded scratch usage on large tables. The defaults below are bench-validated; leave them alone unless you are diagnosing a specific bottleneck. Zero is not a valid value for the byte, row, and file budgets; the server rejects it at startup.

`parquet_writer_file_target_bytes`

Writers rotate the current Parquet file when its size exceeds this many bytes. Smaller values mean more files and more insert overhead; larger values mean bigger peak per-file scratch.

Type: integer (bytes), greater than 0 Default: 524288000 (500 MiB)

`parquet_writer_file_target_rows`

Writers rotate the current Parquet file when its row count exceeds this threshold, whichever fires first with parquet_writer_file_target_bytes.

Type: integer (rows), greater than 0 Default: 1000000

`parquet_ingest_batch_bytes`

The ingest task batches up to this many bytes of Parquet files into a single insert. Bounds per-insert WAL and DuckDB plan memory.

Type: integer (bytes), greater than 0 Default: 2147483648 (2 GiB)

`parquet_ingest_batch_files`

The ingest task batches up to this many Parquet files per insert, whichever fires first with parquet_ingest_batch_bytes.

Type: integer, greater than 0 Default: 4

`parquet_channel_max_bytes`

Total in-flight bytes between the reader and writer legs. Bounds memory regardless of reader count or batch size; a single batch exceeding the cap fails fast with an error naming this key.

Type: integer (bytes), greater than 0 Default: 2147483648 (2 GiB)

`parquet_scratch_safety_margin_gib`

The runtime scratch monitor aborts the snapshot when free scratch space drops below this many GiB, preventing writers from corrupting an in-flight Parquet file on a full disk.

Type: integer (GiB) Default: 1

`parquet_strict_row_reconciliation`

When true, the pre-swap reconciliation gate refuses to swap staging into the target if read, written, and ingested row counters disagree. Set false only for disaster recovery where partial data beats no data; a bypass increments sqp_duckdb_parquet_snapshot_reconciliation_bypass_total so you can alert on it.

Type: bool Default: true

`parquet_reltuples_warn_pct`

Warn when the PostgreSQL planner's row estimate differs from the actual snapshot row count by more than this percentage. A sanity check for stale ANALYZE statistics; never a hard failure.

Type: integer (percent) Default: 10

`parquet_inprocess_staging_drop`

When true, the orchestrator drops the staging table in-process on any snapshot failure so orphans do not accumulate across retries. Set false to preserve staging tables for post-mortem inspection.

Type: bool Default: true

`parquet_reap_orphan_staging`

When true, a startup reaper drops orphan staging tables left by prior hard crashes (SIGKILL, OOM, pod eviction). Set false to disable the reaper; orphans then accumulate until cleaned manually.

Type: bool Default: true

`parquet_halt_on_half_swap`

When true, refuse to start the replicator if the startup reaper detects a half-completed swap (a staging table whose target is missing); an operator must rename or drop the staging table before the server boots again. Set false to acknowledge the risk and boot anyway; the half-swap rows are not auto-recovered.

Type: bool Default: true

Analytics Configuration

On this page