PostgreSQL Performance Tuning: From Slow Queries to Snappy Responses

1
2
3
4
5
6

-- Log queries slower than 500ms
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

-- Check current setting
SHOW log_min_duration_statement;

 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

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total time
SELECT 
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percent,
    substring(query, 1, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with high average time (potential optimization targets)
SELECT 
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    substring(query, 1, 100)
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Reset statistics (after optimization)
SELECT pg_stat_statements_reset();

1

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

1
2
3
4
5

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.name 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01';

1
2
3
4
5
6
7
8
9

-- Single column
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite (order matters!)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

-- Query uses index: WHERE customer_id = ? AND created_at > ?
-- Query uses index: WHERE customer_id = ?
-- Query DOESN'T use index: WHERE created_at > ?  (leading column missing)

1
2
3
4
5

-- Index only active orders
CREATE INDEX idx_orders_active ON orders(customer_id) 
WHERE status = 'active';

-- Much smaller index, faster queries on active orders

1
2
3
4
5

-- For case-insensitive searches
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Query must match exactly
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

1
2
3
4
5
6

-- Include columns in the index
CREATE INDEX idx_orders_covering ON orders(customer_id) 
INCLUDE (total, status);

-- This query can be satisfied entirely from the index
SELECT customer_id, total, status FROM orders WHERE customer_id = 123;

1
2
3
4
5

-- Bad: fetches all columns
SELECT * FROM orders WHERE customer_id = 123;

-- Good: fetch only needed columns
SELECT id, total, status FROM orders WHERE customer_id = 123;

1
2
3
4
5
6

-- Bad: counts all matching rows
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
FROM orders WHERE customer_id = 123;

-- Good: stops at first match
SELECT EXISTS(SELECT 1 FROM orders WHERE customer_id = 123);

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

-- Bad: many round trips
INSERT INTO logs (message) VALUES ('msg1');
INSERT INTO logs (message) VALUES ('msg2');
INSERT INTO logs (message) VALUES ('msg3');

-- Good: single statement
INSERT INTO logs (message) VALUES 
    ('msg1'),
    ('msg2'),
    ('msg3');

-- Or use COPY for bulk loads
COPY logs (message) FROM '/tmp/data.csv' WITH CSV;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- CTEs are optimization barriers before PostgreSQL 12
-- In 12+, they can be inlined

-- Force materialization when needed
WITH order_totals AS MATERIALIZED (
    SELECT customer_id, SUM(total) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM order_totals WHERE total > 1000;

1
2
3
4
5
6
7
8

-- Bad: OFFSET scans all previous rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Good: keyset pagination (remember last seen ID)
SELECT * FROM orders 
WHERE id > 12345  -- last_seen_id
ORDER BY id 
LIMIT 20;

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

-- shared_buffers: 25% of RAM for dedicated server
ALTER SYSTEM SET shared_buffers = '4GB';

-- effective_cache_size: 50-75% of RAM (hint for planner)
ALTER SYSTEM SET effective_cache_size = '12GB';

-- work_mem: per-operation memory (sorts, hashes)
-- Be careful: can multiply with parallel queries
ALTER SYSTEM SET work_mem = '256MB';

-- maintenance_work_mem: for VACUUM, CREATE INDEX
ALTER SYSTEM SET maintenance_work_mem = '1GB';

1
2
3
4

-- Max connections (lower is often better, use connection pooling)
ALTER SYSTEM SET max_connections = 200;

-- Use PgBouncer or similar for connection pooling

1
2
3
4
5

-- wal_buffers: usually auto-tuned
ALTER SYSTEM SET wal_buffers = '64MB';

-- checkpoint_completion_target: spread checkpoint I/O
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

1
2
3
4
5

-- Enable parallel queries (default in modern PostgreSQL)
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;
ALTER SYSTEM SET parallel_setup_cost = 100;

1
2
3

-- Some need restart, some just reload
SELECT pg_reload_conf();
-- Or: sudo systemctl restart postgresql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Manual vacuum
VACUUM VERBOSE orders;

-- Vacuum and reclaim space to OS
VACUUM FULL orders;  -- Locks table, use carefully

-- Update statistics for query planner
ANALYZE orders;

-- Both
VACUUM ANALYZE orders;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Check autovacuum activity
SELECT schemaname, relname, last_vacuum, last_autovacuum, 
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

-- Tables needing vacuum
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

1
2
3
4
5

-- More aggressive vacuum for high-churn tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

1
2
3
4
5

SELECT pid, now() - pg_stat_activity.query_start AS duration, 
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle';

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

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;

1
2
3
4
5
6
7

SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

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

-- Unused indexes (candidates for removal)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index size
SELECT indexrelname, 
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

1
2
3
4
5
6
7
8

SELECT 
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(sum(heap_blks_hit) * 100.0 / 
          nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS ratio
FROM pg_statio_user_tables;

-- Should be > 99% for a well-tuned database