PostgreSQL Performance Tuning

Most "slow Postgres" is not slow Postgres. It is a missing index, a query the planner cannot optimize, or a connection pool fighting itself. Before you touch a single server setting, learn to read what the database is already telling you.

Start with EXPLAIN ANALYZE

The query planner will tell you exactly what it is doing if you ask. Never tune a query you have not explained:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Read it bottom-up. A Seq Scan over a large table in a hot query is the most common red flag - it means Postgres is reading every row because no useful index exists.

Index for the query, not the column

An index on (customer_id, status) serves the query above far better than two separate single-column indexes, because the planner can satisfy both predicates from one structure. Composite index column order matters: put the most selective, equality-tested columns first.

CREATE INDEX idx_orders_customer_status
    ON orders (customer_id, status);

Watch for the traps

• Functions on indexed columns (WHERE lower(email) = ...) defeat the index unless you build a matching expression index.
• SELECT * pulls columns you do not need and blocks index-only scans.
• Stale statistics mislead the planner - run ANALYZE after large data changes.

Then look at connections

Each Postgres connection is a backend process with real memory cost. Hundreds of direct connections from an app server will starve the database long before CPU does. Put a pooler such as PgBouncer in front and size the pool to your cores, not your traffic. Tune the query path first, the configuration last - that order is almost always where the wins are.