PostgreSQL Performance Tuning#

Most PostgreSQL performance problems come from missing indexes, bad query plans, connection overhead, or table bloat. This covers how to diagnose each one.

Reading EXPLAIN ANALYZE#

EXPLAIN shows the query plan. EXPLAIN ANALYZE actually executes the query and shows real timings.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=1 width=120) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: (customer_id = 42)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 12
Planning Time: 0.152 ms
Execution Time: 0.048 ms

What to look for: Seq Scan on large tables means a missing index. Rows Removed by Filter means the index fetched extra rows that a composite index would eliminate. actual rows far from estimated rows means stale statistics – run ANALYZE tablename;. Nested Loop with high loops count usually wants a hash join; check the inner table’s indexes.

For destructive statements, wrap in a transaction:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;

Common Slow Query Patterns#

Functions on indexed columns: WHERE LOWER(email) = 'user@example.com' cannot use a B-tree index on email. Fix: CREATE INDEX idx_email_lower ON users (LOWER(email));

Large IN lists: WHERE id IN (1, 2, ..., 10000) generates huge plan trees. Use WHERE id = ANY(ARRAY[...]) or a temp table join.

SELECT * when you need 2 columns: Forces full row reads. A covering index can serve the query from the index alone.

Index Types#

B-tree (default) – Equality and range queries. Covers =, <, >, BETWEEN, IN. Right choice 90% of the time.

CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

GIN – Full-text search, JSONB containment, array membership.

CREATE INDEX idx_data_jsonb ON events USING gin (metadata jsonb_path_ops);

GiST – Geometric data, range types, nearest-neighbor queries.

BRIN – Very large append-only tables where the indexed column correlates with physical order (like timestamps). Tiny index size, useless if values are randomly distributed.

CREATE INDEX idx_events_created ON events USING brin (created_at);

pg_stat_statements#

The most important tool for finding slow queries in production. Enable it:

shared_preload_libraries = 'pg_stat_statements'

After restart: CREATE EXTENSION pg_stat_statements;

Find top queries by total time:

SELECT calls, round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  substr(query, 1, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

A 5 ms query called 2 million times consumes more than a 500 ms query called 100 times. Optimize by total_exec_time, not mean.

Connection Pooling with PgBouncer#

Each PostgreSQL connection is a process using 5-10 MB. PgBouncer multiplexes many client connections onto a small server pool.

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

Use pool_mode = transaction for best multiplexing – the server connection returns to the pool after each transaction. Applications connect on port 6432 instead of 5432.

Vacuum and Autovacuum Tuning#

MVCC means updates and deletes leave dead tuples. VACUUM reclaims this space. Default autovacuum settings are conservative.

Check bloat:

SELECT schemaname, relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

If dead_pct exceeds 20% on large tables, tune autovacuum:

autovacuum_vacuum_scale_factor = 0.02    # trigger at 2% dead instead of 20%
autovacuum_max_workers = 5               # default 3
autovacuum_naptime = 15s                 # default 1min

Per-table override for high-churn tables:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01);

Never run VACUUM FULL during business hours – it takes an ACCESS EXCLUSIVE lock, blocking all reads and writes.

Backward PostgreSQL Debugging PostgreSQL Replication Forward