PostgreSQL Performance Tuning
Demo Creator
@seed-creator · muallif
Reading EXPLAIN ANALYZE
EXPLAIN shows the plan the optimizer intends to run; EXPLAIN ANALYZE actually runs it and reports the real row counts and timings at each node. That difference is everything. The plan is a prediction, and predictions are where performance goes wrong. Read the tree from the innermost node outward — that is execution order — and at each step compare the estimated rows against the actual rows.
Focus on the widest gaps between estimate and reality. When the planner expects 10 rows and gets 100,000, every decision built on that estimate is suspect: it may have chosen a nested loop that is catastrophic at the real cardinality, or a sequential scan where an index would have won. Those misestimates almost always trace back to stale statistics or to a correlation between columns that the planner assumes are independent.
Watch for a few specific smells. A Nested Loop with a large outer row count multiplies work per row and balloons quickly. A Sort or Hash node that spills to disk — you will see "Sort Method: external merge Disk" — means work_mem was too small for that query. And a Rows Removed by Filter far larger than the rows returned tells you the scan read a huge amount it then threw away, which is usually a missing or unusable index.
A Seq Scan on a 10-million-row table is not always bad. Without an index it is the only choice; with one, it means the planner decided the index was not selective enough — and it is usually right.
Indexes That Actually Get Used
An index only helps if the planner believes it is cheaper than the alternative and if the query is written so the index can apply. Wrapping an indexed column in a function — WHERE lower(email) = $1 — defeats a plain index on email; you need a matching expression index on lower(email). A leading wildcard in LIKE '%term' cannot use a normal B-tree at all. And an index on a low-cardinality column like a boolean status is often ignored because scanning is cheaper than bouncing between the index and the heap.
Composite indexes are directional. An index on (author_id, created_at) supports filtering by author and sorting by date, and it supports filtering by author alone, but it cannot efficiently serve a query that filters only by created_at — the leading column has to be constrained first. Order the columns by how you query: equality predicates first, then the range or sort column last.
Check pg_stat_user_indexes for indexes with idx_scan = 0. An index that is never scanned still costs you on every INSERT, UPDATE, and DELETE. Unused indexes are pure overhead — drop them.
The N+1 Query Problem
Fetching a list of 100 articles and then issuing 100 separate queries, one per author, is the classic N+1. Each round trip is cheap on its own, but the network latency and per-query overhead multiply until the endpoint that "worked in testing" collapses under real data. The fix is a single JOIN, or a batched second query using WHERE author_id = ANY($1) with the collected ids.
ORMs and lazy-loaded relations hide this pattern behind innocent-looking property access, so it rarely shows up in code review. The only reliable defense is to log every SQL statement in development. When one HTTP request emits a burst of near-identical queries differing only by a bound id, you have found an N+1 — and you will be surprised how often you find one.
Connection Pooling
Connection pools should be sized to the database's max_connections minus a buffer for admin access, then divided across all your application replicas. Over-provisioning pools is a common cause of mysterious timeout spikes.
Each Postgres connection is a backend process with real memory cost, so the server caps them with max_connections. If ten app replicas each open a pool of fifty, you are demanding five hundred connections — far past a typical default of one hundred — and clients start queueing or erroring. Size each pool so the total across replicas leaves headroom for migrations and manual access.
For serverless or very high replica counts, put a transaction-mode pooler such as PgBouncer in front of the database. It multiplexes thousands of short-lived client connections onto a small pool of real backends, which is the only sane way to survive a fleet that scales connections faster than the database can create backends.
Partial and Covering Indexes
A partial index only covers rows matching a WHERE clause, so an index defined WHERE deleted_at IS NULL is dramatically smaller on a table where most rows are soft-deleted, and it is faster to scan because it never contains the dead rows. It also matches your queries exactly, since those queries already filter to live rows.
A covering index goes further by stashing extra columns in the index with INCLUDE, so the query can be answered from the index alone without visiting the heap — an "index-only scan". This is a large win for hot read paths like a paginated list, where the same handful of columns are read over and over and the heap fetch was the expensive part.