PostgreSQL Performance Doctor

PostgreSQL Performance Doctor: practical plan

Investigation plan

Capture evidence

Record query text, bind values or representative parameters, PostgreSQL version, table sizes, row estimates, recent deploys and workload timing. Use EXPLAIN (ANALYZE, BUFFERS) on a safe environment or carefully on production for bounded queries.

Compare plan behavior

Check sequential scans, join order, estimated versus actual rows, sort spills, buffer reads, loop counts and parallel workers. Confirm statistics are current and parameter selectivity varies.

Index review

Inspect existing indexes and write workload. Consider a composite index only when predicates and ordering support it; estimate storage and write cost. Avoid adding speculative indexes to solve a planner or data-model issue.

Concurrency checks

Review lock waits, connection pool saturation, long transactions, vacuum progress and I/O. Query latency may be caused by contention rather than the join itself.

Change protocol

Test a candidate index or query rewrite with representative data, compare plans and p95 latency, then deploy with rollback. Monitor reads, writes, bloat and replication lag. Do not tune global settings without workload evidence.

Measurement and review

Record the baseline, source, date range, denominator and owner. Use one primary measure plus quality guardrails; compare like-for-like cohorts and disclose missing data. Treat estimates as scenarios, not observed outcomes.

Risks and controls

Check relevance, consent, disclosure, workload and downside before scaling. Keep an evidence trail, obtain human review for material commitments, and pause if trust, quality or compliance signals deteriorate.

Next steps

1. Confirm the goal, audience and available capacity.
2. Gather the minimum evidence for a first test.
3. Assign an owner, budget and review date.
4. Run a small pilot, record outcomes and decide whether to iterate, expand or stop.