DevOps

PostgreSQL Performance Tuning: Complete Guide (2026)

2026-07-15·13 min read
#PostgreSQL#database#performance#optimization#SQL

PostgreSQL is powerful, but out-of-the-box it's tuned for a machine with 256MB of RAM running in 2004. To get real performance, you need to configure it for your hardware and workload.

This guide covers everything we've learned running PostgreSQL in production — from indexing to configuration to query optimization.

Indexing Strategies

1. Know Your Index Types

PostgreSQL offers more index types than most developers realize:

| Index Type | Use Case | Example | |------------|----------|---------| | B-tree | Default — equality and range queries | WHERE created_at > '2026-01-01' | | GIN | Full-text search, JSONB, arrays | WHERE tags @> ['python'] | | GiST | Geospatial, range types | PostGIS queries | | BRIN | Large tables with natural ordering | Time-series data | | Hash | Equality only (rarely needed) | WHERE id = 123 |

2. Composite Index Column Order

Order matters. A composite index on (a, b, c) can be used for:

  • WHERE a = 1 ✅
  • WHERE a = 1 AND b = 2 ✅
  • WHERE b = 2 AND c = 3 ❌ (cannot use the index)
  • WHERE a = 1 AND c = 3 ⚠️ (uses index for a only)

Rule: Most selective column first? NO. The rule is: column used in equality conditions first, then range conditions.

-- Good: equality first, range last
CREATE INDEX idx_orders ON orders (user_id, status, created_at);

-- Supports these efficiently:
-- WHERE user_id = 123 AND status = 'paid' AND created_at > '2026-01-01'
-- WHERE user_id = 123 AND status = 'paid'
-- WHERE user_id = 123

3. Partial Indexes

Don't index rows you'll never query:

-- Only index active users (90% of users might be inactive)
CREATE INDEX idx_active_users ON users (email) 
WHERE status = 'active';

-- Much smaller, faster to scan, faster to maintain

4. Expression Indexes

If you frequently query a computed value, index it:

-- Case-insensitive email lookup
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Now this query uses the index:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

5. Check Unused Indexes

Indexes slow down writes. Remove ones that aren't used:

SELECT 
    schemaname,
    relname AS table,
    indexrelname AS index,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

If idx_scan is 0, that index has never been used for reads. It's pure write overhead. Drop it.

Query Optimization

6. Always Use EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE user_id = 123;

What to look for:

| Indicator | Meaning | Action | |-----------|---------|--------| | Seq Scan | Full table scan | Probably needs an index | | Index Scan | Using index efficiently | Good | | Bitmap Heap Scan | Index + heap fetch | OK for many rows | | Hash Join | Building hash table | Fine for small datasets | | Nested Loop | Row-by-row join | Slow for large datasets | | Sort | Sorting in memory/disk | Add index for ORDER BY | | Materialize | Caching subquery results | May indicate expensive subquery |

7. Replace N+1 Queries

The most common performance killer:

# ❌ N+1: 1 query to get users, N queries to get orders
users = db.query("SELECT * FROM users LIMIT 100")
for user in users:
    orders = db.query(f"SELECT * FROM orders WHERE user_id = {user.id}")

-- ✅ Single query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders 
    WHERE user_id = u.id 
    ORDER BY created_at DESC 
    LIMIT 5
) o ON true
WHERE u.status = 'active'
LIMIT 100;

LATERAL joins are PostgreSQL's secret weapon for correlated subqueries.

8. Use CTEs Wisely

Common Table Expressions can make queries readable, but in PostgreSQL < 12, they act as optimization fences. In PostgreSQL 15+, most CTEs are inlined automatically.

-- Good use of CTE for readability
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT au.name, COUNT(ro.id) as order_count
FROM active_users au
LEFT JOIN recent_orders ro ON au.id = ro.user_id
GROUP BY au.name;

But don't over-nest. A 5-level deep CTE chain can confuse the query planner.

9. Optimize Aggregations

-- Slow: Aggregating entire table
SELECT category, AVG(price) FROM products GROUP BY category;

-- Faster: Filter first, then aggregate  
SELECT category, AVG(price) 
FROM products 
WHERE status = 'active' AND stock > 0
GROUP BY category;

For large datasets, consider materialized views:

CREATE MATERIALIZED VIEW category_stats AS
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MAX(price) as max_price,
    MIN(price) as min_price
FROM products
WHERE status = 'active'
GROUP BY category;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY category_stats;

Configuration Tuning

10. The Big Three Settings

These affect performance more than anything else:

# postgresql.conf

# Shared buffers: 25% of total RAM
shared_buffers = 2GB          # For an 8GB server

# Effective cache size: 50-75% of total RAM  
# (tells the planner how much cache is available)
effective_cache_size = 6GB

# Work mem: per-sort/hash memory
# Formula: (RAM - shared_buffers) / max_connections / 2
work_mem = 16MB               # Start here for most workloads

11. WAL and Checkpoints

# WAL (Write-Ahead Log) settings
wal_buffers = 16MB            # Small but important for write-heavy workloads
max_wal_size = 4GB            # Default is 1GB — too small for production
min_wal_size = 1GB
checkpoint_timeout = 15min    # Default 5min causes too-frequent checkpoints
checkpoint_completion_target = 0.9

12. Parallel Queries

PostgreSQL can use multiple CPUs for a single query:

max_parallel_workers_per_gather = 4    # Default is 2
max_parallel_workers = 8               # Match CPU cores
max_parallel_maintenance_workers = 4   # For CREATE INDEX, VACUUM

-- Force parallel execution for testing
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

-- Check if a query uses parallelism
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

13. Autovacuum Tuning

PostgreSQL uses MVCC, which means updates create dead tuples. Autovacuum cleans them up.

# Make autovacuum more aggressive on write-heavy tables
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- Default 0.2
    autovacuum_analyze_scale_factor = 0.02,   -- Default 0.1
    autovacuum_vacuum_cost_limit = 2000       -- Default 200
);

14. Connection Pooling

PostgreSQL handles connections by forking a process per connection. 500+ connections will degrade performance.

Use PgBouncer as a connection pooler:

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

pool_mode = transaction (transaction-level pooling) gives the best throughput. Your application won't notice the difference, and PostgreSQL will handle far more clients.

Monitoring

15. Key Queries for Monitoring

Slow queries:

-- Enable pg_stat_statements first
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by average time
SELECT 
    substring(query, 1, 100) as query,
    calls,
    round(mean_exec_time::numeric, 2) as avg_ms,
    round(max_exec_time::numeric, 2) as max_ms,
    total_exec_time as total_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Table bloat:

-- Estimate dead tuples
SELECT 
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC;

If dead_pct is above 10-15%, the table needs vacuuming.

Lock monitoring:

SELECT 
    l.relation::regclass AS table_name,
    l.mode,
    a.usename,
    a.query,
    a.query_start,
    now() - a.query_start AS duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

16. Database Size Monitoring

-- Table sizes including indexes
SELECT 
    schemaname || '.' || relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Production Checklist

Indexing

  • [ ] No Seq Scan on frequently-run queries
  • [ ] Foreign key columns are indexed
  • [ ] Unused indexes removed
  • [ ] Partial indexes for filtered queries

Configuration

  • [ ] shared_buffers = 25% of RAM
  • [ ] effective_cache_size = 75% of RAM
  • [ ] work_mem tuned for your connection count
  • [ ] Connection pooling via PgBouncer
  • [ ] Autovacuum running and tuned

Monitoring

  • [ ] pg_stat_statements enabled
  • [ ] Slow query logging enabled (log_min_duration_statement = 500)
  • [ ] Table bloat monitored
  • [ ] Disk space alerts set up
  • [ ] Connection count monitored

Maintenance

  • [ ] Regular ANALYZE on important tables
  • [ ] Monthly REINDEX on high-write tables (or use REINDEX CONCURRENTLY)
  • [ ] Backup strategy tested (not just configured — actually restored)
  • [ ] Replication lag monitored

Conclusion

PostgreSQL performance tuning is not about finding one magic setting. It's about systematic optimization:

  1. Index strategically — the right index can give 1000× improvements
  2. Write better queries — N+1 queries and missing filters are bigger bottlenecks than config
  3. Tune for your hardware — the default config is intentionally conservative
  4. Monitor continuously — you can't optimize what you don't measure
  5. Use connection pooling — it's the easiest win for most applications

Start with EXPLAIN ANALYZE on your slowest queries. The database will tell you exactly what's wrong. You just need to listen.