PostgreSQL Performance Tuning Guide

PostgreSQL performance depends on proper configuration and query optimization. Here's how to tune it effectively.

Memory Configuration#

-- View current settings
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;

-- postgresql.conf recommendations

-- Shared buffers: 25% of RAM (up to 8GB)
shared_buffers = 4GB  -- For 16GB RAM server

-- Effective cache size: 75% of RAM
effective_cache_size = 12GB

-- Work memory per operation (careful with this)
work_mem = 64MB  -- Consider: work_mem * max_connections

-- Maintenance operations
maintenance_work_mem = 1GB

-- WAL buffers
wal_buffers = 64MB

Connection Settings#

-- postgresql.conf

-- Maximum connections
max_connections = 100  -- Lower than you think

-- Use connection pooling (PgBouncer) for more connections

-- Connection timeout
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

Query Analysis#

-- Enable query timing
\timing on

-- Explain query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2024-01-01';

-- Understanding EXPLAIN output
/*
Seq Scan: Full table scan (often bad for large tables)
Index Scan: Using index (good)
Index Only Scan: Data from index only (best)
Bitmap Heap Scan: Multiple index conditions
Nested Loop: Joining small result sets
Hash Join: Joining larger result sets
*/

-- Find slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Index Optimization#

-- Create appropriate indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index for common queries
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Partial index for common filters
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

-- Index for pattern matching
CREATE INDEX idx_users_email_pattern
ON users(email varchar_pattern_ops);

-- Check index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes
SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND pg_relation_size(relid) > 10000000
ORDER BY seq_tup_read DESC;

Table Maintenance#

-- Analyze table statistics
ANALYZE orders;

-- Vacuum to reclaim space
VACUUM orders;

-- Full vacuum (locks table)
VACUUM FULL orders;

-- Reindex
REINDEX INDEX idx_orders_user_id;

-- Automatic maintenance settings
-- postgresql.conf
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

-- Check table bloat
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Query Optimization#

-- Use EXISTS instead of IN for subqueries
-- ❌ Slow
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- ✓ Faster
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 100
);

-- Use LIMIT with ORDER BY
-- ❌ Fetches all rows first
SELECT * FROM orders ORDER BY created_at DESC;

-- ✓ Efficient with index
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20;

-- Avoid SELECT *
-- ❌ Fetches unnecessary columns
SELECT * FROM users WHERE id = 1;

-- ✓ Select only needed columns
SELECT id, name, email FROM users WHERE id = 1;

-- Use covering indexes
CREATE INDEX idx_orders_covering
ON orders(user_id)
INCLUDE (total, status);

-- Query uses index-only scan
SELECT total, status FROM orders WHERE user_id = 123;

Partitioning#

-- Range partitioning for time-series data
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMP NOT NULL,
    event_type TEXT,
    data JSONB
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatic partition creation (pg_partman extension)
-- Or use application logic to create partitions

-- Queries automatically use appropriate partition
SELECT * FROM events
WHERE created_at >= '2024-01-15'
AND created_at < '2024-01-20';

Monitoring Queries#

-- Active connections
SELECT
    datname,
    usename,
    client_addr,
    state,
    query_start,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Lock monitoring
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Table sizes
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

Configuration Checklist#

Memory:
✓ shared_buffers = 25% of RAM
✓ effective_cache_size = 75% of RAM
✓ work_mem based on query complexity
✓ maintenance_work_mem for maintenance

WAL:
✓ wal_buffers = 64MB
✓ checkpoint_completion_target = 0.9
✓ max_wal_size = 4GB

Query Planner:
✓ random_page_cost = 1.1 (SSD)
✓ effective_io_concurrency = 200 (SSD)
✓ default_statistics_target = 100

Connections:
✓ Use connection pooling
✓ Appropriate max_connections
✓ Statement timeout set

Best Practices#

Indexes:
✓ Index foreign keys
✓ Use partial indexes
✓ Monitor unused indexes
✓ Consider covering indexes

Queries:
✓ Use EXPLAIN ANALYZE
✓ Avoid SELECT *
✓ Use prepared statements
✓ Batch bulk operations

Maintenance:
✓ Regular VACUUM ANALYZE
✓ Monitor table bloat
✓ Archive old data
✓ Test configuration changes

PostgreSQL performance tuning requires understanding your workload. Configure memory based on available resources, create appropriate indexes for your queries, and monitor regularly. Use EXPLAIN ANALYZE to understand query behavior and pg_stat_statements to find optimization targets.