sawrus/db-performance

Skill: Database Performance

Expertise: EXPLAIN ANALYZE, index design (partial/covering), pg_stat_statements, autovacuum tuning, PgBouncer sizing.

When to load

When investigating slow queries, designing indexes, tuning PostgreSQL config, or sizing PgBouncer pools.

Query Analysis with pg_stat_statements

-- Enable (add to postgresql.conf, then restart or reload)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  left(query, 120) AS query_snippet,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top queries by mean execution time (find worst-per-call)
SELECT
  left(query, 120),
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(rows::numeric / calls, 1)   AS rows_per_call
FROM pg_stat_statements
WHERE calls > 100   -- ignore one-offs
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Queries with high I/O (missing index candidates)
SELECT
  left(query, 120),
  calls,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  shared_blks_read + shared_blks_hit AS total_blocks,
  round(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE calls > 50
ORDER BY shared_blks_read DESC
LIMIT 10;

-- Reset stats after tuning
SELECT pg_stat_statements_reset();

EXPLAIN ANALYZE (reading execution plans)

-- Always use ANALYZE BUFFERS for real cost data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days';

/* Reading the output:
   Seq Scan          → full table scan, may need index
   Index Scan        → good, using index
   Index Only Scan   → best, covering index (no heap access)
   Nested Loop       → OK for small datasets; bad for large
   Hash Join         → good for large joins
   Merge Join        → good for pre-sorted data

   Key numbers:
   - actual time=START..END ms per row
   - rows=N vs rows=N (estimated vs actual — big diff = stale stats)
   - Buffers: hit=N read=N  (high 'read' = cache miss → index opportunity)
*/

Index Design Patterns

-- Standard B-tree (equality and range queries)
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders(status, created_at)
  WHERE status IN ('pending', 'processing');   -- partial index — smaller, faster

-- Covering index (index-only scan — no heap access)
CREATE INDEX CONCURRENTLY idx_orders_customer_covering
  ON orders(customer_id, created_at)
  INCLUDE (status, total_amount);   -- INCLUDE avoids heap fetch for these columns

-- Expression index (for function-based queries)
CREATE INDEX CONCURRENTLY idx_users_email_lower
  ON users(lower(email));           -- for: WHERE lower(email) = lower($1)

-- JSON/JSONB index
CREATE INDEX CONCURRENTLY idx_events_data_type
  ON events USING GIN (data jsonb_path_ops);  -- for: WHERE data @> '{"type":"click"}'

-- Multicolumn order: selectivity matters
-- For: WHERE status='active' AND created_at > X
-- ✅ (status, created_at) — filter on status first (low cardinality OK as prefix)
-- ❌ (created_at, status) — date range first is wide; wastes I/O

Identifying Missing Indexes

-- Tables with high sequential scans (candidates for indexing)
SELECT
  schemaname || '.' || relname AS table,
  seq_scan,
  seq_tup_read,
  idx_scan,
  round(seq_scan::numeric / NULLIF(seq_scan + idx_scan, 0) * 100, 1) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Unused indexes (wasting write overhead)
SELECT
  schemaname || '.' || tablename AS table,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique          -- don't drop unique constraints
  AND indpred IS NULL          -- don't drop partial indexes without analysis
ORDER BY pg_relation_size(indexrelid) DESC;

PostgreSQL Configuration Tuning

-- Key parameters for a 16GB RAM server
ALTER SYSTEM SET shared_buffers = '4GB';          -- 25% of RAM
ALTER SYSTEM SET effective_cache_size = '12GB';   -- 75% of RAM
ALTER SYSTEM SET work_mem = '64MB';               -- per sort/hash; set conservatively
ALTER SYSTEM SET maintenance_work_mem = '1GB';    -- for VACUUM, CREATE INDEX
ALTER SYSTEM SET max_worker_processes = 8;
ALTER SYSTEM SET max_parallel_workers = 4;
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;

-- WAL tuning (for high-write workloads)
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '4GB';

SELECT pg_reload_conf();

PgBouncer Pool Sizing

Formula:
  max_server_connections = min(max_connections_pg - 5, available_connections)
  default_pool_size = max_server_connections / number_of_services
  max_client_conn = default_pool_size × 10   (clients can queue)

Example (max_connections=200, 5 services):
  default_pool_size = (200 - 5) / 5 = 39 → set to 40
  max_client_conn = 400
  reserve_pool_size = 5 (emergency burst)

# pgbouncer.ini
[pgbouncer]
pool_mode = transaction          # best for stateless apps
max_client_conn = 500            # total client connections
default_pool_size = 40           # server connections per database+user
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
client_idle_timeout = 0
query_wait_timeout = 30          # fail fast if no server available

Autovacuum Tuning for High-Write Tables

-- Per-table autovacuum settings for hot tables
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead rows (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- less aggressive throttling
);