Tune PostgreSQL Performance

• shared_buffers defaults to 128 MB — set 25 % RAM (4 GB RAM → 1 GB)
• work_mem affects JOIN/ORDER — too low = disk sort, too high = OOM
• pg_stat_statements: CREATE EXTENSION + 20 MB RAM, stats for every query
• pgbouncer in front of Postgres: transaction pool, 10k clients → 100 connections
• EXPLAIN (ANALYZE, BUFFERS) shows the real plan + disk reads

-- pgbench benchmark before/after tuning
$ pgbench -c 10 -T 60 mydb

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

-- Index + verify
CREATE INDEX CONCURRENTLY ON orders (user_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 1 LIMIT 20;

• pg_stat_statements
• Postgres backups