Database architectures

A database architecture is how a database system is put together: the components a query passes through inside one server, how that server maps connections onto processes or threads, and how several machines share, or refuse to share, memory, disks and the log. Knowing the architecture tells you where a database’s limits come from (the lock table, the single writer, the network hop) so you can choose the right class of system before you choose a product.

Picture it first: one kitchen, then three ways to grow

A busy thali restaurant has a host who seats diners and carries orders in, a manager who decides whether a cook is free or the order must wait, a head cook who reads each order and plans it (dal first, rotis last), line cooks who do the cooking, a pantry and a cold store, and an order book where every order is written down before cooking starts, so nothing is forgotten if the lights go out.

When the restaurant outgrows its kitchen, it has three ways to grow:

• One bigger kitchen with more cooks sharing one pantry, until the pantry door becomes the bottleneck.
• Several kitchens sharing one central cold store. Any kitchen can cook anything, but each must check that no other kitchen is using the same crate.
• Branches with their own pantries, each serving its own neighbourhood, coordinating only when one order spans two branches.

Map it back: the host is the client communications manager, the manager is the process manager with admission control, the head cook is the query optimiser, the line cooks are the executor, the pantry is the buffer pool, the cold store is the disks, and the order book is the write-ahead log. The three ways to grow are shared memory, shared disk and shared nothing.

Inside one server: five components

Hellerstein, Stonebraker and Hamilton’s survey Architecture of a Database System describes five main components that relational database servers share. Follow one query, a food-delivery app asking for the status of order 9182, through them:

1. Client communications manager. The app connects, directly (two-tier) or through an application server (three-tier). This layer speaks the wire protocol, authenticates, and later buffers result rows for the client.
2. Process manager. It gives the query a worker (a process or a thread) and makes the admission control decision: run now, or wait until memory and CPU are free.
3. Relational query processor. The parser checks syntax and permissions against the catalog; the rewriter simplifies the query (expanding views, folding constants); the optimiser picks access paths and join order using statistics; the executor runs the plan as a tree of operators, most often as iterators that each call next() on their inputs (query execution plans).
4. Transactional storage manager. Operators call access methods (heap files, B-trees), which read pages through the buffer pool. The lock manager keeps concurrent transactions correct (concurrency control), and the log manager makes committed changes durable and aborted ones undoable (write-ahead logging).
5. Back out. Rows flow up through the executor into the communications buffer and back to the app; at commit, the log is flushed and every layer cleans up.

Beside them sit shared utilities: the catalog and memory manager used by every query, plus background services such as statistics, backup, loading and replication.

Read the figure as a map of bottlenecks. Too many connections strain steps 1 and 2 (connection pooling); a bad plan is a step 3 problem; a working set bigger than the buffer pool, hot locks and slow log flushes are step 4 problems. Scaling architectures differ mainly in which of these parts they copy, split or share.

Process models: who runs your query

The process manager maps connections onto operating-system workers in one of three ways, as the survey describes them:

PostgreSQL’s documentation calls its model “process per user”: the postmaster spawns one backend per connection. MySQL runs one thread per connection unless you buy its commercial thread pool. Oracle offers both a dedicated server process per client and a shared-server mode in which each server process serves many clients. In 2007 the survey listed DB2, PostgreSQL and Oracle as process-per-worker systems and DB2, SQL Server, MySQL, Informix and Sybase as thread-per-worker; products change, so check current documentation before quoting one. An external pooler such as PgBouncer turns a process-per-connection server into a pooled one from the outside; PostgreSQL vs MySQL has the memory arithmetic.

Embedded or client-server

Everything so far assumed a client-server database: a separate server process that many applications reach over the network (client-server architecture). The alternative is an embedded database, a library linked into your program.

SQLite’s own advice is blunt: it “does not compete with client/server databases. SQLite competes with fopen().” Its architecture is the same anatomy compressed into a library: a tokenizer and parser, a code generator that compiles SQL into bytecode, a virtual machine that runs it, a B-tree layer, and a pager that caches fixed-size pages (4,096 bytes by default), provides atomic commit and locks the database file. There is no communications or process manager, because your own threads call it.

What you gain: no server to install, patch or fail over; a query is a function call, not a network round trip; the whole database is one portable file. What you pay, in SQLite’s words:

• One writer at a time. Readers are unlimited, but writers queue. Fine when each write takes milliseconds; wrong for write-heavy multi-user systems.
• Not over a network filesystem. Many network filesystems have buggy file locking, which can corrupt the database when several machines write.
• Size. 281 TB maximum, in one file.

Even so, SQLite says it suits most low-to-medium-traffic websites, with “fewer than 100K hits/day” as a conservative rule of thumb, and some servers keep one SQLite file per user. DuckDB applies the embedded idea to analytics: it runs inside the host process by default and uses a columnar-vectorized engine built for scans and aggregations rather than single-row lookups.

Many machines: shared memory, shared disk, shared nothing

When one machine isn’t enough, the question is what the machines share. Michael Stonebraker named the three answers in his 1986 paper The Case for Shared Nothing, and the terms are still how distributed databases are classified.

Shared memory. All processors see the same RAM and disks. Today that is simply a multi-core server: the process models above already run on it, with one lock table and one buffer pool. You gain the simplest programming model; you pay with a ceiling at the largest machine you can buy, and the machine fails as a unit. Stonebraker’s verdict was that it “does not scale to a large number of processors”.

Shared disk. Each node has its own CPUs and memory, but all nodes reach the same disks, usually over a storage network. Any node can read any data, partitioning is optional, and a failed node doesn’t make data unreachable. The price is coordination: each node caches pages and takes locks locally, so the cluster needs a distributed lock manager and a cache-coherence protocol to keep buffer pools consistent, which the survey calls complex and a bottleneck under contention. Oracle RAC is a prominent example: several instances open one database, and Cache Fusion ships block images between instances’ buffer caches over a private interconnect while the Global Cache and Global Enqueue Services track who holds which block and lock.

Shared nothing. Each node owns its CPUs, memory and disks, and nodes talk only by messages. Tables are split horizontally (by hash, range or round-robin) and each node handles locking and logging for its own slice. You gain capacity by adding nodes, with no shared component to saturate. You pay with partitioning work, distributed deadlock detection and two-phase commit for transactions spanning nodes, and partial failures. Stonebraker’s key argument: if you can partition so that most transactions are locally sufficient (touch one node), messages stay cheap. He called such workloads “delightful” and estimated 85% of the TP1 benchmark’s transactions were.

Three ways to grow a relational database

Real deployments combine these ideas into three topologies. Each has its own deep-dive page; here is how they compare architecturally.

Single writer with replicas. One primary takes every write and ships its log to replicas, which serve reads and stand by for failover. In hardware it is shared nothing, but logically there is one writer, so you keep full SQL and single-node transactions. You pay with a write ceiling of one machine and replica lag (data replication).

Sharded shared nothing. Several independent primaries, each owning a slice of the data and each with its own replicas, behind a router: application code, Vitess for MySQL or Citus for PostgreSQL. Writes scale with the number of shards; you pay with cross-shard queries and transactions, resharding, and a shard key you must choose well (database sharding).

Distributed SQL. The database shards itself. Spanner divides tables into splits of contiguous rows, replicates them with Paxos, and lets each replica set’s elected leader handle writes, which commit once a majority of voting replicas agree. CockroachDB splits data into ranges, replicates each to three nodes by default with Raft, and accepts SQL on any node. You gain automatic sharding with ACID transactions across ranges; you pay a consensus round trip on every write, more latency for transactions that span ranges, and retries under contention (consensus and leader election).

Cloud-native: separating compute from storage

The newest family keeps a familiar engine on top but replaces its storage layer with a replicated network service. Amazon Aurora is the reference design, described in a SIGMOD 2017 paper.

Its starting point is that the bottleneck in the cloud is the network. A traditional engine writes data pages, log records and extra copies (MySQL’s double-write buffer and binary log) to storage, and a synchronously mirrored setup does much of that on every copy, waiting on each in turn. Aurora’s answer is “the log is the database”: the only writes that cross the network are redo log records. Storage nodes apply those records to build pages in the background; the database never writes pages, not for checkpoints and not for cache eviction.

Durability comes from quorums. Each 10 GB segment of the volume is stored six times across three availability zones, two per zone. A write needs 4 of 6 acknowledgements and a read quorum is 3 of 6. Check the arithmetic: 3 + 4 = 7 > 6, so read and write sets always overlap, and 4 > 6 ÷ 2, so two writes can’t both succeed without seeing each other. Lose a whole zone (2 copies) and 4 remain, still a write quorum; lose a zone plus one more node and 3 remain, still enough to read and rebuild. Small segments keep repair fast: 10 GB over a 10 Gbps link is 80 Gb ÷ 10 Gbps = 8 s, in line with the paper’s 10-second figure.

A cluster has one writer and up to 15 read replicas on the same volume. Replicas add no storage or write cost; they receive the writer’s redo stream and apply it to pages they already cache, and the paper reports they typically lag by 20 ms or less. Crash recovery generally takes under 10 seconds, because redo is applied continuously by storage rather than replayed at startup. In the paper’s 30-minute write-only benchmark, Aurora sustained 35× more transactions than a synchronously mirrored MySQL setup, with 7.7× fewer I/Os per transaction on the database node. Today’s documentation puts the maximum volume at 256 TiB.

You gain fast failover (a replica already sees the same storage), storage that grows by itself, cheap replicas and continuous backup. You pay with still one writer, a network round trip whenever a page isn’t cached, and a design you can only rent from one cloud.

Others push the split further. Neon runs unmodified PostgreSQL as stateless compute that streams WAL to safekeepers, which agree on it with a Paxos-based quorum; a pageserver rebuilds any page version on demand, and object storage keeps history. That turns branching a database or scaling compute to zero into metadata operations. Warehouses do the same for analytics: Snowflake describes itself as a hybrid, with one central store like shared disk and MPP compute clusters that cache their part of the data locally, like shared nothing (OLTP vs OLAP).

Storage engines, row vs column, and memory

Three more choices sit inside the storage manager. Each has its own page, so here is one paragraph apiece.

B-tree or LSM. A B-tree engine updates pages in place through the buffer pool and a write-ahead log, which favours reads; an LSM engine buffers writes in memory and flushes sorted files that background compaction merges, which favours write throughput at the cost of read and space amplification (reads and writes).

Row or column. Row stores keep each row’s fields together, ideal for fetching one order by key; column stores keep each column together, compress well and scan only the columns a query needs, ideal for aggregates over billions of rows (OLTP vs OLAP).

On disk or in memory. In-memory databases keep the whole dataset in RAM and use the disk only for durability; Redis, for example, can persist with point-in-time snapshots or an append-only log of writes. You gain microsecond access and pay in RAM cost and a recovery story that depends on that log.

Choosing an architecture

Numbers for the interview

The defensible default is one primary with replicas, and you leave it when you hit one of three walls. Each can be estimated.

Write rate. Assume a simple write transaction costs about 0.5 ms of server CPU (an assumption; measure yours). A 32-vCPU primary at a 60% target gives 32 × 0.6 ÷ 0.0005 ≈ 38,000 transactions a second. If indexes don’t fit in memory, disk I/O caps you much lower; database scaling works that case through.

Data size and restore time. Restoring 10 TB at 500 MB/s takes 10,000,000 MB ÷ 500 MB/s = 20,000 s ≈ 5.6 hours. If your recovery-time objective is one hour, either shard (ten 1 TB shards restore in parallel in 2,000 s, about 33 minutes) or use storage where point-in-time restore is a metadata operation, as Neon advertises (disaster recovery).

Geography. Mumbai to Frankfurt is about 6,560 km in a straight line. Light in fibre covers roughly 200,000 km a second, so one round trip takes at least 2 × 6,560 ÷ 200,000 s ≈ 66 ms. Every write from Europe to a Mumbai primary pays that. Shard users to a home region, or use distributed SQL that places each range’s leader near its users (multi-region and active-active).

Reads rarely force a change: at 60,000 reads a second and an assumed 15,000 per replica, you need 4 replicas plus a spare, well within Aurora’s 15.

Going deeper: where each architecture breaks

• One server. The ceiling is the biggest machine plus the operational limits around it: backups, restores, index builds and vacuum on multi-terabyte tables take hours.
• Shared disk. A hot block ping-pongs between nodes’ caches over the interconnect, so contention that one server handles in memory becomes network traffic. And because every node reads the same pages, a page corrupted before it reaches storage is corrupt for all of them, a weakness the survey points out.
• Shared nothing. Hot partitions, scatter-gather queries whose latency is set by the slowest shard, two-phase commit for cross-shard writes, and resharding while live (distributed transactions).
• Distributed SQL. Every write pays a consensus round trip; contended rows cause transaction retries the application must handle; a range that loses its majority stops accepting writes.
• Cloud-native storage. A cold cache turns every page read into a network round trip; the single writer remains the write ceiling; the quorum maths assumes independent failures, which is why Aurora invests in fast repair of small segments.
• Embedded. Writers serialise on one file lock, and a database file on a network filesystem can be corrupted by broken locking.

Cheat sheet

How to say it in the interview

“Inside one server, a query goes from the communications manager to a process or thread, through parse, rewrite, optimise and execute, and into the storage manager’s buffer pool, lock manager and log. When one server isn’t enough, the question is what the machines share. Shared memory is one big box. Shared disk lets every node reach the same storage but needs distributed locking and cache coherence, as in Oracle RAC. Shared nothing partitions the data, scales best, and needs two-phase commit across partitions. Here I’d start with a managed single-writer PostgreSQL plus replicas, possibly Aurora-style storage for fast failover, and move to sharding by tenant only when writes or restore time outgrow one primary.”

Why does process-per-connection matter for design? Each connection costs a whole OS process, so memory and context switching grow with client count. Put a pooler in front and keep active connections near the core count; a thread-per-connection server tolerates more connections but hits the same CPU limits.

What is the difference between shared disk and shared nothing? In shared disk every node can read every page, so nodes must coordinate locks and keep their caches coherent. In shared nothing each node owns a partition and coordinates only for work that spans partitions, using two-phase commit. Shared nothing scales further; shared disk avoids choosing a partition key.

How is Aurora different from a primary with replicas? Only redo log records leave the writer, and storage turns them into pages. Six copies across three zones with a 4-of-6 write quorum make the volume durable on its own, so replicas share it instead of keeping copies, and failover needs no data copy. It is still one writer.

When would you choose distributed SQL over sharding? When you need strongly consistent transactions across what would be shard boundaries, or writes in several regions, and you can accept consensus latency on every write. If data splits cleanly by tenant and cross-tenant transactions are rare, sharding a familiar database is simpler.

Where to go next

• SQL vs NoSQL and database types: the database families, before you choose an architecture.
• PostgreSQL vs MySQL: two engines, two process models and storage layouts.
• Database scaling: the full ladder, from query tuning to distributed SQL.
• Data replication: leaders, followers, lag and failover.
• Database sharding: shard keys, routing and resharding.
• Consensus and leader election: the Raft and Paxos underneath distributed SQL.
• Reads and writes: B-trees, LSM-trees and the buffer pool.
• OLTP vs OLAP: row and column stores, and warehouses with separate storage.

References and further reading

1. Architecture of a Database System (Hellerstein, Stonebraker and Hamilton, Foundations and Trends in Databases, 2007)15721.courses.cs.cmu.eduThe five main DBMS components and a query’s path through them, the three process models, and shared-memory, shared-nothing and shared-disk designs with examples.
2. The Case for Shared Nothing (Stonebraker, 1986)web.archive.orgThe original comparison of shared memory, shared disk and shared nothing, and the argument that most transactions can be kept on one node.
3. SQLite: Appropriate Uses For SQLitesqlite.orgSQLite competes with fopen(), not client/server engines; one writer at a time, the 281 TB limit, the network filesystem warning and the 100K hits/day rule of thumb.
4. Why DuckDBduckdb.orgAn in-process analytical database with a columnar-vectorized engine, embedded in the host process by default.
5. Oracle Real Application Clusters Administration: Introduction to Oracle RACdocs.oracle.comMany instances opening one database, and Cache Fusion with the Global Cache and Global Enqueue Services keeping buffer caches and locks in sync.
6. Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases (Verbitski et al., SIGMOD 2017)amazon.scienceOnly redo crosses the network, six copies in three AZs with 4/6 write and 3/6 read quorums, 10 GB segments, replica lag and recovery numbers.
7. Amazon Aurora User Guide: Aurora DB clustersdocs.aws.amazon.comOne primary (writer) instance and up to 15 Aurora Replicas sharing one cluster volume, with automatic failover to a replica.
8. Spanner documentation: Replicationcloud.google.comSplits of contiguous rows replicated with Paxos, a leader per replica set handling writes, and commits needing a majority of voting replicas.
9. CockroachDB documentation: Architecture Overviewcockroachlabs.comSQL on any node, data split into ranges replicated three ways by default, and Raft consensus per range.
10. Neon documentation: Architecture overviewneon.comStateless PostgreSQL compute streaming WAL to a Paxos quorum of safekeepers, with a pageserver rebuilding pages on demand.