0%
Database Internals: Build the Engine
Storage Structures
Durability
Concurrency Control
Query Processing
Access Paths
Distribution
The Anatomy of a Database
A database engine is not one program with one idea in it. It is six or seven subsystems with sharply different jobs, assembled in a fairly standard arrangement that has barely changed since the 1980s, and knowing the arrangement is most of knowing how databases behave.
Why a decomposition is worth learning at all
Because every surprising thing a database does is attributable to one of these subsystems, and without the decomposition every surprise looks like magic. A query plan that changed overnight, a lock that appeared from nowhere, a recovery that took an hour, a disk that filled up during a read-only workload: each belongs to exactly one layer, and naming it is most of the diagnosis.
The alternative is a mental model in which the database is a black box that is sometimes slow, and that model cannot be improved by experience, only accumulated as folklore. The arrangement in this section is what turns a surprising behaviour into a question with an answerable form.
The connection and session layer
At the front sits whatever accepts connections, authenticates them, and holds the per-session state: the current transaction, temporary objects, session variables, prepared statements and the client's view of what is in flight.
This layer is unglamorous and causes a surprising amount of operational trouble, because session state is memory that scales with connection count and because connection establishment is expensive in every real engine. Connection pooling exists entirely because of this layer, and most of the difference between a database that handles ten thousand clients and one that falls over at five hundred is here rather than in the query engine.
The parser and the binder
The parser turns text into a syntax tree. The binder, sometimes called the analyser or resolver, attaches meaning to the names in that tree by looking them up in the catalog: which table, which column, which type, which function.
Separating these two is not pedantry. Parsing can be done without touching any shared state and is therefore cheap and cacheable; binding requires the catalog and is sensitive to concurrent schema changes, which is why a statement prepared before a column was dropped must be invalidated. The binder is also where type coercion decisions get made, and those decisions quietly determine whether an index can be used later.
The rewriter
Between binding and planning, most engines apply transformations that preserve meaning but change shape: expanding views, unnesting subqueries, pushing predicates down, simplifying constant expressions, and converting some outer joins to inner ones.
These rewrites are algebraic rather than cost-based, meaning they are applied because the result is always at least as good, not because a model said so. This distinction matters when reading a plan, because a shape you did not write and do not recognise may have arrived here rather than from the optimizer, and the two are debugged differently.
The optimizer
The optimizer chooses among the many physically distinct ways to compute the same logical result: which index to use, which join algorithm, which join order, whether to sort or hash, whether to parallelise. It chooses by estimating costs from statistics.
This is the subsystem with the worst reputation and it is mostly deserved, not because the algorithms are bad but because the estimates feeding them are. An optimizer given accurate cardinalities makes good decisions almost always; the same optimizer given an estimate that is wrong by a factor of a thousand makes a catastrophic one, and nothing downstream can recover.
The execution engine
The executor runs the chosen plan, pulling or pushing rows through a tree of operators: scans, filters, joins, aggregations, sorts. How it does this, one row at a time or in batches, defines its performance character more than any individual operator.
This is the layer where the difference between a transactional and an analytical engine is most visible, since the same logical operators implemented row-at-a-time and vector-at-a-time differ by an order of magnitude on large scans and in the opposite direction on tiny lookups. A later lesson is devoted to this choice.
The access methods
Below the executor sit the structures that actually find rows: heap files, B-trees, hash indexes, LSM trees. They expose a narrow interface, roughly "give me the row for this key" and "give me rows in this range", and hide everything about how pages are arranged.
That narrow interface is what makes a storage engine replaceable, and it is why the same SQL layer can sit on top of radically different storage. It is also where the read and write amplification characteristics of the whole system are determined, since the access method decides how many pages must be touched to answer a question.
The buffer pool
Pages live on disk and are operated on in memory, and the buffer pool is the fixed-size region of memory holding recently used pages along with the bookkeeping that decides which to keep. Every read and write goes through it.
This is the single most important performance structure in a transactional database, because the ratio of requests served from it to requests that reach storage is the difference between microseconds and milliseconds. It is also where the engine's most interesting policy decisions live: what to evict, when to write a modified page back, and how to prevent a large scan from destroying everything useful in it.
The storage manager
Underneath, something owns the files: allocating space, tracking which pages are free, grouping pages into larger units, and issuing the actual reads and writes to the operating system or the device.
Its decisions are invisible until they are not. Whether logically adjacent pages are physically adjacent determines whether a range scan is sequential or random, which on spinning disks was a factor of a hundred and on modern storage is still a factor of several. Space reclamation also lives here, which is why a table that has had most of its rows deleted does not necessarily get smaller.
The three managers that cut across everything
Transactions, locks and logs are not a layer in the stack. They are concerns that reach into every layer: the transaction manager tracks who is doing what, the lock manager arbitrates conflicts, and the log manager records changes so they survive a crash.
Drawing them as a column beside the stack rather than a box inside it is the single most useful correction to the naive picture. It explains why a question about isolation cannot be answered by looking at the executor, why durability is not a property of the storage manager alone, and why these three subsystems are where the genuinely difficult engineering is.
The catalog, which is a database inside the database
Table definitions, column types, index descriptions, statistics and permissions all have to be stored somewhere, and the somewhere is usually ordinary tables managed by the same engine, with a small bootstrap to get started.
This self-hosting is elegant and has real consequences: catalog access is subject to the same locking and caching as user data, which is why a schema change can block queries, and why a catalog that has grown very large through many temporary tables slows down planning for everything. Statistics being catalog data is also why they can be stale, which brings the optimizer's problems back around.
What to take from the layers
Three things. The arrangement is standard across engines, so learning it once transfers to all of them. The vertical path from text to rows is six subsystems with clean handoffs, and almost every performance question is attributable to one of them. And transactions, locks and logs cut across the whole stack rather than sitting in it, which is why they are both the hardest part of the engineering and the hardest part to reason about from the outside.
Draw transactions, locks and logs as a column beside the stack rather than a box inside it. The naive picture puts them somewhere near storage, and that picture cannot explain why an isolation question involves the executor, why a schema change takes a lock, or why durability is not the storage manager's problem alone. The redraw is the single most useful correction to make early.
Tracing one statement through the subsystems is the fastest way to make the arrangement concrete, and the difference between a read and a write is where most of the interesting detail lives.
Arrival and session setup
A statement arrives on a connection that already exists, which matters because establishing one would involve authentication, a handshake, memory allocation and often a fork or a thread assignment. The session's transaction state determines how the statement is treated.
If no transaction is open, most engines begin an implicit one that commits at the end of the statement, which is why a single statement is still atomic. If one is open, the statement joins it and its effects become visible to others only at commit. This is the first point at which the cross-cutting transaction manager touches a statement, and it happens before any parsing.
Parsing, and why the cache matters here
The text is tokenised and parsed into a tree. Because this is pure computation over the text, the result can be cached by statement text, and engines do, since parsing the same query ten thousand times per second is pure waste.
The cache is also the origin of a class of confusing behaviour, because what is cached is often not just the parse but the whole plan, keyed by the statement text or by a prepared statement handle. A plan chosen for one parameter value and reused for another with a very different selectivity is the parameter sensitivity problem, and it begins here.
Binding against the catalog
Names become object identifiers. This requires reading the catalog, which requires locks, which is why a long-running query holds a lock that blocks a concurrent attempt to drop or alter the table it refers to.
That lock is a frequent operational surprise. The query is reading data and seems like it should conflict with nothing, but it is also depending on the table's definition remaining valid for its duration, and the engine enforces that with a lock most people never think about. This is the mechanism behind a schema migration that hangs behind an analytics query.
Rewriting into a canonical shape
Views are expanded into their definitions, subqueries are often converted into joins, predicates are pushed toward the scans, and constants are folded. The query the optimizer sees may look quite unlike the one that was written.
This is why reading an execution plan sometimes shows operations against tables the statement never mentioned, or an absence of a subquery that is plainly in the text. Knowing that the rewriter exists converts those from mysteries into expected behaviour, and knowing that its transformations are unconditional explains why they cannot be turned off selectively.
Planning, and what it needs from the catalog
The optimizer enumerates plans and prices them. Its inputs are the available access methods for each table, the statistics on each column, and a cost model relating page reads, CPU and memory to a single comparable number.
The enumeration is bounded, because the number of join orders grows factorially and a query with fifteen tables cannot be fully explored. So the plan chosen is the cheapest among those considered, which is a weaker claim than the cheapest available, and for large queries the gap between those two can be substantial. This is one reason very wide queries behave unpredictably.
Execution, operator by operator
The executor walks the plan tree. In the classical model each operator asks its child for the next row, so execution is driven from the top by repeated calls that propagate down to the scans and return rows back up.
The important consequence is that nothing is materialised unless an operator requires it. A query returning the first ten rows of an unsorted scan reads approximately ten rows' worth of pages, while the same query with a sort must consume everything before producing anything. Which operators block in this way is the most useful thing to know when reading a plan.
Where the pages come from
A scan asks the access method for rows, the access method asks for specific pages, and the buffer pool either has them or fetches them from storage. This is the only path by which data reaches a query.
Everything about query latency in a transactional workload reduces to how many pages this path touches and how many of them were already resident. An index lookup touching four pages all resident is microseconds; the same lookup with four storage reads is milliseconds. The difference is a factor of a thousand and it is invisible in the plan, which is why plans and timings must be read together.
What an update adds
A write does everything above and then more: it must acquire locks on what it modifies, record the change in the log before the change reaches disk, modify the page in the buffer pool, mark that page dirty, and maintain every affected index.
The index maintenance is the part people underestimate. A table with six indexes turns one logical row update into seven physical structure modifications, each with its own page reads, page writes and log records. This is the entire explanation for why insert throughput falls as indexes are added, and why the right number of indexes is a trade rather than a maximum.
Write-ahead logging in the path
Before a modified page may be written to its home location, the log record describing the modification must be on durable storage. This ordering is the whole of what makes crash recovery possible, and it is enforced on the write path of every statement.
Its cost shape is unusual and worth noting now: the log write is sequential and small, the page write is random and large, and the ordering means the fast write gates the slow one rather than the other way round. That is why the log device matters disproportionately, and why commit latency is dominated by one flush rather than by the size of the change.
Commit, and what it actually waits for
At commit the engine flushes the log up to this transaction's last record and then reports success. It does not wait for the modified data pages, which are written back later by a background process at its own convenience.
This separation is the central trick of database durability and it is worth stating plainly: the data you committed is not on disk in its home location, but a description of it is, and that is sufficient because recovery can reconstruct the rest. Everything about checkpoints, recovery time and the behaviour after a crash follows from this one design decision.
Returning rows, and the cost of the client
Results are serialised and written to the connection, often incrementally as the client reads them. A client that reads slowly holds the query, its locks and its resources open for the duration.
This is a real and routinely overlooked failure mode. A report that fetches a million rows into an application that processes each one as it arrives keeps a transaction open for minutes, which in an MVCC engine prevents cleanup of old row versions and in a locking engine blocks writers. The fix is almost always to fetch in batches rather than to tune anything in the database.
What to take from the path
Three things. Binding takes catalog locks, which is why reads block schema changes and why a migration can hang behind an analytics query. A write is a read plus locks plus a log record plus index maintenance for every index, which is why writes scale differently from reads. And commit waits for the log and not for the data pages, which is the design decision from which checkpoints, recovery and crash behaviour all follow.
A client that reads results slowly holds the query, its snapshot and its locks open for the whole time it takes. A report streaming a million rows into an application that processes each as it arrives keeps a transaction open for minutes, which blocks version cleanup in an MVCC engine and blocks writers in a locking one. The fix is batched fetching in the application, not a database setting.
Databases do not read rows from disk. They read fixed-size pages into a cache they manage themselves, and almost everything distinctive about database performance follows from that choice and from the engine's refusal to let the operating system make these decisions for it.
Why the unit is a page and not a row
Storage devices and operating systems transfer blocks, not bytes, so there is no such thing as reading one row from disk. Fixing a page size, commonly four, eight or sixteen kilobytes, makes every structure in the engine a graph of fixed-size nodes, which simplifies everything above.
The consequences are pervasive. Space allocation becomes bookkeeping over equal-sized slots. A row must fit in a page or be handled specially, which is why large values are stored out of line. And the page is the unit of locking, of logging, of checksumming and of transfer, so one decision propagates into every subsystem.
What a page contains
A header with identity and version information, a region of row data, a directory mapping slot numbers to offsets within the page, and usually free space in the middle that lets rows grow or be added without rewriting the page.
The indirection through the slot directory is what allows a row to move within its page without invalidating references to it, which matters because an update that makes a row longer has to put it somewhere. This small design choice is the reason index entries can point at a page and a slot rather than a byte offset, which in turn is why a page can be reorganised cheaply.
The buffer pool as the engine's own cache
A fixed-size array of page-sized frames, a hash table mapping page identifiers to frames, a replacement policy deciding what to evict, and per-frame state recording whether a page is dirty and how many operations are currently using it.
The pin count is the part that distinguishes this from an ordinary cache. A page being read by an operator is pinned and cannot be evicted regardless of what the replacement policy wants, because evicting it would pull memory out from under running code. Correctness constraints like this are why a database cannot simply use a generic cache.
Why the engine refuses the operating system's cache
The operating system already caches file pages, and a database could rely on that. Most do not, because the operating system cannot know which pages matter, cannot be told not to evict a pinned page, and gives no control over when a write becomes durable.
That last point is the decisive one. Write-ahead logging requires knowing that a specific write has reached stable storage before another write is issued, and a cache that may reorder or defer writes at its own discretion makes that guarantee unobtainable. The engine needs the ordering, so the engine must own the cache.
Direct I/O and double buffering
When an engine manages its own cache and the operating system also caches the same file, every page occupies memory twice and is copied an extra time. Direct I/O bypasses the operating system cache to avoid exactly this.
The trade is real rather than obvious. Direct I/O gives control, removes the duplication and makes performance predictable, at the cost of losing the operating system's readahead and its own buffering, which the engine must then reimplement. Engines differ on this: some use direct I/O everywhere, some use it only for the log, and some rely on the operating system cache deliberately and are simpler for it.
Durability and the many places a write can rest
A write passes through the engine's buffer, possibly the operating system's page cache, possibly the storage device's volatile write cache, and only then the medium. Each of those is a place where it can be lost in a power failure.
This is why fsync and its relatives exist and why their behaviour is so heavily scrutinised: the call is the only mechanism for pushing a write through those layers, and a device whose write cache lies about having done so breaks durability in a way no software can detect. A database's durability guarantee is only as good as the weakest layer in that chain.
Sequential and random access, which still differ
Reading a hundred adjacent pages in one request is far cheaper than a hundred requests for scattered pages, even on solid state storage where the ratio is smaller than it was on spinning disks. Access methods are designed around this fact.
It is why a B-tree tries to keep logically adjacent leaves physically adjacent, why an LSM tree writes large sorted runs, why table scans can beat index scans past a certain selectivity, and why fragmentation matters. The ratio changing with hardware shifts where those crossovers fall but does not remove them, which is why cost models remain parameterised rather than fixed.
Readahead, and why selectivity decides the plan
When the engine detects a sequential pattern it issues reads ahead of demand, turning what would be a series of waits into a stream. This changes the cost of a scan by a large factor and therefore changes which plan is correct.
The crossover is the interesting part. For a predicate matching one percent of a large table an index is clearly better; for one matching forty percent a sequential scan with readahead usually wins, because the index path does random reads and visits most pages anyway. The optimizer's job is to find that crossover, and its estimate of selectivity is precisely what it needs to do so.
Writing pages back, and why not immediately
A dirty page could be written as soon as it is modified, and no engine does this. Deferring lets several modifications to a page be combined into one write, lets writes be scheduled when the device is idle, and lets them be issued in an order the device likes.
The cost of deferring is recovery time, since every modification not yet in its home location must be replayed from the log after a crash. Checkpoints exist to bound that, and the tuning question for every engine is the same trade: write more eagerly for faster recovery, or more lazily for better throughput.
Keeping a scan from destroying the cache
A single large table scan reads more pages than the buffer pool holds and, under a naive replacement policy, evicts everything useful in favour of pages it will never look at again. Every serious engine defends against this.
The defences are instructive because they are all forms of the same insight, that recency alone is a bad predictor. Favouring pages touched more than once, keeping scan pages in a small dedicated region, or admitting them to the pool only on a second access all encode the same idea, and it recurs in every cache design in this course.
Checksums and detecting a page that lies
Pages carry a checksum verified on read, because storage can return a page that is corrupt or is an old version of itself without reporting any error. Without the check, the engine would operate on wrong data and propagate it confidently.
The failure this guards against is more common than it sounds and particularly nasty, since an undetected bad page can be written into a backup and replicated to every replica, destroying the redundancy that was supposed to protect against it. Verification on read is cheap and the alternative is silent corruption, which is why it is on by default almost everywhere now.
What to take from the storage boundary
Three things. The page is the unit of everything, so one choice about its size and layout propagates into locking, logging, allocation and transfer. The engine owns its cache rather than using the operating system's because write-ahead logging requires control over write ordering, which no shared cache can offer. And a write rests in several caches on its way to the medium, so a durability guarantee is only as strong as the weakest layer in that chain.
The reason an engine manages its own page cache is not performance, it is ordering. Write-ahead logging needs to know that one specific write reached stable storage before another is issued, and a cache that may reorder or defer writes at its discretion makes that guarantee unobtainable. Performance control is a welcome side effect of a decision forced by correctness.
Atomicity, consistency, isolation and durability are usually taught as four properties of one feature. They are better understood as four promises implemented by three different subsystems with almost nothing in common, which is why they fail independently and are tuned independently.
Which subsystem provides which letter
Durability comes from the log manager. Atomicity comes from the log manager too, via the ability to undo. Isolation comes from the lock manager or from multiversion machinery. Consistency, in the original sense, comes from the application and from declared constraints.
Separating these out explains a great deal that the four-letter formulation obscures. It explains why you can weaken isolation without touching durability, why a system can be durable and offer almost no isolation, and why the consistency letter is the odd one out and is a frequent source of confusion with the unrelated consistency of distributed systems.
Atomicity as the ability to undo
A transaction that modifies ten pages and then aborts must leave no trace. The engine achieves this by recording enough information to reverse each change, so that abort and crash recovery are the same mechanism applied at different times.
This is why abort is not free and can be slower than the work it undoes, which surprises people: rolling back a large deletion means applying every undo record, which is as much work as the deletion was. It is also why a transaction that modifies a great deal and then fails imposes a cost nobody budgeted for.
Durability as a promise about one flush
Durability means that a committed transaction survives a crash, and it is achieved by writing the log records up to the commit point to stable storage before reporting success. Nothing about the data pages is required.
The promise is therefore quite precisely bounded, and the boundary is worth knowing. It says nothing about surviving the loss of the storage device, which is replication's job, and nothing about surviving a write cache that lies. Many systems also offer to weaken it, flushing the log asynchronously in exchange for throughput, which trades a bounded amount of recent committed work for speed.
Isolation as the hardest letter
Isolation is the promise that concurrent transactions do not see each other's intermediate states. It is the hardest to provide because the strongest form, behaving as though transactions ran one at a time, is expensive, and every weaker form permits specific anomalies.
This is why isolation is the only one of the four that comes with a dial, and why that dial is calibrated in anomalies rather than in strength: a level is defined by which of dirty reads, non-repeatable reads, phantoms and write skew it allows. Four lessons of this course are about this one letter, which is a fair reflection of its difficulty.
Consistency, the letter that does not belong
The C in ACID means that a transaction moves the database from one state satisfying its declared constraints to another. The engine enforces what has been declared and nothing more, so most of what people mean by consistency lives in the application.
It is worth being blunt about this because the letter causes real confusion. It is not the consistency of a distributed system, it is not the C of the CAP theorem, and it does not mean the data is correct in any application sense. It means the engine checks the constraints you gave it, which is a much smaller promise than the word suggests.
The transaction manager, which owns identity
Something has to assign transaction identifiers, track which transactions are active, committed or aborted, and make that state visible to everything else. That bookkeeping is small in volume and central in importance.
Almost every interesting mechanism in later lessons consults it. Visibility rules ask whether the transaction that wrote a row version had committed by the time a snapshot was taken. Lock conflict resolution asks who holds what. Recovery asks which transactions were in flight at the crash. The transaction table is the shared reference all of them read.
The lock manager as a data structure with a hard job
Conceptually a map from resource identifiers to queues of granted and waiting requests, with compatibility rules deciding which can coexist. The difficulty is that it is consulted on nearly every operation and must therefore be extremely fast while also detecting deadlock.
Its practical consequences are everywhere. Lock granularity trades concurrency for bookkeeping, which is why engines escalate from row locks to table locks under memory pressure, surprising everybody. Deadlock detection means some transaction is chosen to die, which is why applications must be prepared to retry. And lock waits are invisible in a plan, which is why a query can be slow for reasons no plan explains.
Multiversioning as the alternative to locking readers
Instead of making readers wait for writers, keep multiple versions of each row and let each transaction read the version appropriate to its snapshot. Readers then never block and writers never block readers.
The appeal is obvious and the cost is that old versions accumulate and must be cleaned up, which converts a concurrency problem into a storage and maintenance problem. That trade is the single most consequential design choice in modern transactional engines, and the operational problems it produces, bloat and a cleanup process that cannot keep up, are among the most common in practice.
Why these three fail independently
A system can lose durability through a lying write cache while isolation works perfectly. It can deadlock constantly while every commit is durable. It can corrupt data through a missing application-level check while the engine upholds every promise it made.
Recognising the independence is what makes diagnosis possible. A data correctness problem is a question about which letter was violated, and the answer determines which subsystem to look at and whether the engine was even involved. Treating ACID as one feature makes that question unanswerable.
The cost of each promise
Durability costs a flush per commit, which is why group commit exists. Atomicity costs undo information, which is storage and write amplification. Isolation costs either blocking or version maintenance. Each has a dial and each dial has been turned by somebody on a production system.
Knowing which cost you are paying is what lets you reason about the settings. Asynchronous commit is a durability trade. A lower isolation level is a correctness trade. Lock timeouts are an availability trade against a correctness one. These get adjusted under pressure, often correctly, and they only make sense as costs attached to particular promises.
Why the boundaries are worth memorising
The four letters are the most repeated thing in database education and the decomposition into subsystems is comparatively rare, yet the decomposition is what carries explanatory weight. Learning it once reframes every subsequent topic.
It also makes the rest of this course legible as a structure rather than a list, since the durability lessons are about the log manager, the concurrency lessons are about locking and versioning, and the query lessons sit entirely above all of it. Those groups are not arbitrary, and the reason they divide where they do is this decomposition.
What to take from the transaction layer
Three things. The four letters are provided by three unrelated subsystems, which is why they fail and are tuned independently. Isolation is the only one with a dial, and that dial is calibrated in permitted anomalies rather than in degrees of strength. And the C does not mean what the word suggests: it is the engine checking declared constraints, and everything else people mean by consistency lives in the application.
At a mid level it is enough to name the four letters and know a transaction either commits entirely or not at all. At a senior level the expectation is to say which subsystem provides each letter, to know that commit waits for the log and not the data pages, and to know which anomalies each isolation level permits. At a staff level it is reasoning about the costs independently: that asynchronous commit is a durability trade and a lower isolation level is a correctness trade, that multiversioning converts a concurrency problem into a storage one, and that most of what a team means by consistency was never the engine's promise to keep.
The subsystems described so far have to run somewhere, and how an engine maps them onto processes and threads shapes its operational character more than any single algorithm. The variety among real engines is also narrower than it appears.
Process per connection
The oldest arrangement gives each connection its own operating system process. Isolation is excellent, since a crash in one takes nothing else down, and the operating system handles scheduling.
The cost is that a process is expensive to create and to hold, which makes connection establishment slow and puts a hard practical ceiling on connection count well below what an application might open. Every deployment using this model ends up placing a connection pooler in front of it, and that pooler becomes a component with its own failure modes and its own configuration nobody understands.
Thread per connection
Replacing processes with threads keeps the one-to-one mapping and makes each connection much cheaper. Shared memory becomes the default rather than an arrangement, which simplifies the buffer pool and complicates everything about safety.
The ceiling moves up by an order of magnitude and does not disappear, since each thread still has a stack and the scheduler still has to manage them all. Thread-per-connection engines handle thousands of connections comfortably and tens of thousands badly, which is why the thread pool exists.
Thread pools and the decoupling of clients from workers
A fixed set of worker threads pulls work from a queue of ready requests, so ten thousand connections can be served by sixty-four workers. Connection count and concurrency become separate numbers.
This is the arrangement most modern engines use and the one whose behaviour under overload is best, because a queue is a far better response to excess load than unbounded concurrency: requests wait instead of all slowing down together. The complication is that a worker must not block, since a blocked worker holds a scarce resource, which pushes the design toward asynchronous storage access.
Background work, which every model needs
Independent of the client model, every engine runs work on its own schedule: flushing dirty pages, writing checkpoints, cleaning up old versions, collecting statistics, merging files, rebuilding indexes. None of this is triggered by a request.
This background set is where a surprising share of operational problems live, because the work competes with the foreground for the same resources and because falling behind is usually invisible until it is severe. A cleanup process that cannot keep up, a checkpoint that takes longer than the interval between checkpoints, statistics that have not been collected for a month: all are backlogs rather than failures.
Where the storage engine boundary falls
Some systems draw a formal line between the SQL layer and the storage engine, with a documented interface between them, which is how one database can offer several storage engines and how a storage engine can be used as a library by something that is not a database.
This boundary is more than an implementation detail because it determines what can be swapped and what cannot. A pluggable storage engine can change the access methods and the page layout and cannot change the transaction model it is given, which is why engines with this structure have a characteristic set of limitations around transactions that span components.
Embedded engines, which omit most of the stack
An embedded engine runs inside the application process with no connection layer, no authentication, and no network protocol. What remains is the parser, the planner, the executor and the storage, which is still most of a database.
Their existence makes a useful point about which parts are essential. The subsystems an embedded engine keeps are exactly the ones this lesson spent most of its time on, and the ones it drops are the ones concerned with being a server. That is a good guide to what is fundamental about a database and what is incidental to deploying one.
Storage engines as libraries
A storage engine with no SQL layer at all exposes get, put, delete and iterate over ordered keys, plus transactions. It is a database with the top half removed, and a great deal of infrastructure is built directly on one.
This is worth knowing because the ordered key-value interface turns out to be the natural boundary. Almost every structure above it, including relational tables and secondary indexes, can be encoded as ordered keys, which is why so many distributed databases are built by putting a query layer over a replicated instance of exactly this.
The analytical and transactional divergence
Engines optimised for many small transactions and engines optimised for scanning large volumes make opposite choices at almost every point: row versus column layout, row-at-a-time versus vectorised execution, in-place updates versus append-only files.
They share the arrangement in this lesson and differ in the implementation of nearly every box, which is why a single engine doing both well is difficult rather than merely unfashionable. Knowing which set of choices an engine made predicts its behaviour far better than knowing its name, and the course returns to this when covering execution models.
Distributed engines as this arrangement, repeated
A distributed database is usually this same stack on each node, with a layer above deciding which node holds what and how to coordinate across them. The subsystems do not change; their inputs become remote.
This framing keeps the distribution lessons tractable, because the new problems are specifically about coordination, agreement and partial failure, while planning, storage and concurrency control remain recognisable. It also explains why distributed transactions are hard in a specific way: the local transaction manager was never designed to take instructions from somewhere else.
Why the variety is narrower than it looks
Dozens of engines exist and the arrangement is nearly universal. What differs is the access method, the concurrency control scheme, the execution model and the process model, and each of those has a handful of known options with known trade-offs.
So learning an engine is largely a matter of answering four or five questions about it, which is a far more tractable task than it appears from the outside. This is the most encouraging fact in the course: the space of designs is small, the trade-offs are well documented, and a new engine is almost always a new combination rather than a new idea.
How the rest of this course is arranged
Storage structures next, since the access method determines most performance characteristics. Then durability, then concurrency control, then query processing and access paths, then distribution, and finally a tour of four real engines to see the choices made concretely.
That order is deliberate: each group depends on the one before it, and the real-engine tour at the end is only legible once the alternatives are known. By then the four or five questions above will be the right questions to ask of any engine, which is the actual goal rather than familiarity with any particular one.
A short summary of this lesson
A database engine is a standard arrangement of subsystems: a connection layer, a parser, a binder, a rewriter, an optimizer, an executor, access methods, a buffer pool and a storage manager in a vertical path, with the transaction, lock and log managers cutting across all of them. A statement passes down that path, with binding taking catalog locks that let a read block a schema change, and a write adding locks, a log record and maintenance of every affected index, which is why writes scale differently from reads. Commit waits for the log and not for the data pages, and every aspect of checkpointing, recovery and crash behaviour follows from that. The page is the unit of everything, the engine owns its own page cache because write-ahead logging needs control over write ordering, and a write rests in several caches before reaching the medium. The four ACID letters come from three unrelated subsystems, so they fail and are tuned independently, with isolation the only one carrying a dial calibrated in permitted anomalies. And the variety among real engines is narrow: they share this arrangement and differ in the access method, the concurrency control scheme, the execution model and the process model, which makes learning any engine a matter of answering four or five questions about it.
Learning a new engine is mostly four questions: which access method, which concurrency control scheme, which execution model, and which process model. Every engine shares the arrangement in this lesson, each of those four has a handful of known options with documented trade-offs, and a new engine is almost always a new combination rather than a new idea. Asking the four is far faster than reading the manual front to back.