Slow-Query Review Without Seeing Your Data
Slow-Query Review Without Seeing Your Data
Getting a second opinion on database performance usually means handing over access to production data, running EXPLAIN plans against real rows, or shipping a full backup to a consultant. For teams under compliance obligations, contractual data-residency terms, or simple internal policy, that's often a non-starter. The good news is that most slow-query diagnosis doesn't actually require the data itself — it requires the schema, the statistics, and the query log. Separating those from customer rows is what makes outside review possible without a data exposure event.
Why the Data Itself Rarely Matters
PostgreSQL's query planner makes decisions based on table structure, indexes, constraints, and statistical metadata — not on the literal contents of a `customer_email` or `order_total` column. When an expert reviews a slow query, they're looking at:
- The table and index definitions (`\d+`, `pg_indexes`)
- Planner statistics (row counts, distinct value estimates, histogram boundaries)
- The actual query text and its execution plan
None of this requires reading a single production row. A schema-only dump, paired with `ANALYZE` statistics, gives the planner everything it needs to reproduce the same decisions it would make against live data — including sequential scans, misestimated joins, or missing index usage.
Building a Safe Diagnostic Package
The practical approach is a three-part export: schema DDL, statistics snapshot, and the query log itself.
1. Schema-only dump via `pg_dump --schema-only` captures tables, indexes, constraints, and views with zero row data.
2. Statistics can be extracted from `pg_stats` and `pg_class` (reltuples, relpages) so a reviewer — or a reconstructed local database — sees realistic cardinalities instead of an empty table's defaults.
3. Query logs, ideally captured with `log_min_duration_statement` or via `pg_stat_statements`, provide the actual SQL patterns causing pain, along with timing and call frequency.
Together, these three artifacts let an external reviewer run `EXPLAIN (ANALYZE, BUFFERS)` against a structurally identical, statistically realistic — but empty — clone. This is a core piece of any solid performance audit workflow: reproduce the plan, not the production dataset.
What This Approach Can and Can't Catch
Schema-plus-statistics review is excellent for structural problems: missing indexes, poor join order, inefficient predicates, overly broad `SELECT *` patterns, or type mismatches forcing implicit casts. It's also well suited to reviewing index strategy decisions before they're deployed.
It's less reliable for issues tied to data skew that statistics don't fully capture — highly correlated columns, unusual value distributions in multi-column predicates, or lock contention patterns that only show up under real concurrent load. For those cases, redacted or sampled query logs with parameter placeholders (rather than literal values) can add context without exposing actual customer content.
Making Redaction Part of the Workflow
Query logs themselves need care. Literal values in `WHERE` clauses can leak sensitive data even when table contents are never shared directly. Using parameterized logging, or scrubbing literals before export, keeps the log genuinely safe to hand to an outside reviewer — including a tool like Querk — while preserving the query shape that actually matters for diagnosis.
For most performance problems, that's the whole point: the shape of the query and the shape of the schema tell the story. The data was never the interesting part.