What is Shadow Database Testing?

Shadow database testing is a database validation technique in which a continuously-synchronized replica of the production database — the shadow — receives a mirrored copy of all live production queries while running a proposed schema change. Response times and query plans from the shadow are compared against the production baseline in real time. Because the shadow carries production data volume and sees the exact production query mix, regressions that only appear under real conditions are caught before deployment. The shadow receives no application traffic directly and its results are never returned to users; it exists solely as a safety net.

How It Works

Shadow database testing requires three moving parts working in concert:

1. Data replication. A CDC (change-data-capture) pipeline continuously replicates every write from the production database to the shadow using logical replication. The shadow stays current to within a few seconds of production, so the data under test is effectively identical.
2. Query capture. A transparent proxy sits in front of the production database and intercepts every query at the PostgreSQL wire-protocol level. The proxy adds negligible latency (typically under 500 μs) and is invisible to the application.
3. Query replay. Captured queries are replayed against the shadow, which is running the proposed schema migration. The proxy records execution time, query plan, and error status for every replayed query and compares them against the production result. A query that takes 92× longer on the shadow than on production — as happened in one real incident we documented — surfaces immediately rather than on deployment night.

┌─────────────┐    write    ┌──────────────────┐
│  Production │────────────▶│  Shadow Database  │
│  Database   │   (CDC/WAL) │  (proposed schema)│
└─────────────┘             └──────────────────┘
       ▲                            ▲
       │ queries                    │ replayed queries
       │                            │
┌─────────────────────────────────────────────────┐
│                  scry-proxy                      │
│   capture → fanout → compare → report            │
└─────────────────────────────────────────────────┘
       ▲
       │
┌─────────────┐
│ Application │
└─────────────┘

Why Staging Isn't Enough

Staging environments fail to catch the categories of regressions that most often cause production incidents, for two fundamental reasons.

Volume mismatch. A staging database is typically seeded from a subset of production data — sometimes a few percent of the actual row counts. PostgreSQL's query planner is statistics-driven: the same query that uses an index scan on 50,000 rows may switch to a sequential scan on 50 million, adding orders of magnitude of latency. A migration that alters column statistics, removes an index, or changes a data type will produce a misleading query plan in staging and a catastrophic one in production.

Query pattern mismatch. Staging traffic is generated by integration tests, synthetic load scripts, or manual QA — none of which reproduce the long-tail of real queries. Production workloads include unusual parameter combinations, concurrent write patterns, and accumulated edge cases that no test suite anticipates. Shadow testing replays the actual query stream, so these edge cases are exercised against the proposed schema before the migration runs.

Shadow Testing vs. Load Testing

Shadow testing and load testing are complementary techniques that answer different questions.

Load testing asks: can this system handle N requests per second? It stresses infrastructure capacity — connection limits, CPU headroom, memory pressure — using synthetic or recorded traffic replayed at elevated volume. It does not validate correctness of schema changes.

Shadow database testing asks: does this schema change break any existing query? It is not concerned with peak throughput. It is concerned with behavioral fidelity: does every query that works today still work — and perform comparably — against the proposed schema? The traffic volume is production volume, not amplified volume. The goal is regression detection, not capacity measurement.

In practice, teams run load tests to validate infrastructure sizing and shadow tests to validate schema changes. They are not substitutes for each other.

Configuring ScryData for Shadow Testing

ScryData provides two components — scry-proxy and scry-backfill — that together implement the shadow testing pattern for PostgreSQL. Setup takes three steps.

Step 1: Replicate production data to the shadow

Point scry-backfill at your production database and the shadow. It establishes a logical replication slot and keeps the shadow synchronized using WAL-based CDC.

# Set the replication source and target
export SCRY_SOURCE__HOST=prod-db.internal
export SCRY_SOURCE__PORT=5432
export SCRY_SOURCE__DATABASE=myapp

export SCRY_TARGET__HOST=shadow-db.internal
export SCRY_TARGET__PORT=5432
export SCRY_TARGET__DATABASE=myapp

# Start continuous replication
./scry-backfill replicate

Step 2: Capture production queries via the proxy

Route your application's database connections through scry-proxy. The proxy intercepts queries at the wire-protocol level with sub-millisecond overhead and emits anonymized query events for replay.

# Point the proxy at production
export SCRY_BACKEND__HOST=prod-db.internal
export SCRY_BACKEND__PORT=5432
export SCRY_PUBLISHER__ANONYMIZE=true

# Start the proxy (application connects to port 5433)
./scry-proxy

Step 3: Replay captured queries against the shadow

With the shadow synchronized and queries flowing through the proxy, enable shadow replay. ScryData fans out each captured query to the shadow, records execution time and query plan, and flags any query whose p99 latency on the shadow exceeds a configurable threshold relative to production.

# Enable shadow replay in the proxy config
export SCRY_SHADOW__ENABLED=true
export SCRY_SHADOW__HOST=shadow-db.internal
export SCRY_SHADOW__PORT=5432
export SCRY_SHADOW__REGRESSION_THRESHOLD=2.0   # flag at 2x slowdown

./scry-proxy

Regression alerts appear in the Prometheus metrics and the ScryData dashboard. When all queries pass, promote the migration to production with confidence.