Backend Development7 min read

Database Performance Tuning

Tune database performance with EXPLAIN ANALYZE, composite indexes, query rewriting, connection pooling, and read replicas.

databaseperformancepostgresqlindexing

Database performance is the most impactful optimization in most applications. A poorly indexed query that takes 2 seconds can be reduced to 2 milliseconds with a single CREATE INDEX. Understanding query plans, index types, and connection management transforms slow applications into fast ones.

Measure first with slow query logs and EXPLAIN ANALYZE. Indexes are the highest-ROI optimization. Never optimize without measuring before and after. Don't pre-optimize schema - add indexes when queries are slow.

Workflow

Identify slow queries with logs and pg_stat_statementsYou can't optimize what you haven't identified; slow query logs surface the biggest problems.
Read and act on EXPLAIN ANALYZEEXPLAIN ANALYZE shows exactly how PostgreSQL executes a query - seq scans, index scans, join strategies, and actual vs estimated row counts.
Design indexes for query patternsWrong index type or column order is as bad as no index.
Eliminate N+1 and optimize join queriesN+1 queries are the most common ORM-caused performance problem - one query per row instead of one query for all rows.
Configure connection pooling and read replicasEach PostgreSQL connection uses ~5MB RAM; 1000 connections = 5GB for connection overhead. Read replicas scale read traffic.

Output Format

Slow query log enabled, EXPLAIN ANALYZE analyzed for top-10 slow queries, indexes added with CONCURRENTLY, N+1 eliminated, PgBouncer configured, read replica routing for read-heavy endpoints.

Examples

Composite index: `CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders (user_id, status, created_at DESC)` - for: `WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20`

Partial index: `CREATE INDEX CONCURRENTLY idx_pending_orders ON orders (user_id, created_at) WHERE status = "pending"` - smaller, faster for pending-only queries

Keyset pagination: `SELECT * FROM posts WHERE (created_at, id) < ($lastCreatedAt, $lastId) ORDER BY created_at DESC, id DESC LIMIT 20`

Edge Cases & Gotchas

CONCURRENT index creation: `CONCURRENTLY` allows reads/writes during index build but takes longer; always use it in production to avoid table locks
Index bloat: frequent updates/deletes cause index bloat; run `REINDEX CONCURRENTLY` during maintenance windows; monitor with `pgstattuple`
Vacuum and autovacuum: dead tuples accumulate and slow down queries; ensure autovacuum is running; for high-churn tables, tune `autovacuum_vacuum_scale_factor` down

When to Break These Rules

For read-heavy analytics queries that join many tables with complex aggregations, consider a columnar store (Redshift, BigQuery, ClickHouse) rather than tuning OLTP PostgreSQL - it's architecturally the wrong tool.

Reference Files

PostgreSQL EXPLAIN: https://www.postgresql.org/docs/current/sql-explain.html
PgBouncer: https://www.pgbouncer.org/
Use The Index, Luke: https://use-the-index-luke.com/
pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html