Skip to main content

​
Overview

In backend performance problems, the layer that most often becomes the bottleneck is the database. This page covers the principle at the root of database performance work and the concrete methods derived from it. For the prerequisites — how to set the goal of an improvement and in what order to consider fixes — see Performance. The decision that comes before this page — which kind of database should hold the data — is covered in Choosing a Database. This page is about using the database you chose, fast. For convenience the examples use a relational database, but many of the ideas here carry over to NoSQL stores (DynamoDB, MongoDB, etc.). Tuning server parameters is out of scope; the focus is on improvements in queries, schema, and the application. The focus is mainly on read performance: in most web services reads far outnumber writes, and the bottleneck appears on the read side first. Write-specific concerns — the maintenance cost of indexes, distributing writes — are covered where they arise.

​
Why the database becomes the bottleneck

The database tends to become the bottleneck because how long reading data takes differs by orders of magnitude depending on where it is read from. The numbers vary by hardware and configuration, so read them as a comparison of orders of magnitude, not absolutes. For work that makes many reads and round trips, latency determines the time taken; for work that reads a large amount at once, throughput does. In a database, index lookups live in the world of random reads, while full scans and large aggregations live in the world of sequential reads. Note the last row of the table. In most managed cloud databases the storage itself is network-attached, so a single read carries tens of times the latency of local NVMe. The large throughput number is no comfort here: random reads pay the latency on every access, so repeating a 1ms read 1,000 times takes a full second by itself. No amount of bandwidth can fill in the waiting that accumulates across repetitions. Real databases fill in this slowness with large amounts of main memory (the buffer pool), and some configurations add local NVMe as a cache tier. The flip side: the moment a query reads more than the cache can hold, the numbers in the table appear as they are. No matter how fast the rest of the application is, how much storage the database reads and how many round trips it makes largely determine the speed of the whole system.

​
The principle: minimize storage reads

The thinking at the root of database performance work therefore collapses into one principle: minimize the amount of data read from storage. Index design, query tuning, and application-side changes all come down to eliminating wasted storage reads. Even when the data is cached in memory (the buffer pool), scanning less is still faster. Some billing models also translate the amount read directly into infrastructure cost (Aurora’s I/O billing, DynamoDB’s read capacity, and so on). Carry the single question “how much storage does this query read?” and the individual techniques that follow stop being items to memorize and connect as applications of one principle.

​
Observe how much is being read

To reduce reads, first learn which queries are reading, and how much. There are three entry points.
  1. Slow query log — Records SQL statements that exceed a threshold (e.g. 0.5 seconds). The first data source for finding candidates that read too much.
  2. Execution plans (EXPLAIN) — Show, before running the query, how much it plans to read (how to read them is covered below).
  3. Engine metrics — Show how reads look across the whole system rather than per query: cache hit rate (buffer pool hit rate, etc.; a drop signals growing physical disk reads), CPU and IOPS, active connections (connection pool exhaustion), and lock waits (transactions jamming on each other).

​
Reducing reads in practice

Once the heavy readers are identified, cut the amount they read. There are four layers, ordered from closest to the query outward: indexes, execution plans, application design, and how the data is stored.

​
What indexes really do: read fewer blocks

An index is a mechanism for locating where in storage the target data lives (which block or page), so unnecessary reads can be skipped. Why a full table scan is slow — Without an index, the database engine must read the whole table from storage even when it is looking for a single row. That is the state where the amount of storage read is at its maximum. On a small table, though, reading everything is often not a problem, and the optimizer may deliberately choose a full scan over an index; it becomes a problem when the amount read is large. How an index cuts reads — With a well-designed index, the database reads only the blocks where the target data lives, after just a few lookups.
  • Composite index order (leftmost-prefix matching) — For a condition like WHERE tenant_id = 1 AND status = 'active' AND created_at > :since, place the equality columns first and the range column last. Columns after a range condition cannot be used to narrow the search, so more data blocks get read. Since the order among equality columns barely affects selectivity, choose an order whose prefix can be shared with other queries.
  • Covering indexes — When every column in the SELECT is contained in the index, the database can skip reading the table itself and complete the query from the index alone.
Note that an index makes reads faster in exchange for a maintenance cost on every write. An index no query uses slows writes down without helping reads, so be careful not to accumulate too many.

​
Reading execution plans

When tuning a query, first check the execution plan: with what algorithm, and over how much data (rows, pages), does the database intend to execute it? The command to obtain the plan (EXPLAIN, etc.) and its output format differ by database, but the three common things to look for are the same.
  1. Scan type — Is it reading the whole table from storage instead of using an index?
  2. Estimated rows — Is filtering failing, producing a plan that reads an unnecessarily huge number of rows?
  3. Spills to disk — Are sorts or intermediate results exceeding memory and being written out to storage?
When the optimizer gets it wrong — Which index to use and in what order to join tables is chosen automatically by the optimizer based on statistics. But the optimizer is not always right: sudden data growth, heavy skew, query complexity, and a crowd of candidate indexes can throw off its cost estimates, producing an unsuitable index choice or an inefficient join order. First check whether the statistics are stale and refresh them; if the bad plan persists, force the join order with hints, or split and simplify the query so that the amount of data read first is under your control.

​
Reducing reads through application design

Beyond tuning individual queries, revisiting how the application talks to the database also cuts reads. Eliminating N+1 queries — Issuing a query N times inside a loop incurs a round trip each time, each with its own storage or cache access. Fetch in bulk with joins or IN clauses (eager loading) to cut both the number of accesses and the total blocks read. The round-trip cost is the same for writes: replace repeated one-row INSERTs and UPDATEs with bulk writes. Avoiding deep OFFSET — A query like SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 100000 reads and discards the first 100,000 rows to return the next 20. Rewrite it to key off the last position fetched (cursor style), and it reads just the next 20 rows without skipping over anything.
Cursor-style pagination depends on the previous fetch position, so it cannot jump to an arbitrary page number. It trades against the UI: it suits “next / previous” navigation and infinite scroll.
Eliminating repeated access to the same data — What looks like separate logic on the application side can be, from the database’s point of view, the same tables and blocks being read over and over. For data referenced many times within one batch or request (master data, etc.), fetch it once up front and keep it in memory; instead of aggregating the same range inside a loop, combine it into one query or read from a pre-aggregated table. Checking the emitted SQL — The convenient methods of ORMs and libraries hide what SQL gets issued, and how many times. Rails’s in_batches, for example — a method that processes a large number of records in fixed-size batches — re-executes the original query in full every time it fetches a batch. That goes unnoticed while the query is cheap, but with a heavy join or subquery, its expensive part is paid once per batch. When applying a convenience method to a large amount of data, check the actually emitted SQL in the logs.

​
Reducing reads by changing how data is stored

When query- and application-side work cannot cut reads any further, changing how the data is stored can reduce the work done at read time. Unlike the techniques so far, both of these carry a price (see “What you trade away” in Performance). Denormalization — Values that were assembled with joins on every read are stored in the row ahead of time. Reads become simple lookups, but updates take on the responsibility of keeping multiple places consistent, with the risk of divergence. Pre-aggregation (summary tables) — Aggregation results are written out to a table in advance, and readers reference only the small result. A large scan turns into reading a few rows, but the aggregation axes become fixed and the freshness of the result is bound to the update frequency.

​
Keeping the cache hit rate high

However well the queries are tuned, response time has a floor as long as storage is being accessed. This is where the database’s internal cache, such as the buffer pool, matters: when the frequently accessed data (the working set) fits in memory, no storage I/O occurs. If the cache hit rate still degrades after cutting reads through query work, consider adding memory — or eliminating the full-table-read queries that keep evicting the cache.

​
When a single database is not enough

If the limits are reached even after all of the above, look beyond tuning within a single database, toward architectural separation of roles and specialized engines. For what to add — and what a second database costs — see also Choosing a Database. Read distribution with read replicas — Separate writes (primary) from reads (replicas) and spread the load physically. Requires tolerating the eventual consistency introduced by replication lag. Distributing writes and data volume with functional splits and sharding — Write throughput and total data volume cannot be distributed with replicas. Consider a functional split first — moving a domain or a group of tables to a separate database — and when that is not enough, use sharding: splitting the data across multiple databases by a key such as tenant or user ID. With either split, however, joins and transactions across the boundary are lost, and changing how the data is divided later is hard — so adopt them after confirming the other options are not enough.
Instead of operating the split yourself, you can adopt a database that takes it on. Distributed SQL databases such as Cloud Spanner and CockroachDB handle the splitting as a managed layer while keeping SQL and distributed transactions, and partition-first NoSQL stores such as DynamoDB grow write capacity and storage as far as the workload needs. For what each kind is good and bad at, see Choosing a Database.
Separating OLTP and OLAP — Databases do two kinds of work with different characters: the small reads and writes that support day-to-day operations (OLTP: Online Transaction Processing), and the bulk reads for aggregation and analytics (OLAP: Online Analytical Processing). Running heavy analytics on the database that carries the OLTP work consumes I/O and memory and squeezes normal processing. Move the work to a database that matches the use case and the required freshness.
  • Data warehouses (DWH; BigQuery / Snowflake / Redshift, etc.) — Specialized for large-scale batch aggregation and reporting
  • Real-time OLAP (ClickHouse, etc.) — Specialized for low-latency aggregation and search over large data: log analytics, dashboards
  • HTAP (TiDB, etc.) — Hybrid Transactional/Analytical Processing: OLTP and OLAP combined in one distributed database

Performance

The judgment foundation for all tuning: why to improve, in what order, and what to observe.

Choosing a Database

The decision that comes before this page: which kind of database should hold the data. Also covers what a second database costs.

Modifiability

The property of accepting change at low cost and low risk. Denormalization and pre-aggregation can damage it.