Database Performance Cheat Sheet

Database performance work is usually about removing avoidable work, not hunting for one magical setting. Slow systems tend to waste time in predictable places: poor access patterns, excessive round trips, unnecessary scans, lock contention, and data models that do not match how the application actually reads and writes data.

Measure before tuning

Start with the query path, not with generic folklore. Latency percentiles, query plans, lock wait metrics, cache hit ratios, and connection pool usage tell you where time is being spent. A database can show high CPU because of full-table scans, bad joins, or repeated sorting. The remedy depends on the shape of the workload.

Performance optimisation without workload data often creates local improvements that move the bottleneck elsewhere. A faster query that increases write amplification or memory pressure may still be a net loss.

Optimise schema and indexing for access patterns

Tables should reflect how the application groups and filters data. Indexes are most effective when they support the actual WHERE, JOIN, and ORDER BY clauses used in production. Indexing every column is not a strategy. Each index speeds up some reads while making writes, vacuuming, and storage heavier.

Composite indexes matter because databases use left-to-right prefix rules in common engines. An index on (tenant_id, created_at) helps a query filtered by tenant and ordered by creation time. It may not help a query filtered only by created_at. Understanding that constraint prevents a lot of accidental inefficiency.

Query shape often dominates raw hardware

N+1 query patterns, wide row fetches, and large offset pagination can make a healthy database look slow. Fetch only required columns. Batch related reads where that improves locality. Prefer keyset or cursor pagination for large datasets that change frequently.

Joins are not inherently bad. Bad joins are bad. If the cardinality is understood and the join keys are indexed, a relational engine can execute a join more efficiently than an application stitching rows together over the network.

Contention is a hidden performance killer

As traffic grows, lock duration and hot rows become decisive. Long transactions hold resources and block unrelated work. Frequent updates to a single counter, ledger row, or queue table can serialise the system even when the database has plenty of CPU and memory.

This is where workload-aware design matters. Partitioning data, using append-heavy patterns, or moving some coordination to queues can reduce contention more effectively than scaling the instance vertically.

Cache carefully, operate continuously

Application caches, read replicas, and materialised views can take pressure off the primary database, but each one introduces staleness, invalidation, or operational overhead. Use them where the read pattern is stable and the consistency cost is understood.

Database performance is never a one-time exercise. As product behaviour changes, yesterday's optimal index or schema choice may become today's bottleneck. The durable habit is to treat performance as a property of the workload, the data model, and the operating constraints together. When those stay aligned, the database usually performs well without heroics.