Postgres Performance — The First Five Things to Check

When a client tells me their app got slow, I run the same five checks in the same order. The order matters: each one is cheap, each one narrows the search, and I've never had to go past five to find the actual problem.

Nothing here requires a consultant or a paid tool. It's an afternoon, and it assumes you're on Postgres, which for a SaaS product is usually the right call anyway.

1. pg_stat_statements — find the query burning the most total time

Enable the extension if it isn't already, then sort by total_exec_time descending. Not mean_exec_time. Total.

This is the check people get wrong most often. Everyone hunts for the slow query, the one taking 4 seconds. But a 4-second report that runs twice a day costs your database 8 seconds. A 25ms query called 300 times per page load, across thousands of requests, is where the server is actually living.

You're looking for the top five by total time, and for the ratio between calls and time. High calls with modest mean time is a query pattern problem (check 4). Low calls with huge mean time is a plan problem (check 2).

Reset the stats, let it run through a normal business hour, then look. Stats accumulated since the last restart mix in a migration you ran six weeks ago.

2. EXPLAIN (ANALYZE, BUFFERS) on the top offender

Always with ANALYZE so you get real execution rather than the planner's fantasy, and BUFFERS so you see whether it's reading from cache or disk.

Three things I look for, in order:

Sequential scans on large tables. Small tables get seq-scanned and that's correct; the planner knows better than you for anything under a few thousand rows. A seq scan on a 20-million-row table is your problem, and it's usually one missing index or a query written so the index can't be used: a function wrapped around the indexed column, a type mismatch forcing a cast, a leading wildcard in a LIKE.

Estimated rows versus actual rows. This is the single most useful number on the page. If the planner expected 12 rows and got 400,000, every decision it made downstream was based on a lie. Usually that means stale statistics, so run ANALYZE on the table. Sometimes it means correlated columns the planner can't reason about, which is what extended statistics are for.

Nested loops over big row counts. A nested loop is great for a handful of rows and catastrophic for a million, and the planner only picks it because of a bad estimate. Fix the estimate rather than reaching for planner hints.

3. Audit the indexes — both directions

Missing indexes are the obvious half. Query pg_stat_user_tables for tables with a high seq_scan count relative to idx_scan and you have your candidates.

The half people skip: useless indexes cost you continuously. Check pg_stat_user_indexes for indexes with idx_scan near zero. Every one of them gets updated on every write to that table, takes space, and slows down vacuum. I've dropped a dozen unused indexes off a heavily-written table and watched write latency improve measurably with zero read regression.

Two more things while you're here. Composite index column order matters: an index on (user_id, created_at) serves queries filtering on user_id, but one on (created_at, user_id) mostly doesn't. And check for bloat, because an index on a table with heavy update churn grows far past its useful size. REINDEX CONCURRENTLY fixes it without taking the table offline.

4. Count your queries, not just their speed

This is the one no index will fix.

Turn on statement logging for one request and count how many queries a single page load produces. If a list of 50 orders produces 51 queries, your ORM is lazy-loading a relation in a loop, and every one of those queries is individually fast, indexed, and fine. Together they're 51 network round trips.

Every ORM has the fix and every ORM makes it opt-in. Prisma's include, Django's select_related and prefetch_related, ActiveRecord's includes, SQLAlchemy's joinedload. The problem is that the broken version looks completely reasonable in code, which is why it survives review.

The version that hurts more is N+1 across a network boundary: a REST or GraphQL resolver calling a service that queries the database, once per item. Same problem, further away, harder to see.

5. Connections and pooling

Every Postgres connection is a separate OS process with its own memory. A small instance holding 500 open connections spends its resources on context switching and per-connection overhead rather than on your queries, and most of those connections are idle.

Check pg_stat_activity grouped by state. If idle dwarfs active by an order of magnitude, you don't have a load problem, you have a connection management problem.

Put PgBouncer in front, in transaction mode, and let a few dozen real connections serve hundreds of clients. Transaction mode breaks session-level features like prepared statements and LISTEN/NOTIFY, so check what your driver does before flipping it.

Serverless makes this worse. Every cold Lambda or edge function wants its own connection, concurrency spikes with traffic, and nothing stops you from exhausting the database. If you're serverless on Postgres, a pooler isn't an optimisation, it's a prerequisite. It's a quiet reason hosted platforms are pleasant: Supabase ships a pooler by default and most people never learn they needed one.

Two closing notes

Autovacuum. If your slow query got slower over months with no code change, check bloat. Heavy update and delete traffic leaves dead tuples that scans still have to walk past. The default autovacuum settings are tuned for a small database from a decade ago; on a large busy table you want it triggering far more aggressively. pg_stat_user_tables shows dead tuple counts and last vacuum time.

Don't put a cache in front of an unfixed query. I did this. A dashboard endpoint took 3 seconds, the deadline was Friday, so I dropped Redis in front of it with a five-minute TTL and shipped. It worked, and then it created three new problems: stale numbers that made a client think their revenue had dropped, a thundering herd every time the key expired under load, and a cache invalidation path that nobody understood six weeks later. When I finally read the plan, the fix was a composite index on two columns and a rewritten IN clause. The query went to 40ms and I deleted the cache.

Caching a slow query converts a performance problem into a correctness problem, and correctness problems are more expensive. Read the plan first. The plan is right there, and it's free.