PostgreSQL Performance Tuning: The Practical Guide

Start With EXPLAIN ANALYZE

Before touching any configuration or adding any indexes, understand what the query planner is actually doing. EXPLAIN ANALYZE is your most important tool.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT * FROM orders\nWHERE user_id = 42 AND status = 'pending'\nORDER BY created_at DESC\nLIMIT 20;

Read the output bottom-up. The indented nodes are the inner operations; the less-indented nodes show you what happens to their output. The numbers that matter are actual rows vs estimated rows — a large discrepancy means your statistics are stale.

The Most Common Culprits

1. Missing indexes on foreign keys and filter columns. Every foreign key that is used in a JOIN should have an index. Every column that appears in a WHERE clause with high selectivity should have an index.
2. N+1 queries. Loading a list and then querying the database once per row. The fix is a JOIN or a single WHERE id = ANY($1) query with the collected IDs.
3. Bloated tables. Dead rows from UPDATE and DELETE operations accumulate until VACUUM removes them. Check pg_stat_user_tables for high n_dead_tup counts.
4. Locking. Long-running transactions block everything behind them. pg_locks and pg_stat_activity will show you what is waiting and why.

Index Types Worth Knowing

Most people only use B-tree indexes. These others are worth keeping in your toolkit:

• Partial indexes: Index only the rows that match a condition. CREATE INDEX ON orders (created_at) WHERE status = 'pending' is much smaller and faster than a full index.
• GIN indexes: For jsonb columns and full-text search.
• BRIN indexes: For large time-series tables where rows are inserted in order. Tiny size, surprisingly effective.

Connection Pooling Is Not Optional

PostgreSQL creates a new process for every connection. At 500 concurrent connections, you are spending more memory on connection overhead than on actual query execution. Use PgBouncer or a built-in pooler in transaction mode. This single change often produces the most dramatic throughput improvement.

Tune the application before tuning the database. Most performance problems are query problems, not configuration problems.