PostgreSQL Performance Tuning: A Practical Guide

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1 second
SELECT pg_reload_conf();

-- Use pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_seconds,
    mean_exec_time as avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Top queries by calls
SELECT
    query,
    calls,
    total_exec_time / calls as avg_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- With actual execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.email = 'user@example.com';

-- Key things to look for:
-- - Seq Scan on large tables (missing index?)
-- - High "actual rows" vs "planned rows" (stale statistics?)
-- - Nested Loop with many iterations (missing index on join?)
-- - Sort operations (can index help?)

explain_key_indicators:
  seq_scan:
    meaning: Full table scan
    concern: Slow on large tables
    fix: Add appropriate index

  index_scan:
    meaning: Using index
    good: Usually efficient
    watch: Very wide index ranges

  bitmap_heap_scan:
    meaning: Index scan + heap lookup
    when: Multiple conditions, moderate selectivity
    normal: For OR conditions or partial matches

  nested_loop:
    meaning: For each outer row, scan inner
    concern: High row count in outer
    fix: Ensure inner side has index

  hash_join:
    meaning: Build hash table, probe
    when: Larger datasets
    memory: Uses work_mem

  sort:
    meaning: Sorting required
    concern: "external merge Disk" = spilling to disk
    fix: Increase work_mem or add index

-- Tables with high sequential scan ratios
SELECT
    schemaname || '.' || relname as table,
    seq_scan,
    seq_tup_read,
    idx_scan,
    CASE
        WHEN seq_scan > 0 THEN seq_tup_read / seq_scan
        ELSE 0
    END as avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Suggested indexes from pg_qualstats (extension)
CREATE EXTENSION IF NOT EXISTS pg_qualstats;
SELECT * FROM pg_qualstats_suggest_indexes();

-- B-tree (default, most common)
CREATE INDEX idx_orders_customer ON orders(customer_id);

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

-- Composite index (order matters!)
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
-- Good for: WHERE customer_id = ? AND status = ?
-- Good for: WHERE customer_id = ?
-- NOT good for: WHERE status = ?

-- Covering index (index-only scans)
CREATE INDEX idx_orders_covering
ON orders(customer_id)
INCLUDE (status, total);
-- Can return status, total without heap lookup

-- GIN for full-text search
CREATE INDEX idx_products_search
ON products USING gin(to_tsvector('english', description));

-- Find unused indexes (waste of space and write overhead)
SELECT
    schemaname || '.' || indexrelname as index,
    idx_scan as scans,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

# Memory settings
shared_buffers:
  purpose: PostgreSQL cache
  recommendation: 25% of RAM
  example: "4GB"  # For 16GB RAM server

effective_cache_size:
  purpose: Planner's estimate of available cache
  recommendation: 50-75% of RAM
  example: "12GB"  # For 16GB RAM server

work_mem:
  purpose: Per-operation memory for sorts, hashes
  recommendation: Depends on concurrent queries
  calculation: "(RAM - shared_buffers) / max_connections / 4"
  example: "64MB"
  caution: Multiplied by concurrent operations

maintenance_work_mem:
  purpose: Memory for VACUUM, CREATE INDEX
  recommendation: 512MB - 2GB
  example: "1GB"

# Connection settings
max_connections:
  recommendation: Keep low, use connection pooling
  typical: 100-200
  with_pgbouncer: Can be lower

# WAL settings
wal_buffers:
  recommendation: 64MB
  default: -1 (auto-tuned)

checkpoint_completion_target:
  recommendation: 0.9
  purpose: Spread checkpoint writes

# Query planner
random_page_cost:
  default: 4.0
  ssd: 1.1-1.5
  purpose: Cost estimate for random I/O

effective_io_concurrency:
  default: 1
  ssd: 200
  purpose: Concurrent I/O operations

-- View current setting
SHOW shared_buffers;
SHOW work_mem;

-- Change for session
SET work_mem = '256MB';

-- Change permanently (requires restart for some)
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '64MB';

-- Reload config (for parameters that don't require restart)
SELECT pg_reload_conf();

-- Check which need restart
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

connection_issues:
  too_many_connections:
    - Each connection uses memory
    - Context switching overhead
    - Locks compete

  short_lived_connections:
    - Connection overhead is high
    - SSL handshake is slow
    - Authentication cost

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0

auth_type = hba
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction  # Best for most apps
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

# Timeouts
server_idle_timeout = 600
client_idle_timeout = 0

pool_modes:
  session:
    description: Connection held for entire session
    use_when: Application manages transactions
    limitation: No connection sharing

  transaction:
    description: Connection held for transaction only
    use_when: Most web applications
    benefit: High connection reuse
    limitation: No session-level state

  statement:
    description: Connection per statement
    use_when: Rarely
    limitation: No multi-statement transactions

-- Check tables needing vacuum
SELECT
    schemaname || '.' || relname as table,
    n_dead_tup,
    n_live_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Manual vacuum
VACUUM (VERBOSE) orders;

-- Vacuum and analyze
VACUUM ANALYZE orders;

-- Full vacuum (rewrites table, locks it)
-- Only when really necessary
VACUUM FULL orders;

autovacuum_settings:
  autovacuum_vacuum_threshold:
    default: 50
    purpose: Minimum tuples before vacuum

  autovacuum_vacuum_scale_factor:
    default: 0.2
    meaning: Vacuum when 20% of table is dead
    for_large_tables: Consider lower (0.01)

  autovacuum_analyze_threshold:
    default: 50

  autovacuum_analyze_scale_factor:
    default: 0.1

  autovacuum_naptime:
    default: 1min
    purpose: Time between autovacuum runs

-- Per-table autovacuum settings for large tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

-- Current activity
SELECT
    pid,
    age(clock_timestamp(), query_start) as duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start;

-- Lock contention
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;