Slow-Query Review Without Seeing Your Data

Slow-Query Review Without Seeing Your Data

Getting a second opinion on database performance usually means a tradeoff: either grant an outside expert access to production data, or settle for generic advice based on a few pasted query plans. Neither option is great. One creates compliance risk and approval delays; the other produces recommendations that don't account for real table sizes, index selectivity, or data distribution. There's a middle path that's increasingly common in PostgreSQL shops: schema-only clones paired with query logs.

Why Data Access Isn't Actually Necessary

Query planning in PostgreSQL depends on structure and statistics, not the literal contents of rows. The planner makes decisions based on table and column definitions, indexes, constraints, foreign keys, and the statistics stored in `pg_statistic` (row counts, most-common values, histogram boundaries, correlation estimates). None of that requires reading actual customer data.

A schema-only clone captures DDL — tables, views, indexes, constraints, sequences — without copying a single row. Combined with `ANALYZE` run against representative statistics (or imported stats from the source database), the clone behaves like the real database from the planner's perspective, while containing zero sensitive values. An expert reviewing this clone can reproduce `EXPLAIN` plans, test index changes, and validate rewrites without ever touching production rows.

What the Query Log Adds

Schema alone tells you what's possible; it doesn't tell you what's actually slow. That's where query logs come in. Enabling `log_min_duration_statement`, or exporting aggregated statistics from `pg_stat_statements`, surfaces the queries that matter most — ranked by total time, call frequency, or mean latency — without exposing bound parameter values if logging is configured carefully.

Reviewers work from this log to identify patterns: sequential scans on large tables, repeated queries that could be batched, missing indexes on foreign key columns, or query shapes that defeat index usage through implicit type casts. Cross-referencing log entries with the schema clone lets an outside expert run `EXPLAIN (ANALYZE, BUFFERS)` against synthetic or statistically-seeded data that mirrors production volume, producing plans that are directly comparable to what's happening live.

Setting Up a Safe Review Workflow

A practical workflow looks like this:

1. Dump schema only, using `pg_dump --schema-only`, including extensions and custom types.

2. Export or copy statistics so the clone's query planner behaves realistically — this matters more than people expect, since an empty table with no stats will produce misleading plans.

3. Collect query logs or `pg_stat_statements` output over a representative window, long enough to capture peak-load behavior and periodic batch jobs.

4. Share both artifacts through a controlled channel, ideally with parameter values redacted or generalized.

Tools built for this kind of workflow — Querk included — automate the schema extraction and statistics transfer so teams don't have to script `pg_dump` flags and stats imports by hand each time. For teams formalizing this as a recurring practice, it's worth treating it the same way as database performance audits are treated elsewhere: scheduled, documented, and tied to specific optimization goals rather than one-off firefighting.

Where This Approach Falls Short

Schema-only review isn't a complete substitute for production access in every case. Data skew that statistics don't capture well — correlated columns, unusual value distributions within a single partition, or bloat from dead tuples — can cause the clone's plans to diverge from reality. Reviewers should treat schema-clone find