PostgreSQL Advanced Performance Tuning

PostgreSQL is incredibly powerful, but poor configuration and missing indexes can tank performance. This guide covers advanced techniques for high-traffic production systems.

Understanding EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

Key metrics:

• Seq Scan on large tables = missing index
• Buffers: hit = data from cache (good)
• Buffers: read = data from disk (slow)
• actual rows vs rows = planner accuracy

Index Strategies

-- Basic B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_status_created 
ON orders(status, created_at DESC);

-- Partial index (smaller, faster)
CREATE INDEX idx_orders_pending 
ON orders(created_at)
WHERE status = 'pending';

-- Expression index
CREATE INDEX idx_users_lower_email 
ON users(LOWER(email));

-- Covering index (avoids heap fetch)
CREATE INDEX idx_orders_user_covering
ON orders(user_id) INCLUDE (total, status, created_at);

Index Types

-- Hash index (equality only)
CREATE INDEX idx_sessions_token 
ON sessions USING HASH (token);

-- GIN for arrays and JSONB
CREATE INDEX idx_products_tags 
ON products USING GIN (tags);

CREATE INDEX idx_users_metadata 
ON users USING GIN (metadata jsonb_path_ops);

-- BRIN for time-series (tiny size)
CREATE INDEX idx_events_created 
ON events USING BRIN (created_at) 
WITH (pages_per_range = 128);

Query Optimization

-- EXISTS vs IN for subqueries
-- GOOD: EXISTS short-circuits
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id AND o.status = 'completed'
);

-- Window functions vs subqueries
-- GOOD: single pass
SELECT name, dept,
  AVG(salary) OVER (PARTITION BY dept) as dept_avg
FROM employees;

-- Avoid functions on indexed columns
-- BAD: cannot use index
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- GOOD: use expression index
SELECT * FROM users WHERE email = LOWER('ALICE@EXAMPLE.COM');

Table Partitioning

CREATE TABLE orders (
  id BIGSERIAL,
  user_id INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  total DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 
  PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Partition pruning: only scans relevant partitions
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Key Configuration Settings

# postgresql.conf
shared_buffers = 8GB          # 25% of RAM
effective_cache_size = 24GB   # 75% of RAM
work_mem = 64MB               # Per-query sort/hash
random_page_cost = 1.1        # For SSDs
max_parallel_workers_per_gather = 4

Monitoring

-- Find slow queries
SELECT query,
  calls,
  total_exec_time / calls AS avg_ms,
  100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 10;

-- Find missing indexes
SELECT schemaname, tablename, seq_scan, n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan DESC;

Summary

PostgreSQL performance optimization process:

1. Identify slow queries with pg_stat_statements
2. Analyze query plans with EXPLAIN (ANALYZE, BUFFERS)
3. Add targeted indexes based on query patterns
4. Tune memory settings for your workload
5. Use partitioning for time-series data
6. Deploy PgBouncer for connection pooling