PostgreSQL Performance Tuning for Backend Developers

Slow database queries are one of the most common performance bottlenecks in web applications. Here are practical techniques to diagnose and fix them.

Finding Slow Queries

Enable the slow query log:

ALTER SYSTEM SET log_min_duration_statement = 1000; -- log queries > 1s
SELECT pg_reload_conf();

Use EXPLAIN ANALYZE to understand query plans:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

Indexing Strategies

-- Composite index for common filter + sort patterns
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- GIN index for JSONB columns
CREATE INDEX idx_metadata ON events USING GIN (metadata);

N+1 Query Prevention

-- Instead of N+1 queries, use JOINs
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Connection Pooling with PgBouncer

Configure PgBouncer for transaction-mode pooling to handle thousands of concurrent connections efficiently.

Conclusion

Database performance is about understanding your access patterns and building indexes to match them. Profile first, optimize second.