PostgreSQL Performance: The Basics

Before reaching for complex solutions, these fundamentals solve most PostgreSQL performance problems.

EXPLAIN ANALYZE

Always start here:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Look for:

• Seq Scan on large tables (usually means missing index)
• High actual rows vs estimated rows (stale statistics)
• Nested Loop with large outer tables (consider Hash Join)

Missing Indexes

The most common performance fix:

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Create targeted index
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Use CONCURRENTLY to avoid locking the table during creation.

Connection Pooling

PostgreSQL creates a new process per connection. Without pooling, 100 connections means 100 processes.

Use PgBouncer:

[databases]
mydb = host=127.0.0.1 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

Vacuum and Analyze

PostgreSQL needs regular maintenance:

-- Update statistics (helps query planner)
ANALYZE orders;

-- Reclaim dead tuple space
VACUUM ANALYZE orders;

-- Check if autovacuum is keeping up
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Quick Wins

Start with these before diving into partitioning or read replicas.