PostgreSQL Performance Tuning: Complete Optimization Guide

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123
AND created_at > '2022-01-01';

# postgresql.conf
# Shared memory buffer
shared_buffers = 25% of RAM (e.g., 4GB for 16GB RAM)
# Work memory per operation
work_mem = 64MB
# Maintenance operations
maintenance_work_mem = 1GB
# Effective cache size (OS cache estimate)
effective_cache_size = 75% of RAM

# Maximum connections
max_connections = 200
# Consider using PgBouncer for connection pooling

# WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

CREATE INDEX idx_orders_customer
ON orders (customer_id);

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC);

CREATE INDEX idx_users_metadata
ON users USING GIN (metadata);

-- Check index usage
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes;
-- Reindex
REINDEX INDEX idx_orders_customer;
-- Remove unused indexes
DROP INDEX unused_index;

-- Slow
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE active);
-- Faster
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.id = o.customer_id AND c.active
);

-- Bad
SELECT * FROM orders;
-- Good
SELECT id, customer_id, total FROM orders;

-- With cursor-based pagination
SELECT * FROM orders
WHERE id > :last_id
ORDER BY id
LIMIT 20;

-- Regular vacuum
VACUUM ANALYZE orders;
-- Full vacuum (blocks table)
VACUUM FULL orders;

autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

CREATE TABLE orders (
  id SERIAL,
  created_at TIMESTAMP,
  ...
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2022
PARTITION OF orders
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

[databases]
mydb = host=localhost dbname=mydb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;