Readyset Docs

Replicate PostgreSQL tables into Analytics with CDC, then route analytical queries to it through the Router while writes stay on PostgreSQL.

Upcoming feature. This tutorial exercises Analytics, which is not part of QueryPilot GA v1. It is documented here ahead of availability; details may change before release.

This tutorial builds the HTAP shape: Analytics (binary querypilot-analytics) keeps a continuously updated DuckDB copy of selected PostgreSQL tables via CDC replication, and the Router (binary querypilot) routes analytical queries to it while everything else stays on PostgreSQL. Applications connect to one endpoint and never know two engines are involved.

You need:

Docker with Compose, and access to the QueryPilot images (see Getting Started).
A PostgreSQL server you can reach from the Docker host, with psql access.
Superuser or equivalent rights on that server to enable logical replication.

Both containers run with host networking, so localhost in the configs means the Docker host. The walkthrough assumes PostgreSQL on localhost:5432 with user app_user, password app_pass, database appdb; substitute your own values consistently.

Prepare PostgreSQL for CDC.

Analytics uses logical replication. The source server needs:

-- postgresql.conf (restart required if you change wal_level)
-- wal_level = logical
-- max_replication_slots >= 1
-- max_wal_senders >= 1
SHOW wal_level;

The replication user needs the REPLICATION attribute, and pg_hba.conf must permit replication connections from the Docker host. Also set max_slot_wal_keep_size (tens of GB is a reasonable production bound) so a stopped Analytics cannot pin unbounded WAL on the source; see Operations.

Create a demo table and fill it server-side:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id INT NOT NULL,
    category TEXT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (id, customer_id, category, total)
SELECT g,
       (g % 1000) + 1,
       (ARRAY['books','games','tools','garden'])[1 + g % 4],
       round((random() * 500)::numeric, 2)
FROM generate_series(1, 1000000) g;

Create the project layout.

mkdir -p htap/configs && cd htap

Write docker-compose.yml. This is the shape QueryPilot ships in its deployment tooling: Analytics starts first, the Router waits until it reports healthy.

services:
  querypilot-analytics:
    image: ${QUERYPILOT_ANALYTICS_IMAGE:-readysettech/querypilot-analytics:latest}
    container_name: querypilot-analytics
    network_mode: host
    volumes:
      - analytics-data:/var/lib/sqp-duckdb
      - ./configs/querypilot-analytics.toml:/etc/sqp/querypilot-analytics.toml:ro
    environment:
      - QUERYPILOT_ANALYTICS_CONFIG=/etc/sqp/querypilot-analytics.toml
      - QUERYPILOT_ANALYTICS_UNSAFE_BIND_ALL=true
    healthcheck:
      test: ["CMD", "curl", "-sf", "http://localhost:18080/api/health"]
      interval: 15s
      timeout: 5s
      start_period: 600s
      retries: 3

  querypilot:
    image: ${QUERYPILOT_IMAGE:-readysettech/querypilot:latest}
    container_name: querypilot
    network_mode: host
    volumes:
      - ./configs/querypilot-router.toml:/etc/sqp/querypilot.toml:ro
    environment:
      - QUERYPILOT_CONFIG=/etc/sqp/querypilot.toml
    depends_on:
      querypilot-analytics:
        condition: service_healthy

volumes:
  analytics-data:

The 600 second health-check start period accommodates the initial snapshot, which can take tens of minutes on large tables. QUERYPILOT_ANALYTICS_UNSAFE_BIND_ALL=true permits the non-loopback binds the container needs; the Analytics wire listener performs no password verification, so keep these ports off untrusted networks and let the Router be the front door (see Security).

Configure Analytics.

Write configs/querypilot-analytics.toml:

[server]
bind = "0.0.0.0:15432"      # PostgreSQL wire protocol
api_bind = "0.0.0.0:18080"  # HTTP management API

[duckdb]
database_path = "/var/lib/sqp-duckdb/analytics.duckdb"
memory_limit = "8GB"
threads = 8
# Set to your PostgreSQL server's timezone for correct TIMESTAMPTZ::date casts.
# timezone = "UTC"

[replicator]
enabled = true
source_type = "postgresql"
source_url = "postgres://app_user:app_pass@127.0.0.1:5432/appdb"
pg_publication = "querypilot_htap_pub"
pg_slot_name = "querypilot_htap_slot"
tables = ["orders"]
snapshot_on_startup = true

[metrics]
enabled = true
bind = "0.0.0.0:19090"

tables lists exactly what gets replicated; you can grow the set later without a restart via POST /api/tables (see the analytics API reference). The publication is created automatically if missing, and the slot name is stable across restarts. On first start Analytics snapshots the listed tables in full, then streams changes. Full key detail is in the analytics configuration reference.

Configure the Router with two pools and a routing rule.

Write configs/querypilot-router.toml:

[general]
pool_mode = "transaction"

[[listeners]]
protocol = "postgres"
bind = "0.0.0.0:5433"

# Analytics pool: Analytics is just a PostgreSQL backend to the Router
[pools.analytics]
protocol = "postgres"
role = "replica"
username = "app_user"
password = "<your-password>"
database = "appdb"
min_connections = 1
max_connections = 50
backends = [{ host = "127.0.0.1", port = 15432 }]

# Primary pool: your PostgreSQL
[pools.postgres]
role = "primary"
username = "app_user"
password = "<your-password>"
database = "appdb"
min_connections = 1
max_connections = 20
backends = [{ host = "127.0.0.1", port = 5432 }]

[routing]
default_pool = "postgres"

# Send aggregations to Analytics
[[routing.pattern_rules]]
name = "route_aggregations"
type = "regex"
pattern = "(?i)(\\bSUM\\s*\\(|\\bCOUNT\\s*\\(|\\bAVG\\s*\\(|\\bMIN\\s*\\(|\\bMAX\\s*\\()"
pool = "analytics"
priority = 10

[[routing.pattern_rules]]
name = "route_group_by"
type = "regex"
pattern = "(?i)\\bGROUP\\s+BY\\b"
pool = "analytics"
priority = 10

[metrics]
enabled = true
bind = "0.0.0.0:9090"

Everything not matched by a rule falls through to default_pool, so writes and point lookups run on PostgreSQL. The two regex rules catch aggregate functions and GROUP BY. Pattern rules with type = "template" match one exact query shape instead of a keyword class; see the Router guide for the full decision order. An alternative to explicit rules is read_write_split, which sends every read to the analytics pool; the Deployment templates ship that shape.

Because the listener binds 0.0.0.0, the pool credentials double as client-auth credentials: clients authenticate to the Router with app_user/app_pass.

Start the stack and watch the snapshot.

docker compose up -d

Track snapshot progress through the management API:

curl -s http://localhost:18080/api/status
curl -s http://localhost:18080/api/tables
curl -s http://localhost:18080/api/snapshot/orders   # 404 once the snapshot is done

/api/status reports the replication phase (snapshotting, then streaming), the source position, and per-table snapshot progress. The Router container starts once Analytics reports healthy:

docker compose ps

Verify routing on both paths.

Ask the Router where queries would go, without sending traffic. The admin API listens on the metrics port + 1 (see the admin API reference):

curl -s "http://localhost:9091/api/routing/test?sql=SELECT%20category,%20SUM(total)%20FROM%20orders%20GROUP%20BY%20category"
# ... "target_pool": "analytics" ...

curl -s "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20orders%20WHERE%20id%20=%2042"
# ... "target_pool": "postgres" ...

Then run both for real through one connection:

psql -h localhost -p 5433 -U app_user -d appdb

-- Served by Analytics (DuckDB)
SELECT category, COUNT(*), SUM(total) FROM orders GROUP BY category ORDER BY 2 DESC;

-- Served by PostgreSQL
SELECT * FROM orders WHERE id = 42;

Under traffic, sqp_routing_decisions_total on the Router's metrics port splits by decision_type and target_pool:

curl -s http://localhost:9090/metrics | grep sqp_routing_decisions_total

Verify CDC freshness.

Write a row directly to PostgreSQL, then read it back through the analytics path:

-- on PostgreSQL (port 5432)
INSERT INTO orders (id, customer_id, category, total) VALUES (1000001, 7, 'books', 19.99);

-- through the Router (port 5433); COUNT(*) matches the rule, so this runs on DuckDB
SELECT COUNT(*) FROM orders;

The count reflects the new row once the change stream applies it, typically within seconds. Replication lag is exported as a metric on the Analytics metrics port:

curl -s http://localhost:19090/metrics | grep sqp_duckdb_replication_lag_seconds
curl -s http://localhost:18080/api/status   # phase should be "streaming"

Alert on this metric in production; the metrics reference lists the rest of the sqp_duckdb_* series.

Clean up.

docker compose down       # keep the DuckDB volume
docker compose down -v    # wipe replicated data too

If you tear the stack down for good, drop the replication slot on PostgreSQL so it stops retaining WAL:

SELECT pg_drop_replication_slot('querypilot_htap_slot');

Where to go next

Analytics guide for snapshot strategies, checkpointing, schema-change limits, and other source types.
Performance for measured analytics speedups and snapshot throughput.
Deployment for the Kubernetes and Helm versions of this shape.
Read/write splitting tutorial for routing by statement class instead of by pattern.

Tutorial: HTAP Analytics

Where to go next

On this page