PostgreSQL 17 Performance Tuning: 20 Settings That Transformed Our 500GB Database

# /etc/postgresql/17/main/postgresql.conf
# Our server: 64GB RAM, 16 vCPU, NVMe SSD

# --- MEMORY ---
# shared_buffers: PostgreSQL's own cache. 
# Rule: 25% of RAM for dedicated DB servers
shared_buffers = 16GB                    # was 128MB (default)

# effective_cache_size: How much OS cache PostgreSQL can assume exists
# Rule: 50-75% of total RAM
effective_cache_size = 48GB             # was 4GB

# work_mem: Per-sort, per-hash-join memory allocation
# CAREFUL: This multiplies by (max_connections * operations per query)
# Rule: (RAM - shared_buffers) / (max_connections * 2-4)
work_mem = 64MB                          # was 4MB

# maintenance_work_mem: For VACUUM, CREATE INDEX, ALTER TABLE
# Rule: 5-10% of RAM, at least 1GB for large databases
maintenance_work_mem = 3GB              # was 64MB

# effective_io_concurrency: Parallel I/O requests for bitmap heap scans
# Rule: 200 for SSDs, 2-4 for spinning disks
effective_io_concurrency = 200          # was 1

# --- WRITE-AHEAD LOG ---
# wal_buffers: WAL write buffer
wal_buffers = 64MB                       # was -1 (auto = 3% shared_buffers = tiny)

# checkpoint_completion_target: Spread checkpoint writes over this fraction
checkpoint_completion_target = 0.9      # was 0.5 (caused I/O spikes)

# max_wal_size: Maximum WAL size before forcing checkpoint
max_wal_size = 4GB                       # was 1GB (caused too-frequent checkpoints)

# --- PARALLELISM ---
# max_worker_processes: Total background workers
max_worker_processes = 16               # was 8 (match vCPU count)

# max_parallel_workers_per_gather: Parallel query workers per query
max_parallel_workers_per_gather = 8     # was 2

# max_parallel_workers: Total parallel workers (subset of max_worker_processes)
max_parallel_workers = 16               # was 8

# max_parallel_maintenance_workers: For parallel index builds
max_parallel_maintenance_workers = 4    # was 2

# --- CONNECTIONS ---
# Use PgBouncer in front of PostgreSQL instead of increasing max_connections
max_connections = 200                    # was 100 — but we also added PgBouncer

# --- QUERY PLANNER ---
# random_page_cost: Cost of a random disk page (lower = prefer index scans)
# Rule: 1.1 for SSDs (vs default 4.0 for spinning disks)
random_page_cost = 1.1                  # was 4.0 — this single change improved 40% of queries!

# seq_page_cost: Cost of a sequential disk read (keep at 1.0)
seq_page_cost = 1.0                     # no change

# cpu_tuple_cost: Hint to planner about tuple processing cost
cpu_tuple_cost = 0.01                   # was 0.01 (no change needed)

# --- STATISTICS ---
# default_statistics_target: Number of histogram buckets per column
# Higher = better query plans for skewed data distributions
default_statistics_target = 250        # was 100

# --- LOGGING (for analysis, not production overhead) ---
log_min_duration_statement = 500       # Log queries >500ms
log_checkpoints = on
log_lock_waits = on
log_temp_files = 10MB                   # Log when temp files exceed 10MB

-- Check dead tuple accumulation per table
SELECT 
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Our output showed 40M+ dead tuples in top tables, 60%+ dead ratio
-- This means table scans were reading 3x more data than necessary

-- postgresql.conf: Tune autovacuum globally
autovacuum_max_workers = 6              -- was 3 (more parallel vacuum workers)
autovacuum_vacuum_cost_delay = 2ms      -- was 20ms (faster vacuuming)
autovacuum_vacuum_cost_limit = 800      -- was 200 (more I/O budget per vacuum)
autovacuum_vacuum_scale_factor = 0.01   -- was 0.2 (vacuum when 1% dead, not 20%)
autovacuum_analyze_scale_factor = 0.005 -- was 0.1 (analyze more frequently)

-- Per-table override for highest-churn tables
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 1000,
  fillfactor = 70    -- Leave 30% free space for HOT updates
);

ALTER TABLE user_sessions SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 2
);

-- Run manual VACUUM ANALYZE after changing settings
VACUUM ANALYZE events;
VACUUM ANALYZE user_sessions;

-- Find tables with sequential scans that should have indexes
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND seq_tup_read / NULLIF(seq_scan, 0) > 1000  -- Only large sequential scans
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Find unused indexes (wasting write performance and space)
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

-- 1. Covering index (index-only scan — reads no heap pages)
-- Before: index on user_id alone, still needed heap fetch for email
CREATE INDEX CONCURRENTLY idx_users_user_id 
  ON users(user_id);

-- After: covering index includes all columns needed by common queries
CREATE INDEX CONCURRENTLY idx_users_covering 
  ON users(user_id) INCLUDE (email, name, status, created_at);

-- 2. Partial index (smaller, faster for filtered queries)
-- Before: full index on all orders
CREATE INDEX idx_orders_status ON orders(status);

-- After: only index active orders (90% of queries filter on active)
CREATE INDEX CONCURRENTLY idx_orders_active 
  ON orders(created_at, customer_id) 
  WHERE status = 'active';

-- 3. Expression index (for computed column queries)
-- Query: WHERE LOWER(email) = $1
CREATE INDEX CONCURRENTLY idx_users_email_lower 
  ON users(LOWER(email));

-- 4. BRIN index for time-series tables (100x smaller than B-tree)
-- For append-only tables with sequential timestamps
CREATE INDEX CONCURRENTLY idx_events_created_brin 
  ON events USING BRIN (created_at) 
  WITH (pages_per_range = 64);

-- 5. GIN index for JSONB columns
CREATE INDEX CONCURRENTLY idx_metadata_gin 
  ON events USING GIN (metadata jsonb_path_ops);

-- Build indexes in parallel (PostgreSQL 17)
SET max_parallel_maintenance_workers = 7;
CREATE INDEX CONCURRENTLY idx_large_table_composite 
  ON large_table(tenant_id, created_at, status);

-- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for production queries
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.tenant_id = 'abc123'
  AND u.status = 'active'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

-- Look for:
-- "Rows Removed by Filter: LARGE_NUMBER" → add an index
-- "Seq Scan on large_table" → missing index
-- "Hash Join  (cost=... rows=1 ...)" with actual rows=10000 → stale statistics
-- "Shared Hit Blocks=0, Read Blocks=LARGE" → cold cache or bloated tables

-- Run ANALYZE to update statistics after bulk data changes
ANALYZE VERBOSE users;
ANALYZE VERBOSE orders;

-- ANTI-PATTERN 1: SELECT * when you need 3 columns
-- Before: 800ms (fetching 40 columns, 2MB per result)
SELECT * FROM events WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 100;

-- After: 45ms (covering index, only 3 columns, index-only scan)
SELECT id, event_type, created_at 
FROM events WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 100;

-- ANTI-PATTERN 2: N+1 queries (the most common killer)
-- Before: 1 query to get users + 1 query per user for orders = 1001 queries
-- After: single query with lateral join
SELECT u.id, u.email, recent_orders.data
FROM users u
CROSS JOIN LATERAL (
  SELECT json_agg(o.*) as data
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 5
) recent_orders
WHERE u.tenant_id = $1;

-- ANTI-PATTERN 3: LIKE with leading wildcard (can't use B-tree index)
-- Before: full table scan every time
SELECT * FROM products WHERE name LIKE '%widget%';  

-- After: pg_trgm GIN index for LIKE anywhere
CREATE EXTENSION pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm 
  ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name LIKE '%widget%';  -- Now uses index!

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling: connection returned after each transaction
# NOT session pooling (can't use SET, LISTEN/NOTIFY with transaction mode)
pool_mode = transaction
max_client_conn = 1000      # Max clients connecting to PgBouncer
default_pool_size = 25      # Actual connections to PostgreSQL
reserve_pool_size = 5       # Emergency connections
reserve_pool_timeout = 3    # Seconds before using reserve pool

# PgBouncer stats: connect on port 6432 with user pgbouncer
# SHOW POOLS; SHOW CLIENTS; SHOW SERVERS;