How to improve database performance
A ladder of moves that takes a slow query from seconds to microseconds.
Database performance is won layer by layer: index the columns you filter and join on, write queries that avoid N+1 and select only what you need, measure with EXPLAIN, pool connections, cache hot reads in Redis, offload reads to replicas, and finally partition or shard when one box can't keep up. Each move relieves the current bottleneck and exposes the next.
Index the right columns
The first and largest win is indexing the columns you filter, join, and sort on, so the database seeks straight to matching rows instead of scanning the whole table. A missing index on a WHERE or JOIN column is the most common cause of a query that gets slower as data grows. Index deliberately, though — every index adds write cost and storage, so index the access patterns you actually have.
Fix the query shape
Rewrite queries to ask for less work: replace SELECT * with only the columns you use, add selective WHERE clauses, and avoid functions on indexed columns that defeat the index. The classic killer is the N+1 pattern — one query for a list, then one more per row — which you collapse into a single join or an IN batch. Fewer, tighter queries beat many chatty ones.
Measure with EXPLAIN
Stop guessing and ask the database what it's doing. EXPLAIN shows the planner's chosen path, and EXPLAIN ANALYZE actually runs it and reports real row counts and timings. A sequential scan where you expected an index seek, or a row estimate wildly off from reality, tells you exactly where to add an index or refresh statistics.
Pool connections
Opening a database connection is expensive — a TCP handshake, authentication, and session setup — and doing it per request wastes most of your latency budget. A connection pool keeps a set of warm connections open and hands them out, so requests reuse rather than rebuild them. Pooling also caps concurrency, protecting the database from being swamped by more connections than it can serve.
Cache hot reads
Most workloads read the same hot data far more than they write it, so put an in-memory cache like Redis in front of the database. Repeated reads are answered in microseconds and never reach the DB at all, which is the highest-leverage move on both latency and load. Cache-aside is the common pattern: check the cache, fall back to the DB on a miss, and populate the cache with a bounded TTL.
Add read replicas
When reads still dominate after caching, replicate the database so copies can serve queries in parallel while the primary handles writes. The application routes reads to replicas and writes to the primary, multiplying read throughput without touching the write path. The cost is replication lag — a replica may trail the primary by milliseconds — so route reads that must be perfectly fresh back to the primary.
Partition, shard, denormalize
When a single table or node can't keep up, split the data. Partitioning breaks one big table into smaller physical pieces by range or hash so queries touch less data; sharding spreads rows across separate database nodes to scale writes horizontally. Denormalizing and batching complement this by trading redundancy and slight delay for cheaper reads — the last, most complex tier because cross-shard joins and consistency get hard.
Why databases get slow
A database is almost always the first component to buckle under load, because it's the one tier that can't just be cloned. App servers are stateless and easy to add; the database holds the single source of truth, and every request eventually depends on it. So when a system feels slow, the database is the usual suspect — and the good news is that database performance is one of the most *learnable*, high-leverage areas in all of engineering.
The core reason queries slow down is almost always the same: the database is doing more work than it needs to. It's scanning millions of rows to find a handful, re-opening connections it could have reused, recomputing answers it already produced a second ago, or funneling all traffic through one machine that's out of headroom. Every technique in this article is really one idea applied at a different layer — *do less work, or do it somewhere cheaper.*
The crucial mindset is that these moves form a ladder, applied bottom-up. You start with the cheapest, highest-leverage change (indexing) and only climb to the expensive, complex tiers (sharding) when the ones below are exhausted. Reaching for sharding before you've added an index is the classic mistake — enormous effort to solve a problem a five-second migration would have fixed.
Indexing and query shape: where most wins live
The overwhelming majority of database performance problems are solved at the bottom two rungs, before you touch infrastructure at all.
Indexing is the single biggest lever. An index is a sorted data structure (usually a B+tree) that lets the database seek directly to matching rows instead of scanning the entire table. The rule of thumb is to index the columns you filter on (WHERE), join on, and sort by (ORDER BY). A query filtering an unindexed column on a ten-million-row table reads all ten million rows; with the right index it reads a few. But indexes aren't free — each one must be updated on every insert and update and costs storage, so you index the access patterns you actually have, not every column speculatively.
Query shape is the other half. The same data can be fetched cheaply or catastrophically depending on how you ask:
- **Avoid
SELECT *** — fetch only the columns you use. Extra columns mean more data off disk, more network transfer, and can prevent an *index-only scan* where the index alone answers the query. - Kill the N+1 pattern — the classic performance bug where you run one query to fetch a list of N items, then loop and run one more query *per item*. That's N+1 round trips where one
JOINor a singleIN (...)batch would do. ORMs make this trap especially easy to fall into. - Keep indexes usable — wrapping an indexed column in a function (
WHERE lower(email) = ...) or using a leading wildcard (LIKE '%foo') forces a scan, because the index's sort order no longer applies.
Get these two rungs right and most "slow database" complaints disappear.
Measure before you optimize: EXPLAIN
The most important habit in database tuning is refusing to guess. Every serious database ships a way to ask the query planner what it intends to do, and reading that output is the difference between fixing the real bottleneck and cargo-culting changes that do nothing.
`EXPLAIN` shows the planner's chosen execution plan without running the query: which indexes it will use, whether it will do a sequential scan or an index seek, in what order it will join tables, and its estimated row counts and cost. `EXPLAIN ANALYZE` goes further — it actually executes the query and reports the *real* timings and row counts alongside the estimates.
The two signals you hunt for are:
- A sequential scan (reading the whole table) where you expected an index scan. That almost always means a missing or unused index.
- A large gap between estimated and actual rows. The planner chooses plans based on table statistics; if those are stale, it makes bad choices. Refreshing statistics (
ANALYZE) often fixes a mysteriously slow query on its own.
EXPLAIN turns performance work from superstition into diagnosis. You look at the plan, find the expensive step, add exactly the index or rewrite that fixes it, and confirm the plan changed.
Connections, caching, and replicas
Once queries themselves are efficient, the next rungs move work off the database entirely.
Connection pooling. Opening a database connection is surprisingly expensive: a TCP handshake, authentication, and session setup that can cost more than the query it carries. Doing this per request wastes most of your latency budget and, worse, can exhaust the database's connection limit under load. A connection pool (PgBouncer, or a pool inside the app/ORM) keeps a set of warm connections open and lends them out, so requests reuse rather than rebuild them. It also acts as a governor, capping concurrency so a traffic spike can't open ten thousand connections and topple the server.
Caching. Real workloads are skewed — a small fraction of data serves most reads. Putting an in-memory cache like Redis in front of the database answers those hot reads in microseconds without ever touching disk or the DB. The common pattern is cache-aside: check the cache, and on a miss read the database and populate the cache with a bounded TTL. A cache that satisfies even 90% of reads removes the vast majority of load from your slowest tier. The cost is staleness, which you manage with TTLs and by invalidating on writes that must stay correct.
Read replicas. When reads still dominate after caching, replicate the database. Read replicas are copies that serve queries in parallel while the primary handles all writes; the app routes reads to replicas and writes to the primary. This multiplies read throughput cheaply, but replicas trail the primary by some replication lag, so a read immediately after a write might not see it. The fix is to route reads that must be perfectly fresh back to the primary.
The last resort: partitioning, sharding, and denormalization
Caching and replicas scale *reads*. When writes themselves outgrow a single machine, or a single table grows too large to query efficiently, you have to split the data — the most powerful but most complex tier of the ladder.
Partitioning breaks one large table into smaller physical pieces within the same database, by range (orders by month) or by hash of a key. Queries that target one partition touch far less data, and old partitions can be dropped or archived wholesale. Sharding takes it across machines: rows are spread over multiple independent database nodes by a shard key (user ID, region), so each node owns a slice and writes scale horizontally. Sharding unlocks near-unlimited throughput but is genuinely hard — cross-shard joins and transactions become painful, and choosing a bad shard key creates hotspots — which is exactly why it's the *last* move, not the first.
Denormalization and batching round out the toolkit. Denormalizing deliberately duplicates data (storing a customer_name on the orders table, or a precomputed comment_count) to avoid joins on hot read paths, accepting write-side redundancy in exchange for cheaper reads. Batching amortizes overhead by grouping many operations into one round trip — a single bulk INSERT instead of a thousand, or one batched fetch instead of an N+1 loop.
The through-line across every rung is the same: measure to find the real bottleneck, apply the cheapest move that relieves it, and climb only as far as the metrics force you. Most systems never need sharding; nearly all of them need indexes.
Indexing is the single biggest lever: an index turns a full-table scan into a targeted lookup on the columns you filter, join, and sort by.
Fix query shape first — kill N+1 loops with joins or batching, and SELECT only the columns you actually use instead of SELECT *.
EXPLAIN (and EXPLAIN ANALYZE) shows the planner's chosen path so you optimize what's actually slow, not what you guess is slow.
Connection pooling reuses expensive TCP+auth handshakes; caching in Redis answers hot reads in microseconds and never touches the DB.
Read replicas absorb read-heavy load; partitioning and sharding split data so no single table or node is the bottleneck.
Denormalizing and batching trade write-side redundancy and latency for far cheaper reads on hot paths.
Quick quiz
A query filtering on an unindexed column gets slower as the table grows. Why?
What problem does the N+1 query pattern describe?
Database index types
The data structures that turn a full-table scan into a pinpoint lookup.
How a SQL query executes
The pipeline that turns a text string into rows fetched off disk.
Where do we cache — and how it goes wrong
Caching lives at every layer. So do the ways it can bite you.
A crash course on scalability
How one box on a laptop becomes a system that serves millions.