Choosing a database

Choose the database by how data is read and written

KindThe access pattern it servesAWSAzureGoogle CloudRelationaljoins, transactions, a schemaRDS, AuroraSQL Database, PostgreSQLCloud SQL, AlloyDB, SpannerKey-value, documentget by key at any scale, in msDynamoDB, DocumentDBCosmos DBFirestore, BigtableIn-memorymicroseconds; leaderboardsElastiCache, MemoryDBAzure Cache for RedisMemorystoreGraphrelationships many hops deepNeptuneCosmos DB (Gremlin)Spanner GraphTime seriesappend metrics, query by timeTimestreamAzure Data ExplorerBigtableWarehousescan billions of rows to sumRedshiftMicrosoft FabricBigQueryChoose the database by how the data is read and written, not by habit.Write down the queries first: what is looked up, by which key, how often, how big, how fresh. A relational databasehandles almost anything at moderate scale and is the safe default. A key-value store handles one access pattern atany scale with flat latency. Analytics belongs in a warehouse, never on the database that takes orders.

Match the access pattern to the store: relational for joins and transactions, key-value for lookups at any scale, a warehouse for analysis.

Read replicas

Replicas multiply read capacity, but each is a moment behind

Appall writesPrimaryReplica 140 ms behindReplica 2120 ms behindReplica 3900 ms behindasynchronous copyreads and reports go to replicasReplicas multiply read capacity, but each one is a moment behind the primary.Writes still go to one primary; each replica replays them asynchronously and serves reads. Five replicas give roughlysix times the read capacity, ideal for read-heavy apps and for reports that would otherwise slow the primary. Thecost is lag: a user who saves and then reads from a replica may not see the change. RDS allows up to 15 replicas.

Read replicas copy the primary asynchronously and serve reads; writes still go to one primary, and replicas lag.

Scaling order · Going deeper

Seven ways to scale a database, cheapest first

1. Fix the queriesindexes, no N+1 loops, smaller resultsfree; often the biggest win2. Pool connectionsa proxy shares a few connections (RDS Proxy)stops connection storms from functions3. Cache readscache-aside in front (lesson 5)hot reads leave the database4. Add read replicasreads and reports to copiesreads scale out; writes do not5. Scale upa larger instance; faster storage (IOPS)simple, with a ceiling6. Partition and shardsplit by key across databaseswrites scale; queries get harder7. Change the databasekey-value or distributed SQLwhen one pattern needs unlimited scaleScale a database in this order: each step is cheaper and less disruptive than the next.Most slow databases are slow queries. Fix those, pool connections, and cache before buying anything. Replicas and abigger instance carry most systems a long way. Partitioning and sharding scale writes but make every query, join andtransaction across shards harder, so they come last, unless the design starts on a store built for them.

Fix queries, pool connections with RDS Proxy, cache, add replicas, scale up; shard last.

Multi-AZ

A synchronous standby in another zone loses nothing on failover

Zone aZone bapp1 writePrimary2 copied before the commit returnsStandbyserves no reads3 acknowledgedzone a failspromoted to primaryno committed write lostA synchronous standby in another zone has every committed write, so failover loses nothing.Each commit waits until the standby has it too, adding a millisecond or two. In return the recovery point is zero:when the primary’s zone fails, the standby is promoted and the DNS name moves to it. This is RDS Multi-AZ. It is foravailability, not scale: a classic Multi-AZ standby cannot be read. A Multi-AZ DB cluster has two standbys that can.

A Multi-AZ standby receives every write before it commits, so failover loses no committed data.

Standby or replica · Going deeper

A standby is for staying up; a replica is for reading more

Multi-AZ standbyRead replicaCopysynchronousasynchronousPurposesurvive a zone failurescale reads, reportsServes readsno (Multi-AZ instance)yesData lost on failovernonewhatever had not arrivedFailoverautomatic, 60 to 120 smanual promotionOther regionsnoyes: cross-region replicasA standby is for staying up; a replica is for reading more. Exams test which one a scenario needs.“Survive an Availability Zone failure without data loss” means Multi-AZ. “Reports slow down the application” or“read-heavy” means read replicas. A replica can also be promoted for disaster recovery in another region, acceptingthat the last moments of writes may be missing. Many designs use both: Multi-AZ for the primary, replicas for reads.

Survive a zone with no data loss: Multi-AZ. Too many reads or slow reports: read replicas.

Aurora storage

Six copies in three zones, and every replica reads the same storage

Zone aZone bZone cWriterReplicaReplicaone shared cluster volume, 10 GiB segmentscopy 1copy 2copy 3copy 4copy 5copy 6A write counts once 4 of 6 copies have it; a read needs 3 of 6.Lose a whole zone (2 copies) and writes go on.Aurora keeps six copies of the data in three zones, and every replica reads that same storage.Instead of each database keeping its own disk, the instances share one volume that copies each piece six ways. Replicasdo not replay a separate copy, so they lag by milliseconds (typically under 100 ms), up to 15 can be added, and any onecan be promoted in about 30 seconds. Storage grows automatically. Azure SQL Hyperscale and Google AlloyDB work similarly.

Aurora writes each segment to six copies in three zones; a write needs four, so a lost zone does not stop it, and replicas lag by milliseconds.

Partitioning and sharding

Split data by key across machines, and choose a key that spreads load

shard = hash(user_id) mod 4user 17shard 1user 42shard 2user 99shard 3user 7shard 4Even keys: each shard takes a quartershard 125%shard 225%shard 325%shard 425%One celebrity key is 30% of all trafficshard 117.5%shard 217.5%shard 347.5%shard 417.5%Split data by key across machines, and pick a key that spreads both the data and the traffic.Partitioning places each row by its key: hash the key for an even spread, or use ranges for ordered scans. Writes thenscale with the number of shards. But the busiest key decides the busiest shard: here one shard carries 47.5% of the load.Choose keys with many distinct, evenly used values; split a hot key by adding a suffix (write sharding).

Partitioning places rows by key; the busiest key decides the busiest shard, here 47.5% of all load.

DynamoDB capacity · Going deeper

Throughput is priced in read and write units, rounded up by size

Reads1 read capacity unit (RCU) = one strongly consistent read a second, up to 4 KB, or two eventually consistent500 reads/s of 6 KB items → ⌈6 ÷ 4⌉ = 2 RCU each1,000 RCU strongly consistent500 RCU eventually consistent: halfWrites1 write capacity unit (WCU) = one write a second, up to 1 KB100 writes/s of 2.5 KB items → ⌈2.5 ÷ 1⌉ = 3 WCU each = 300 WCUEach partition serves up to 3,000 RCUand 1,000 WCU: one hot key hits that limithowever much the table has in total.DynamoDB prices throughput in units of reads and writes, rounded up by item size.Provisioned mode: you set RCU and WCU (with auto scaling) and pay for them every hour; cheapest for steady traffic.On-demand mode: you pay per request and it absorbs spikes with no planning; best for new or unpredictable tables.Smaller items cost fewer units. Global tables copy a table to other regions, all writable (active-active).

500 reads a second of 6 KB items need 1000 RCU strongly consistent, or 500 eventually consistent.

Consistency

Read from a copy and you may read the past

PrimaryReplica0 ms50 ms100 ms150 ms200 mswrite: name = “Ana”copied ~80 ms laterread at 40 ms: old nameread at 150 ms: “Ana”Fix where it matters: read your own writes from the primary, or ask for a strongly consistent read.Read from a copy and you may read the past. Eventually consistent means right, soon.Every asynchronous copy (replica, cache, other region) serves slightly old data for a moment. For most screens thatis fine. For the user who just saved, or for money, it is not. DynamoDB reads are eventually consistent unless you askfor strongly consistent ones, which cost twice the capacity. Choose per query, not per system.

Reads from replicas, caches and other regions are eventually consistent; ask for a strongly consistent read where it matters.

Lesson complete

You finished “Databases at scale”

Nicely done.

Open in a new tab