El nivel de datos
Por qué el estado es lo difícil de escalar, y cómo la Base de datos, la Caché, la Réplica de lectura y los almacenes de quórum cambian velocidad por frescura.
State is the hard part of scaling. App Servers are easy to copy because they hold no data, so ten copies are as correct as one. Data is different, because every copy must agree with the others or users see the wrong answer. That is why the data tier becomes the long-term challenge of every Company.
The Database is the source of truth#
The Database answers read queries, write queries, and transactions. Until you link a better Component, it also does three other jobs badly: full-text search, uploaded files stored as rows, and report scans. Each costs far more CPU than a normal read. A Search Engine, Object Storage, and a Data Warehouse take those jobs away.
Four limits cap a Database:
CPU. Every query costs CPU, and a cold Database, just restarted, needs more of it per query.
Connections. Postgres runs one process per connection and accepts a fixed number. MySQL gives each connection a thread and holds several times as many. Read Errors and retries for why more App Servers make this worse.
The volume's IOPS. Every write and every read that misses the buffer cache waits on the disk. A general purpose volume answers 3,000 of those a second, a provisioned one 64,000.
The buffer cache. Report scans fill memory with cold data. Other reads then go to disk, and writes wait behind the scans. This is why analytics and transactions should not share a Database. See OLTP and OLAP.
A bigger box lifts the first two limits. Sharding lifts the CPU limit on writes, because each shard takes its share of them, but each shard bills like a Database of its own.
Two engines, two shapes of work#
Postgres and MySQL are the same job with different costs. Postgres holds 100 connections at about
8 MB each and answers reads, scans, and full-text search about 5–10% cheaper than MySQL. MySQL
holds 500 connections at about 1 MB each and answers writes and checkouts about 30% cheaper,
because InnoDB updates the row in place. It also changes schema online, while many Postgres
ALTERs take the table lock, and only Postgres keeps dead rows for VACUUM, so only Postgres meets
Table bloat. A read-heavy Company wants Postgres; a write-heavy one wants MySQL.
Replicas always run their primary's engine, and an engine change lands on every Database at once. Replication only copies between matching engines.
More than one Database: group them#
A second Database is not a follower. Box the Databases that hold one dataset together: the box makes one Data Group, and its topology says how those Databases share the group's rows. Single primary (the start) takes writes on one Database and reads on Read Replicas. Sharded gives each Database its partition of the group's rows, so writes scale but cross-shard transactions cost up to 3× and can fail on either shard. Multi-leader copies every row to every Database and lets each take writes, so local writes are fast but certification drops conflicting writes (first commit wins) and every write costs 1.25× the CPU to replicate. Switching a group's topology moves its rows: 24 Dev Hours and a minute a GB, like a resharding. A group with one Database has nowhere to move them, so it takes the pick at once and the copy starts with the second Database in the box.
Rows follow the Services. Each Service owns an equal share of the Company's data, so a group holds the shares of the Services whose datastores sit in it. Two groups never touch each other's rows, and neither takes the shared-database penalty for the other, which is what lets one Board hold two independent products. A Database outside a box holds its own share and stands alone.
Quorum stores (Wide-column Store) and consensus (Distributed SQL) are the other answers to "writes on more than one node": a quorum waits for R of N on a read and W of N on a write, while consensus waits for a majority on every write. See Topologies.
Slow queries hide until you grow#
Two code problems cost little at first and a lot later.
An unindexed query scans the table. Its cost grows with the number of rows, and the rows grow with your users, so it is invisible in the Garage and becomes the bottleneck later. The Slow query incident is this problem. Query analysis shows it, and the Add index task fixes it.
An N+1 query comes from an ORM that sends one query per item in a list. Laravel, Rails, and Django make extra reads on page views and API reads until you ship Eager loading. See N+1 query.
The Cache: memory in front of the Database#
A Cache answers read queries from memory and sends misses on to the Database. Its hit ratio decides how much load it takes off:
The square root models skewed access. Most reads ask for a few hot keys, so a small Cache already holds the hottest ones, and the first gigabyte is worth more than the tenth. Hot data grows with your users, so a Cache that was big enough last Era may be too small in the next.
Writes invalidate keys, so a write-heavy mix lowers the hit ratio. A longer TTL keeps more hits but serves more stale data. A Cache that restarts comes back cold, and every miss goes to the Database at once, which is a cache stampede. Stampede protection and Cache warming soften it.
Read Replicas: more reads, older data#
A Read Replica runs like a Database but answers only reads and reports. Writes go to the primary and then stream to the replica, so the replica is always a little behind, and the lag grows with your write rate. See replication lag.
Three Nodes take load off a busy Database, and each has its own job:
A bigger Database is the only fix for writes. It costs the most, and a resize restarts it.
A Cache is the cheapest fix for reads, while the same hot rows are read again and again. It cannot answer a report, and its hit ratio falls as writes rise.
A Read Replica answers a read of any row and takes the reports, at 0.7× the box price of a Database, and placing one restarts nothing. Some of its reads come back stale.
A read from a lagging replica can return old data. Whether that matters depends on the kind:
A page view accepts slightly old data.
An API read that comes back stale fails as
stale.A checkout that reads its own write fails as
stale, which costs real money.
Read-your-writes routing sends the reads that care to the primary. That fixes most stale errors, at the price of some read load on the primary.
SandboxPago desactualizadoMantén el Apdex por encima de 0.9 durante 90 segundos.Quorums: the game's namesake#
A Wide-column Store keeps each key on N replicas. You choose two numbers: R, the replicas a read asks, and W, the replicas a write waits for.
Smaller R and W are faster, because each operation waits for an earlier reply, and they cost fewer replica operations. The price is that a weak quorum serves stale reads. A quorum that needs every replica fails while one replica is down. See quorum.
A Wide-column Store keeps N = 3 replicas. Which setting makes every read see the latest write?
Overlap does not give a single, ordered history. Concurrent writes can still conflict, so even a strong quorum gets an occasional stale read or duplicate on checkouts. A Distributed SQL Node fixes that with consensus: every write waits for a majority of its replicas. Inside one region, that is cheap, but across regions every write pays a long round trip. See Raft.
Hot keys#
Spreading data across nodes only helps if the load spreads too. In the Hot key incident, a large share of reads asks for one key, such as a celebrity's post. That key lives on one shard, so that shard saturates while the others idle. Hybrid fan-out lets you split hot keys across shards. See hot key.
The pattern behind all of it#
Every data choice in Quorum trades among speed, freshness, and survival. A Cache and a replica buy speed with freshness, and a strong quorum and consensus buy freshness with speed. Copies in other zones and regions buy survival with Burn. The Data track shows the research behind each tool:
- Caché
- Pooling de conexiones
- Réplicas de lectura
- Análisis de consultas
- Búsqueda de texto completo
- Almacenamiento de objetos
- Estrategias de caché
- Reglas de ciclo de vida del almacenamiento
- Políticas de retención
- Protección contra estampidas
- Precalentamiento de caché
- Enrutado read-your-writes
- Almacenamiento de datos
- Streaming de eventos
- Embeddings
- Recuperación a un punto en el tiempo
- Replicación síncrona
- Sharding
- Bases de datos documentales
- Fan-out híbrido
- Replicación sin líder
- Bases de datos de consenso
- Captura de cambios de datos
- Migraciones de esquema en línea
- Outbox transaccional
- Expandir y contraer
For practical steps, see Scale the database and Fix slow queries.