A self-driving polystore: one PostgreSQL source of truth, many specialized read models — routed, measured, and promoted with receipts.
Every application eventually outgrows one database. Analytics wants columnar storage, full-text search wants BM25 indexes, similarity search wants ANN vector indexes, relationship queries want a graph — and the standard answer is to bolt on more databases, each with its own sync pipeline, consistency bugs, and operational tax. You end up choosing between one database that's mediocre at everything or five databases and a pile of glue code.
You shouldn't have to choose. Postgres stays the single place applications write (single-writer CQRS). Change Data Capture streams every committed write to a set of specialized read models — a columnar DuckDB snapshot for analytics, a BM25 index for search, an HNSW vector index for similarity, a property graph for traversals — each one a disposable, rebuildable projection of the canonical data. A query router classifies each read and sends it to the engine that serves it best, logging every decision. Over time, the system observes its own workload and proposes better routings and new projections — validated by shadow tests and promoted only under a human gate. That's the "self-driving" part: the database tunes its own physical design, and it has to prove every change before it ships.
┌─────────────────────────────────────────────┐
writes │ PostgreSQL 18 (CANONICAL WRITE STORE) │
───────────────────► │ wal_level=logical, 1 publication, 1 slot │
└──────────────┬──────────────────────────────┘
│ logical replication (pgoutput)
▼
┌─────────────────────────────────────────────┐
│ PROJECTION RUNNER │
│ decodes WAL → applies to read models │
│ tracks applied LSN per projection │
└───┬───────────────┬───────────────┬─────────┘
▼ ▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ DuckDB read │ │ AGE graph │ │ pg_search / │
│ model │ │ (in Postgres)│ │ pgvector │
│ (analytics) │ │ │ │ (in Postgres)│
└──────────────┘ └──────────────┘ └──────────────┘
▲ ▲ ▲
└───────────────┴───────────────┘
│
┌──────────────┴──────────────┐
│ ROUTER │
│ parse → classify → dispatch │
│ logs every decision │
└──────────────────────────────┘
Two invariants hold everywhere:
- Never a correctness regression. Writes and anything unroutable go straight to Postgres. Every specialized path falls back to Postgres on any error. A wrong routing decision can only ever cost latency, never a wrong answer.
- Nothing is promoted without proof. Routing changes and new projections must win a shadow test — same query, both backends, identical results required — before a human approves them.
Database projects love a bare "50× faster!" headline. This project forbids itself that. The divergence benchmark reports two separately-labeled numbers and includes a control:
- Engine-win — the same query, same data, on the specialized engine vs. row-store Postgres. This measures the engine.
- Materialization-win — a pre-aggregated read model vs. computing live. This measures the projection design, and it is never presented as an engine speedup.
- The control — a point lookup (
WHERE user_id = ?) that plain Postgres must win. If the control doesn't favor Postgres, the harness is unfair and the results are void.
Measured on this codebase (MacBook Air, arm64; Postgres 18.4 in Docker vs embedded DuckDB 1.5, both stock configs; 25 iterations through the router; result identity asserted before timing). Full methodology and caveats: docs/bench/phase0-results.md.
| case | kind | postgres p50 | duckdb p50 | speedup |
|---|---|---|---|---|
| daily revenue by category (2.27M-row join+agg) | engine-win | 1289 ms | 104 ms | 12.4× |
| cart contents lookup (selective join) | control | 0.9 ms | 2.3 ms | 0.4× — Postgres wins, as it must |
Write→projection freshness, measured live: a committed insert is visible in DuckDB in ~25 ms (budget: 2 s). The live acceptance suite (polystore.verify) re-checks freshness, routing, cross-engine result identity, and the control on every run.
A synthetic social-commerce domain (~50k users, ~20k products, ~500k orders, ~1.5M order lines, ~100k reviews, ~500k follows), deliberately shaped so each query class has a clear best-fit engine:
| Query class | Example | Best-fit engine |
|---|---|---|
| Analytics | daily revenue by category | DuckDB (columnar) |
| Graph | friends-of-friends purchases (2-hop) | Apache AGE |
| Search | BM25 ranked product search | ParadeDB pg_search |
| Vector | similar products (ANN) | pgvector (HNSW) |
| Point lookup | a user's cart | Postgres itself (the control) |
| Phase | Scope | Status |
|---|---|---|
| 0 | Postgres 18 + pgoutput CDC consumer → DuckDB read model + rules router + divergence benchmark | ✅ shipped — measured results |
| 1 | Search (pg_search), vector (pgvector), graph (AGE) projections | planned |
| 2 | Query-signature monitor, cost-based routing, freshness/lag SLAs | planned |
| 3 | Self-driving controller: propose → shadow-test → human-gated promote | planned |
| 4 | Learned cost models + offline LLM control plane (never on the hot path) | planned |
| — | Postgres-wire gateway (drop-in: apps change only a connection string) | planned |
| — | HybridRAG retrieval surface: fan-out across vector + search + graph, fused re-ranking | planned |
| — | AutoGraph: controller derives and maintains a knowledge-graph projection from the observed workload | planned |
Phase 0's exit gate — all met, measured live by polystore.verify: a write to orders is visible in the DuckDB read model in ≤2s (measured ~25ms); the analytics query shows a ≥10× engine-win (measured 12.4×); the point-lookup control favors Postgres (it does, 0.4×); every routing decision is in the ledger.
# 1. canonical store (Postgres 18, logical replication enabled, schema auto-applied)
docker compose up -d
# 2. python environment + unit tests (no database needed for these)
uv sync
uv run pytest
# 3. deterministic dataset: ~2.9M rows across 6 tables, regenerates in ~45s
uv run python -m polystore.gen
# 4. live acceptance: freshness, routing, cross-engine identity, control — 4 checks
uv run python -m polystore.verify
# 5. the divergence benchmark (writes docs/bench/phase0-results.md)
uv run python -m polystore.bench.divergence
# optional: run the CDC consumer as a daemon (verify/bench drive it in-process,
# so don't run both at once — the projection file has a single writer)
uv run python -m polystore.cdcCONTEXT.md— the project's ubiquitous language (canonical store, projection, cast, engine-win, control, …)docs/adr/— Architecture Decision Records; every hard-to-reverse choice, with the alternatives it rejecteddocs/research/verified-plan.md— the fact-checked research report grounding these decisions (tool choices verified against primary sources, June 2026)