PostgreSQL is reliable and full-featured, but getting good performance out of it requires understanding how it works internally. This guide covers configuration, indexing, query tuning, connection pooling and maintenance for production databases.
Understanding PostgreSQL architecture
Tuning makes more sense once you know how PostgreSQL is put together:
Key components
- Shared buffers: PostgreSQL's main data cache
- WAL (write-ahead log): the transaction log
- Background writer: writes dirty pages to disk
- Vacuum: cleans up dead tuples
Configuration tuning
Memory configuration
Memory settings often make the biggest difference:
-- postgresql.conf settings
shared_buffers = '256MB' # 25% of RAM for dedicated servers
effective_cache_size = '1GB' # Total available RAM for caching
work_mem = '4MB' # Per-operation memory
maintenance_work_mem = '64MB' # Maintenance operations memory
Connection and query settings
max_connections = 100 # Adjust based on your workload
random_page_cost = 1.1 # For SSD storage
effective_io_concurrency = 200 # For SSD storage
checkpoint_completion_target = 0.9 # Spread checkpoint writes
wal_buffers = '16MB' # WAL buffer size
Query optimization
Understanding EXPLAIN
EXPLAIN shows how PostgreSQL runs a query:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;
Key metrics to watch
- Execution time: total time to run the query
- Shared hit: data found in shared buffers
- Shared read: data read from disk
- Planning time: time spent building the execution plan
Advanced indexing strategies
B-Tree indexes (default)
The default and most common index type:
CREATE INDEX idx_users_created_at ON users (created_at);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
Partial indexes
Index only part of a table to save space and speed up lookups:
CREATE INDEX idx_active_users
ON users (email)
WHERE status = 'active';
Expression indexes
Index a computed value:
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
GIN and GiST indexes
For JSONB, full-text search and other complex data types:
-- For JSONB data
CREATE INDEX idx_user_preferences_gin
ON users USING gin (preferences);
-- For full-text search
CREATE INDEX idx_articles_fts
ON articles USING gin (to_tsvector('english', content));
Connection pooling
Busy applications need a connection pooler:
PgBouncer configuration
# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
pool_mode = transaction
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 100
default_pool_size = 20
Connection pool sizing
A rough starting point:
- CPU cores × 2 for CPU-intensive workloads
- CPU cores × 5-10 for I/O intensive workloads
Query performance patterns
Efficient pagination
Avoid large OFFSET values; paginate on a key instead:
-- Bad: Using OFFSET
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;
-- Good: Using cursor-based pagination
SELECT * FROM orders
WHERE id > $last_seen_id
ORDER BY id LIMIT 10;
Batch processing
Process large datasets in chunks:
-- Process records in batches
DO $$
DECLARE
batch_size INT := 1000;
processed INT := 0;
BEGIN
LOOP
UPDATE orders
SET status = 'processed'
WHERE status = 'pending'
AND id IN (
SELECT id FROM orders
WHERE status = 'pending'
LIMIT batch_size
);
GET DIAGNOSTICS processed = ROW_COUNT;
EXIT WHEN processed = 0;
COMMIT;
PERFORM pg_sleep(0.1); -- Brief pause
END LOOP;
END $$;
Monitoring and maintenance
Essential monitoring queries
Current activity
SELECT pid, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';
Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND (now() - pg_stat_activity.query_start) > interval '1 minute';
Index usage
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;
Regular maintenance
VACUUM and ANALYZE
-- Manual vacuum
VACUUM ANALYZE users;
-- Check for bloat
SELECT schemaname, relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS size,
n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
Advanced performance techniques
Partitioning
For large tables, partitioning can significantly improve performance:
-- Range partitioning by date
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
customer_id INT,
amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);
-- Create partitions
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
Materialized views
For complex aggregations:
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT
u.id,
u.name,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent,
MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Refresh periodically
REFRESH MATERIALIZED VIEW user_order_stats;
Common performance anti-patterns
Avoid these mistakes
- N+1 queries: use a JOIN instead of one query per row
SELECT *: select only the columns you need- Unnecessary ORDER BY: remove it when the order does not matter
- Missing WHERE clauses: filter data as early as possible
- Inefficient EXISTS: use a JOIN where it fits better
Example optimization
-- Bad: N+1 query pattern
SELECT * FROM users;
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;
-- Good: Single query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
Performance testing
Load testing tools
- pgbench: the benchmarking tool that ships with PostgreSQL
- Apache JMeter: application performance testing
- Artillery: a load testing toolkit
pgbench example
# Initialize test database
pgbench -i -s 10 mydb
# Run benchmark
pgbench -c 10 -j 2 -t 1000 mydb
Troubleshooting common issues
Slow queries
- Check execution plans with EXPLAIN ANALYZE
- Look for missing indexes
- Consider query rewriting
- Check statistics are up to date
High CPU usage
- Identify expensive queries
- Check for excessive sorting/grouping
- Look for sequential scans on large tables
- Consider adding appropriate indexes
Memory issues
- Monitor shared buffer hit ratio
- Check for excessive work_mem usage
- Look for memory-intensive operations
- Consider adjusting configuration
Performance monitoring dashboard
A useful monitoring setup shows:
- Query performance metrics
- Connection pool statistics
- Index usage statistics
- Vacuum and maintenance status
Conclusion
PostgreSQL tuning is never finished. Learn your workload and access patterns, configure for your hardware, index for the queries you actually run, and keep monitoring and maintaining the database as data grows.
Measure first, change one thing at a time, and measure again.
Performance checklist
- Memory settings optimized for hardware
- Appropriate indexes created and maintained
- Connection pooling implemented
- Regular VACUUM and ANALYZE scheduled
- Query performance monitoring in place
- Slow query log analysis automated
- Backup and recovery tested
Need help with PostgreSQL performance? Get in touch for a database health check.
