Database Performance

Database performance tuning is the highest-leverage optimization in most production systems — a missing index can turn a 5ms query into a 5-second query at scale. The standard optimization hierarchy is: add indexes → optimize queries → tune connection pooling → add read replicas → shard. EXPLAIN ANALYZE in PostgreSQL and EXPLAIN in MySQL reveal query execution plans, exposing sequential scans, sort operations, and join strategies that explain slow query behavior. At companies like LinkedIn, database optimization is a dedicated specialization with measurable business impact.

Key Points

• Index selection: B-tree indexes suit equality and range queries; composite indexes must match query column order; covering indexes include all projected columns to avoid heap lookups.
• Slow query log: enable in MySQL (`slow_query_log=ON, long_query_time=0.1`) and PostgreSQL (`log_min_duration_statement=100`) to capture queries exceeding threshold.
• EXPLAIN ANALYZE: shows actual row counts, cost estimates, and execution time per node — look for Seq Scans on large tables and Sort operations that spill to disk.
• Connection pooling: PgBouncer (PostgreSQL) and ProxySQL (MySQL) pool connections at the proxy layer — reduces per-connection overhead from ~10MB RAM to near zero for thousands of app connections.
• N+1 query problem: loading N parent records then making N separate queries for children — eliminate with JOIN, `SELECT IN`, or ORM eager loading (`.include()`, `.joinedload()`).
• Batch operations: replace per-row INSERTs with bulk INSERT — PostgreSQL supports `INSERT ... SELECT` and COPY for millions of rows/second throughput.
• Partial indexes: index only a subset of rows (`CREATE INDEX ON orders(user_id) WHERE status = 'pending'`) — dramatically smaller indexes with full selectivity for filtered queries.
• Vacuum and autovacuum: PostgreSQL MVCC leaves dead row versions — autovacuum reclaims space and updates statistics; tune autovacuum_vacuum_scale_factor for high-write tables.