PostgreSQL Performance Tuning: Indexing Strategies and Query Optimization

PostgreSQL Performance Tuning

Optimize your PostgreSQL database for maximum performance.

Analyzing Queries with EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users
WHERE email = 'test@example.com';

-- Look for:
-- Seq Scan (bad for large tables)
-- Index Scan (good)
-- Nested Loop (can be slow)

Creating Effective Indexes

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index for common queries
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- GIN index for JSONB columns
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

Query Optimization Tips

-- Bad: Using functions on indexed columns
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Good: Create a functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Bad: SELECT *
SELECT * FROM orders WHERE user_id = 1;

-- Good: Select only needed columns
SELECT id, total, created_at FROM orders WHERE user_id = 1;

Configuration Tuning

# postgresql.conf
shared_buffers = 256MB              # 25% of RAM
effective_cache_size = 768MB        # 75% of RAM
work_mem = 64MB                     # Per-operation memory
maintenance_work_mem = 512MB        # For VACUUM, CREATE INDEX

Connection Pooling with PgBouncer

# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Regular maintenance with VACUUM and ANALYZE keeps your database running smoothly.