Database Performance Tuning — Troubleshooting

An index exists on the column, but the query plan still shows a Seq Scan

Before assuming the index is broken, check the most common actual causes: low table selectivity — if a query matches a large fraction of the table's rows (say, status = 'active' when 80% of rows are active), the planner correctly decides a sequential scan is cheaper than an index scan plus the corresponding heap fetches, because index scans aren't automatically faster for non-selective conditions; a function or type mismatch on the filtered column (WHERE customer_id::text = '42' won't use a plain index on the integer customer_id column — the cast changes what's actually being matched); or stale planner statistics, where the query planner's row-count estimates are out of date after a large bulk load or delete, leading it to choose a suboptimal plan. ANALYZE table_name; refreshes statistics and is worth running specifically after any large data change, before concluding an index problem exists.

A query was fast yesterday, slow today, with no code change

The most common real cause isn't a query regression — it's a data volume or distribution change: the table grew past a size where the previously-chosen plan (say, a nested loop join that was fine for a small inner table) is no longer efficient, or the actual distribution of values in a filtered column shifted enough that stale planner statistics now produce a meaningfully wrong cost estimate. Compare EXPLAIN ANALYZE output from before and after if available — a plan that changed from an Index Scan to a Seq Scan, or a join order that flipped, points directly at the cause; if the plan looks the same but is simply executing more rows, that's a genuine growth-driven slowdown rather than a plan regression, and the fix is likely a missing index or partitioning strategy the previous data volume didn't yet require.

Deadlock errors appearing in application logs

A deadlock itself isn't necessarily the bug — PostgreSQL and MySQL both detect true deadlocks automatically and roll back one of the involved transactions, which the application is expected to catch and retry. The actual problem worth fixing is the underlying lock ordering inconsistency that makes deadlocks possible in the first place: if transaction A updates row 1 then row 2, while transaction B elsewhere updates row 2 then row 1, a deadlock is possible under concurrent execution. Auditing the codebase for every place multiple rows are locked/updated within one transaction, and ensuring a consistent ordering (always by primary key ascending, for instance) across every such code path, is what actually eliminates the deadlock risk rather than just handling the retry after the fact.

Connection pool exhausted, application errors on "too many connections"

If the database's max_connections limit is being hit, the fix is very rarely "raise max_connections" — that just delays the same problem at a higher, more resource-intensive ceiling. Check first whether connections are actually being released properly (a connection acquired in application code but never returned to the pool due to a missing finally/try-with-resources block is a common, real leak), and whether the application-level pool size itself is misconfigured relative to the number of application instances — N application replicas each configured for a pool of 50 connections will collectively request up to 50×N database connections, which can exceed max_connections even with zero actual leaks, simply from pool-size math not accounting for replica count. A dedicated external pooler (PgBouncer) sitting between many application instances and the database is the standard fix once this math stops working at the application layer alone.

Query performance degraded gradually over weeks, no single obvious cause

This pattern points most often at accumulating table/index bloat (covered on the Advanced tab) from autovacuum not keeping pace with a high write rate — check n_dead_tup relative to n_live_tup in pg_stat_user_tables for the affected table specifically. A second common gradual cause: an index that was well-suited to the application's query patterns months ago no longer matches current query patterns as the application evolved, and queries are now falling back to a less-optimal existing index or a sequential scan without anyone having deliberately added or removed an index recently — periodically reviewing pg_stat_user_indexes for both unused indexes (wasting write performance for no read benefit) and, separately, confirming genuinely hot query patterns still have a matching index is worth doing as routine maintenance, not just reactively once degradation is already noticeable.