PostgreSQL Performance Tuning — EXPLAIN, Indexes, and Query Optimization

Overview

Optimize slow PostgreSQL queries with EXPLAIN ANALYZE, proper indexing strategies, and configuration tuning. This guide covers the essential concepts, practical examples, and production-ready patterns you need to get started.

Getting Started

Before diving in, make sure you have the necessary prerequisites installed. This guide assumes basic familiarity with the underlying technology stack.

Core Concepts

Understanding the fundamentals is essential before applying advanced patterns. The following sections break down the key ideas with working code examples.

Best Practices

• Start simple and add complexity only when needed
• Write tests alongside your implementation
• Document decisions that aren't obvious from the code
• Monitor in production with appropriate logging and metrics

Common Pitfalls

Developers commonly run into a few specific issues when first implementing these patterns. Understanding them upfront saves significant debugging time later.

Production Checklist

• Error handling and graceful degradation
• Logging and observability
• Performance testing under realistic load
• Security review

Why PostgreSQL Performance Matters

PostgreSQL is a powerful open-source database, but without proper tuning, even simple queries can become slow. This guide covers practical optimization techniques that can improve query performance by 10x or more.

Understanding PostgreSQL Architecture

PostgreSQL uses a process-based architecture with:

• Background Writer: Writes dirty pages to disk
• WAL Writer: Writes transaction logs
• Checkpointer: Ensures data durability
• Autovacuum: Cleans up dead tuples

Key Configuration Parameters

Memory Settings

# In postgresql.conf
shared_buffers = 4GB           # 25% of RAM for dedicated DB
effective_cache_size = 12GB    # 75% of RAM estimate
work_mem = 64MB                # Per-operation memory
maintenance_work_mem = 1GB     # For VACUUM, CREATE INDEX

Indexing Strategies

B-Tree Index (Default)

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at DESC);

Composite Indexes

CREATE INDEX idx_users_active_email ON users(active, email);
-- Order matters! This index works for:
-- WHERE active = true
-- WHERE active = true AND email = 'x'

Partial Indexes

CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Query Optimization with EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

Key metrics to watch:

• Seq Scan: Full table scan (usually bad)
• Index Scan: Using index (good)
• Index Only Scan: Best case - no heap access
• Execution Time: Actual query time

VACUUM and Table Maintenance

-- Manual vacuum
VACUUM ANALYZE users;

-- Check table bloat
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       n_dead_tup::float / NULLIF(n_live_tup, 0) as dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Connection Pooling

Use PgBouncer for connection pooling:

# pgbouncer.ini
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Common Performance Issues

• Missing indexes: Check slow query logs
• Outdated statistics: Run ANALYZE regularly
• Table bloat: VACUUM or VACUUM FULL
• Inefficient queries: Use EXPLAIN ANALYZE
• Too many connections: Use connection pooling

Frequently Asked Questions

How do I find slow queries in PostgreSQL?

Enable log_min_duration_statement = 1000 in postgresql.conf to log queries taking more than 1 second.

What is the difference between VACUUM and VACUUM FULL?

VACUUM reclaims space for future use. VACUUM FULL reclaims space to the OS but requires an exclusive lock.

How many indexes should a table have?

Enough to cover your queries, but not more. Each index slows down INSERT/UPDATE. Start with primary keys and foreign keys.

Why is my query slow even with an index?

Possible causes: outdated statistics (run ANALYZE), wrong index type, or the query planner chose a seq scan. Check EXPLAIN ANALYZE output.