Why databases exist

Plain files lose data in two ways, and a database exists to stop both

A crash in the middle of a writeAccount A100 → 50, writtenAccount B20 → 70, never writtenpower fails hereA transfer of 50 is half done: it left A and never reached B.Two writers at the same momentDeposit 1reads 100writes 110Deposit 2reads 100writes 110Two deposits of 10. The balance is 110, not 120: one is lost.Durabilityonce COMMIT returns, the change survives a crashIsolationtransactions never see each other’s half-finished workA database is one program that owns the files and keeps two promises, so that no application has to.Durability costs a wait for the disk to confirm, 1 – 10 ms on an SSD, which is why databases batch many commits into one flush.

A database owns the files and makes two promises: committed work survives a crash, and concurrent work does not collide.

Why databases exist · Going deeper · 1 of 2

The database stack — bytes on disk to data pipelines Data pipelines batch · stream · warehouse · lakehouse · lineage Query languages SQL · MQL · Cypher · PromQL · vector search Query engine parser · planner · optimizer · executor Transactions ACID · isolation levels · MVCC · WAL · locks Access methods heap · B-tree · LSM tree · hash · bitmap · vector Storage fixed-size pages · WAL · fsync · checksums each layer hides the one below; the database holds its promise by coordinating all six

The database stack

Bytes on a page, indexes that find rows, transactions that hold invariants, replication that survives losing a machine, a planner that picks a plan, and pipelines that move it all to where analytics needs it. What differs between products is the access method and the language on top.

Why databases exist · Going deeper · 2 of 2

Files-and-locks done by hand versus a transactional database Files + locks (by hand) Transactional database every app reinvents the wheel one ACID API, many clients App A App B App C data.bin torn writes when power dies mid-update lost updates when two writers race no atomic multi-record changes recovery code is bespoke and rarely tested network-FS locks are unreliable App A App B App C DBMS BEGIN · COMMIT · ROLLBACK all-or-nothing commits snapshot reads ignore in-flight writes crash recovery is the database's problem one well-tested implementation, many clients

Doing it by hand versus a database

Without a database every application would write its own log, call fsync correctly on every platform, replay partial records after a crash and manage its own locks. Skipping the flush looks fast in benchmarks and corrupts data at the next power cut. Group commit batches many transactions into one flush.

SQL and the query planner

You state the question in SQL; a planner chooses how to answer it

SELECT o.id, c.nameFROM orders o JOIN customers c  ON o.cid = c.idWHERE o.total > 100What you wrote: the question.What the database knows about the data:orders: 10 million rows · customers: 200,000 rowsabout 5% of orders are over 100estimated cost of three ways to compute the same answerNested looplook up a customer for every order98 millionHash joinload customers into memory, scan orders once1.1 million: chosenMerge joinsort both tables, then walk them together4.5 millionSQL says what you want. A planner decides how, from statistics it keeps about your data.When the statistics are wrong, so is the choice: expecting 100 rows and meeting 10 million turns a 50 ms query into a 50 minute one.

The relational model separates what the data is from how it is stored, so a query planner can pick the cheapest route.

SQL and the query planner · Going deeper · 1 of 2

SQL is parsed, type-checked, planned logically, then planned physically SQL SELECT u.name FROM users u JOIN orders o ON o.user_id = u.id Logical plan (relational algebra) π_name ( σ_… ( users ⋈_id orders ) ) project · select · join — order not yet fixed cost-based optimizer Physical plan (operator tree) Project [u.name] Hash Join · u.id = o.user_id — right input — Seq Scan users Index Scan orders Choices the planner made: · hash join over nested-loop because orders is large · seq scan on users because no useful index on created_at · index scan on orders.user_id because the join key is indexed EXPLAIN ANALYZE shows actual rows + time per node — the canonical debugging tool.

From SQL text to a physical plan

A table has a primary key identifying each row; a foreign key in another table points at it. The query is parsed, checked, turned into a logical plan and then a physical one. EXPLAIN shows the choice; EXPLAIN ANALYZE runs it and shows the real row counts beside the estimates.

SQL and the query planner · Going deeper · 2 of 2

Three candidate physical plans for the same join, scored by the optimizer's cost model SELECT o.id, c.name FROM orders o JOIN customers c ON o.cid = c.id WHERE o.total > 100 stats: orders ≈ 10M rows · customers ≈ 200K rows · selectivity(total > 100) ≈ 0.05 Nested-loop join Hash join Merge join for each o in orders: if o.total > 100: lookup customers by o.cid build hash(customers) scan orders, filter, probe hash by cid sort orders by cid sort customers by cid merge in lockstep probe cost · 10M × log 200K I/O · heavy random build · 200K hashed I/O · two seq scans sort cost · two big sorts I/O · spill if cold cost ≈ 9.8e7 cost ≈ 1.1e6 ✓ cost ≈ 4.5e6 planner picks hash join: customers fits in memory, orders scanned once if customers had 200M rows, hash build would spill — merge join wins if orders had an index on cid and selectivity was very low — nested loop wins

Three plans, scored

The cost model counts rows and I/O per operator. Stale statistics are how a query silently regresses overnight. A separate trap: NULL is not equal to itself. WHERE x = NULL matches nothing; write IS NULL. And NOT IN with a NULL in the list rejects every row.

Write-ahead log

Write the intention to a log first, and a crash can always be repaired

COMMIT1 log itWrite-ahead logappend, then flush to disk2 only now is the client told “committed”3 laterData filesupdated in the backgroundThe log on disk when the power fails, oldest firstT1T1T1T1 ✓T2T2T2 ✓T3updateupdateupdateCOMMITupdateupdateCOMMITupdatepower fails1 Redo: replay every record, so the data files match the log.2 Undo: reverse whatever has no COMMIT. T3 disappears.T1 and T2 survive: exactly what their clients were promised. T3’s client was never told “committed”.A change is durable the moment its COMMIT record is in the log on disk. Everything else can catch up later.One small sequential write per commit, instead of many scattered ones. After a crash the log is the truth,and the data files are rebuilt from it.

The write-ahead log makes a transaction all-or-nothing and durable: logged before applied, replayed after a crash.

Write-ahead log · Going deeper

Write-ahead log: log first, ack the client, page-flush asynchronously, replay on crash client UPDATE x = x + 1 DB process build log record append + fsync WAL (sequential) LSN 41 · 42 · 43 · 44 · COMMIT ack to client later · background writer flushes dirty pages data pages (heap) random I/O · lazy on crash: 1. find last checkpoint LSN · 2. REDO every committed log record · 3. UNDO uncommitted txns data pages converge to the state implied by the log · committed work survives, in-flight work disappears one fsync on a small sequential file beats fsyncing every changed page

ACID and the log

Atomicity: all of a transaction or none. Consistency: declared rules hold before and after. Isolation: concurrent transactions appear to run alone. Durability: committed work survives. The log gives the first and last. PostgreSQL calls it the WAL, MySQL the redo log; the recovery algorithm, ARIES, is the same.

Check yourself

T1 updateT1 COMMITT2 updateT2 updatethe log, oldest first; the crash comes at the dashed line

The power fails with this log on disk. After restart, which changes are in the database?

Isolation anomalies

Two transactions can each be correct and still break a rule together

On callAlice ✓   Bob ✓Alice sees 2 on call: fine to goAlice takes herself offBob sees 2 on call: fine to goBob takes himself offOn callnobodyEach read a consistent picture. They changed different rows, so the database saw no conflict.Read committednever read unfinished workdefault: PostgreSQL, OracleSnapshotplus: one stable view throughoutdefault: MySQLSerializableplus: as if run one at a timethe only level that stops thisIsolation comes in levels, and the default level is not the strongest.This bug is write skew. Stopping it takes serializable isolation, an explicit lock on the rows you read, or a rule the databasechecks at the end. Stronger isolation means fewer surprises, and more waiting or retrying.

Isolation levels decide what concurrent transactions may see of each other. Only the strongest prevents every anomaly.

Isolation anomalies · Going deeper · 1 of 2

Isolation levels and the anomalies each one still allows Level Dirty read Non-repeat. Phantom Write skew Read uncommitted Read committed Repeatable read Snapshot Serializable possible possible possible possible possible possible possible possible possible possible prevented prevented prevented prevented prevented prevented prevented prevented prevented prevented

Levels and the anomalies each allows

Dirty read: seeing uncommitted data. Non-repeatable read: a row changes between your two reads. Phantom: a range query returns different rows the second time. Write skew: the doctors. PostgreSQL’s “repeatable read” is really snapshot isolation. Code that assumes serializable without setting it is where these bugs hide.

Isolation anomalies · Going deeper · 2 of 2

Write skew: two transactions read consistent snapshots, write disjoint rows, commit, invariant breaks invariant T1 (Alice) T2 (Bob) database on_call ≥ 1 at all times SELECT count(*) FROM oncall → 2 SELECT count(*) FROM oncall → 2 UPDATE me SET on_call=false UPDATE me SET on_call=false read snapshot S1 read snapshot S2 write Alice off write Bob off COMMIT ✓ COMMIT ✓ result: on_call = 0 — invariant broken, no transaction saw an inconsistent snapshot SERIALIZABLE detects the read/write conflict pattern and aborts one; snapshot isolation alone cannot

Write skew in detail

Each transaction reads a consistent snapshot and writes a different row, so no write conflict is detected. Fixes: lock the rows you read with SELECT … FOR UPDATE, add a constraint that checks the final state, or run at SERIALIZABLE and retry when the database aborts one of the pair.

MVCC

Keep old versions of a row, and readers never have to wait for writers

time →A beginsB beginsA sets 150B readsA commitsB reads againC begins, readssees 100still sees 100sees 150version 1: balance 100created long ago · replaced by Aversion 2: balance 150created by Atwo physical copies of the same row, kept side by sideAn update never overwrites. It adds a version, and each transaction keeps reading the ones that existed when it began.So readers and writers never block each other. The cost is space: old versions stay until no open transaction can stillsee them, and one forgotten hour-long transaction can bloat a whole table.

MVCC: an update adds a new version instead of overwriting, and every transaction reads a frozen snapshot.

MVCC · Going deeper

MVCC: each row carries xmin/xmax; readers see the snapshot they began with T100 T105 T110 T115 T120 time → id=7 name="Alice" bal=100 id=7 name="Alice" bal=80 id=7 name="Alice" bal=120 xmin=100 xmax=110 xmin=110 xmax=120 xmin=120 xmax=∞ reader at snapshot 105 sees v1 (bal=100) reader at snapshot 117 sees v2 (bal=80) writers don't block readers; readers don't block writers; only conflicting writers serialize

Versions, visibility and vacuum

Each row version records which transaction created it and which replaced it; a reader checks those against its snapshot. The alternative, two-phase locking, makes readers and writers block each other and must detect deadlocks. Old versions are removed by a background process (VACUUM in PostgreSQL), which a long-open transaction holds back.

Check yourself

On callAlice, BobAlice: checks rule, signs offBob: checks rule, signs offat the same time

A rule says at least one doctor must be on call. Two are, and both sign off at the same instant, each after checking the rule. Under the default isolation level, what happens?

B-tree indexes

An index finds one row in a billion by reading five pages

find id 512each node is one 8 KB page of several hundred keysroot1 – 399800 and up400 – 799512the row’s addressin memoryleaf: one disk readno index1,000,000,000 rows readB-tree4 or 5 pages readAn index is a sorted tree of pages. Hundreds of keys per page means a billion rows are only 4 or 5 steps deep.The price: every insert must update every index too. And an index only helps when the query wants a small shareof the rows, under roughly 5 – 10%. Beyond that, reading the whole table in order is faster.

A B-tree index is a shallow sorted tree of disk pages: lookups take a handful of steps instead of a full scan.

B-tree indexes · Going deeper

A 3-level B-tree (fanout shown small for clarity) 25 | 60 10 | 18 35 | 48 72 | 88 3,7 12,15 20,23 28,32 40,45 52,58 65,70 75,82 internal nodes hold separator keys; leaves hold (key, row pointer) pairs leaves are linked left-to-right (B+ tree variant) so range scans are sequential I/O balanced by construction: every leaf at the same depth on insert overflow, a node splits and promotes its median key to the parent

Inside a B-tree

Pages are 8 KB in PostgreSQL and 16 KB in MySQL. Every leaf sits at the same depth. When a leaf overflows it splits in two and its middle key moves up to the parent; only when the root splits does the tree grow taller. Partial indexes and covering indexes are often worth more than indexing a low-variety column.

LSM trees

For heavy writes, never edit a file: append, then merge later

Memorywritememtableevery write lands here: fastDisk, level 04 filesflushed as sorted files, never editedlevel 1merged together in the backgroundlevel 2each level about 10× the lastA read checks memory, then each level in turn. A small filter on every file answers “definitely not here”, so most files are skipped.A B-tree edits pages in place and reads fast. An LSM tree only ever appends, and writes fast.An LSM tree sustains 5 – 10× more writes on the same hardware, paid for with background merging and slower single lookups.PostgreSQL, MySQL and SQLite use B-trees. Cassandra and RocksDB use LSM trees. The index tells you the workload.

An LSM tree trades read speed for write speed. Which index a database uses tells you the workload it was built for.

LSM trees · Going deeper · 1 of 2

LSM tree: writes flow into the memtable, flush to L0, compact downward through exponentially larger levels memtable in-RAM · skip list flush L0 L0 L0 L0 L0 SSTables · may overlap in key range compact L1 L1 L1 L1 L1 ≈ 10× L0 · sorted, non-overlapping L2 (≈10× L1) … L3, L4, L5 — each ≈10× the previous writes · append-only — fast (no random I/O until flush) reads · memtable + bloom-filtered SSTables + merge compaction is the cost — write amplification of ≈10× total across all levels

Memtable, levels and compaction

RocksDB, Cassandra, ScyllaDB and HBase all run variants. Bloom filters cut read work by 10 – 100×. Some databases, such as CockroachDB and TiDB, put a SQL layer on top of an LSM key-value store.

LSM trees · Going deeper · 2 of 2

Lifecycle of one SELECT — parser, planner, executor, index, buffer pool, MVCC client query SELECT * FROM orders WHERE customer_id = 42 1 · Parser SQL → AST resolve names · check types 2 · Planner / optimizer pick idx_orders_customer over seq scan estimated rows = 30 3 · Executor · B-tree lookup descend idx_orders_customer to 42 return 30 (page, slot) row pointers 4 · Buffer pool · fetch heap pages most pages hit cache cold pages → 1 read per miss 5 · MVCC visibility filter drop versions newer than this txn drop versions deleted before snapshot 6 · Result rows to client a write would also: append a new row version flush WAL record before commit the same SQL string drives a different physical plan tomorrow if statistics or table size change

One SELECT, end to end

The query is parsed and planned; the executor walks the chosen index to get row addresses; the buffer pool, the database’s in-memory cache of pages, supplies those pages; and a visibility check drops row versions the transaction’s snapshot should not see.

Database families

Different shapes of question get different shapes of database

Relationalany question, joins,transactionsPostgreSQL, MySQLDocument{ name: "Ada",  address: {    city: "Paris"  } }whole objects thatvary in shapeMongoDBGraphfollowing chains ofrelationshipsNeo4jTime seriestime →appending, thensummarising by timePrometheus, InfluxDBVectorfinding what is mostsimilar in meaningpgvector, PineconeEach specialised store gives up general questions in order to be excellent at one.Large systems combine several: relational for transactions, documents for content, time series for telemetry, vectors for search.The lines blur: PostgreSQL alone now stores JSON documents, time series and vectors.

Document, graph, time-series and vector stores each specialise in one access pattern; relational stays the general tool.

Database families · Going deeper

Five database families, each shaped to its primary access pattern Relational Document Graph Time-series Vector id name v 1 A · 2 B · { "id": 1, "addr": { "city": "Paris" }, "tags": ["a","b"] } index-free adjacency timestamps · samples ≈1.5 bytes/sample k-NN in 768-d space HNSW · IVF · cosine PostgreSQL, MySQL, SQLite, Oracle MongoDB, Couchbase Neo4j, JanusGraph InfluxDB, TimescaleDB pgvector, Pinecone

Five families

Time-series stores compress to under 2 bytes per sample. Vector stores index points of 768 or 1,536 dimensions with HNSW, a layered graph giving fast approximate nearest neighbours at over 95% recall. Many of these stores choose availability over strong consistency: fine for a shopping cart, hostile for a bank balance.

Row and column storage

Store a table by row for transactions, by column for analysis

One table, and one question: what is the average age?Row storeeach row kept together1Alice30NYC2Bob42LA3Carol28NYChas to read every value to use one column in fourColumn storeeach column kept togetherid123nameAliceBobCarolage304228cityNYCLANYCreads only the age column: a quarter of the data here, 2% of a 50-column tableTransactionsthousands of tiny reads and writes a secondone row, in milliseconds → row storeAnalyticsa few enormous questions at a timemillions of rows, in seconds → column storeThe same table stored two ways, for two opposite jobs.Running a big analytical question on the transactional database, or the reverse, is behind most “the database is slow” reports.So teams keep both kinds, and move data between them.

OLTP touches one row at a time; OLAP scans one column of millions. The storage layout decides which is fast.

Row and column storage · Going deeper · 1 of 2

Row-major versus column-major storage of the same table Logical table id name age city 1 Alice 30 NYC 2 Bob 42 LA 3 Carol 28 NYC Row-major (heap) Column-major (Parquet) [ 1 · Alice · 30 · NYC ] [ 2 · Bob · 42 · LA ] [ 3 · Carol · 28 · NYC ] id 1 2 3 name Alice Bob Carol age 30 42 28 city NYC LA NYC + point lookups by row id are one read + INSERTs are one append − scanning one column reads every row − poor compression (mixed types per page) + scans of one column read only that column + excellent compression (uniform type per page) + vectorised SIMD execution per column − single-row INSERT/UPDATE is expensive

Row-major versus column-major

Parquet, ORC and the formats inside Snowflake, BigQuery and Redshift are columnar, adding dictionary and run-length encoding and per-page minimum and maximum values, which let a scan skip pages that cannot match. A single-row insert or update is expensive in a column store.

Row and column storage · Going deeper · 2 of 2

OLTP and OLAP workloads pull storage and engines in opposite directions OLTP OLAP transactional · row-oriented · point ops analytical · columnar · aggregate ops dimension value dimension value concurrency thousands of txns/sec query shape point lookups, small writes rows touched 1 to a few latency target single-digit ms storage layout row-major (heap + B-tree) freshness read-your-writes data volume GB to low TB canonical engine PostgreSQL · MySQL concurrency a few queries at once query shape scans, joins, aggregates rows touched millions to billions latency target seconds to minutes storage layout column-major (Parquet) freshness minutes to a day data volume TB to PB canonical engine Snowflake · BigQuery HTAP systems try to serve both — most teams just keep two systems and pipe between them

Two workloads, opposite needs

Transactional: thousands of operations a second, each touching a few rows, in single-digit milliseconds, on gigabytes to low terabytes. Analytical: a few queries at once, each over millions to billions of rows, in seconds to minutes, on terabytes to petabytes.

Check yourself

columns of the table (half of the 50 shown)

A table has 50 columns and a billion rows. A query averages one column. Roughly how much of the stored data must a column store read?

Data pipelines

Pipelines carry each change from where data is born to where it is used

AppDatabaserows, transactionsChange streamevery insert, update, deleteread from the database’s own log: no extra loadWarehousecolumns, historyReports, MLBatcha whole day at onceexact and simple to reason about,but the answer is hours oldStreamrecord by recordunder a second old, but late andout-of-order records need handlingpurple = when results are producedData is born in transactional databases and used somewhere else, so pipelines carry every change across.A warehouse keeps its storage to itself. A lakehouse keeps open files on cheap object storage, which many engines can read.When two reports disagree about the same number, lineage, the recorded path from source to chart, is how you find out why.

Data pipelines move changes from transactional databases into warehouses, in batches or as a continuous stream.

Data pipelines · Going deeper · 1 of 3

Batch processing aggregates closed windows; stream processing aggregates over open ones Batch Stream scheduled jobs over fixed windows continuous over open windows 00:00 06:00 12:00 18:00 24:00 job job job job + exact aggregates over closed windows + deterministic, easy to retry + scales with data volume − latency = window size − reprocessing ripples downstream tumbling tumbling tumbling tumbling overlapping (sliding) windows + sub-second latency + continuous aggregates and joins − ordering, lateness, exactly-once are hard − window choice changes the answer − back-pressure when consumers fall behind most platforms run both — stream for fresh, batch for canonical reconciliation

Batch and stream

Streaming adds windows, tumbling, sliding or per session, over which to aggregate, and rules for events that arrive after their window closed. Most platforms run both: a stream for freshness and a batch for the canonical, reconciled figures.

Data pipelines · Going deeper · 2 of 3

Change data capture: the OLTP database's WAL becomes a stream of row-level events application OLTP database CDC connector log / broker consumers app tables WAL Debezium Kafka topic INSERT/UPDATE commit appends to WAL events WAL position offset 100 offset 101 offset 102 offset 103 offset 104 warehouse search index cache invalidator audit / lineage event shape · { before, after, op (c|u|d), source: {ts, lsn, table} } guarantees · at-least-once delivery, in-commit-order, snapshot + tail bootstrap pitfalls · schema-change events need handling; huge transactions blow the connector buffer

Change data capture

Debezium and similar tools tail the write-ahead log and emit each row change in commit order. The transactional database becomes the source of truth for an event-driven system without any change to application code.

Data pipelines · Going deeper · 3 of 3

Closed warehouse vs open lakehouse: where the data lives and who can read it Warehouse (closed) Lakehouse (open) SQL engine Snowflake · BigQuery · Redshift Proprietary columnar storage opaque file format · vendor-tied + best query performance — engine and storage co-designed + workload management, governance in one product − lock-in: Spark, DuckDB cannot read the bytes directly − egress to other tools means exporting Spark Trino DuckDB Table format Iceberg · Delta · Hudi — ACID + time travel + schema evolution Parquet / ORC files on object storage S3 · GCS · Azure Blob — open, columnar, public spec + same bytes readable by many engines — no copy + storage and compute scale and price independently + open formats survive vendors − less integrated than a single-vendor warehouse − table format choice (Iceberg vs Delta vs Hudi) is a real commitment

Warehouse versus lakehouse

A lakehouse keeps Parquet files on object storage and adds a table format, Iceberg, Delta Lake or Hudi, that brings atomic commits, schema changes and time travel to plain files. ELT loads raw data first and transforms it inside the warehouse with SQL, which tools like dbt organise and test.

Summary

A database is a stack, and every layer rests on one promise

Pipelinescarry data to where questions are askedCopies and shardssurvive a lost machine, outgrow a single one (lesson 6)Query planneryou say what you want, it works out howTransactionsall or nothing, and isolated from each otherIndexesfind one row among a billion in a few stepsPages on disk and the logwhat survives a crashA database is a promise about durability and consistency. Every layer above is built on that promise.The same stack sits inside PostgreSQL, MySQL, MongoDB and the engines under modern warehouses.

From pages and the log up to pipelines, each layer adds one capability. Next: operations, keeping all of this running.

Summary · Going deeper · 1 of 2

Four replication topologies, each a different point on the consistency-latency-availability surface Primary-replica Multi-primary Synchronous Raft / Paxos async stream writes anywhere all-or-nothing commit quorum log replication P R R R P P P P W W W W L F F F quorum = 2 of 3 + low write latency + scales reads − stale reads on replicas − failover loses tail + writes anywhere + no single SPOF − conflicts need merge − causal hazards + no data loss + replicas read-fresh − slowest node sets latency − loses availability if any down + majority survives loss + linearizable reads − needs odd member count − cross-region RTT cost examples · PostgreSQL streaming · Galera/BDR · Galera-sync · etcd, Spanner, CockroachDB CAP corner · AP eventual · AP eventual + merge · CP loses A under partition · CP loses A under partition "semi-sync" — wait for one replica ack, async to the rest — is the most common pragmatic compromise

Replication topologies

An asynchronous primary with replicas is the default for read-heavy web apps. Consensus-replicated storage is the default where a committed write must never be lost. Multi-primary suits write-anywhere cases where the application can resolve conflicts.

Summary · Going deeper · 2 of 2

Three partitioning strategies for the same orders table Range partition Hash partition List partition PARTITION BY RANGE (created_at) PARTITION BY HASH (user_id) PARTITION BY LIST (region) orders_2024q1 created_at IN [Jan, Apr) orders_2024q2 created_at IN [Apr, Jul) orders_2024q3 created_at IN [Jul, Oct) orders_2024q4 created_at IN [Oct, Jan) shard 0 hash(user_id) % 4 = 0 shard 1 hash(user_id) % 4 = 1 shard 2 hash(user_id) % 4 = 2 shard 3 hash(user_id) % 4 = 3 orders_us region IN ('us-east','us-west') orders_eu region IN ('eu-west','eu-cent') orders_apac region IN ('ap-se','ap-ne') orders_default region NOT IN above + time-bounded queries scan one partition + trivial archival: drop old partitions − writes hot-spot on the newest partition + writes spread evenly across shards + no hot partition − range queries fan out to all shards + data residency / locality controls + regional ops stay in-region − skew if one region dominates traffic

Partitioning one table

Range partitioning suits time series and history; hash spreads point lookups evenly; list suits tenants and data-residency rules. Real systems combine them, for example by day and then by a hash of the user id.

Lesson complete

You finished “Data”

Answer the questions along the way to see your score here.

Open in a new tab