Debugging PostgreSQL Performance

Let's chat about performance, optimization, and debugging a PostgreSQL database. Can you read EXPLAIN ANALYZE output fluently? Do you know how to find which tenant is causing a slow query, how to use pg_stat_statements, how to spot lock contention? If you have a hard time answering those, don't worry. Today we're going to cover all of them and practice how to get better at it.

The goal here: you should be able to look at a slow API endpoint and trace it down to the exact query, the exact tenant, the exact missing index, in under 10 minutes. That's the bar.

EXPLAIN ANALYZE: read the plan, not the vibes

Let's start with EXPLAIN ANALYZE because everything else builds on it.

So why does this exist?

The query planner makes a cost-based guess about the best way to execute your SQL query. EXPLAIN ANALYZE actually runs the query and shows you whether that guess was correct.

Always use this form:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

ANALYZE → Runs the query and reports what actually happened.

BUFFERS → Shows cache hits vs. disk reads. Without it, performance analysis is incomplete.

⚠️ ANALYZE executes the query for real, including writes. Use BEGIN and ROLLBACK to test without committing changes.

The single most important skill: estimated vs actual
Every node prints two row counts. When rows (estimate) and actual rows diverge by more than ~10x, the planner is working from bad information.

How to read the tree

The plan is a tree. Read the most-indented node first. That's where execution starts. Costs and times are cumulative: a parent node's time includes its children.

actual time=X..Y → X is time to first row, Y is time to last row, per loop. If loops=5000, the real total for that node is Y × 5000. This is how a "0.05 ms" node secretly costs 4 seconds.

The best resource if you're starting out is explain.depesz.com. You can paste any EXPLAIN output and it visualizes the slow nodes. Use it on your own queries until you can predict what it'll say before you paste.

Index Design

I'm not going to go deep into index design here. Instead, read Use The Index, Luke! by Markus Winand.

It's free, easy to read, and probably the best resource available on SQL indexes. Many senior engineers use indexes every day, but haven't taken the time to read it from start to finish.

If you read one resource on indexing, make it this one.

If you've ever thought indexes were some kind of database magic, you're not alone. Most of the confusion disappears once you realize they're just a clever way of keeping data sorted.

The key idea is that a B-tree index is a sorted copy of one or more columns, plus pointers to the corresponding table rows.

The fact that it's sorted is what gives it its power. PostgreSQL can quickly find the rows it needs instead of reading the entire table.

The rule that matters most: leftmost prefix

An index on (tenant_id, created_at, status) is sorted by tenant_id, then within
each tenant by created_at, then by status. So it can serve:

✅ WHERE tenant_id = ?
✅ WHERE tenant_id = ? AND created_at > ?
✅ WHERE tenant_id = ? AND created_at > ? AND status = ?
✅ ORDER BY created_at      (within a fixed tenant_id)
❌ WHERE created_at > ?     (skips the leading column, can't seek)
❌ WHERE status = ?         (skips two leading columns)

You can only use a contiguous prefix, starting from the left. This is why column
order in a composite index is a design decision, not an afterthought.

Things that quietly break index usage

• Using a function on the indexed column

  WHERE lower(email) = ? can't use a regular index on email.

  Fix: create an expression index:

  CREATE INDEX ON users (lower(email));

• Modifying the column in the filter

  WHERE created_at + interval '1 day' > now() makes it harder to use the index.

  Instead, apply the calculation to the constant side of the comparison.

• Leading wildcards

  LIKE '%foo' can't efficiently use a B-tree index.

  LIKE 'foo%' can.

  For substring searches, use a trigram index (pg_trgm).

• Type mismatches

  Implicit casts on the column side can prevent index usage. Make sure the types match.

• Low-selectivity columns

  An index on a boolean column or a status column with only a few possible values is often not useful by itself. PostgreSQL may choose a sequential scan instead. Consider a composite index or a partial index.

Index features worth knowing

• Covering indexes (INCLUDE)

  Add extra columns to the index so PostgreSQL can answer the query without reading the table. This can enable an Index Only Scan, which is often faster.
  

  CREATE INDEX ON orders (tenant_id, created_at)
  INCLUDE (total, status);
  

• Partial indexes

  Index only the rows you actually query. The index is smaller and cheaper to maintain. Often faster, too.
  

  CREATE INDEX ON jobs (created_at)
  WHERE status = 'pending';
  

• B-tree is the one you'll use 95% of the time. Learn it first. The others exist for specialized data types and query patterns.

The trade-off: indexes aren't free

Indexes make reads faster, but they make writes more expensive.

Every INSERT, UPDATE, and DELETE must update the table and all of its indexes. More indexes mean more disk, more memory, more work on every write.

Regularly check for unused indexes (pg_stat_user_indexes.idx_scan = 0) and remove them when appropriate.

The goal isn't to have more indexes. The goal is to have the fewest indexes needed to support your important queries.

pg_stat_statements: where the server actually spends its life

When someone tells me "the database is slow," this is usually the first place I look. Most performance problems aren't caused by one terrible query. They're caused by a handful of queries quietly consuming time all day long.

A production application can execute thousands of different queries. You can't run EXPLAIN ANALYZE on all of them.

pg_stat_statements solves this problem by collecting execution statistics for every normalized query. Queries that differ only by parameter values are grouped together, making it easy to see where the database is spending its time.

To enable it, add the following to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart PostgreSQL, then create the extension:

CREATE EXTENSION pg_stat_statements;

Learn these columns:

• calls → How many times the query ran.
• total_exec_time → Total time spent executing the query.
• mean_exec_time → Average execution time per call.
• rows → Total rows returned or affected.
• shared_blks_hit vs shared_blks_read → Cache hits vs disk reads.

The most useful question you can ask in production is:

Where is my database spending most of its time?

This query answers it:

SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time) AS total_ms,
  round(mean_exec_time, 2) AS mean_ms,
  rows,
  round(
    100.0 * shared_blks_hit /
    nullif(shared_blks_hit + shared_blks_read, 0),
    1
  ) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This tells you:

• which queries are globally expensive,
• slowest average queries,
• most frequently executed queries.

This is often where real tuning starts in production.

Sort by total time, not average time

Many engineers focus on mean_exec_time, but total_exec_time is usually the more important metric.

A query that takes 3 ms and runs 2 million times per hour can consume far more resources than a report that takes 2 seconds and runs twice a day.

Think about it this way:

• Sort by total execution time to find where the server is spending most of its effort.
• Sort by average execution time to find individually slow queries.

Both views are useful, but total_exec_time is where you'll usually find the biggest wins.

Resetting the stats

When you want to start a fresh measurement window:

SELECT pg_stat_statements_reset();

Running the "Top 20 by total execution time" report every week is one of the simplest ways to catch performance problems before they become outages.

auto_explain: catch the slow query you can't reproduce

pg_stat_statements tells you which queries are slow overall. What it can't tell you is why a query was slow at 2 AM during an incident.

Execution plans can change as data grows, statistics become outdated, or different parameter values are used. A query that's normally fast can suddenly take a very different path.

That's where auto_explain helps.

It automatically logs the full execution plan for queries that exceed a time threshold, capturing the plan that was actually used when the slowdown happened.

Configure it in postgresql.conf:

session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.log_format = 'text'

Use it carefully in production

log_analyze = on measures actual execution times, which adds overhead to every logged query.

A safe approach is:

• Start with a relatively high threshold (for example, 500 ms or 1 second).
• Leave log_analyze off initially.
• Enable more detailed logging only when investigating a performance issue.

Why it matters

Think of the tools as solving different problems:

• pg_stat_statements answers: "Which queries are costing us the most?"
• auto_explain answers: "What plan did PostgreSQL actually use when this query became slow?"

Together they form one of the most valuable performance debugging tool sets in PostgreSQL.

pg_stat_statements helps you find the problem.

auto_explain helps you understand it.

When you have both, performance investigations become much less about guessing and much more about reading the evidence.

pg_locks: who is blocking whom

When a query suddenly stops making progress, the problem is often not the query itself. It's waiting for a lock.

pg_locks shows one row for every lock currently held or being waited on by PostgreSQL sessions.

The most important column is:

• granted = true → the lock has been acquired.
• granted = false → the session is waiting for a lock held by someone else.

Joining it with pg_stat_activity gives you the lock plus the SQL behind it:

SELECT
  l.pid,
  l.locktype,
  l.mode,
  l.granted,
  left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
ORDER BY l.granted, l.pid;

A waiting query is often a symptom of lock contention somewhere else in the system.

Deadlocks: not a database failure, a concurrency bug

A deadlock happens when two transactions are waiting on each other:

1. Transaction A holds lock 1 and wants lock 2.
2. Transaction B holds lock 2 and wants lock 1.

Neither transaction can continue, creating a cycle.

PostgreSQL detects this situation automatically (after deadlock_timeout, which defaults to 1 second) and resolves it by aborting one of the transactions with a deadlock detected error.

Your application should be prepared to catch this error and retry the transaction.

One lesson that's easy to forget: a deadlock isn't a database failure. It's a concurrency bug. PostgreSQL is doing exactly what it should, so the system can move forward.

For lock contention specifically, the canonical resource is the Postgres wiki page "Lock Monitoring." Search for it, bookmark it; the query at the bottom that shows blocking chains is one you'll run many times in your career.

Use it with pg_stat_activity

On its own, pg_locks is hard to read: raw pids and lock modes, no context.

That's why the query above joins it with pg_stat_activity. Together they show you:

• Which queries are currently waiting.
• Which sessions are blocking them.
• How long they've been waiting.
• The SQL being executed by both sides.

When debugging database issues, think of these views as a pair:

• pg_stat_activity → What is everyone doing?
• pg_locks → Who is blocking whom?

Together they help you diagnose lock contention, blocked queries, and deadlocks before they turn into production incidents.

Once you've worked through all of this, the natural next step is MVCC and VACUUM.

That's where you'll learn why idle in transaction causes bloat, why dead rows affect query performance, and why a perfectly reasonable index can still be slow.

In my experience, MVCC is the point where PostgreSQL starts to make sense as a system instead of a collection of features. Many performance problems that look mysterious become obvious once you understand how PostgreSQL stores and cleans up row versions.

Comments

[Wilbur Suero]

The loops multiplication point is where most people's mental model of query cost breaks down. actual time=0.05..0.08 is not the cost of that node, it's the cost per loop. The real number is that times loops. We tend to read execution plans the way we read code: linearly, locally. But a query plan is a system. Cost accumulates across iterations, not just across nodes.

Same with pg_stat_statements: the 3ms query that runs 2M times is usually more interesting than the 2s report that runs twice a day. Sorting by mean hides the system's actual load distribution. Total time shows you where the work actually concentrates.