Measure the query users are waiting for
Performance work starts with a real query and a real workload. Identify the endpoint or job that is slow, capture its parameters, row counts, frequency, and latency distribution, then run the same shape against a representative dataset. A query that takes ten milliseconds once may be responsible for an incident when it runs ten thousand times. Conversely, a rare report that takes a second may be acceptable. Tie database latency to a user or business operation before changing schema.
Use database statistics and application traces together. A database view can show total time and calls, while a trace shows whether the query is only a small part of a slow request. Record whether the query runs inside a transaction and whether a connection pool is saturated. Connection wait time can look like query time from the user's perspective, so optimize the correct boundary.
Read EXPLAIN as a plan, not a verdict
EXPLAIN shows the planner's chosen operations, estimated rows, costs, joins, and scan methods. EXPLAIN ANALYZE executes the query and reports actual timing and row counts, so use it carefully for writes and production data. Compare estimated and actual rows at each node. Large differences often indicate stale statistics, correlated columns, skewed values, or a predicate the planner cannot estimate well. An index is not automatically the answer to an estimate problem.
Look for sequential scans on a table that is genuinely large and filtered selectively, nested loops that repeat an expensive inner scan, sorts that spill to disk, and joins that process far more rows than the final result needs. Check planning time and execution time separately. A complex query can spend more time planning than running, especially when generated dynamically. Keep the plan output with the query parameters so a future engineer can reproduce the reasoning.
Design indexes around access patterns
An index should support a known filter, join, ordering, or uniqueness rule. For a composite index, put columns that narrow the search and match the query's leading predicates in a useful order, then consider ordering columns. A single index on every column creates write overhead, storage cost, and planner choices without guaranteeing a faster query. Remove indexes that have no reads after verifying workload statistics and maintenance requirements.
Partial indexes are valuable when a query repeatedly targets a stable subset, such as active records. Covering indexes can avoid table lookups for a narrow projection, but adding many included columns increases size and update cost. For text search, use the operator class and extension that match the query rather than expecting a B-tree to solve every pattern. Validate the index with EXPLAIN on representative values, including values that are common and rare.
Keep statistics and data shape honest
The planner relies on statistics collected by ANALYZE. After a large data change, partition load, or unusual distribution shift, stale statistics can choose a poor plan. Autovacuum and analyze settings should match table size and write volume. A table with a small number of very common values may need a higher statistics target for the columns that drive important filters. Do not raise targets everywhere; tune the columns that show estimate errors.
Data modeling also affects plans. Functions applied to a filtered column can prevent a normal index from being used unless you add an expression index. Implicit casts can change operator behavior. A query that wraps a timestamp in a date function may scan more rows than a half-open range. Prefer sargable predicates that let the planner use the column's ordering.
Control transactions and connection pools
A fast query inside a long transaction can still create contention. Keep transactions short, lock only the rows needed, and avoid network calls while holding a database transaction open. Inspect lock waits when requests are slow. A connection pool should have a limit that the database can serve; adding more connections can increase context switching and memory pressure rather than throughput.
Set statement and lock timeouts appropriate for the operation. A user-facing request should not hold a connection indefinitely while a report runs. Cancelled requests should release connections promptly. Monitor pool wait time, active connections, idle-in-transaction sessions, and blocked queries. These signals often identify the root cause before CPU or disk metrics become alarming.
Change one thing and compare
Performance fixes should be measured as controlled changes. Capture a baseline plan and latency sample, add or adjust one index or predicate, then compare CPU, reads, writes, planning time, and tail latency. Test concurrent workload, not only a single query in a quiet session. An index that makes one read fast can make inserts slower or cause a different query to choose a worse plan.
Use migrations for schema changes and include a rollback or removal path. Build an index concurrently when the production platform and table size require it, and understand the operational limits of that command. After deployment, watch the query over a normal traffic cycle. The best result is not the lowest isolated execution time; it is a stable improvement for the operation users care about.
Keep a performance runbook
Document the important queries, their expected latency, indexes that support them, and the metrics that indicate regression. Include a safe way to capture EXPLAIN output without exposing customer data. When a query becomes slow, the operator should know how to identify parameters, check locks and pool waits, inspect the plan, and decide whether to rollback, add a temporary limit, or schedule a deeper fix.
PostgreSQL rewards evidence. Measure the workload, inspect estimates and actuals, align indexes with access patterns, keep statistics current, control transactions, and validate changes under concurrency. Avoid tuning by folklore. A plan that is right for one table size or data distribution may be wrong after the next growth phase, so keep the feedback loop running.
Measure plans against production-shaped data
A query plan from a tiny local table is not evidence for a production workload. Capture representative row counts, data distribution, concurrent connections, and parameter values before choosing an index or rewrite. Compare planning time, execution time, rows removed by filters, buffer reads, and lock waits. After release, watch the slow-query sample and verify the optimization helps the intended path without making writes, vacuum, or another critical query materially worse.
Implementation example
Capture a representative query with its parameters, row counts, and application context before changing it. Compare the plan before and after an index or rewrite, and include the write, vacuum, and storage cost of the change. An index is a contract with future data distribution, not a free speed button.
explain (analyze, buffers, verbose)
select id, status
from webhook_events
where tenant_id = $1 and status = $2
order by created_at desc
limit 50;Verify and troubleshoot
Test cold and warm cache, common and worst-case parameters, concurrent requests, and a production-shaped dataset. Inspect planning time, execution time, rows removed, buffer hits, disk reads, lock waits, and connection acquisition. Confirm the endpoint's tail latency improves rather than optimizing a rare local query while the real bottleneck remains pool or network wait.
Operations and recovery
Monitor slow-query samples, plan changes after statistics refresh, index size, vacuum health, and database CPU. Roll out high-impact indexes or query rewrites behind a canary and keep the previous query path available until metrics stabilize. If a plan regression appears, restore the old path or index state and preserve the evidence that explains the regression.
References and further reading
Use PostgreSQL EXPLAIN, planner statistics, index documentation, and the Supabase Postgres performance guidance. Record the PostgreSQL version and dataset assumptions with each benchmark.