PostgreSQL Performance Tuning: From Slow Queries to a Database That Purrs

1
2
3
4
5

-- Check if it's already loaded
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Install it (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1
2
3
4
5
6

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000        # Track up to 10k distinct queries
pg_stat_statements.track = all        # Track top + nested queries
pg_stat_statements.track_io_timing = on  # Include I/O wait time (requires pg >= 14)
pg_stat_statements.save = on          # Persist stats across restarts

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Top 20 queries by total execution time
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Top queries by mean execution time (slow individual calls)
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows / calls AS avg_rows,
    left(query, 120) AS query_snippet
FROM pg_stat_statements
WHERE calls > 10  -- Filter noise
ORDER BY mean_exec_time DESC
LIMIT 20;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Queries doing the most I/O (shared block reads = cache misses)
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit /
        NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

1

SELECT pg_stat_statements_reset();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- What's running right now?
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '1 second'
ORDER BY duration DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Find blocking locks
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Table-level lock overview
SELECT
    pid,
    relation::regclass AS table,
    mode,
    granted,
    left(query, 80) AS query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation IS NOT NULL
ORDER BY granted, pid;

1
2
3
4
5
6
7
8

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 10;

1
2
3

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'processed' WHERE created_at < now() - interval '1 year';
ROLLBACK;

1

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Simple index on a foreign key (almost always missing and always needed)
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index — order matters
-- Supports: WHERE status = ? AND created_at > ?
-- Also supports: WHERE status = ?
-- Does NOT efficiently support: WHERE created_at > ? (alone)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Include columns to create a covering index (avoids heap fetch)
-- The query can be answered entirely from the index, no table access needed
CREATE INDEX idx_orders_user_covering ON orders (user_id)
INCLUDE (status, total_amount, created_at);

1
2
3
4
5

-- Verify index-only scan is happening
EXPLAIN SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 12345;
-- Look for "Index Only Scan" in the plan

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Only index unprocessed orders (if 95% are 'completed', this index is tiny)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Only index non-deleted rows
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

-- Useful for queues: index only unprocessed items
CREATE INDEX idx_jobs_unprocessed ON background_jobs (priority DESC, created_at)
WHERE processed_at IS NULL;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Case-insensitive email lookup
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- Queries must use the same expression to hit this index:
SELECT * FROM users WHERE lower(email) = lower('User@Example.com');

-- Index on extracted JSON field
CREATE INDEX idx_events_user ON events ((payload->>'user_id'));

-- Index on date part (group by day without scanning full timestamp)
CREATE INDEX idx_orders_date ON orders (date_trunc('day', created_at));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Query:
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & performance');

-- JSONB containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Query:
SELECT * FROM events WHERE payload @> '{"type": "purchase", "currency": "USD"}';

-- Array containment
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Query:
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Range type overlap queries (e.g., scheduling conflicts)
CREATE TABLE reservations (
    room_id int,
    during tstzrange
);
CREATE INDEX idx_reservations_during ON reservations USING GIST (during);

-- Find conflicting reservations
SELECT * FROM reservations
WHERE during && tstzrange('2024-03-01', '2024-03-05');

-- Trigram similarity search (fuzzy string matching)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIST (name gist_trgm_ops);

-- Find similar names
SELECT name FROM users WHERE name % 'Jon Smith' ORDER BY name <-> 'Jon Smith' LIMIT 10;

1
2
3
4

-- For an events table that's append-only and ordered by created_at
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at);
-- This index might be 1000x smaller than a B-tree on the same column
-- Trades precision for size: queries check block ranges, not individual rows

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- Indexes on a table and their usage stats
SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Unused indexes (candidates for removal)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisprimary        -- Don't list primary keys
  AND NOT indisunique         -- Don't list unique constraints
ORDER BY pg_relation_size(indexrelid) DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Find missing indexes: tables with many sequential scans on large tables
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 100        -- Many sequential scans
  AND n_live_tup > 10000   -- On non-trivial tables
ORDER BY seq_tup_read DESC
LIMIT 20;

1
2
3
4
5
6

# Bad: N+1 — 1 query for users, N queries for orders
users = db.query("SELECT * FROM users WHERE active = true")
for user in users:
    user.order_count = db.query(
        "SELECT count(*) FROM orders WHERE user_id = %s", user.id
    )[0]

1
2
3
4
5
6
7
8
9

-- Good: single query with aggregation
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.active = true
GROUP BY u.id, u.email;

1
2
3
4

# SQLAlchemy
users = session.query(User).options(
    selectinload(User.orders)
).filter(User.active == True).all()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- PostgreSQL < 12: CTEs are optimization fences (always materialized)
-- This CAN'T push the WHERE clause into the CTE scan
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > now() - interval '30 days'
)
SELECT * FROM recent_orders WHERE user_id = 12345;

-- PostgreSQL >= 12: CTEs are inlined by default unless they have side effects
-- or you use MATERIALIZED keyword

-- Force materialization (useful when CTE result is used multiple times)
WITH MATERIALIZED expensive_computation AS (
    SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id
)
SELECT u.email, ec.total
FROM users u
JOIN expensive_computation ec ON ec.user_id = u.id
WHERE ec.total > 1000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- Bad: correlated subquery to find rank within group
SELECT
    o.*,
    (SELECT count(*) FROM orders o2
     WHERE o2.user_id = o.user_id AND o2.created_at <= o.created_at) AS user_order_number
FROM orders o;

-- Good: window function
SELECT
    *,
    row_number() OVER (PARTITION BY user_id ORDER BY created_at) AS user_order_number
FROM orders;

-- Running total per user
SELECT
    user_id,
    created_at,
    amount,
    sum(amount) OVER (PARTITION BY user_id ORDER BY created_at
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_total
FROM orders;

1
2
3
4
5
6
7
8
9

-- Top 3 most recent orders per user
SELECT u.email, o.*
FROM users u
CROSS JOIN LATERAL (
    SELECT * FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

1
2
3
4
5
6
7
8
9

-- Get the most recent order per user (cleaner than a self-join or window function)
SELECT DISTINCT ON (user_id)
    user_id,
    id AS order_id,
    created_at,
    total_amount
FROM orders
ORDER BY user_id, created_at DESC;
-- Requires composite index: (user_id, created_at DESC)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# postgresql.conf

# shared_buffers: PostgreSQL's in-process cache
# Rule of thumb: 25% of total RAM
# A server with 32GB RAM:
shared_buffers = 8GB

# effective_cache_size: hint to the planner about total memory available
# for caching (includes OS page cache). Set to ~75% of RAM.
# Does NOT allocate memory — only influences query planning decisions.
effective_cache_size = 24GB

# work_mem: memory per sort/hash operation (not per query, not per connection)
# A complex query may use multiple work_mem allocations.
# Too high × many connections = OOM. Too low = spill to disk.
# Formula: (available RAM - shared_buffers) / (max_connections * 3)
# For 32GB, 8GB shared_buffers, 100 connections:
work_mem = 64MB

# maintenance_work_mem: for VACUUM, CREATE INDEX, ALTER TABLE
# Can be set high; only one or few operations run at once
maintenance_work_mem = 2GB

# huge_pages: use Linux huge pages for shared_buffers (reduces TLB pressure)
# Requires OS configuration: echo 'vm.nr_hugepages = 4096' >> /etc/sysctl.conf
huge_pages = try

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# max_wal_size: how much WAL to accumulate before forcing a checkpoint
# Too small: frequent checkpoints cause I/O spikes
# Too large: longer crash recovery time
# For a busy database: 4-16GB
max_wal_size = 4GB
min_wal_size = 1GB

# checkpoint_completion_target: spread checkpoint I/O over this fraction
# of the checkpoint interval. Higher = smoother I/O.
checkpoint_completion_target = 0.9

# wal_compression: compress WAL records (reduces I/O at cost of CPU)
wal_compression = on

# For SSDs: set to 1 for reliability; can set to 0 for replicas
# synchronous_commit = on  # default

# wal_buffers: WAL write buffer (auto-tuned since PG 9.1, usually fine)
# wal_buffers = 64MB  # Only override if you see WAL write bottlenecks

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# random_page_cost: cost of a non-sequential disk fetch
# Default: 4.0 (assumes spinning disk). For SSD: 1.1-2.0
# Lowering this makes the planner prefer index scans over seq scans
random_page_cost = 1.1

# effective_io_concurrency: how many I/O operations to issue in parallel
# For SSD: 200-300. For NVMe: higher. For HDD: 2-4.
effective_io_concurrency = 200

# parallel query settings
max_parallel_workers_per_gather = 4   # Workers per query node
max_parallel_workers = 8             # Total parallel workers (≤ max_worker_processes)
max_worker_processes = 16

# Enable parallel operations
parallel_leader_participation = on

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Increase statistics for a frequently filtered column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE events ALTER COLUMN event_type SET STATISTICS 1000;

-- Then update statistics
ANALYZE orders;
ANALYZE events;

-- Check current statistics targets
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND attstattarget != -1;  -- -1 means default

1
2
3
4
5
6
7
8
9

-- Large batch import: more maintenance memory, no synchronous commit
SET work_mem = '512MB';
SET maintenance_work_mem = '4GB';
SET synchronous_commit = off;

-- Re-creating a large index
SET maintenance_work_mem = '8GB';
CREATE INDEX CONCURRENTLY idx_events_created ON events (created_at);
RESET maintenance_work_mem;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Find tables with high dead tuple ratios (candidates for autovacuum tuning)
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- For a high-update table (e.g., user sessions, order status updates)
ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- Vacuum when 1% are dead (not 20%)
    autovacuum_vacuum_threshold = 100,         -- Or 100 dead tuples, whichever is less
    autovacuum_analyze_scale_factor = 0.005,  -- Analyze more frequently
    autovacuum_vacuum_cost_delay = 2           -- Less throttling (default: 2ms, was 20ms before PG 13)
);

-- For a large, mostly-read table
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 5% threshold is fine
    autovacuum_freeze_max_age = 500000000      -- Allow longer before anti-wraparound vacuum
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# postgresql.conf

# Number of autovacuum workers
autovacuum_max_workers = 6   # Default: 3. Increase for many active tables.

# Throttling: pause between chunks of vacuum work (lower = more aggressive)
autovacuum_vacuum_cost_delay = 2ms   # Default since PG 13

# Cost budget per round before throttling pause
autovacuum_vacuum_cost_limit = 400   # Default: 200. Higher = faster vacuuming.

# Trigger thresholds (global defaults, overridden per-table above)
autovacuum_vacuum_scale_factor = 0.05   # Lower than default 0.20
autovacuum_analyze_scale_factor = 0.02  # Lower than default 0.10

# Log when autovacuum takes longer than this
log_autovacuum_min_duration = 1000  # ms; log vacuum ops over 1 second

1
2
3
4
5
6
7
8

-- Check transaction ID age — alert if any database is within 200M transactions of wraparound
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2000000000 - age(datfrozenxid) AS xid_remaining,
    pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
ORDER BY xid_age DESC;

1
2
3
4
5

-- Emergency: vacuum all tables to freeze old transactions
VACUUM FREEZE ANALYZE tablename;

-- Or the nuclear option (takes locks, use with caution):
-- VACUUM FREEZE;  -- entire database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# docker-compose.yml
services:
  pgbouncer:
    image: bitnami/pgbouncer:latest
    restart: unless-stopped
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: myapp
      POSTGRESQL_USERNAME: app_user
      POSTGRESQL_PASSWORD: ${DB_PASSWORD}
      PGBOUNCER_DATABASE: myapp
      PGBOUNCER_PORT: 6432
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: 1000     # Max app connections to PgBouncer
      PGBOUNCER_DEFAULT_POOL_SIZE: 25     # Real PG connections per database+user pair
      PGBOUNCER_MIN_POOL_SIZE: 5          # Keep at least 5 warm connections
      PGBOUNCER_RESERVE_POOL_SIZE: 5      # Extra connections for bursts
      PGBOUNCER_RESERVE_POOL_TIMEOUT: 5   # Seconds before using reserve pool
      PGBOUNCER_MAX_DB_CONNECTIONS: 50    # Total real PG connections across all pools
      PGBOUNCER_IDLE_TRANSACTION_TIMEOUT: 30  # Kill idle transactions after 30s
    ports:
      - "6432:6432"
    depends_on:
      - postgres

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# pgbouncer.ini

[databases]
; Specific database with its own pool settings
myapp = host=127.0.0.1 port=5432 dbname=myapp pool_size=30

; Wildcard: route any database name to the PostgreSQL server
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

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

pool_mode = transaction
max_client_conn = 5000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3.0

; Important: require apps to reconnect after server restart
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30

; Timeouts
query_timeout = 0           ; No query timeout (set at app level)
client_idle_timeout = 600   ; Disconnect idle app connections after 10 minutes
idle_transaction_timeout = 30  ; Kill idle-in-transaction after 30 seconds

; Admin interface
admin_users = postgres
stats_users = monitoring_user

; Logging
log_connections = 0   ; Noisy; disable in production
log_disconnections = 0
log_pooler_errors = 1

; Performance
tcp_keepalive = 1
tcp_keepcnt = 5
tcp_keepidle = 60
tcp_keepintvl = 10

1
2
3
4
5
6
7

# /etc/pgbouncer/userlist.txt
# Format: "username" "md5password_or_plaintext"
"app_user" "md5abc123..."

# Generate md5 hash:
echo -n "passwordusername" | md5sum
# Then prefix with "md5": "md5<hash>"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# Connect to the admin console
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

-- Pool status
SHOW POOLS;
-- cl_active: clients actively running queries
-- cl_waiting: clients waiting for a server connection (watch this!)
-- sv_active: server connections in use
-- sv_idle: idle server connections (available)

-- Stats
SHOW STATS;
-- total_query_count, total_xact_count, avg_query_time

-- Current clients
SHOW CLIENTS;

-- Current servers
SHOW SERVERS;

1
2
3
4
5
6
7
8
9

# docker-compose.yml addition
services:
  pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter:latest
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "postgres://monitoring_user:password@pgbouncer:6432/pgbouncer?sslmode=disable"
    ports:
      - "9127:9127"