--- title: 'Relational Database Administration' source: 'https://academia.sh/en/courses/database-administration' language: en updated: '2026-08-23T18:24:12+00:00' license: 'CC BY-SA 4.0' lessons: 25 outcomes: - "Explains the engine's storage, logging, and concurrency mechanisms" - 'Chooses index and partitioning strategies from measurement' - 'Builds a backup, recovery, and replication setup that meets stated targets' --- # Relational Database Administration An advanced course that opens up the engine: process and memory architecture, physical storage, the write-ahead log and checkpoints, multi-version concurrency control, index types and maintenance, table partitioning and sharding, and the operational side — privileges, backup and recovery, replication, high availability, connection pooling, monitoring, bulk loading, and version upgrades. ## Engine Architecture Process and memory layout, how data settles into files as pages, how the write-ahead log provides durability, how checkpoints sync the log with the data file, multi-version concurrency control, dead row cleanup, and querying the system catalog. ### Process and Memory Architecture Source: https://academia.sh/en/courses/database-administration/process-and-memory-architecture The difference between server-based and in-process engine setups, the division of labor among background processes, the split between the shared buffer pool and per-connection memory, the measured effect of pool size on the hit rate, and the shared pool's advantage over private caches. The Advanced SQL course built the practice of measuring how much work a query does: index definition, predicate phrasing, join order, statistics freshness. All of those decisions lived at the query level, and the engine itself stayed a black box. When the plan output read `SCAN loan`, it was known that two million rows were read; where those rows came from, whether they sat in memory or on disk, and who did the reading were never asked. This lesson opens that box. Its question is: when a query arrives, which process handles it, which memory does the data come from, and how much does the size of that memory change the result? The difference between keeping a table in memory and reading it from disk is no smaller than the difference between a good index and a bad one. #### The Engine's Two Setup Forms Relational engines are built in two distinct forms, and this choice determines the rest of the architecture. A **server-based engine** runs as a process group independent of the application. The application sends a request over a network connection, the engine processes the request in its own processes, and returns the result. Only these processes touch the data files. Hundreds of application instances can connect to the same database; all of them talk to a single authoritative process. An **in-process engine** is a library linked into the application's own process. There is no separate server; the query runs in the same process as the function call the application makes. The data file is accessed directly through operating system calls; concurrency is carried out through file locks. The distinction is not a quality ranking. An in-process engine eliminates network latency and inter-process communication entirely and needs no setup or administration; in exchange, having no central point of control means it does not handle high-write- concurrency workloads and fine-grained authorization as well as a server-based setup does. The runs in this course use an in-process engine (`sqlite3`); places where a server-based setup behaves differently are noted separately in the text. #### The Division of Labor Among Background Processes In a server-based engine, the process that handles an incoming query does only part of the work. The rest falls to **background processes** that run continuously without anyone requesting them. Their names vary by engine; their functions do not. **The connection handler.** Accepts new connections, performs authentication, and assigns a worker process or thread to that connection. This worker is the one that parses, plans, and executes the query. **The log writer.** Takes change records from the buffer waiting to be written to disk at commit time and transfers them to the file. The next two lessons examine in detail what this process writes. **The checkpoint process.** Writes pages that have changed in memory to the data files at regular intervals. Its job is to bound how far back recovery has to go. **The cleanup process.** Collects old versions of deleted or updated rows that no one sees anymore and makes their space reusable. In this course, this is the subject of the Dead Row Cleanup lesson. **The statistics collector.** Updates information such as row counts, value distributions, and access counters for tables. In the Advanced SQL course, the numbers cited in the Statistics and Cardinality lesson as the planner's source of estimates come from this process. The reason for this separation is to avoid keeping the query waiting. If a query had to collect its own garbage or write every changed page to disk, response time would be inflated by work unrelated to the user's request. Background processes move this work outside the query; in exchange, administration comes down to tuning how often and how aggressively these processes run. #### Shared Memory and Per-Connection Memory The memory an engine uses is not a single pool; it splits into two distinct classes, and this split is the basis of server sizing. The **shared buffer pool** is the common area that holds pages read from the data file. All connections use the same pool. When a query reads a page, the page enters the pool; if another connection requests the same page, the disk is never read. The pool also sits on the write path: a modified page is changed in the pool first, and writing it to disk is deferred. **Per-connection memory** is the space a single query allocates for its own work: a sort buffer, the hash table of a hash join, temporary result sets. This space is not shared; each connection requests its own share separately. The split between the two classes matters for sizing because they scale differently. The shared pool is a fixed cost: doubling the connections does not require doubling the pool. Per-connection memory, though, is multiplied by the connection count; a sort buffer allocated per query can be allocated two hundred times over with two hundred concurrent connections. Most configurations that run out of memory arise from this multiplication being overlooked. #### The Measured Effect of the Buffer Pool In an in-process engine, the page cache is per connection and its size is set by a configuration value. The run below executes the same query three times with two different cache sizes and prints the page cache hit/miss counters. The data set is the library loan records carried over from earlier courses. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, year INTEGER NOT NULL, branch_id INTEGER NOT NULL ); INSERT INTO book (book_id, title, author, year, branch_id) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 200000) SELECT n, 'Book ' || n, 'Author ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8) FROM counter; SQL sqlite3 library.db "SELECT 'book table page count:', count(*) FROM dbstat WHERE name='book';" for size in 200 2000; do printf '=== cache %s pages ===\n' "$size" sqlite3 library.db <&1 | grep -E 'Page cache (hits|misses)' PRAGMA cache_size = $size; .stats on SELECT count(*) FROM book WHERE year > 2000; SELECT count(*) FROM book WHERE year > 2000; SELECT count(*) FROM book WHERE year > 2000; SQL done ``` ``` book table page count:|1777 === cache 200 pages === Page cache hits: 2 Page cache misses: 1778 Page cache hits: 2 Page cache misses: 1776 Page cache hits: 2 Page cache misses: 1776 === cache 2000 pages === Page cache hits: 2 Page cache misses: 1778 Page cache hits: 1778 Page cache misses: 0 Page cache hits: 1778 Page cache misses: 0 ``` The numbers tell the whole story. The table holds 1777 pages. When the cache is 200 pages, nearly every scan has to reread almost all of the pages: by the end of the first scan only the last 200 pages remain in the cache, and the second scan starts from the beginning and requests pages that are not there. The hit rate stays close to zero across all three scans. When the cache is 2000 pages, the first scan still produces 1778 misses — the data is not yet in memory. But the second and third scans produce not a single miss: every page is in the pool. Same query, same plan, same step count; the only difference is where the pages come from. Reading the output requires one caution: the `.stats on` counters belong to the engine's own page cache and do not cover the operating system's file cache. A miss does not necessarily mean the data came from disk — it only means it was not found in the engine's pool. The page count, however, is independent of the environment: the same data and the same page size give 1777 pages on every machine. #### Pool Size and the Working Set The measurement showed the jump from one extreme to the other. The curve in between is what actually matters when choosing a pool size, and trying it out one value at a time on a real engine is expensive. The model below shows it cheaply: a portion of page requests piles onto a small slice of the table, and the pool makes room by evicting the least recently used page. This is a **model**; it is not the engine's own buffer pool. The real engine's eviction policy is more elaborate, and writes affect the pool as well as reads. What the model shows is the shape of the relationship between size and hit rate. ```sh cat > pool.mjs <<'JS' // Model: a page pool that evicts the least recently used page. class Pool { constructor(capacity) { this.capacity = capacity; this.pages = new Map(); this.hits = 0; this.misses = 0; } read(page) { if (this.pages.has(page)) { this.pages.delete(page); this.pages.set(page, 1); this.hits++; return; } this.misses++; if (this.pages.size >= this.capacity) this.pages.delete(this.pages.keys().next().value); this.pages.set(page, 1); } rate() { return this.hits / (this.hits + this.misses); } } function generator(seed) { // deterministic pseudorandom generator let a = seed >>> 0; return () => { a = (a + 0x9e3779b9) >>> 0; let t = a; t = Math.imul(t ^ (t >>> 15), t | 1); t ^= t + Math.imul(t ^ (t >>> 7), t | 61); return ((t ^ (t >>> 14)) >>> 0) / 4294967296; }; } const TABLE = 2000, HOT = 200, REQUESTS = 500000; function genLoad(seed) { // 90% of requests go to the 200 hot pages const r = generator(seed), l = new Int32Array(REQUESTS); for (let i = 0; i < REQUESTS; i++) l[i] = r() < 0.9 ? Math.floor(r() * HOT) : HOT + Math.floor(r() * (TABLE - HOT)); return l; } console.log('— single pool: hit rate by capacity —'); const load = genLoad(2024); for (const k of [20, 50, 100, 200, 400, 1000, 2000]) { const h = new Pool(k); for (const s of load) h.read(s); console.log(String(k).padStart(6), 'pages →', (h.rate() * 100).toFixed(1).padStart(5), '%'); } console.log('— same total memory: four private caches / one shared pool —'); const loads = [1, 2, 3, 4].map((i) => genLoad(2024 + i)); const priv = [1, 2, 3, 4].map(() => new Pool(100)); let hitsPriv = 0, missPriv = 0; loads.forEach((y, i) => { for (const s of y) priv[i].read(s); }); priv.forEach((h) => { hitsPriv += h.hits; missPriv += h.misses; }); const shared = new Pool(400); for (let i = 0; i < REQUESTS; i++) for (const y of loads) shared.read(y[i]); console.log('four × 100 pages private →', ((hitsPriv / (hitsPriv + missPriv)) * 100).toFixed(1), '%'); console.log('one × 400 pages shared →', (shared.rate() * 100).toFixed(1), '%'); JS node pool.mjs ``` ``` — single pool: hit rate by capacity — 20 pages → 8.1 % 50 pages → 20.1 % 100 pages → 39.4 % 200 pages → 73.6 % 400 pages → 91.1 % 1000 pages → 94.4 % 2000 pages → 99.6 % — same total memory: four private caches / one shared pool — four × 100 pages private → 39.4 % one × 400 pages shared → 91.1 % ``` The shape of the curve is uniform: the hit rate rises quickly until the pool grows large enough to hold the frequently requested set of pages, and past that point the gain flattens out. This set is called the **working set**. In the model the working set is 200 pages; when the pool reaches 200, the rate is 73.6%, at 400 it is 91.1%, and beyond that doubling the memory again gains only three points. The administrative decision follows from this: size the pool not to the size of the whole table, but to the portion that is actually touched. The working set of a hundred-gigabyte database can be a few gigabytes; raising memory just past that threshold is both cheaper and nearly as effective as trying to fit the entire database in memory. The second part of the model turns the split from the beginning of the lesson into a number. Total memory is 400 pages in both arrangements. When four connections each keep their own 100-page cache, the hit rate stays at 39.4%; when the same memory is one shared pool, it rises to 91.1%. The reason is clear: the four connections' working sets overlap heavily, the same hot pages are stored four times over in private caches, and each copy costs its own space. This is why server-based engines share their buffer pool. #### Summary - A server-based engine runs as a separate process group and only those processes touch the data files; an in-process engine is a library linked into the application's own process. - Background processes move log writing, checkpointing, cleanup, and statistics collection off the query's response path. - Memory splits into two classes: the shared buffer pool does not scale with the connection count, while the sort and hash areas allocated per connection do. - In the measurement, a 1777-page table was reread on every scan with a 200-page cache; with a 2000-page cache, it produced no misses after the second scan. - The model showed that the hit rate rises quickly until the working set fits in the pool and then flattens, and that the same total memory gives 91.1% in a shared pool versus 39.4% in four private caches. #### Next Step In this lesson, a page appeared as a unit held in memory: something that enters the pool, gets evicted, produces a hit or a miss. What the page itself is, how many rows fit inside it, what happens when a row does not fit, and how all these pages sit on disk in files were not asked. The next lesson takes up the data as it exists on disk: it measures the relationship among page size, page count, and file size, counts the table's and the index's share of the file separately, and defines the tablespace concept. ### Physical Storage Layout Source: https://academia.sh/en/courses/database-administration/physical-storage-layout The page as the engine's smallest unit of read and write, how page size and page count determine file size exactly, measuring in-page fullness and rows per page, overflow pages, and the tablespace concept. The previous lesson used the page as a unit in memory: something that enters the buffer pool, gets evicted, produces a hit or a miss. What the page itself is was never asked. Why is data read page by page instead of row by row, how many rows fit into a page, and what happens when a row does not fit? This lesson's question is the data's form on disk. Three relationships can be measured: the relationship between page size and file size, the fullness inside a page, and the table's and the index's share of the total space. All three can be counted; none is left to guesswork. #### Why the Page Exists A **page** is the smallest unit the engine moves between disk and memory. Even when a single row is requested, the entire page that row sits on is read. The reason lies in the hardware. It is the same argument as the memory hierarchy discussion in the How Computers Work course: disk access has a fixed cost, and that cost is measured not by the number of bytes read but by the number of accesses. The work of reading a hundred-byte row is nearly identical to the work of reading a four-kilobyte page. A fixed-size unit also simplifies bookkeeping: every slot in the buffer pool is the same size, and one page can take another page's place without producing fragmentation. Page size is set when the database is created, and changing it later requires rewriting the data. A small page means fewer wasted bytes carried along on selective reads; a large page means fewer accesses and less bookkeeping on sequential scans. #### Page Count Determines File Size The relationship can be measured exactly. The block below sets up the library data set carried over from earlier courses and places three numbers side by side. ```sh rm -f library.db cat > setup.sql <<'SQL' CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, registration_date TEXT NOT NULL ); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, year INTEGER NOT NULL, branch_id INTEGER NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO branch (branch_id, name, city) VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),(3,'Kadikoy','Istanbul'), (4,'Beyoglu','Istanbul'),(5,'Konak','Izmir'),(6,'Nilufer','Bursa'), (7,'Selcuklu','Konya'),(8,'Cankaya','Ankara'); INSERT INTO member (member_id, name, city, registration_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000) SELECT n, 'Member ' || n, CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir' WHEN 3 THEN 'Bursa' ELSE 'Konya' END, date('2015-01-01', '+' || (n % 3200) || ' days') FROM counter; INSERT INTO book (book_id, title, author, year, branch_id) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 200000) SELECT n, 'Book ' || n, 'Author ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8) FROM counter; INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 library.db < setup.sql sqlite3 -header -column library.db <<'SQL' SELECT (SELECT page_size FROM pragma_page_size) AS page_size, (SELECT page_count FROM pragma_page_count) AS page_count, (SELECT page_size FROM pragma_page_size) * (SELECT page_count FROM pragma_page_count) AS computed_bytes; .shell echo "actual file size: $(wc -c < library.db)" SQL ``` ``` page_size page_count computed_bytes --------- ---------- -------------- 4096 20873 85495808 actual file size: 85495808 ``` The computed value and the actual file size are identical. This is not an approximation; it is a definition: the data file contains nothing but equal-sized pages laid end to end. When the file grows, the page count rises; the page count does not fall without the file shrinking. These two sentences will be used directly later, when bloat and space reclamation are discussed. Page size is 4096 bytes in this environment. Page count, and therefore file size, depends on the data; a database built with a different page size would give different values for both numbers, but the relationship would not change. #### What Is Inside a Page Not every page does the same job. For a table stored in a B-tree, three page types are distinguished: a **leaf page** holds the rows themselves, an **internal page** holds the keys that route to subtrees, and an **overflow page** holds the trailing portion of a value that does not fit in one page. ```sh [ -f library.db ] || sqlite3 library.db < setup.sql # data set built in the first block sqlite3 -header -column library.db <<'SQL' SELECT pagetype AS page_type, count(*) AS pages, sum(ncell) AS cells, round(1.0 * sum(ncell) / count(*), 1) AS cells_per_page FROM dbstat WHERE name = 'loan' GROUP BY pagetype; SELECT name AS object, count(*) AS pages, sum(ncell) AS cells, round(100.0 * sum(payload) / sum(pgsize), 1) AS fill_pct FROM dbstat GROUP BY name ORDER BY pages DESC; SQL ``` ``` page_type pages cells cells_per_page --------- ----- ------- -------------- internal 46 17901 389.2 leaf 17902 2000000 111.7 object pages cells fill_pct ------------- ----- ------- -------- loan 17948 2017901 82.8 book 1777 201771 82.5 member 1146 121141 84.0 sqlite_schema 1 4 17.2 branch 1 8 3.4 ``` Two million rows sit across 17,902 leaf pages: an average of 112 rows per page. Above them are 46 internal pages, each carrying an average of 389 keys. The height of the tree follows from this: one of the 46 internal pages is the root, the remaining 45 sit below it and point to the leaves. So reaching any row in a two-million-row table takes three page reads. The Data Structures course's B-Trees lesson stated that "height grows with the logarithm of the row count"; here that claim is a measured number. The fill column gives a second piece of information: roughly 83% of the pages are full of actual data. The remainder goes to bookkeeping — cell pointers and the page header — and to deliberately left free space. Some of that free space is necessary: a page packed completely full forces any single inserted row to split the page. The branch table showing up at 3.4% fill is not a defect; eight rows landed on a single page, and the rest of the page stayed empty. In small tables, the smallest unit is still a page. #### When a Row Does Not Fit a Page Given that page size is fixed, what happens when a row's length exceeds it? The engine keeps the start of the row in the normal page and chains the rest onto overflow pages. ```sh rm -f overflow.db sqlite3 -header -column overflow.db <<'SQL' CREATE TABLE book_summary(book_id INTEGER PRIMARY KEY, summary TEXT NOT NULL); INSERT INTO book_summary VALUES (1, replace(hex(zeroblob(40)),'0','a')); INSERT INTO book_summary VALUES (2, replace(hex(zeroblob(600)),'0','b')); INSERT INTO book_summary VALUES (3, replace(hex(zeroblob(6000)),'0','c')); SELECT book_id, length(summary) AS summary_length FROM book_summary; SELECT pageno AS page, pagetype AS type, ncell AS cell, payload AS payload, unused AS unused FROM dbstat WHERE name = 'book_summary' ORDER BY pageno; SQL ``` ``` book_id summary_length ------- -------------- 1 80 2 1200 3 12000 page type cell payload unused ---- -------- ---- ------- ------ 2 internal 1 0 4077 3 overflow 0 4092 0 4 overflow 0 4092 0 5 leaf 2 1288 2791 6 leaf 1 3821 258 ``` The first two summaries — 80 and 1200 characters — fit together into a single leaf page (page 5). The third is 12,000 characters and fills a leaf page by itself: that page holds 3,821 bytes, and the rest spreads across two overflow pages (3,821 + 4,092 + 4,092 = 12,005; the difference comes from the row header). The page numbers not being in order reflects the order in which pages were allocated, not a logical order. The cost of this shows up on reads. A long text column weighs down a row even for queries that never look at that column — fewer rows fit per page, and a scan reads more pages. This is the reason behind a schema design habit: moving rarely read long text fields into a separate table preserves the row density of the main table. #### The Table's and Index's Share of the File An index is also a B-tree, and it too sits in pages. The same counter shows the index's space cost on the same scale as the table. ```sh [ -f library.db ] || sqlite3 library.db < setup.sql # data set built in the first block sqlite3 library.db 'CREATE INDEX loan_member ON loan(member_id);' sqlite3 -header -column library.db " SELECT name AS object, count(*) AS pages, count(*) * 4096 / 1048576 AS mib FROM dbstat GROUP BY name ORDER BY pages DESC;" ``` ``` object pages mib ------------- ----- --- loan 17948 70 loan_member 5738 22 book 1777 6 member 1146 4 sqlite_schema 1 0 branch 1 0 ``` An index on a single integer column takes up roughly a third of the table's space: 22 MiB against a 70 MiB table. The ratio is not surprising — an index entry carries the key value and a reference that leads to the row, not the whole row, but that is still a fixed cost per row. This is the cost the Advanced SQL course's Index Concept lesson passed over with "an index takes up space"; here it is countable object by object. This count is put to direct use in administration. Which table and which index have bloated a database is determined by this query, not by guesswork; capacity planning and finding redundant indexes both start from the same list. #### File Layout and the Tablespace Which files pages land in varies by engine, and this is the visible face of administration. Three layouts are common: keeping the entire database in a single file, writing each table and index to its own file, or managing multiple files as a pre-allocated pool of space. A **tablespace** is an abstraction that names the location these files sit in. A tablespace name is given when a table is created; which directory or which disk that name maps to is defined separately by administration. Its payoff is separating the data's physical location from the schema: a frequently read table can be placed on fast storage and an archival table on cheap storage, without touching the table definition. It is worth stating plainly, since this split cannot be shown in the environment used here: in the runs in this lesson, the entire database is a single file, and the tablespace concept has no counterpart. Everything measured about pages, fill, and overflow is independent of this layout; the number of files and the tablespace naming are specific to the engine. #### Summary - A page is the smallest unit the engine moves between disk and memory; the fixed cost of an access makes reading a single row pointless. - File size is the product of page size and page count; in the measurement, 4096 × 20,873 = 85,495,808 bytes came out exactly equal to the file's actual size. - Two million rows landed on 17,902 leaf pages (112 rows per page), with 46 internal pages above them; the tree's height is three, and page fill sits around 83%. - A value that does not fit a page chains onto overflow pages; a 12,000-character text left 3,821 bytes in a leaf page and used two overflow pages. - An index is also stored in pages: a single-integer-column index took up 22 MiB next to a 70 MiB table. #### Next Step This lesson showed how data sits on disk, but it did not ask how it gets there. When a row is updated, the relevant page changes in memory; what happens if power is cut before that page is written to disk? If a committed transaction's result surviving depended on pages being written to disk, every commit would mean writing to random locations. The next lesson takes up the solution to this problem: writing changes to a sequential log before the data file. The log file's appearance and growth, and the recovery of data from it after a crash, will be shown by measurement. ### Write-Ahead Log Source: https://academia.sh/en/courses/database-administration/write-ahead-log The cost of tying a commit to writing data pages to disk, the definition of the write-ahead rule, the log file growing at commit while the data file stays unchanged, showing that committed data lives only in the log, crash recovery, and the measured effect of log mode and the sync setting. The previous lesson showed that data sits on disk in pages. When a row is updated, the page in memory changes first. What if the machine shuts down before that page is written to disk? The Transactions topic said that a committed change is durable; how is that promise kept while the page is not yet on disk? The first answer that comes to mind — writing every changed page to disk at commit time — is both expensive and insufficient. This lesson covers why it falls short and what is actually done instead. What gets measured is precise: right after a commit, which file holds the committed data? #### Why Writing the Pages Is Not the Answer Writing changed pages to disk at commit time has three separate problems. **The writes are scattered.** A single transaction can touch pages far apart from each other: a loan row, the page the member sits on, the different leaves of three separate indexes. Writing these means small writes scattered across the whole file. The cost of a sequential write is per byte, not per access. **A write can be split.** Writing a four-kilobyte page to disk may not be a single indivisible operation at the hardware level. If power is cut mid-write, part of the page is new and part is old. That is worse than a lost update: it is a corrupted page. **The same page is written over and over.** A frequently updated page is rewritten at every commit. Yet writing that page's final state once would be enough. #### The Write-Ahead Rule The solution is to separate durability from the data pages. The engine first writes the change to the **write-ahead log**: sequential records appended to the end of a file, describing what changed. The rule is a single sentence: > Before a data page is written to disk, the log record describing the change to that > page must already be durable on disk. A commit is complete once the log records are durable. The data pages can stay in memory; writing them out is deferred. This arrangement solving all three problems is direct. The write is sequential, because the log is only ever appended to. The danger of a split write disappears, because an incompletely written log record is recognized and ignored during recovery — the data page, meanwhile, has not been touched at all yet. A frequently updated page is also written just once, because the page landing on disk is no longer tied to a commit. Recovery follows from this. After a crash, on startup, the engine reads the log and applies committed changes that have not yet reached the data file. Uncommitted changes are not applied. The result is an exact return to the committed state at the moment of the crash. The Relational Theory course referred to this structure by the name **write-ahead log** when covering ACID's durability property; it is the same structure, and this course will use the catalog's naming throughout. #### The Log File's Appearance The claim can be measured. The run below sets up a database, turns on log mode, then inserts five thousand rows and checks the size of both files. The automatic checkpoint is disabled; why is the subject of the next lesson. ```sh rm -f log.db log.db-wal log.db-shm sqlite3 log.db <<'SQL' CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup_date TEXT, return_date TEXT); INSERT INTO loan SELECT n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-01-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) SELECT n FROM s); PRAGMA journal_mode = WAL; PRAGMA wal_autocheckpoint = 0; UPDATE loan SET return_date = '2024-02-01' WHERE loan_id = 1; .shell echo "--- start ---" .shell echo "data file: $(wc -c < log.db) log: $(wc -c < log.db-wal)" INSERT INTO loan SELECT 100000+n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-03-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<5000) SELECT n FROM s); .shell echo "--- after the 5000-row transaction commits ---" .shell echo "data file: $(wc -c < log.db) log: $(wc -c < log.db-wal)" SELECT count(*) AS total_rows FROM loan; SQL ``` ``` wal 0 --- start --- data file: 1372160 log: 4152 --- after the 5000-row transaction commits --- data file: 1372160 log: 152472 55000 ``` The first two lines are the values the two settings return: log mode became `wal`, and the automatic checkpoint was reset to zero. The real result is below. After the five-thousand-row insert commits, the data file's size did not change by a single byte — it was 1,372,160, and it stayed 1,372,160. The log file, though, grew from 4,152 bytes to 152,472 bytes. The query sees the new rows: 55,000. File sizes depend on the environment and the page size; what does not change is which file grows. #### Where the Committed Data Is The data file not changing means the committed rows are not there. The direct way to test this is to separate the files and open them apart from each other. ```sh rm -f log.db log.db-wal log.db-shm data-only.db data-and-log.db data-and-log.db-wal sqlite3 log.db <<'SQL' CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup_date TEXT, return_date TEXT); INSERT INTO loan SELECT n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-01-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) SELECT n FROM s); PRAGMA journal_mode = WAL; PRAGMA wal_autocheckpoint = 0; INSERT INTO loan SELECT 100000+n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-03-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<5000) SELECT n FROM s); .shell cp log.db data-only.db .shell cp log.db data-and-log.db .shell cp log.db-wal data-and-log.db-wal SQL echo "--- data-only file copied ---" sqlite3 data-only.db "SELECT count(*) FROM loan;" echo "--- data file + log copied ---" sqlite3 data-and-log.db "SELECT count(*) FROM loan;" ``` ``` wal 0 --- data-only file copied --- 50000 --- data file + log copied --- 55000 ``` The result leaves no room for argument. When only the data file is copied, fifty thousand rows show up: the committed five thousand rows are not there. When the log is copied along with it, fifty-five thousand rows show up. The committed data sits inside the log right after the commit. This observation has a direct consequence on the backup side, one this course will return to in the Operations topic: a file-level backup of a running database has to cover the log too. A backup that copies only the data file does not contain the most recently committed transactions. #### Recovery After a Crash Seeing that recovery actually works requires a real crash. The script below commits two thousand rows in a single transaction, then kills the process without any shutdown procedure: the files are not closed, no checkpoint is taken, no buffer is flushed. ```sh rm -f crash.db crash.db-wal crash.db-shm cat > crash.mjs <<'JS' import { DatabaseSync } from 'node:sqlite'; const db = new DatabaseSync('crash.db'); db.exec('PRAGMA journal_mode = WAL'); db.exec('PRAGMA wal_autocheckpoint = 0'); db.exec(`CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, pickup_date TEXT)`); db.exec('BEGIN'); for (let i = 1; i <= 2000; i++) db.exec(`INSERT INTO loan VALUES (${i}, ${1 + (i * 13) % 500}, '2024-03-01')`); db.exec('COMMIT'); console.log('committed; row count within the transaction:', db.prepare('SELECT count(*) AS c FROM loan').get().c); process.kill(process.pid, 'SIGKILL'); // terminate without cleanup, without a shutdown JS node crash.mjs echo "process exit code: $?" echo "--- files at the moment of the crash ---" echo "data file: $(wc -c < crash.db) log: $(wc -c < crash.db-wal)" echo "--- reopening the database ---" sqlite3 crash.db "SELECT count(*) FROM loan;" ``` ``` committed; row count within the transaction: 2000 process exit code: 137 --- files at the moment of the crash --- data file: 4096 log: 61832 --- reopening the database --- 2000 ``` Exit code 137 shows the process was terminated by a kill signal. At the moment of the crash, the data file is a single page's worth: neither the table definition nor the rows made it there. Everything is in the 61,832-byte log. When the database is reopened, all two thousand rows are in place. This is how durability is achieved. A commit is not writing the data to where it will finally sit; it is recording, irreversibly, what changed. The row's data settling into place is the subject of the next lesson. #### Log Mode and the Sync Setting Keeping a log does not have a single form, and the choice makes a measurable difference. There are two axes. The first axis is **log mode**. In a write-ahead log, changes are appended to a separate file and the data file stays as it was. The alternative is a **rollback journal**: a copy of the page's prior state is written to a separate file before the change, the page's new state is written directly into the data file, and if the transaction is rolled back the old pages are written back. The mode's name and its options are specific to the engine. The second axis is **sync level**: whether the engine waits at commit time for the operating system to confirm the write actually reached disk, or hands the write off and continues. {/* run: skip */} ```sh cat > mode.mjs <<'JS' import { DatabaseSync } from 'node:sqlite'; import { rmSync } from 'node:fs'; function measure(mode, sync) { for (const suffix of ['', '-wal', '-shm', '-journal']) rmSync('mode.db' + suffix, { force: true }); const db = new DatabaseSync('mode.db'); db.exec(`PRAGMA journal_mode = ${mode}`); db.exec(`PRAGMA synchronous = ${sync}`); db.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, pickup_date TEXT)'); const insert = db.prepare('INSERT INTO loan VALUES (?, ?, ?)'); const start = process.hrtime.bigint(); for (let i = 1; i <= 2000; i++) { // each row is its own transaction db.exec('BEGIN'); insert.run(i, 1 + (i * 13) % 500, '2024-03-01'); db.exec('COMMIT'); } const duration = Number(process.hrtime.bigint() - start) / 1e6; db.close(); return duration; } const measurements = [['delete', 'FULL'], ['wal', 'FULL'], ['wal', 'NORMAL']]; const results = measurements.map(([m, s]) => [m, s, measure(m, s)]); const baseline = results[0][2]; console.log('log mode sync 2000 txns (ms) relative'); for (const [m, s, d] of results) console.log(m.padEnd(13), s.padEnd(10), d.toFixed(0).padStart(14), (baseline / d).toFixed(1).padStart(12) + '×'); JS node mode.mjs ``` The output varies from machine to machine; one sample run: ``` log mode sync 2000 txns (ms) relative delete FULL 34 1.0× wal FULL 9 3.6× wal NORMAL 9 3.8× ``` Timings depend on the environment; what is worth reading is the relationship between them. In this run, the write-ahead log committed roughly three and a half times faster than the rollback journal. The reason is the write pattern: with a rollback journal, every transaction changes both the journal file and the data file and then deletes the journal file at the end; with a write-ahead log, only one file is appended to. This run was taken on a filesystem that lives in memory; there, sync does not wait for the write to reach disk, so on a real disk the gain from relaxing it can be larger than the table shows. The third line is the trade-off itself. Relaxing sync sped the measurement up a little further, because the commit no longer waits for the write to reach disk; the gain depends on how fast the disk is. The cost of this is durability: in a sudden power loss, the last few committed transactions can be lost. The database does not become corrupted — the log's structure prevents that — but a transaction that was reported "committed" may not come back. This setting means deliberately giving up ACID's durability property, and it is defensible only for data whose loss is acceptable. #### Summary - Writing data pages to disk at commit time produces scattered writes, split writes, and the same page being written over and over. - The write-ahead rule requires the log record describing a change to be durable before the data page reaches disk; a commit is complete once the log is durable. - In the measurement, a five-thousand-row insert left the data file completely unchanged when it committed; the log file grew from 4,152 to 152,472 bytes. - Copying only the data file showed 50,000 rows; copying it with the log showed 55,000: committed data sits inside the log. - After a process was killed, the data file stayed at a single page, yet all two thousand rows were recovered from the log. - Log mode and sync level make a measurable difference; relaxing sync sped the sample run up a little further and traded away durability in exchange. #### Next Step In this lesson's runs, the automatic checkpoint was disabled and the log file grew without ever shrinking. This is not sustainable: a log that grows without bound fills the disk, and it lengthens recovery, because the number of records to read at startup grows. The changes in the log need to be applied to the data file so the log can be shortened. The next lesson takes up this operation: what a checkpoint does, how often it should run, and how the trade-off between frequency and recovery time is set. ### Checkpoints Source: https://academia.sh/en/courses/database-administration/checkpoints Changes in the log being applied to the data file, how a checkpoint changes both the data file and the log file, the difference between reusing the log and truncating it, the trade-off between checkpoint frequency and recovery work, and how readers delay a checkpoint. In the previous lesson, the log file grew and never shrank, because the automatic checkpoint had been disabled. That state cannot last long. A log that grows without bound fills the disk; on top of that, recovery gets longer as the number of records to read at startup grows. The data file, meanwhile, can sit unchanged for days while the information inside it grows increasingly stale. These two files sooner or later have to be brought in sync. A **checkpoint** is the name of that syncing. This lesson's questions are: how does each file change during the sync, why does the log sometimes shrink and sometimes not, and how often should this be done? #### What a Checkpoint Does A checkpoint applies the changes accumulated in the log to the pages in the data file. The sequence of steps is short: the log records are read, the final state of the corresponding pages is written to the data file, the write is confirmed durable, and only then is that log space considered free. The ordering is mandatory. If log space is freed before the data file write is durable, a change ends up with neither copy existing anywhere. This is the other face of the write-ahead rule: the log record becomes durable **before** the data page, and the log space becomes free **after** the data page. There is one more constraint. A checkpoint cannot overwrite pages that are currently visible to an in-progress read; the next lesson will establish why. This means a long-running read can delay a checkpoint, and the log keeps growing in the meantime. In administration, one of the common answers to "why did the log bloat" is a forgotten long-running read. #### A Measured Checkpoint The run below compares the state before and after a checkpoint using three measurements: the size of the data file, the size of the log file, and the database's logical page count. ```sh rm -f checkpoint.db checkpoint.db-wal checkpoint.db-shm sqlite3 checkpoint.db <<'SQL' CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup_date TEXT, return_date TEXT); INSERT INTO loan SELECT n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-01-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) SELECT n FROM s); PRAGMA journal_mode = WAL; PRAGMA wal_autocheckpoint = 0; INSERT INTO loan SELECT 100000+n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-03-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) SELECT n FROM s); .shell echo "--- before checkpoint ---" .shell echo "data: $(wc -c < checkpoint.db) log: $(wc -c < checkpoint.db-wal)" SELECT 'page count in the data file', page_count FROM pragma_page_count; PRAGMA wal_checkpoint(PASSIVE); .shell echo "--- after PASSIVE checkpoint ---" .shell echo "data: $(wc -c < checkpoint.db) log: $(wc -c < checkpoint.db-wal)" SELECT 'page count in the data file', page_count FROM pragma_page_count; PRAGMA wal_checkpoint(TRUNCATE); .shell echo "--- after TRUNCATE checkpoint ---" .shell echo "data: $(wc -c < checkpoint.db) log: $(wc -c < checkpoint.db-wal)" SQL ``` ``` wal 0 --- before checkpoint --- data: 1372160 log: 576832 page count in the data file|472 0|140|140 --- after PASSIVE checkpoint --- data: 1933312 log: 576832 page count in the data file|472 0|0|0 --- after TRUNCATE checkpoint --- data: 1933312 log: 0 ``` Before the checkpoint, the data file is 1,372,160 bytes, or 335 pages. But the query reports the page count as 472. The 137 pages that do not exist anywhere in the data file are in the log. This is the concrete form of the first lesson's architecture: the database's **logical** state is read from the union of the data file and the log; the data file alone is an old snapshot. The `PRAGMA wal_checkpoint` output gives three numbers: a busy flag, the number of frames in the log, and the number of frames processed. On the first call, `0|140|140` — not busy, 140 frames in the log, all processed. The frame count can be verified against the file size: each frame is a page plus a 24-byte header, and there is a 32-byte section at the start of the file; 32 + 140 × (4096 + 24) = 576,832, identical to the measured size. The frame count exceeding 137 is because some pages appear in the log more than once: during inserts the tree's upper levels also change, and each change writes a separate frame. After the checkpoint, the data file grows to 1,933,312 bytes: exactly 472 pages. The logical page count did not change, because the data did not change — only its location did. The second call returns `0|0|0`: nothing left to process. #### Reuse Versus Truncation After the first checkpoint, the log file's size stayed at 576,832 bytes; even though its contents had been processed, the file did not shrink. This is not a defect; it is deliberate behavior: a processed log file can be **overwritten from the start**. Truncating a file and then growing it back out is unnecessary work for the file system; overwriting the same space is cheap. The second call's `TRUNCATE` form actually resets the file. Its place is situations where the log has bloated as a one-off event and the space is genuinely wanted back. In a system that runs continuously, the log file sitting at a stable size is expected behavior; being reset continuously is not. The form names used here (`PASSIVE`, `TRUNCATE`) are specific to this tool. The distinction itself — reusing processed log space versus truncating the file — is independent of the engine. #### The Frequency Trade-Off How often should a checkpoint be taken? The question looks like a matter of picking a setting value; it is really a choice between two costs. A frequent checkpoint writes the same page to the data file over and over and weighs down the write path. An infrequent checkpoint lets the log grow and lengthens recovery. The run below changes the threshold and measures five thousand small transactions, placing two results side by side: elapsed time, and the number of frames left in the log at the end of the run. The second number is how much work recovery would have to do if a crash happened right then. ```sh cat > frequency.mjs <<'JS' import { DatabaseSync } from 'node:sqlite'; import { rmSync, statSync } from 'node:fs'; const FRAME = 4096 + 24; // page + frame header function measure(threshold) { for (const suffix of ['', '-wal', '-shm']) rmSync('frequency.db' + suffix, { force: true }); const db = new DatabaseSync('frequency.db'); db.exec('PRAGMA journal_mode = WAL'); db.exec('PRAGMA synchronous = NORMAL'); db.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, return_date TEXT)'); db.exec(`INSERT INTO loan SELECT n, 1+(n*13)%500, NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) SELECT n FROM s)`); db.exec('PRAGMA wal_checkpoint(TRUNCATE)'); db.exec(`PRAGMA wal_autocheckpoint = ${threshold}`); const update = db.prepare('UPDATE loan SET return_date = ? WHERE loan_id = ?'); const start = process.hrtime.bigint(); for (let i = 1; i <= 5000; i++) { db.exec('BEGIN'); update.run('2024-04-01', 1 + (i * 37) % 20000); db.exec('COMMIT'); } const duration = Number(process.hrtime.bigint() - start) / 1e6; const frame = Math.round((statSync('frequency.db-wal').size - 32) / FRAME); db.close(); return [duration, frame]; } console.log('threshold (pages) 5000 txns (ms) frames left in log'); for (const e of [1, 10, 100, 1000, 0]) { const [d, f] = measure(e); console.log(String(e === 0 ? 'off' : e).padStart(17), d.toFixed(0).padStart(16), String(f).padStart(19)); } JS node frequency.mjs ``` {/* mask: (?<=\S)[ ]+[0-9]+(?=[ ]+[0-9]+$) */} ``` threshold (pages) 5000 txns (ms) frames left in log 1 51 6 10 26 15 100 23 103 1000 23 1000 off 25 5719 ``` Timings depend on the environment; the relationship is what to read from ratios. When a checkpoint is taken at every page, five thousand transactions took roughly twice as long as against a thousand-page threshold in this run. In exchange, only six frames were left in the log: a crash would leave recovery almost nothing to do. As the threshold grows, the time drops quickly and flattens out past ten pages; the number of frames accumulating in the log, meanwhile, grows linearly. This run was taken on a filesystem that lives in memory; there, a checkpoint does not have to wait for the write to reach disk, so on a real disk the difference in time can be larger than the table shows. The shape of the curve makes the choice easier. A very frequent checkpoint is expensive, and the recovery time it buys back is already small. A very infrequent checkpoint does not shorten the time any further — in the last row, the "off" case's time is no better than the thousand-page threshold — but it lets the log and the recovery work grow without bound. The efficient range sits in the middle, and this is less a performance tuning question than a recovery-target decision: the answer to "how long should we take at most to come back up after a crash" determines how much log is allowed to accumulate. #### Recovery's Work A checkpoint's effect on the recovery side is direct. At startup, the engine has to process the log records after the last checkpoint; the records before the checkpoint are already in the data file. The work recovery has to do is proportional not to the table's size, but to the number of records accumulated since the checkpoint. One measurement caution is needed here. In this environment, recovery consists of scanning the log and rebuilding its internal index, and because the file is in the operating system's cache, its duration does not produce a measurable value at these sizes. This is why the table above used the work itself — the frame count — as the measure instead of time. In engines that also have to write the pages out to the data file separately, the same number is the direct determinant of recovery time. The second face of the frequency decision also shows up here: because a checkpoint writes accumulated pages in bulk, it produces a spike in write load at that moment. In a system running continuously, this spike shows up as regular peaks in latency measurements. Engines soften this by spreading the checkpoint's work over an interval; the setting's name varies by engine, its purpose does not. #### Summary - A checkpoint applies changes accumulated in the log to the pages in the data file; log space becomes free only once that write is durable. - The database's logical state is the union of the data file and the log; in the measurement, the data file was 335 pages while the logical page count came out at 472, with the difference sitting in the log. - A processed log file is generally not truncated; it is overwritten from the start. Truncation is a separate, optional step. - Checkpoint frequency is a choice between two costs: checkpointing at every page slowed the sample run down roughly twofold, while never checkpointing let 5,719 frames accumulate in the log. - Recovery's work is proportional not to table size, but to the number of records accumulated since the last checkpoint. #### Next Step This lesson passed over one constraint: a checkpoint cannot overwrite pages currently visible to an in-progress read. Behind that sentence stands the way the engine provides concurrency. The Advanced SQL course observed that a read inside a transaction was unaffected by commits happening outside it; how that is possible was never asked. The next lesson builds that mechanism: multiple versions of a row stored at the same time, the rule that determines which version a given transaction sees, and readers and writers not blocking each other. ### Multi-Version Concurrency Control Source: https://academia.sh/en/courses/database-administration/multi-version-concurrency-control The difference between lock-based and version-based concurrency control, measuring whether the same read blocks the writer under two log modes, building the version chain and visibility rule through a model, when old versions can be removed, and the conflicts multi-versioning does not remove. At the end of the previous lesson a constraint was left standing: a checkpoint cannot overwrite pages currently visible to an in-progress read. A similar observation had been made in the Advanced SQL course — a read inside a transaction was unaffected by changes committed outside it. Both observations point to the same mechanism, and that mechanism has not been built yet. The question is this: if a row is being updated while a transaction is reading that same row, what does the read see? There are two consistent answers. Either the reader makes the writer wait, or the row's old state is kept somewhere. This lesson takes up the second path and measures its difference from the first. #### Two Approaches **Lock-based control** provides consistency by waiting. A transaction reading a row holds a shared lock on that row; a transaction wanting to change it requests an exclusive lock and waits until the read finishes. The result is correct, but the read and the write serialize each other. **Multi-version concurrency control** provides consistency by keeping. When a row is updated, its old state is not deleted; a new version is added, and each version carries which transaction created it and which transaction invalidated it. A reading transaction picks the version that matches its own starting point. The writer does not wait, the reader does not wait; the two sides look at different versions. This is what the **snapshot** concept introduced in the Advanced SQL course corresponds to. A snapshot is not a copied database; it is a criterion that determines which versions are seen. #### The Measured Difference The difference can be observed. The run below executes the same scenario under two log modes: a reader transaction opens and reads, then a writer on a separate connection tries to update the same table. ```sh rm -f e.db e.db-wal e.db-shm e.db-journal cat > blocking.mjs <<'JS' import { DatabaseSync } from 'node:sqlite'; import { rmSync } from 'node:fs'; function attempt(mode) { for (const suffix of ['', '-wal', '-shm', '-journal']) rmSync('e.db' + suffix, { force: true }); const setup = new DatabaseSync('e.db'); setup.exec(`PRAGMA journal_mode = ${mode}`); setup.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, return_date TEXT)'); setup.exec("INSERT INTO loan VALUES (1,4,NULL),(2,4,NULL),(3,7,'2024-03-09')"); setup.close(); const reader = new DatabaseSync('e.db'); const writer = new DatabaseSync('e.db'); const open = (conn) => conn.prepare('SELECT count(*) AS c FROM loan WHERE return_date IS NULL').get().c; reader.exec('BEGIN'); // the reader's transaction stays open const before = open(reader); let writeResult; try { writer.exec("UPDATE loan SET return_date = '2024-03-20' WHERE loan_id = 1"); writeResult = 'succeeded'; } catch (err) { writeResult = 'blocked: ' + err.message.split('\n')[0]; } const after = open(reader); reader.exec('COMMIT'); const afterCommit = open(reader); reader.close(); writer.close(); console.log(`[${mode}] reader inside transaction: ${before} → ${after} | writer: ${writeResult} | after commit: ${afterCommit}`); } attempt('delete'); attempt('wal'); JS node blocking.mjs ``` ``` [delete] reader inside transaction: 2 → 2 | writer: blocked: database is locked | after commit: 2 [wal] reader inside transaction: 2 → 2 | writer: succeeded | after commit: 1 ``` What the reader sees does not change across the transaction in either case: 2 → 2. Isolation held under both arrangements. What changed is how it was achieved. Under the rollback journal, the writer never got anywhere: the database is locked. A write cannot happen before the read finishes. Under the write-ahead log, the writer finished its work and committed; the reader kept seeing the old value regardless, and only saw the new value (1) once its own transaction ended. Neither side waited. This observation confirms multi-versioning's **observed property**: the reader and the writer do not block each other. How that property is actually delivered varies by engine; in the tool used here, the path is not per-row version chains but snapshots defined through the log. The mechanism itself will be built through a model in the next section. #### The Version Chain and the Visibility Rule In an engine that keeps versions per row, each version carries two extra fields: the number of the transaction that **created** it and the number of the transaction that **deleted** it. If the deleted field is empty, the version is still valid. An update does two things within a single transaction: it fills in the current version's deleted field, and it appends a new version. A delete does only the first of these. The visibility rule is one sentence: a version is visible in a snapshot if its **creating transaction has committed** and its **deleting transaction has not committed**. The model below runs the rule. Real engines' rules are more elaborate — changes a transaction's own uncommitted work has not seen yet, aborted transactions, and subtransactions are handled separately — but the core is the same. ```sh cat > version.mjs <<'JS' // Model: row versions and the visibility rule. Not the actual engine. class Store { constructor() { this.versions = []; this.committed = new Set(); this.nextTxn = 10; } beginTxn() { const t = this.nextTxn; this.nextTxn += 10; return t; } commit(t) { this.committed.add(t); } snapshot() { return new Set(this.committed); } // the committed transactions at that moment insert(t, rowId, value) { this.versions.push({ rowId, value, created: t, deleted: null }); } update(t, rowId, value) { const old = this.versions.find((s) => s.rowId === rowId && s.deleted === null); if (old) old.deleted = t; this.versions.push({ rowId, value, created: t, deleted: null }); } visible(s, snap) { return snap.has(s.created) && (s.deleted === null || !snap.has(s.deleted)); } read(rowId, snap) { const s = this.versions.find((v) => v.rowId === rowId && this.visible(v, snap)); return s ? s.value : '(none)'; } reclaimable(snapshots) { // versions no snapshot can see return this.versions.filter((s) => snapshots.every((a) => !this.visible(s, a))); } } const d = new Store(); const t1 = d.beginTxn(); // the transaction that creates the loan record d.insert(t1, 501, 'return_date = NULL'); d.commit(t1); const readerSnap = d.snapshot(); // the long read starts here const t2 = d.beginTxn(); d.update(t2, 501, "return_date = '2024-03-20'"); d.commit(t2); const newSnap = d.snapshot(); const t3 = d.beginTxn(); d.update(t3, 501, "return_date = '2024-03-21'"); d.commit(t3); const newestSnap = d.snapshot(); console.log('version chain (row 501):'); for (const s of d.versions) console.log(` created=${s.created} deleted=${s.deleted ?? '—'} ${s.value}`); console.log('reader\'s snapshot →', d.read(501, readerSnap)); console.log('second snapshot →', d.read(501, newSnap)); console.log('newest snapshot →', d.read(501, newestSnap)); console.log('reclaimable versions while the reader is open :', d.reclaimable([readerSnap, newestSnap]).length); console.log('after the reader closes :', d.reclaimable([newestSnap]).length); JS node version.mjs ``` ``` version chain (row 501): created=10 deleted=20 return_date = NULL created=20 deleted=30 return_date = '2024-03-20' created=30 deleted=— return_date = '2024-03-21' reader's snapshot → return_date = NULL second snapshot → return_date = '2024-03-20' newest snapshot → return_date = '2024-03-21' reclaimable versions while the reader is open : 1 after the reader closes : 2 ``` Three versions of the same loan record sit in the store at the same time, and three separate snapshots each see a different one of them. No waiting anywhere; every reader finds the version that matches its own criterion. One consequence of this arrangement is that an `UPDATE` statement is not physically an update. Even an update that changes a single byte of a single column writes a full new copy of the row. This is also behind the index cost the Advanced SQL course stated as "the same cost applies to an `UPDATE` that touches a key column": the new version sits in a new place, and the index entries pointing to that row have to be updated too. #### When Versions Can Go Away The last two lines of the model show the actual administrative problem. Old versions cannot be kept forever; a version no longer seen by any snapshot can be removed. The criterion follows directly: a version **older than the oldest open snapshot** is reclaimable. In the model, while the reader's snapshot is open, only one version is reclaimable; once the reader closes, the count rises to two. A single long read is enough to keep two out of a three-version chain pinned in place. The scale of this result matters. A transaction left open for hours keeps the old versions of **every** update made during that time alive. The table's row count may not change, yet the space it occupies on disk keeps growing. The previous lesson's observation that a checkpoint can be delayed by readers is another face of this same rule. #### The Conflict Multi-Versioning Does Not Remove The reader and the writer not blocking each other does not mean every conflict is gone. The run below shows two points together: two writers conflicting, and an open read stalling a checkpoint. ```sh rm -f conflict.db conflict.db-wal conflict.db-shm cat > blocking2.mjs <<'JS' import { DatabaseSync } from 'node:sqlite'; import { rmSync } from 'node:fs'; for (const suffix of ['', '-wal', '-shm']) rmSync('conflict.db' + suffix, { force: true }); const setup = new DatabaseSync('conflict.db'); setup.exec('PRAGMA journal_mode = WAL'); setup.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, return_date TEXT)'); setup.exec(`INSERT INTO loan SELECT n, 1+(n*13)%500, NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) SELECT n FROM s)`); setup.exec('PRAGMA wal_checkpoint(TRUNCATE)'); setup.close(); const writerA = new DatabaseSync('conflict.db'), writerB = new DatabaseSync('conflict.db'); writerA.exec('BEGIN IMMEDIATE'); writerA.exec("UPDATE loan SET return_date = '2024-04-01' WHERE loan_id = 1"); try { writerB.exec('BEGIN IMMEDIATE'); writerB.exec("UPDATE loan SET return_date = '2024-04-02' WHERE loan_id = 2"); console.log('two writers: both proceeded'); } catch (err) { console.log('two writers → second writer:', err.message.split('\n')[0]); } writerA.exec('COMMIT'); writerA.close(); writerB.close(); // does a long read delay the checkpoint const reader = new DatabaseSync('conflict.db'), writer = new DatabaseSync('conflict.db'); writer.exec('PRAGMA wal_autocheckpoint = 0'); reader.exec('BEGIN'); reader.prepare('SELECT count(*) AS c FROM loan').get(); for (let i = 1; i <= 3000; i++) writer.exec(`UPDATE loan SET return_date='2024-05-01' WHERE loan_id=${1+(i*37)%20000}`); console.log('checkpoint while the reader is open :', JSON.stringify(writer.prepare('PRAGMA wal_checkpoint(PASSIVE)').get())); reader.exec('COMMIT'); console.log('checkpoint once the reader is done :', JSON.stringify(writer.prepare('PRAGMA wal_checkpoint(PASSIVE)').get())); reader.close(); writer.close(); JS node blocking2.mjs ``` ``` two writers → second writer: database is locked checkpoint while the reader is open : {"busy":0,"log":3532,"checkpointed":0} checkpoint once the reader is done : {"busy":0,"log":3532,"checkpointed":3532} ``` The first line shows the boundary: when two transactions try to write at the same time, the second one cannot proceed. In the tool used here, this limit applies to the whole database; in engines that resolve conflicts at row granularity, the limit applies only between transactions updating the **same row**. Either way, the principle is the same: versions free up reading, but two writes changing the same data still queue up. This is why the Advanced SQL course's deadlock discussion still holds. The second and third lines close the point the previous lesson left open. While the reader's transaction was open, 3,532 frames had accumulated in the log, and the checkpoint was able to process **zero** of them. Once the reader finished its transaction, the same call processed all 3,532 frames. The reason is singular: those frames carry the versions the reader can see, and they cannot be overwritten before the reader is done. #### Summary - Lock-based control provides consistency by waiting; multi-version control provides it by keeping the row's old state. - In the measurement, the writer was blocked under the rollback journal; under the write-ahead log, the same writer completed and the reader kept seeing the old value throughout its transaction. - Each version carries the transactions that created and deleted it; a version is visible in a snapshot if its creator has committed and its deleter has not. - A version can be removed only once no open snapshot sees it; a single long read kept two of the model's three versions pinned in place. - Multi-versioning separates reading from writing, not two writes that change the same data; an open read also stalls a checkpoint (zero of 3,532 frames could be processed). #### Next Step Old versions becoming reclaimable does not mean they are reclaimed on their own. A separate process does the reclaiming, and if that process cannot keep up, a table keeps growing even when its row count never rises: every update leaves a new version behind, and the old one stays in place. The next lesson takes up this buildup: measuring dead rows, reclaiming space, why reclaimed space does not always return to the operating system, and the balance between the cleanup process and the bloat ratio. ### Dead Row Cleanup Source: https://academia.sh/en/courses/database-administration/dead-row-cleanup The dead versions left behind by deletes and updates, deleted space not returning to the file, why space is not always reusable, the cost of a full rewrite, and the bloat ratio's dependence on cleanup frequency and open transaction duration. The previous lesson showed that a version becomes reclaimable only once no open snapshot sees it anymore. Being reclaimable and being reclaimed are separate things. A separate process collects versions, and if that process cannot keep up with the dead versions being produced, a table keeps growing even when its row count never rises. This lesson's questions can be measured: where does a deleted row's space go, when is it reused, how much does the file grow when it is not, and what does reversing that growth cost? #### Dead Tuples and Bloat A **dead tuple** is a row version no transaction sees anymore. It comes from two sources: a deleted row itself, and an updated row's old version. Under the model from the previous lesson, an update also leaves behind a dead version, because a new version is physically written. **Bloat** is how much larger the space a table or index occupies is than the space needed to hold its live data. It is measured as a ratio: the number of pages occupied divided by the number of pages the same data would occupy compacted. Bloat is not itself a defect; some of it is unavoidable and useful, because the free space leaves room for new rows. The problem is the ratio continuing to grow. Scanning a bloated table reads more pages, holds more space in the buffer pool, and makes backups larger — all for the same data. #### Where Deleted Space Goes The run below tracks four stages using the same three measurements: row count, page count, and in-page fill. The file size is also printed at each stage. ```sh rm -f cleanup.db cat > status.sql <<'EOF' SELECT (SELECT count(*) FROM loan) AS rows_count, (SELECT page_count FROM pragma_page_count) AS pages, (SELECT round(100.0*sum(payload)/sum(pgsize),1) FROM dbstat WHERE name='loan') AS fill_pct; EOF sqlite3 cleanup.db <<'SQL' .mode column .headers on CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup_date TEXT, return_date TEXT); INSERT INTO loan SELECT n, 1+(n*7)%200000, 1+(n*13)%120000, date('2018-01-01','+'||((n*37)%2437)||' days'), NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<400000) SELECT n FROM s); .print '--- start ---' .read status.sql .shell echo "file: $(wc -c < cleanup.db)" DELETE FROM loan WHERE pickup_date < '2020-01-01'; .print '--- 119822 rows deleted ---' .read status.sql .shell echo "file: $(wc -c < cleanup.db)" INSERT INTO loan SELECT 500000+n, 1+(n*7)%200000, 1+(n*13)%120000, '2025-01-01', NULL FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<100000) SELECT n FROM s); .print '--- 100000 new rows (with an increasing key) ---' .read status.sql .shell echo "file: $(wc -c < cleanup.db)" VACUUM; .print '--- after VACUUM ---' .read status.sql .shell echo "file: $(wc -c < cleanup.db)" SQL ``` ``` --- start --- rows_count pages fill_pct ---------- ----- -------- 400000 2705 77.9 file: 11079680 --- 119822 rows deleted --- rows_count pages fill_pct ---------- ----- -------- 280178 2705 54.6 file: 11079680 --- 100000 new rows (with an increasing key) --- rows_count pages fill_pct ---------- ----- -------- 380178 3381 59.2 file: 13848576 --- after VACUUM --- rows_count pages fill_pct ---------- ----- -------- 380178 2571 77.8 file: 10530816 ``` The second stage gives the first result. Roughly a third of the rows were deleted; the page count did not drop by even one, and the file size did not shrink by a single byte. The only thing that changed is fill: it dropped from 77.9% to 54.6%. The delete did not free the pages — it emptied the pages **out**. The reason for this behavior is direct. Deleted rows are scattered across the whole file; a page only fully empties out once every row on it has been deleted. A partially emptied page cannot be removed from the file, because — under the relationship measured in the Physical Storage Layout lesson — the file consists of nothing but pages laid end to end; removing a page in the middle would mean moving everything after it. #### Why Space Is Not Always Reused The third stage gives a more interesting result. With 119,822 rows' worth of empty space in the table, 100,000 rows were inserted, and the file did not shrink — it **grew**: from 2,705 pages to 3,381 pages. The reason is key selection. `loan_id` is a continuously increasing value, and new rows are appended to the far right edge of the tree. The gaps left by deleted rows, though, sit in the middle of the tree — the new keys do not belong there. In a sequential structure, the engine cannot place a key anywhere other than where it belongs. This is one of the most concrete links between schema design and storage. In a table that is appended to with a continuously increasing key and has old records deleted by date, empty space does not close up on its own: new data always gets written to the end, and the gaps always stay in the middle. If the same table were filled with random keys instead, some of the gaps would naturally get used — but at the cost of more page splits. The Table Partitioning topic will bring a structural answer to this problem: instead of deleting old data, dropping the entire old partition. #### Reclaiming Space The fourth stage runs `VACUUM`, and the table drops from 3,381 pages to 2,571 pages; fill returns to 77.8%, close to the first day's value (77.9%). The bloat ratio was 3381 / 2571 ≈ 1.32; the rewrite brought it down to 1.00. Knowing what this operation does comes before knowing when to use it: the table is read from start to end and written out compacted in a new location, then the old copy's space is released. It has three costs. **Locking.** During a full rewrite, the table cannot be modified (in most engines), and in some it cannot even be read. In a running system, this means planned downtime. **Temporary space.** While the new copy is being written, the old copy is still in place; the operation needs extra space roughly equal to the table's size. On a server that is nearly out of disk, running a rewrite to free up space does not work, because it requires space that is not available. **Duration.** The cost is proportional to the whole table, not to the space being reclaimed. For this reason, engines offer two lighter-weight paths: a background cleanup that makes space reusable without locking, and a reorganization that builds a new copy while keeping the table available. Their names and limits are specific to the engine; a full rewrite is, as a rule, the last resort. #### The Balance of Cleanup The real administrative question is not "when should we rewrite," but "what keeps bloat from climbing past a given ratio." The model below varies two variables separately: how often cleanup runs and how long a transaction stays open. It is not the actual cleanup process; what it tracks is the highest amount of space ever allocated, relative to the live data. ```sh cat > cleanup2.mjs <<'JS' // Model: the balance between dead version buildup and cleanup. Not the actual engine. // At each step, UPDATES rows are updated; each update leaves one dead version. // Cleanup runs once every INTERVAL steps and only collects dead versions older // than the oldest open snapshot. Allocated space is not returned to the operating system. function run({ interval, longTxn = null, steps = 600 }) { const LIVE = 100000, UPDATES = 2000; let dead = []; // each entry: the step it was created at let allocated = LIVE, peak = LIVE; for (let i = 1; i <= steps; i++) { for (let k = 0; k < UPDATES; k++) dead.push(i); allocated = Math.max(allocated, LIVE + dead.length); peak = Math.max(peak, allocated); if (i % interval === 0) { const cutoff = (longTxn && i >= longTxn.start && i <= longTxn.end) ? longTxn.start : i; dead = dead.filter((d) => d >= cutoff); // entries older than the open snapshot are collected } } return { remaining: dead.length, bloat: peak / LIVE }; } console.log('— cleanup frequency —'); console.log('interval (steps) remaining dead versions bloat ratio'); for (const a of [1, 5, 25, 100, 600]) { const s = run({ interval: a }); console.log(String(a).padStart(17), String(s.remaining).padStart(25), (s.bloat.toFixed(2) + '×').padStart(12)); } console.log('— same cleanup (interval 5), with an open long transaction —'); for (const length of [0, 100, 300, 600]) { const s = run({ interval: 5, longTxn: length ? { start: 1, end: length } : null }); console.log(`long txn ${String(length).padStart(3)} steps →`, String(s.remaining).padStart(7), 'dead versions,', (s.bloat.toFixed(2) + '×').padStart(6), 'bloat'); } JS node cleanup2.mjs ``` ``` — cleanup frequency — interval (steps) remaining dead versions bloat ratio 1 2000 1.04× 5 2000 1.12× 25 2000 1.52× 100 2000 3.02× 600 2000 13.00× — same cleanup (interval 5), with an open long transaction — long txn 0 steps → 2000 dead versions, 1.12× bloat long txn 100 steps → 2000 dead versions, 3.10× bloat long txn 300 steps → 2000 dead versions, 7.10× bloat long txn 600 steps → 1200000 dead versions, 13.00× bloat ``` The first table is as expected: the more infrequently cleanup runs, the more dead versions pile up and the higher the peak allocated space climbs. Cleanup running at every step keeps bloat at 1.04; running once every six hundred steps drives it to 13.00. Do not be misled by the remaining dead version count coming out the same in every row — what is measured is not the final state but the peak reached over the run; once space is allocated, it is not given back. The second table gives the real warning. Cleanup frequency never changed — it still runs every five steps — but a single transaction staying open for three hundred steps drove bloat from 1.12 to 7.10. Cleanup is running, but it finds nothing to collect: every dead version produced since that transaction's snapshot is still visible. The result comes down to two sentences. What sets the upper bound on bloat is whichever is larger: how often cleanup runs, or **how old the oldest open transaction is**. This is why investigating bloat problems starts not from cleanup settings, but from the list of transactions that have been open for a long time. #### Summary - A dead tuple is a row version no transaction sees anymore; both deletes and updates produce it. Bloat is the ratio of occupied space to needed space. - In the measurement, deleting a third of the rows left the page count and file size completely unchanged; only in-page fill dropped, from 77.9% to 54.6%. - New rows inserted with a continuously increasing key cannot use the gaps in the middle; with 119,822 rows' worth of empty space available, inserting 100,000 rows grew the file from 2,705 to 3,381 pages. - A full rewrite returned fill close to its first-day value (2,571 pages), but it requires locking, temporary space equal to the table's size, and a duration proportional to the whole table. - In the model, two factors set the bloat ratio: cleanup frequency and the age of the oldest open transaction; a single transaction staying open for three hundred steps drove bloat from 1.12 to 7.10. #### Next Step Every question asked so far in this course has been answered with a number: page count, fill, frame count, hit rate. These numbers do not sit off to the side; the engine keeps information about its own structure in ordinary tables and hands it out through ordinary queries. The next lesson takes up that information: where the definitions of tables, columns, indexes, and constraints are stored, what administrative questions can be answered from there, and why querying the schema is superior to hand-maintained lists. ### System Catalog Source: https://academia.sh/en/courses/database-administration/system-catalog Storing schema definitions in queryable tables, turning inventory and administrative rules into queries, measuring schema drift between two copies, and why the catalog is read but not written. Every question asked so far in this course has been answered with a number: page count, fill, frame count, hit rate. The source of these numbers is the engine's own records, and those records do not sit behind a special interface. The engine keeps schema information in ordinary tables and hands it out through ordinary queries. This entire set of tables is called the **system catalog**. The catalog being queryable takes administration out of being a documentation task. Which columns which tables carry, which indexes exist, which constraints are defined — none of this sits written down in a hand-maintained list; it is written in the database itself, and that list is always current, because the statement that makes the change also updates it. #### The Catalog Is an Ordinary Table The block below sets up the library schema. The schema is familiar from earlier courses; indexes, a view, and a value check have been added to it here. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, registration_date TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','closed')) ); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, isbn TEXT UNIQUE, branch_id INTEGER NOT NULL REFERENCES branch(branch_id) ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES member(member_id), book_id INTEGER NOT NULL REFERENCES book(book_id), pickup_date TEXT NOT NULL, return_date TEXT ); CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_book_date ON loan(book_id, pickup_date); CREATE VIEW open_loan AS SELECT * FROM loan WHERE return_date IS NULL; INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'); INSERT INTO member (member_id,name,email,registration_date) VALUES (1,'Alice','alice@example.test','2023-02-14'); INSERT INTO book VALUES (1,'Handbook of Astronomy','Y. Sterling','978-0201896831',1); INSERT INTO loan VALUES (1,1,1,'2025-06-01',NULL); SQL ``` The list of schema objects can be pulled with a single query. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name; SQL ``` ``` type name tbl_name ----- ------------------------- --------- index loan_book_date loan index loan_member loan index sqlite_autoindex_book_1 book index sqlite_autoindex_member_1 member table book book table branch branch table loan loan table member member view open_loan open_loan ``` Two rows in the list stand out as never having been written: the `sqlite_autoindex_` prefixed indexes. No one defining the schema wrote these; the engine created them itself to enforce a uniqueness constraint. The catalog shows not just what was written, but the structure **the engine built**. This distinction is useful in administration: a column being unique means an index cost in the background, and that cost is visible right here. The convention of separating internal object names with a prefix is specific to the engine; what does not change is that internal objects also live in the catalog. #### Extracting an Inventory with a Query Because the catalog is queryable, every question about the schema can be turned into a query. The query below produces a per-table column and constraint count. ```sh sqlite3 library.db <<'SQL' .headers on .mode column -- Schema inventory: column and constraint counts per table. SELECT m.name AS table_name, count(*) AS columns, sum(ti."notnull") AS not_null, sum(ti.pk > 0) AS primary_key FROM sqlite_schema m JOIN pragma_table_info(m.name) ti WHERE m.type='table' GROUP BY m.name ORDER BY m.name; SQL ``` ``` table_name columns not_null primary_key ---------- ------- -------- ----------- book 5 3 1 branch 3 2 1 loan 5 3 1 member 5 3 1 ``` This table is the starting point of a schema design discussion: how many columns each table carries, how many are required, whether any table lacks a primary key. In a hand-maintained document, these three questions each need their own section, and all three go stale; pulled from the catalog, the query gives a current answer every time it runs. #### Audits Written Against the Catalog The catalog's real value is that **rules** can be written as queries. The block below applies, in advance, a rule that will come up in the Indexes and Partitioning topic: every column carrying a foreign key should have its own index, otherwise deletes and updates on the parent table have to scan the child table from end to end. ```sh sqlite3 library.db <<'SQL' .headers on .mode column -- 1. Foreign keys left without an index: raises the cost of deletes and updates. SELECT m.name AS table_name, f."from" AS column_name, f."table" AS target FROM sqlite_schema m JOIN pragma_foreign_key_list(m.name) f WHERE m.type = 'table' AND NOT EXISTS ( SELECT 1 FROM pragma_index_list(m.name) il JOIN pragma_index_info(il.name) ii WHERE ii.seqno = 0 AND ii.name = f."from" ); SQL ``` ``` table_name column_name target ---------- ----------- ------ book branch_id branch ``` The audit produced a single finding: the book table's reference to branch is unindexed. The loan table's two references are covered — one by its own index, the other because it is the first column of a composite index. That a composite index's **first** column, and only that column, does this job is the counterpart here of the rule established in the SQL Performance topic. This audit's advantage over a hand-maintained list is threefold. When a new table is added, the audit covers it automatically. When an index is dropped, the finding comes back automatically. And the audit reads what the engine actually knows, not what a person wrote — when the two diverge, the divergence becomes visible. Other audits can be written with the same pattern: tables without a primary key, indexes that are never used, duplicate indexes sharing the same column list, status columns with no value check. Each one is an administrative rule in the form of a query. #### Measuring Schema Drift Because the catalog is queryable, two databases' schemas can be compared. This is the operation most often needed during version upgrades and cross-environment consistency checks. ```sh # The difference between two schemas: are the development and production copies the same? sqlite3 production.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, registration_date TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, isbn TEXT UNIQUE, branch_id INTEGER NOT NULL REFERENCES branch(branch_id)); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES member(member_id), book_id INTEGER NOT NULL REFERENCES book(book_id), pickup_date TEXT NOT NULL, return_date TEXT); CREATE INDEX loan_member ON loan(member_id); SQL compare() { sqlite3 "$1" "SELECT type||' '||name||' ('||coalesce((SELECT group_concat(ti.name||':'||ti.type, ', ') FROM pragma_table_info(m.name) ti), '')||')' FROM sqlite_schema m WHERE name NOT LIKE 'sqlite_%' ORDER BY type, name;" } compare library.db > development.list compare production.db > production.list echo "--- only in development ---" comm -23 development.list production.list echo "--- only in production ---" comm -13 development.list production.list ``` ``` --- only in development --- index loan_book_date () table member (member_id:INTEGER, name:TEXT, email:TEXT, registration_date:TEXT, status:TEXT) view open_loan (loan_id:INTEGER, member_id:INTEGER, book_id:INTEGER, pickup_date:TEXT, return_date:TEXT) --- only in production --- table member (member_id:INTEGER, name:TEXT, email:TEXT, registration_date:TEXT) ``` The diff shows three items. An index and a view exist only in the development copy. The member table exists on both sides, but it is **not the same**: a status column has been added in development and is missing in production. Including the column list in the comparison makes this difference visible; if only object names had been compared, the two tables would have counted as equal. The direction of the drift is also information. A column present in development but absent in production means a migration that has not yet been applied. An object present in production but absent in development is more concerning: it points to a change written directly to production, with no counterpart in version control. #### Writing to the Catalog The catalog is read; it is **not written by hand**. Schema changes are made with data definition statements, and the engine takes on updating the catalog. Writing directly to catalog tables — even where it is technically possible in some engines — breaks the engine's internal consistency: the physical layout in the data files and the definition in the catalog diverge, and that divergence usually shows up later as a hard-to-diagnose error on some subsequent read. The practical form of the rule is this: migrations are written as statements and kept in version control; the catalog is read only to **verify** whether those migrations have been applied. #### Summary - The system catalog is the set of tables that hold the schema's definitions and can be read with ordinary queries; because the statement that makes a change also updates it, the catalog is always current. - The catalog shows not just what was written but the objects the engine builds on its own: the index behind a uniqueness constraint shows up in the list. - Every administrative rule about the schema can be turned into a query; the unindexed foreign key audit produced a single finding in the model and covers new tables automatically. - Comparing two copies' schemas together with their column lists makes drift visible; comparing only object names would count a changed table as equal. - The catalog is read, not written by hand: schema changes are made with statements, and the catalog is queried to verify that change was applied. #### Next Step The engine's internals close out with this lesson: processes, pages, the log, checkpoints, version chains, cleanup, and the catalog that records all of it. One finding was left open in this section — the unindexed foreign key. The next topic opens the area that finding belongs to: how index types differ from each other, why column order changes the outcome in a composite index, when partial and covering indexes pay off, and which problem splitting a table into partitions solves and which it creates. ## Indexes and Partitioning Tree-based, hash-based, and special-purpose index types, column order in composite indexes, partial and covering indexes, index bloat and maintenance cost, range and hash table partitioning, and the effect of horizontal sharding on the application. ### Index Types Source: https://academia.sh/en/courses/database-administration/index-types The measured cost of an unindexed foreign key, the tree-based index's ability to answer range and order questions, the hash-based approach's equality-only operation, and the inverted index for word-level search. The Engine Architecture topic closed with the system catalog, and it left behind a single finding: the branch reference in the book table was unindexed. That finding was written as a recommendation — "every column carrying a foreign key should have its own index." Recommendations remain opinion until they are measured. This lesson first turns that finding into a number, then moves on to its real question. Query Performance introduced the index as a single structure: a sorted copy of the table kept alongside it. In reality an index is not one structure but a family of structures, and the members of the family are told apart by which question they can answer. One index type answers equality but not range; another finds not the whole value but the words inside it. The choice is made by looking at the shape of the question. #### The Finding Left Open A foreign key constraint requires that a value in one table exist in another table. That requirement also runs in the opposite direction: when a row is deleted from the parent table, the engine must verify that no child row still refers to it. What determines how that verification is carried out is whether the child column carries an index. ```sh rm -f fk.db fk_indexed.db sqlite3 fk.db <<'SQL' PRAGMA foreign_keys = ON; CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, branch_id INTEGER NOT NULL REFERENCES branch(branch_id)); INSERT INTO branch VALUES (1,'Central'),(2,'Bahcelievler'),(3,'Kadikoy'),(4,'Beyoglu'), (5,'Konak'),(6,'Nilufer'),(7,'Selcuklu'),(8,'Cankaya'),(9,'Warehouse'); INSERT INTO book (book_id, title, branch_id) WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000) SELECT n, 'Book ' || n, 1 + (n % 8) FROM s; SQL cp fk.db fk_indexed.db sqlite3 fk_indexed.db 'CREATE INDEX book_branch ON book(branch_id);' for db in fk.db fk_indexed.db; do printf '%s: ' "$db" sqlite3 "$db" <<'SQL' PRAGMA foreign_keys = ON; .stats vmstep DELETE FROM branch WHERE branch_id = 9; SQL done ``` ``` fk.db: VM-steps: 600013 fk_indexed.db: VM-steps: 15 ``` The deleted branch carried no books at all; both runs delete exactly one row. The difference is in the work performed: in the unindexed copy the engine looked at all two hundred thousand books one by one and confirmed that none belonged to branch nine. In the indexed copy the same check took fifteen steps — entering the index and seeing that key had no entries was enough. The ratio here is forty thousand-fold, and it grows with the table. This is the reason behind the check written into the catalog: an index on a foreign key column is needed not for read performance but for **every delete and key update on the parent table**. This cost is invisible in query plans, because the statement that produces it is a `DELETE` statement, and the expensive part is the constraint check. #### The Tree-Based Index The index type used in Query Performance is the **tree-based index (tree index)**: a structure, usually shaped as a B-tree, that keeps its entries sorted by key value. Measurements run on the library dataset; the setup below is used throughout the topic. ```sh rm -f library.db cat > setup.sql <<'SQL' CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, registration_date TEXT NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO member (member_id, name, city, registration_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000) SELECT n, 'Member ' || n, CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir' WHEN 3 THEN 'Bursa' ELSE 'Konya' END, date('2015-01-01', '+' || (n % 3200) || ' days') FROM counter; INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 library.db < setup.sql sqlite3 library.db <<'SQL' CREATE INDEX loan_pickup ON loan(pickup_date); .print '--- equality ---' EXPLAIN QUERY PLAN SELECT count(*) FROM loan WHERE pickup_date = '2021-03-15'; .stats vmstep SELECT count(*) FROM loan WHERE pickup_date = '2021-03-15'; .stats off .print '--- range ---' EXPLAIN QUERY PLAN SELECT count(*) FROM loan WHERE pickup_date BETWEEN '2021-03-15' AND '2021-03-21'; .stats vmstep SELECT count(*) FROM loan WHERE pickup_date BETWEEN '2021-03-15' AND '2021-03-21'; .stats off .print '--- minimum value ---' EXPLAIN QUERY PLAN SELECT min(pickup_date) FROM loan; .stats vmstep SELECT min(pickup_date) FROM loan; SQL ``` ``` --- equality --- QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_pickup (pickup_date=?) 820 VM-steps: 2471 --- range --- QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_pickup (pickup_date>? AND pickup_date=?) 820 VM-steps: 3292 --- same equality, queried through the date itself --- QUERY PLAN `--SCAN loan 820 VM-steps: 6000831 --- range --- QUERY PLAN `--SCAN loan 5744 VM-steps: 7046372 ``` Three results say three separate things. Equality queried through the hash was answered in 3,292 steps — close to the tree index's 2,471 steps, in the same order of magnitude. The second measurement shows the model's limit: the same equality, written against the date column itself, did not use the index. In a real hash index, the engine performs this conversion itself; the caller writes an ordinary equality and the engine computes the search key's hash. In the model, the conversion has to be carried out by whoever writes the query. What stays constant is this: **as long as the search key's hash can be computed**, equality is answered. The third measurement is the real distinction. The range query fell all the way to a full table scan despite the index existing, spending 7,046,372 steps. The reason is structural: the hash function is deliberately disordered. The hash values of two consecutive dates can sit at opposite ends of the index. A range such as "between March 15 and March 21" has no hash counterpart, because which hashes the values in between fall into cannot be computed — not without trying each one individually. From here the hash index's use case follows: columns queried only by equality, where range and order are never wanted. What it earns in exchange is space; when long text keys collapse into a fixed-size hash, the index shrinks. Giving up everything the tree index offers in exchange for this gain is not the preferred trade in most deployments. #### The Inverted Index A third family targets a different question. A tree index treats **the whole value** as the key; this is why it answers prefix queries but cannot find a word sitting in the middle of a value. Searching book subject headings in a library catalog is exactly this question. ```sh rm -f subject.db sqlite3 subject.db <<'SQL' CREATE TABLE book_subject (book_id INTEGER PRIMARY KEY, subject TEXT NOT NULL); INSERT INTO book_subject (book_id, subject) WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000), word(i, w) AS (VALUES (0,'astronomy'),(1,'geography'),(2,'history'),(3,'poetry'),(4,'novel'), (5,'atlas'),(6,'dictionary'),(7,'handbook'),(8,'seamanship'), (9,'architecture'),(10,'music'),(11,'philosophy'),(12,'cartography'), (13,'botany'),(14,'mineralogy'),(15,'law'),(16,'theater'), (17,'logic'),(18,'economics'),(19,'anatomy')) SELECT n, (SELECT w FROM word WHERE i = n % 20) || ' ' || (SELECT w FROM word WHERE i = (n/20) % 20) || ' ' || (SELECT w FROM word WHERE i = (n/400) % 20) FROM s; CREATE INDEX subject_tree ON book_subject(subject); CREATE VIRTUAL TABLE subject_inverted USING fts5(subject, content='book_subject', content_rowid='book_id'); INSERT INTO subject_inverted(subject_inverted) VALUES('rebuild'); SQL sqlite3 subject.db <<'SQL' PRAGMA case_sensitive_like = ON; .print '--- tree index, left-anchored pattern ---' EXPLAIN QUERY PLAN SELECT count(*) FROM book_subject WHERE subject LIKE 'cartography seamanship%'; .stats vmstep SELECT count(*) FROM book_subject WHERE subject LIKE 'cartography seamanship%'; .stats off .print '--- same tree index, left-open pattern ---' EXPLAIN QUERY PLAN SELECT count(*) FROM book_subject WHERE subject LIKE '%cartography seamanship%'; .stats vmstep SELECT count(*) FROM book_subject WHERE subject LIKE '%cartography seamanship%'; .stats off .print '--- inverted index, same phrase ---' EXPLAIN QUERY PLAN SELECT count(*) FROM subject_inverted WHERE subject_inverted MATCH '"cartography seamanship"'; .stats vmstep SELECT count(*) FROM subject_inverted WHERE subject_inverted MATCH '"cartography seamanship"'; SQL ``` ``` --- tree index, left-anchored pattern --- QUERY PLAN `--SEARCH book_subject USING COVERING INDEX subject_tree (subject>? AND subject setup.sql <<'SQL' CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, registration_date TEXT NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO member (member_id, name, city, registration_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000) SELECT n, 'Member ' || n, CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir' WHEN 3 THEN 'Bursa' ELSE 'Konya' END, date('2015-01-01', '+' || (n % 3200) || ' days') FROM counter; INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 library.db < setup.sql ``` Three queries are the three questions the loan desk actually asks. The first wants a member's loan history in date order. The second counts every book picked up on a given day. The third wants a member's records after a given date. The same two columns are indexed in two different orders, and all three queries are run against both indexes. ```sh for cols in "member_id, pickup_date" "pickup_date, member_id"; do rm -f order.db sqlite3 order.db < setup.sql sqlite3 order.db "CREATE INDEX loan_composite ON loan($cols);" printf '=== index: (%s) ===\n' "$cols" sqlite3 order.db <= '2022-01-01'; .stats vmstep SELECT count(*) FROM loan WHERE member_id = 4 AND pickup_date >= '2022-01-01'; SQL done ``` ``` === index: (member_id, pickup_date) === -- Q1: member equality, ordered by date QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_composite (member_id=?) 17 VM-steps: 135 -- Q2: date equality only QUERY PLAN `--SCAN loan USING COVERING INDEX loan_composite 820 VM-steps: 6000831 -- Q3: member equality + date range QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_composite (member_id=? AND pickup_date>?) 8 VM-steps: 37 === index: (pickup_date, member_id) === -- Q1: member equality, ordered by date QUERY PLAN `--SCAN loan USING COVERING INDEX loan_composite 17 VM-steps: 6000101 -- Q2: date equality only QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_composite (pickup_date=?) 820 VM-steps: 2471 -- Q3: member equality + date range QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_composite (pickup_date>?) 8 VM-steps: 2402957 ``` Six measurements fit into one table. With column order `(member_id, pickup_date)`, Q1 spent 135 steps, Q2 spent 6,000,831 steps, and Q3 spent 37 steps. With the reversed order, Q1 became 6,000,101, Q2 became 2,471, and Q3 became 2,402,957. No single order wins all three queries at once. The reason is a single structural fact. A composite index sorts its entries first by the first column, and only for equal first-column values by the second. Such an order only helps if it provides an **entry point**, and the entry point always starts from the beginning. #### The Leftmost Prefix Rule The rule's name is the **leftmost prefix rule (leftmost prefix rule)**: a composite index provides an entry point only for the part of the column list starting from the left. An `(a, b, c)` index answers a condition on `a`, a condition on `a` and `b`, and a condition on `a`, `b`, and `c`; it does not answer a condition on `b` alone, or on `b` and `c` alone. The measurements confirm this three times over. Q2 filters on the date alone: in the `(member_id, pickup_date)` index the date is the second column, so there is no entry point, and the plan says `SCAN`. The same query finishes in 2,471 steps in the `(pickup_date, member_id)` index, because it filters on the first column. Q3 shows the rule's second face. In the `(member_id, pickup_date)` index both conditions reach into the key — the plan states this by writing `(member_id=? AND pickup_date>?)` — and the work finishes in 37 steps. In the reversed order, only the date range becomes the key; the engine reads every record from 2022 onward out of the index and tests the member condition on each one individually. The result is the same eight rows, at a cost of 2.4 million steps. From this the rule for choosing column order follows: **columns filtered by equality go first, range and sort columns go last.** Equality pins the position in the index to a single point and preserves the order of the following column; a range, once entered, scatters the order of the columns that follow it. In the `(member_id, pickup_date)` index, the date order is preserved separately for each member, which is why Q1 never needed a sort node. Nothing forbids the same column from appearing in two indexes at once. The way to speed up all three queries above together is to build two composite indexes; the cost is that both trees are updated on every write. That cost is measured in the Index Maintenance lesson. #### The Partial Index The second relaxation concerns coverage. A **partial index (partial index)** is an index that holds only the rows of the table satisfying one condition. In the library this maps directly: most loan records are closed, and what the day-to-day work asks about is the open loans. ```sh rm -f base.db full.db partial.db sqlite3 base.db < setup.sql cp base.db full.db cp base.db partial.db sqlite3 full.db 'CREATE INDEX loan_member ON loan(member_id);' sqlite3 partial.db 'CREATE INDEX loan_open ON loan(member_id) WHERE return_date IS NULL;' echo "-- covered row count --" sqlite3 base.db "SELECT count(*) AS all_rows FROM loan; SELECT count(*) AS open_rows FROM loan WHERE return_date IS NULL;" echo "-- page count --" for db in base.db full.db partial.db; do printf '%-10s %s\n' "$db" "$(sqlite3 "$db" 'PRAGMA page_count;')" done for db in full.db partial.db; do printf '\n=== %s ===\n' "$db" sqlite3 "$db" <<'SQL' .print '-- a members open loans' EXPLAIN QUERY PLAN SELECT count(*) FROM loan WHERE member_id = 4 AND return_date IS NULL; .stats vmstep SELECT count(*) FROM loan WHERE member_id = 4 AND return_date IS NULL; .stats off .print '-- the same members all loans' EXPLAIN QUERY PLAN SELECT count(*) FROM loan WHERE member_id = 4; .stats vmstep SELECT count(*) FROM loan WHERE member_id = 4; SQL done ``` ``` -- covered row count -- 2000000 222222 -- page count -- base.db 19095 full.db 24833 partial.db 19734 === full.db === -- a members open loans QUERY PLAN `--SEARCH loan USING INDEX loan_member (member_id=?) 6 VM-steps: 103 -- the same members all loans QUERY PLAN `--SEARCH loan USING COVERING INDEX loan_member (member_id=?) 17 VM-steps: 62 === partial.db === -- a members open loans QUERY PLAN `--SEARCH loan USING INDEX loan_open (member_id=?) 6 VM-steps: 36 -- the same members all loans QUERY PLAN `--SCAN loan 17 VM-steps: 6000028 ``` The size difference tracks the row ratio the index covers, exactly. The full index added 5,738 pages to the database (24,833 − 19,095), the partial index added 639 pages (19,734 − 19,095). The ratio 5,738 / 639 ≈ 9.0; the covered-row ratio is 2,000,000 / 222,222 = 9.0 as well. The partial index is small because it holds fewer rows — there is no other trick to it. It wins on the plan side too. On the open-loan query the full index spent 103 steps, the partial index spent 36. The gain comes from two sources: the partial index's tree is shorter, and because every entry coming out of the index already satisfies the condition, there is no need to return to the table and check `return_date`. #### The Partial Index's Boundary The second measurement shows the cost. When the same member's **entire** set of loans was asked for, the partial index could not be used and the query fell to a full table scan: 6,000,028 steps. This should be seen not as a shortcoming but as a consequence of the definition — the partial index has no entry at all for closed loans; using it would have produced a wrong answer. The planner uses a partial index only when the query's condition **implies** the index's condition. In application terms this means a concrete writing discipline: a query that is meant to benefit from a partial index must carry that index's condition in its own `WHERE` clause. If the query fetching open loans does not write `return_date IS NULL`, the index stays out of use. The gain also exists on the write side, and it comes from the same ratio: inserting a row that falls outside the covered condition never touches the index at all. ```sh rm -f write_full.db write_partial.db sqlite3 write_full.db < setup.sql cp write_full.db write_partial.db sqlite3 write_full.db 'CREATE INDEX loan_member ON loan(member_id);' sqlite3 write_partial.db 'CREATE INDEX loan_open ON loan(member_id) WHERE return_date IS NULL;' for db in write_full.db write_partial.db; do printf '%-14s ' "$db" sqlite3 "$db" <<'SQL' .timer on INSERT INTO loan (book_id, member_id, pickup_date, return_date) WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000) SELECT 1 + ((n*7) % 200000), 1 + ((n*13) % 120000), date('2024-01-01','+'||(n%300)||' days'), date('2024-01-15','+'||(n%300)||' days') FROM s; SQL done ``` {/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ``` write_full.db Run Time: real 0.317 user 0.175508 sys 0.141180 write_partial.db Run Time: real 0.080 user 0.077489 sys 0.002005 ``` Since every one of the two hundred thousand inserted rows is a returned record, not a single entry was written to the partial index; in this run the insert took roughly a quarter of the time. Run-time values depend on the environment and change on another machine; what is stable is the ratio between two runs on the same environment. The partial index's natural use case follows from this: **columns with a strongly uneven distribution, where only the small side is queried.** Open loans, records not yet cancelled, unprocessed queue rows, non-null fields. The reverse also holds: if queries ask about both sides of the coverage, a partial index is a misleading saving, because a second, full index will still be needed. #### Summary - A composite index sorts its entries first by the first column; it therefore provides an entry point only for column groups starting from the left — the leftmost prefix rule. - The same two columns in two orders gave three different outcomes across three queries: no order won all three at once, since order is a choice of query set. - When choosing column order, columns filtered by equality go first, range and sort columns go last; a range condition scatters the order of every column that follows it. - A partial index holds only the rows satisfying its condition; in the measurement a full index added 5,738 pages while a partial index added 639 pages, and the ratio tracked the covered-row ratio exactly. - A partial index is used only when the query's condition implies the index's condition; a query outside its coverage falls to a full table scan, and writing rows outside its coverage never touches the index at all. #### Next Step In this lesson one plan line came out with the word `COVERING` twice, and once without it. The difference looked silent but showed up in the numbers: the same index, the same member, 62 steps in one case and 103 in the other. The work in between is returning to the table for every entry found in the index. The next lesson measures that return and builds a way to remove it: putting every column the query asks for into the index. How the gain grows with the number of matching rows, which columns are worth adding to an index, and where this approach stops paying for itself will all be seen through measurement. ### Covering Indexes Source: https://academia.sh/en/courses/database-administration/covering-indexes The real cost of returning from the index to the table, how a covering index removes that return, how the gain grows with the number of matching rows, and how the index approaches a copy of the table as columns are added. The previous lesson's final measurement gave the same index and the same member two different numbers: 62 steps and 103 steps. What made the difference is that the 103-step query also filtered on a column the index does not carry (`iade_tarihi`); that is why one plan carried the word `COVERING` and the other did not. That word describes exactly one thing. If the index carries every column the query asks for, the database produces the answer directly from the index. If it does not, the engine must return to the table for every entry found in the index and read the missing column. This lesson measures how expensive that return is — and the measurement itself produces a finding about which tool should be used to make it. #### The Trace of the Return Measurements run on library loan records. A monthly report question has been chosen: how many distinct books were picked up within a given month? The query touches two columns — `pickup_date` for filtering, `book_id` for counting. ```sh rm -f base.db narrow.db covering.db cat > setup.sql <<'SQL' CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, registration_date TEXT NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO member (member_id, name, city, registration_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000) SELECT n, 'Member ' || n, CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir' WHEN 3 THEN 'Bursa' ELSE 'Konya' END, date('2015-01-01', '+' || (n % 3200) || ' days') FROM counter; INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 base.db < setup.sql cp base.db narrow.db cp base.db covering.db sqlite3 narrow.db 'CREATE INDEX loan_pickup ON loan(pickup_date);' sqlite3 covering.db 'CREATE INDEX loan_pickup_book ON loan(pickup_date, book_id);' echo "-- page count --" for db in base.db narrow.db covering.db; do printf '%-13s %s\n' "$db" "$(sqlite3 "$db" 'PRAGMA page_count;')" done for db in narrow.db covering.db; do printf '\n=== %s ===\n' "$db" sqlite3 "$db" <<'SQL' EXPLAIN QUERY PLAN SELECT count(DISTINCT book_id) FROM loan WHERE pickup_date BETWEEN '2021-03-01' AND '2021-03-31'; .stats vmstep SELECT count(DISTINCT book_id) FROM loan WHERE pickup_date BETWEEN '2021-03-01' AND '2021-03-31'; SQL done ``` ``` -- page count -- base.db 19095 narrow.db 28408 covering.db 30316 === narrow.db === QUERY PLAN |--USE TEMP B-TREE FOR count(DISTINCT) `--SEARCH loan USING INDEX loan_pickup (pickup_date>? AND pickup_date? AND pickup_date&1 | grep -E 'Page cache misses|Run Time' | tr '\n' ' ' .stats on .timer on SELECT count(DISTINCT book_id) FROM loan WHERE pickup_date BETWEEN '$start' AND '$end'; SQL echo done done ``` {/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ``` -- matching row counts -- 1 day|820 1 month|25441 1 year|299547 ### 2021-03-15 .. 2021-03-15 narrow.db Page cache misses: 874 Run Time: real 0.001 user 0.000000 sys 0.001613 covering.db Page cache misses: 8 Run Time: real 0.000 user 0.000000 sys 0.000180 ### 2021-03-01 .. 2021-03-31 narrow.db Page cache misses: 26960 Run Time: real 0.034 user 0.015724 sys 0.019003 covering.db Page cache misses: 146 Run Time: real 0.006 user 0.004852 sys 0.000948 ### 2021-01-01 .. 2021-12-31 narrow.db Page cache misses: 317372 Run Time: real 0.387 user 0.182111 sys 0.204835 covering.db Page cache misses: 1684 Run Time: real 0.068 user 0.066791 sys 0.001009 ``` The numbers show a single pattern. In the narrow index, page miss count tracks the number of matching rows almost one to one: 874 pages for 820 rows, 26,960 pages for 25,441 rows, 317,372 pages for 299,547 rows. In the covering index the same three counts are 8, 146, and 1,684. The source of the ratio is the physical storage layout. Because the index is sorted by date, matching entries sit next to each other, and one page read brings back hundreds of entries. The return to the table, on the other hand, means landing at **random** points in a structure laid out by `loan_id` order; two loan entries that sit side by side in the index are far apart in the table. Every row costs a separate page read, the same page gets read more than once, and the buffer pool cannot hold onto this access pattern. Time confirms it: in this environment, the yearly query ran at 0.387 seconds against 0.068 seconds, roughly a sixfold difference. Run times depend on the environment and change on another machine; what is stable is the page-miss ratio — in the yearly query, 317,372 / 1,684 ≈ 188. From here a rule of measurement discipline follows: when evaluating the effect of a plan change, the measure being used has to see the work that is actually changing. Step count measures a query's logic well; it does not measure disk access. #### The Limit of Covering A covering index is not free. Every column placed in it is repeated in each of the two million entries. The measurement below builds the same index at four different widths and compares the page count of each against the table itself. ```sh rm -f width.db sqlite3 width.db < setup.sql sqlite3 width.db <<'SQL' .mode column .headers on CREATE INDEX d1 ON loan(pickup_date); CREATE INDEX d2 ON loan(pickup_date, book_id); CREATE INDEX d3 ON loan(pickup_date, book_id, member_id); CREATE INDEX d4 ON loan(pickup_date, book_id, member_id, return_date); SELECT name AS object, count(*) AS pages FROM dbstat WHERE name IN ('loan','d1','d2','d3','d4') GROUP BY name ORDER BY pages; SQL ``` ``` object pages ------ ----- d1 9313 d2 11221 d3 13026 d4 17941 loan 17948 ``` The four-column index reaches 17,941 pages; the table itself is 17,948 pages. As every column a query needs gets added to the index, the index approaches becoming a sorted copy of the table. At that point covering is no longer an optimization but a second copy of the data: space doubles, every write goes to two structures at once, and the backup grows in size. The selection criterion follows from this. Columns worth covering are the ones that **appear in the query's result but are narrow relative to the rest of the table.** Adding the single identifier column needed for a count paid 1,908 pages and returned a 188-fold gain in page misses; adding a long text column reverses that arithmetic. The second criterion is the query's width. On a query filtering a single day, the gain went from 874 pages to 8 pages — a small saving in absolute terms. The same index prevented reading three hundred thousand pages on the yearly report. A covering index is worth building for queries that **match many rows and ask for few columns**; there is no need to build one for single-row lookups, because the return there is already a single page. #### Covering and Order Together A covering index's column list does two jobs at once, and the two can conflict. The leftmost prefix rule established in the previous lesson decides column order for filtering and sorting; covering asks only that the column **exist**, not where it sits. The two requirements are combined in one list: filtering and sorting columns go first, where their order matters, and columns added only because they appear in the result go last. The `(pickup_date, book_id)` index is exactly this — the date gives the entry point, the book identifier is only carried along. The reverse order, `(book_id, pickup_date)`, carries the same columns and occupies the same space, but gives no entry point for the date range. Some engines carry this distinction into their syntax and offer a notation that keeps carried-only columns outside the index key; such a column does not participate in the uniqueness check and takes no place in the ordering. The name and existence of this notation is engine-specific; the concept is the same — separating columns that are the key from columns that are only carried. #### Summary - A covering index is one that carries every column the query asks for; it shows up in plan output with the word `COVERING` and reports that the table was never returned to. - The cost of returning to the table does not show up in step count: on the monthly query, step count dropped by twelve percent while page misses fell from 26,960 to 146. - Page misses in a narrow index track the number of matching rows almost one to one, because the return to the table is random access; in a covering index the read proceeds in order within the index. - The gain grows with the number of matching rows: 874 pages against 8 on a single-day query, 317,372 pages against 1,684 on a yearly query. - As columns are added, the index approaches a copy of the table; the four-column index reached 17,941 pages against the table's own 17,948 pages. #### Next Step The three lessons so far treated the index as a pure gain and measured its cost only by the space it occupied at the moment it was built. An index does not stay where it was built: rows get deleted and updated, gaps accumulate in the index tree, the file grows, and every new index stretches the write path a notch further. The next lesson measures that side — how much an index bloats after deletion, what a rebuild recovers, and how much each index added to a table lengthens insert time. ### Index Maintenance Source: https://academia.sh/en/courses/database-administration/index-maintenance The gap that deletion and update leave in index pages, how index order determines whether that gap comes back, the difference between reindexing and a full rewrite, and how write cost grows with the number of indexes. The three lessons so far treated the index as a pure gain and measured its cost only by the space it occupied at the moment it was built. That moment is the smallest amount of space an index occupies over its lifetime. The bloat measured for a table in the Dead Row Cleanup lesson also happens to indexes, and for two reasons it is more stubborn there. When a row is deleted, that row's entry in every index dies with it. When a column is updated, that column's index entry does not stay in place — it is deleted from its old position and written at a new one, so a single update does two jobs inside the index. This lesson measures the trace those two jobs leave and tells apart what each maintenance tool actually recovers. #### Measuring the Bloat The run below follows five stages on library loan records with the same measurements: row count, the table's page count, the page count of each of two indexes separately, the database's total page count, and the file size. ```sh rm -f maintenance.db cat > setup.sql <<'SQL' CREATE TABLE member ( member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, registration_date TEXT NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO member (member_id, name, city, registration_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000) SELECT n, 'Member ' || n, CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir' WHEN 3 THEN 'Bursa' ELSE 'Konya' END, date('2015-01-01', '+' || (n % 3200) || ' days') FROM counter; INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 maintenance.db < setup.sql cat > measure.sql <<'EOF' SELECT (SELECT count(*) FROM loan) AS rows_, (SELECT count(*) FROM dbstat WHERE name='loan') AS loan_table, (SELECT count(*) FROM dbstat WHERE name='loan_member') AS member_index, (SELECT count(*) FROM dbstat WHERE name='loan_pickup') AS pickup_index, (SELECT page_count FROM pragma_page_count) AS total; EOF sqlite3 maintenance.db <<'SQL' .mode column .headers on CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_pickup ON loan(pickup_date); .print '--- setup ---' .read measure.sql .shell echo "file: $(wc -c < maintenance.db)" DELETE FROM loan WHERE pickup_date < '2020-01-01'; .print '--- old records deleted ---' .read measure.sql .shell echo "file: $(wc -c < maintenance.db)" UPDATE loan SET pickup_date = date(pickup_date, '+900 days') WHERE loan_id % 3 = 0; .print '--- one third of the dates updated ---' .read measure.sql .shell echo "file: $(wc -c < maintenance.db)" REINDEX; .print '--- after REINDEX ---' .read measure.sql .shell echo "file: $(wc -c < maintenance.db)" VACUUM; .print '--- after VACUUM ---' .read measure.sql .shell echo "file: $(wc -c < maintenance.db)" SQL ``` ``` --- setup --- rows_ loan_table member_index pickup_index total ------- ---------- ------------ ------------ ----- 2000000 17948 5738 9313 34146 file: 139862016 --- old records deleted --- rows_ loan_table member_index pickup_index total ------- ---------- ------------ ------------ ----- 1400893 17948 5738 6525 34146 file: 139862016 --- one third of the dates updated --- rows_ loan_table member_index pickup_index total ------- ---------- ------------ ------------ ----- 1400893 17948 5738 8102 34146 file: 139862016 --- after REINDEX --- rows_ loan_table member_index pickup_index total ------- ---------- ------------ ------------ ----- 1400893 17948 4022 6524 34146 file: 139862016 --- after VACUUM --- rows_ loan_table member_index pickup_index total ------- ---------- ------------ ------------ ----- 1400893 12565 4022 6524 24258 file: 99360768 ``` This five-row table carries every finding in the lesson. The sections below read it as three questions: why the two indexes behaved differently under deletion, what reindexing and a full rewrite each recover, and at which stage the total page count actually changed. #### Two Indexes, Two Different Behaviors The second stage is, on its own, the lesson's main finding. Roughly thirty percent of the rows were deleted. The pickup-date index fell from 9,313 pages to 6,525 pages — a drop close to the deleted proportion. The member index **stayed** at 5,738 pages instead; not a single page came back. Both indexes lost the same rows; what produces the difference is how the entries were ordered. The delete condition was a date range, and in the date index that range's entries sit next to each other: whole pages emptied out and moved to the free list. In the member index, the same rows' entries are scattered across one hundred twenty thousand members; no page emptied out completely, they all emptied out partially. A page with empty room inside it does not give that room back. The rule is the index-side counterpart of the rule established for tables in the Dead Row Cleanup lesson: **if the delete criterion overlaps with the index order, the space comes back; if it does not, it stays.** The same delete operation produces a saving in one index and dead space in another. The third stage shows the update's second job inside an index. A third of the rows had their date pushed forward; the table's page count did not change, while the pickup-date index rose from 6,525 to 8,102 pages. No row was deleted, no row was added — only index entries moved from their old position to a new one, and the gap the old positions left did not close. The member index did not change at all at this stage (5,738), because the updated column is not its key. #### Reindexing and a Full Rewrite The fourth stage runs `REINDEX`: the indexes are rebuilt from scratch. The member index falls from 5,738 to 4,022 pages — a drop that matches the live-row ratio (1,400,893 / 2,000,000 ≈ 0.70) exactly, because no dead space is left. The pickup-date index falls from 8,102 to 6,524, that is, back to close to its post-delete value. What deserves attention is the total page count of that same row: 34,146, unchanged. The file size did not change either. Reindexing freed the gap **inside the database**; it did not give it back to the operating system. That freed space is available for new data, but it does not lower disk usage and does not shorten backup time. The fifth stage runs a full rewrite. The total falls from 34,146 to 24,258 pages, the file falls from 139,862,016 bytes to 99,360,768 bytes. The table also shrinks (17,948 → 12,565), because a full rewrite copies the table and its indexes together into a new file. The distinction between the two tools fits in one sentence: **reindexing repairs only the indexes and leaves the gap inside the database; a full rewrite repairs everything and gives the gap back to the file system.** Their costs follow the same distinction. Reindexing locks only the affected index and scales with that index's size, independent of table size. A full rewrite locks the table, needs temporary space equal to the table's size, and reads all the data. On a live system the first fits inside a maintenance window; the second usually requires a planned outage. Some engines offer a way to reindex without locking the table against writes: a new index is built in the background and swapped in once ready. The name and limits of this path are engine-specific; the concept is that the index is built as a **duplicate copy** and then swapped in, at the cost of both indexes temporarily occupying space at once. #### Write Cost Grows With the Number of Indexes The other side of maintenance is the cost an index's mere existence adds to every write. The run below inserts the same two hundred thousand rows into five copies whose index count rises from zero to four. ```sh rm -f template.db idx0.db idx1.db idx2.db idx3.db idx4.db sqlite3 template.db < setup.sql for k in 0 1 2 3 4; do cp template.db "idx$k.db"; done sqlite3 idx1.db 'CREATE INDEX i1 ON loan(member_id);' sqlite3 idx2.db 'CREATE INDEX i1 ON loan(member_id); CREATE INDEX i2 ON loan(pickup_date);' sqlite3 idx3.db 'CREATE INDEX i1 ON loan(member_id); CREATE INDEX i2 ON loan(pickup_date); CREATE INDEX i3 ON loan(book_id);' sqlite3 idx4.db 'CREATE INDEX i1 ON loan(member_id); CREATE INDEX i2 ON loan(pickup_date); CREATE INDEX i3 ON loan(book_id); CREATE INDEX i4 ON loan(return_date);' for k in 0 1 2 3 4; do printf 'index count %s: ' "$k" sqlite3 "idx$k.db" <<'SQL' | grep 'Run Time' .timer on INSERT INTO loan (book_id, member_id, pickup_date, return_date) WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000) SELECT 1 + ((n*7) % 200000), 1 + ((n*13) % 120000), date('2024-01-01','+'||(n%300)||' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2024-01-20','+'||(n%300)||' days') END FROM s; SQL done ``` {/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ``` index count 0: Run Time: real 0.072 user 0.069757 sys 0.002221 index count 1: Run Time: real 0.267 user 0.164417 sys 0.103081 index count 2: Run Time: real 0.542 user 0.286158 sys 0.256441 index count 3: Run Time: real 0.678 user 0.369099 sys 0.308876 index count 4: Run Time: real 0.951 user 0.514862 sys 0.436207 ``` The increase is close to linear: in this run each index added roughly 0.22 seconds to insert time, and the four-index copy took more than thirteen times as long as the index-free copy. Run times depend on the environment; what is stable is that cost grows in proportion to the number of indexes. The reason is direct: for every new row, a separate key is written into every index tree. The arithmetic for update is different — only indexes **containing the updated column** pay a cost. The run below performs the same update on two copies that both carry two indexes; the only difference is that in one copy the updated column is indexed. ```sh rm -f update_outside.db update_inside.db cp template.db update_outside.db cp template.db update_inside.db sqlite3 update_outside.db 'CREATE INDEX i1 ON loan(member_id); CREATE INDEX i2 ON loan(book_id);' sqlite3 update_inside.db 'CREATE INDEX i1 ON loan(member_id); CREATE INDEX i2 ON loan(pickup_date);' for db in update_outside.db update_inside.db; do printf '%-16s ' "$db" sqlite3 "$db" <<'SQL' | grep 'Run Time' .timer on UPDATE loan SET pickup_date = date(pickup_date, '+1 day') WHERE loan_id % 5 = 0; SQL done ``` {/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ``` update_outside.db Run Time: real 0.130 user 0.094091 sys 0.036274 update_inside.db Run Time: real 1.371 user 0.620847 sys 0.750057 ``` Same row count, same number of indexes, and in this run more than a tenfold difference in time. The practical consequence is this: indexing a frequently updated column does not cost the same as indexing a column that is never updated. Choosing an index means looking not only at how a column is queried, but also at how often it changes. #### Indexes Paid For but Never Collected Maintenance's last job is finding indexes that should not exist. The pattern established in the System Catalog lesson is used a second time here: an index whose column list is another index's leftmost prefix is, by the leftmost prefix rule, never required on its own for any query. ```sh rm -f audit.db cp template.db audit.db sqlite3 audit.db <<'SQL' .mode column .headers on CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_member_pickup ON loan(member_id, pickup_date); CREATE INDEX loan_pickup ON loan(pickup_date); CREATE INDEX loan_book ON loan(book_id); -- Indexes whose column list is another index's prefix: they waste space for nothing. WITH d AS ( SELECT m.name AS tbl, il.name AS idx, (SELECT group_concat(ii.name, ',') FROM pragma_index_info(il.name) ii) AS cols FROM sqlite_schema m JOIN pragma_index_list(m.name) il WHERE m.type = 'table' AND il.origin = 'c' ) SELECT a.idx AS redundant, a.cols AS its_columns, b.idx AS covered_by, (SELECT count(*) FROM dbstat WHERE name = a.idx) AS pages FROM d a JOIN d b ON a.tbl = b.tbl AND a.idx <> b.idx WHERE b.cols LIKE a.cols || ',%'; SQL ``` ``` redundant its_columns covered_by pages ----------- ----------- ------------------ ----- loan_member member_id loan_member_pickup 5738 ``` The audit produced a single finding, and it also reported the cost: the single-column index on `member_id` is redundant because it is the leftmost prefix of the `(member_id, pickup_date)` index, and it occupies 5,738 pages. Removing it recovers both that space and the share paid on every write. There is a second category this audit cannot find: indexes whose column list is unique yet that no query ever uses. Those are found not from the catalog but from the engine's own usage counters; the name and existence of those counters is engine-specific. The common method is to reset the counter, wait through one work cycle, and add any index that never incremented to a list of candidates. The decision is not made right away — a report that runs once a month does not show up in a one-week observation. #### Summary - Deletion and update leave gaps in indexes; an update does two jobs inside an index, deleting from the old position and writing at the new one. - Whether the space comes back is decided by index order: on a delete by date range, the date index fell from 9,313 to 6,525 pages, while the member index stayed at 5,738 on the same delete. - Reindexing repairs the indexes and leaves the gap inside the database (the total stayed at 34,146 pages); a full rewrite also shrinks the file (24,258 pages, 99,360,768 bytes). - Insert cost grows in proportion to the number of indexes; in the measurement each index added roughly 0.22 seconds in the sample run, and the four-index copy took more than thirteen times as long as the index-free one. - On update, only indexes containing the updated column pay a cost: with the same index count the difference came out more than tenfold; the leftmost prefix audit found one redundant index occupying 5,738 pages. #### Next Step This lesson ran into the same fact twice: when the delete criterion overlapped with index order, space came back on its own; when it did not, maintenance was required. The table-side counterpart of that same fact was measured in the Dead Row Cleanup lesson, and a question was left open there: instead of deleting old data, can old data be kept **in a separate place**? The next lesson answers that question. When a table is split into partitions by range, list, or hash, a query touching only the relevant partition will be shown by the page-miss counts, deleting old data will turn into dropping a partition, and the distribution of the partition key will be measured by number. ### Table Partitioning Source: https://academia.sh/en/courses/database-administration/table-partitioning Splitting a table into ranges, lists, and hashes; showing by measurement that a query touches only the relevant partition; dropping old data instead of deleting it; and computing the distribution of a partition key. The Index Maintenance lesson left a question open. Deleting old loan records left dead space everywhere it failed to overlap with index order, and recovering that space required rewriting the table from scratch. The source of the problem is not the delete itself but the fact that the data being deleted was mixed in among the live data. **Partitioning (partitioning)** prevents this mixing from the outset: a table that is logically a single table is stored physically as more than one piece. Each piece is called a **partition (partition)**, and which partition a row falls into is decided by the **partition key (partition key)**. This lesson sets up three forms of partitioning, measures their gain, and also shows where the gain does not come from. #### Range Partitioning The most common form is **range partitioning (range partitioning)**: value ranges of the partition key correspond to partitions. In library records the natural key is the pickup date, and the natural range is the year. Some engines offer partitioning through their syntax; where they do not, the same structure is built with separate tables and a union view. The run below follows the second path. Each partition table states its own range with a value check, and a view gathers the partitions under a single name. ```sh rm -f unpartitioned.db partitioned.db partition.sql cat > setup.sql <<'SQL' CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date) WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000) SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000), date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'), CASE WHEN n % 9 = 0 THEN NULL ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END FROM counter; SQL sqlite3 unpartitioned.db < setup.sql sqlite3 unpartitioned.db 'CREATE INDEX loan_pickup ON loan(pickup_date);' # One partition table per year, a value check, and a view gathering the partitions. { echo "ATTACH 'unpartitioned.db' AS source;" for y in 2018 2019 2020 2021 2022 2023 2024; do s=$((y + 1)) cat <= '$y-01-01' AND pickup_date < '$s-01-01'), return_date TEXT); INSERT INTO loan_$y SELECT * FROM source.loan WHERE pickup_date >= '$y-01-01' AND pickup_date < '$s-01-01'; CREATE INDEX loan_${y}_pickup ON loan_$y(pickup_date); EOF done echo "CREATE VIEW loan AS" echo " SELECT * FROM loan_2018 UNION ALL SELECT * FROM loan_2019 UNION ALL" echo " SELECT * FROM loan_2020 UNION ALL SELECT * FROM loan_2021 UNION ALL" echo " SELECT * FROM loan_2022 UNION ALL SELECT * FROM loan_2023 UNION ALL" echo " SELECT * FROM loan_2024;" } > partition.sql sqlite3 partitioned.db < partition.sql sqlite3 partitioned.db <<'SQL' .mode column .headers on SELECT count(*) AS view_rows FROM loan; SELECT name AS partition_, count(*) AS pages FROM dbstat WHERE name LIKE 'loan\_20__' ESCAPE '\' GROUP BY name ORDER BY name; SQL ``` ``` view_rows --------- 2000000 partition_ pages ---------- ----- loan_2018 2967 loan_2019 2954 loan_2020 2952 loan_2021 2951 loan_2022 2947 loan_2023 2957 loan_2024 1984 ``` The view returns all two million rows: from the application's point of view the table is still a single one. Underneath it there are seven separate tables, seven separate indexes, and seven separate trees of roughly three thousand pages each. #### Touching Only the Relevant Partition Partitioning's first claim is this: a query that filters on the partition key never reads the other partitions at all. This claim is **invisible** in plan output — the plan lists all seven partitions. ```sh sqlite3 partitioned.db "EXPLAIN QUERY PLAN SELECT count(*) FROM loan WHERE pickup_date BETWEEN '2021-03-01' AND '2021-03-31';" for target in "partitioned.db|loan|partitioned view" \ "partitioned.db|loan_2021|direct 2021 partition" \ "unpartitioned.db|loan|unpartitioned single table"; do db=${target%%|*}; rest=${target#*|}; tbl=${rest%%|*}; label=${rest#*|} printf '%-24s ' "$label" sqlite3 "$db" <&1 | grep -E 'Page cache misses|Run Time' | tr '\n' ' ' .stats on .timer on SELECT count(*) FROM $tbl WHERE pickup_date BETWEEN '2021-03-01' AND '2021-03-31'; SQL echo done ``` {/* mask: (?<=real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ``` QUERY PLAN |--CO-ROUTINE loan | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | `--SEARCH loan_2018 USING INDEX loan_2018_pickup (pickup_date>? AND pickup_date? AND pickup_date? AND pickup_date? AND pickup_date? AND pickup_date? AND pickup_date? AND pickup_date distribution.mjs <<'JS' // Model: distribution of loan records across partitions. Not a real database's // partitioner; what is measured is the relationship between key choice and partition fill. // Loans per member are distributed close to a power law: a few members hold many loans. const MEMBERS = 120000, RECORDS = 2000000; const cities = [['Istanbul', 0.42], ['Ankara', 0.24], ['Izmir', 0.16], ['Bursa', 0.11], ['Konya', 0.07]]; function members() { const weight = []; let total = 0; for (let i = 1; i <= MEMBERS; i++) { const a = 1 / Math.pow(i, 0.8); weight.push(a); total += a; } return weight.map((a, i) => { let threshold = ((i * 7919) % 100) / 100, k = 0, cumulative = 0; while (k < cities.length - 1 && (cumulative += cities[k][1]) <= threshold) k++; return { member_id: i + 1, loans: Math.max(1, Math.round(a / total * RECORDS)), city: cities[k][0] }; }); } const M = 2654435761n; const hashLow = (x) => Number((BigInt(x) * M) % 4294967296n); // low bits const hashHigh = (x) => Number(((BigInt(x) * M) % 4294967296n) >> 20n); // high bits function report(label, bucket) { const busiest = Math.max(...bucket), quietest = Math.min(...bucket); const empty = bucket.filter((s) => s === 0).length; const ratio = quietest === 0 ? 'partitions empty: ' + empty : (busiest / quietest).toFixed(2) + 'x'; console.log(label.padEnd(24), 'partitions', String(bucket.length).padStart(2), '| busiest', String(busiest).padStart(8), '| quietest', String(quietest).padStart(8), '| ratio', ratio); } function distribute(member, P, key) { const bucket = new Array(P).fill(0); for (const m of member) bucket[key(m.member_id) % P] += m.loans; return bucket; } const member = members(); console.log('total loans in model:', member.reduce((t, m) => t + m.loans, 0)); console.log('— hash partitioning, near-power-law key —'); for (const P of [8, 16, 64]) { report('member_id % P', distribute(member, P, (x) => x)); report('hash_high % P', distribute(member, P, hashHigh)); } console.log('— structured key: membership number a multiple of four —'); const fours = member.map((m) => ({ ...m, member_id: m.member_id * 4 })); report('member_id % 8', distribute(fours, 8, (x) => x)); report('hash_low % 8', distribute(fours, 8, hashLow)); report('hash_high % 8', distribute(fours, 8, hashHigh)); console.log('— list partitioning: city —'); const listBucket = cities.map(([name]) => member.filter((m) => m.city === name).reduce((t, m) => t + m.loans, 0)); report('city', listBucket); console.log(cities.map(([name], i) => `${name}=${listBucket[i]}`).join(' ')); JS node distribution.mjs ``` ``` total loans in model: 2001046 — hash partitioning, near-power-law key — member_id % P partitions 8 | busiest 278850 | quietest 238066 | ratio 1.17x hash_high % P partitions 8 | busiest 273855 | quietest 233420 | ratio 1.17x member_id % P partitions 16 | busiest 158156 | quietest 116396 | ratio 1.36x hash_high % P partitions 16 | busiest 154631 | quietest 114336 | ratio 1.35x member_id % P partitions 64 | busiest 69604 | quietest 27466 | ratio 2.53x hash_high % P partitions 64 | busiest 69318 | quietest 26434 | ratio 2.62x — structured key: membership number a multiple of four — member_id % 8 partitions 8 | busiest 1014448 | quietest 0 | ratio partitions empty: 6 hash_low % 8 partitions 8 | busiest 1014448 | quietest 0 | ratio partitions empty: 6 hash_high % 8 partitions 8 | busiest 285482 | quietest 236957 | ratio 1.20x — list partitioning: city — city partitions 5 | busiest 876922 | quietest 139966 | ratio 6.27x Istanbul=876922 Ankara=464332 Izmir=312596 Bursa=207230 Konya=139966 ``` Three findings come out of this. First: hash partitioning balances **keys**, not rows. At eight partitions the imbalance ratio is 1.17; at sixty-four partitions it is 2.53. As partitions shrink, a single very active member's weight grows relative to the whole partition, and the hash function's quality does not change that — the top two rows coming out nearly identical shows this. Second: the hash function's quality becomes decisive when **the key is structured**. With membership numbers all multiples of four, direct division left six of the eight partitions empty. The low bits of the multiplicative hash gave the same result; the reason is arithmetic — multiplying by a single factor and then dividing by a power of two preserves the low bits. Taking the high bits instead brought the distribution back down to a 1.20 ratio. The rule: **the low bits of a hash value are not used to choose a partition.** Third: list partitioning never aims for balance at all. Records split by city came out at a 6.27 ratio, and this is not a flaw but reality itself. List partitioning is chosen when partitions need to be managed separately — when one city's data needs to be stored separately, backed up separately, or deleted separately, imbalance is accepted. #### Summary - Partitioning stores a table that is logically single across more than one physical piece according to a partition key; range, list, and hash forms differ in how the key is turned into a partition. - A query filtering on the partition key does not read the data of irrelevant partitions: in this environment, 139 pages were read through the view against 121 pages read from a single partition directly; the difference is six empty index probes. - With an index already in place, partitioning did not buy read performance; the unpartitioned table answered the same query with 123 pages. - The real gain is removing data as a whole: in this environment, deleting a year and recovering the gap took about 0.71 seconds and freed 1,393 pages; dropping the partition freed 4,363 pages in about 0.016 seconds. - Hash partitioning balances keys, not rows; imbalance grew as partition count rose (1.17 → 2.53). On structured keys, the low bits of a hash value cannot be used to choose a partition. #### Next Step Every partition in this lesson lived inside the same database. Distributing partitions across separate machines takes the same idea one step further and changes its nature: a cross-partition query now travels over a network, a transaction can span more than one machine, and changing the partition count forces data to move. The next lesson takes on this pattern — what choosing a shard key imposes on the application, the cost of a cross-shard query, and the amount of data moved during rebalancing, all counted. ### Sharding Patterns Source: https://academia.sh/en/courses/database-administration/sharding-patterns Distributing partitions across separate machines, the limits a shard key imposes on the application, the queueing delay of a cross-shard query, the join and uniqueness problem across shards, and the amount of data moved during rebalancing. The previous lesson split a table into partitions, and every partition lived inside the same database. Distributing partitions across separate machines is one step beyond the same idea, and at that step its nature changes. **Sharding (sharding)** is the horizontal splitting of data across more than one independent database instance. Every shard carries its own processes, its own buffer pool, its own log, and its own lock manager. Every structure established in the Engine Architecture topic exists once per shard, and the shards are not aware of each other's existence. This is the gain: write load, memory, and disk are genuinely divided. This is also the cost: three abilities that are free within a single engine for one table — joining, uniqueness, and transactions — stop being free across shards. This lesson counts those three costs. Measurements are models built with `node`; they are not a real distributed database. What the model can count is relationships — which choice multiplies which cost by how much — not absolute values. #### What the Shard Key Imposes on the Application The **shard key (shard key)** is the column that decides which shard a row falls into. The library data has two candidates, and the choice determines which query stays cheap. In a setup sharded by member ID, all of a member's loan records sit in a single shard. The question "this member's open loans" goes to a single shard; the query is no different from one against a single-machine setup. In the same setup, the answer to "who has borrowed this book" is scattered across every shard. In a setup sharded by book ID, the relationship reverses. Which choice is correct does not come from the data but from **the workload's question distribution**: whichever key the most frequent, lowest-latency question filters on is the shard key. This decision does not resemble a reversible index decision on a single-machine setup. An index can be dropped and rebuilt; changing the shard key means moving all of the data. This is the first thing sharding imposes on the application: **query shape determines data layout, and changing it later is expensive.** #### The Cross-Shard Query A query that does not filter on the shard key is sent to every shard, a partial result comes back from each, and a coordinator merges them. This pattern is called **scatter-gather (scatter-gather)**, and its cost runs against intuition: even if the total work performed stays the same, response time gets worse, because the query has to wait for **the slowest shard**. ```sh cat > cross_shard.mjs <<'JS' // Model: for loan records distributed across 8 shards, the number of shards touched by // each query type and the response time. Not a real distributed database; what is // counted is the relationship between shards touched and queueing delay. const SHARDS = 8, QUERIES = 20000; // Reproducible pseudo-random generator (linear congruential). let state = 20240728; const random = () => (state = (state * 1103515245 + 12345) % 2147483648) / 2147483648; // A single shard's response time: 2 ms base plus weighted queueing. One percent chance of being slow. function shardTime() { const r = random(); return r < 0.99 ? 2 + 3 * r : 40 + 60 * r; } function measure(shardsTouched) { const times = []; for (let i = 0; i < QUERIES; i++) { let slowest = 0; for (let p = 0; p < shardsTouched; p++) slowest = Math.max(slowest, shardTime()); times.push(slowest + 1); // 1 ms coordination share } times.sort((a, b) => a - b); const pct = (q) => times[Math.floor(QUERIES * q)]; return { median: pct(0.5), p95: pct(0.95), p99: pct(0.99), worst: times[QUERIES - 1] }; } console.log('shards touched median p95 p99 worst'); for (const d of [1, 2, 4, 8]) { const s = measure(d); console.log(String(d).padStart(14), s.median.toFixed(1).padStart(9), s.p95.toFixed(1).padStart(8), s.p99.toFixed(1).padStart(8), s.worst.toFixed(1).padStart(9)); } console.log('\n— query mix: does the shard key apply —'); const mix = [ ['a members loans (shard key applies)', 1], ['a books loan history (no key)', SHARDS], ['monthly report (no key)', SHARDS], ]; for (const [label, d] of mix) { const s = measure(d); console.log(label.padEnd(38), 'shards', String(d).padStart(2), '| median', s.median.toFixed(1).padStart(5), '| p99', s.p99.toFixed(1).padStart(6), 'ms'); } JS node cross_shard.mjs ``` ``` shards touched median p95 p99 worst 1 4.5 5.9 6.0 101.0 2 5.1 5.9 100.6 101.0 4 5.5 6.0 100.8 101.0 8 5.8 100.5 100.9 101.0 — query mix: does the shard key apply — a members loans (shard key applies) shards 1 | median 4.5 | p99 6.0 ms a books loan history (no key) shards 8 | median 5.8 | p99 100.9 ms monthly report (no key) shards 8 | median 5.8 | p99 100.9 ms ``` The model assumes every shard has a one-percent chance of responding slowly. Median time is barely touched by this assumption: 4.5 ms at one shard, 5.8 ms at eight shards. The tail collapses instead. The 99th percentile is 6.0 ms at one shard and 100.9 ms at eight shards — a seventeenfold increase. The arithmetic behind it is direct. If a single shard's chance of being slow is one percent, the chance that **at least one** of eight shards is slow is $1 - 0.99^8 \approx 0.077$, that is, 7.7 percent. Scatter-gather turns every shard's rare flaw into the query's frequent flaw. From this comes the most commonly misunderstood side of sharding: sharding grows **throughput**, it does not shrink a single query's latency. A measurement that looks only at average times never sees this degradation; seeing it requires looking at the tail percentiles. #### Joining, Uniqueness, and Transactions Three abilities change in nature at the shard boundary. **Joining.** Loan records sharded by member ID can be joined, within a shard, with a member table sharded by the same key; the two tables' related rows sit on the same machine. This is called **co-location (co-location)**, and between two large tables it is the only way for a join to stay cheap in a sharded design. The book table cannot be co-located with loan records, because a loan record cannot belong to two different keys at once. The common solution is to **copy** small, rarely changing tables to every shard; in the library example the branch list is such a table. **Uniqueness.** In a single engine, a uniqueness constraint is enforced with an index. No such index spans shards: each shard sees only its own rows, so uniqueness can only be given **within a shard**. A field requiring global uniqueness — a member's email address, for instance — either becomes the shard key or is kept in a separate lookup table. Auto-incrementing identifiers fail for the same reason; in sharded setups, identifiers are drawn not from a central source but from a generator that either embeds the shard number or is wide enough that collision probability is negligible. **Transactions.** The atomicity defined in the ACID Properties lesson relies on a single engine's log. A transaction writing to two shards at once writes to two separate logs, and getting the two to commit together requires a separate protocol: the coordinator first sends a prepare command to every shard, and commits only if it receives approval from all of them. This protocol's cost is not the extra round trips but the possibility of **hanging** — if the coordinator fails between prepare and commit, the shards hold their locks and wait. This is why the golden rule of a sharded design is: a transaction must stay within a single shard. If it cannot, the design has chosen either the wrong shard key or the wrong transaction boundary. #### Rebalancing Shard count does not stay fixed. When a new shard is added, the key-to-shard mapping changes, and every changed mapping means data has to move. How much moves depends on how the mapping was built, and this can be computed. ```sh cat > rebalance.mjs <<'JS' // Model: the fraction of rows that must move when the shard count changes. Not a real // rebalancing procedure; what is counted is only the mapping's effect on move cost. const MEMBERS = 120000, BUCKETS = 4096; const M = 2654435761n; const hash = (x) => Number(((BigInt(x) * M) % 4294967296n) >> 8n); // Loans per member: a few members hold many loans. const member = []; { let total = 0; const weight = []; for (let i = 1; i <= MEMBERS; i++) { const a = 1 / Math.pow(i, 0.8); weight.push(a); total += a; } for (let i = 0; i < MEMBERS; i++) member.push({ id: i + 1, loans: Math.max(1, Math.round(weight[i] / total * 2000000)) }); } const totalRows = member.reduce((t, m) => t + m.loans, 0); function modMove(oldCount, newCount) { let moved = 0; for (const m of member) if (hash(m.id) % oldCount !== hash(m.id) % newCount) moved += m.loans; return moved; } // Virtual bucket: the key always falls into a fixed number of buckets, and buckets are assigned to shards. function bucketAssignment(P) { const assignment = new Array(BUCKETS); for (let b = 0; b < BUCKETS; b++) assignment[b] = b % P; return assignment; } function bucketMove(oldCount, newCount) { const oldAssignment = bucketAssignment(oldCount); const target = Math.floor(BUCKETS / newCount); const count = new Array(newCount).fill(0); const newAssignment = new Array(BUCKETS).fill(-1); for (let b = 0; b < BUCKETS; b++) { // buckets that can stay in place come first const p = oldAssignment[b]; if (p < newCount && count[p] < target) { newAssignment[b] = p; count[p]++; } } let p = 0; for (let b = 0; b < BUCKETS; b++) { // remaining buckets go to the shards below target if (newAssignment[b] !== -1) continue; while (p < newCount - 1 && count[p] >= target) p++; newAssignment[b] = p; count[p]++; } let moved = 0; for (const m of member) { const b = hash(m.id) % BUCKETS; if (oldAssignment[b] !== newAssignment[b]) moved += m.loans; } return moved; } console.log('total loans in model:', totalRows); console.log('transition direct modulo virtual bucket'); for (const [o, n] of [[8, 9], [8, 12], [8, 16]]) { const a = modMove(o, n), b = bucketMove(o, n); const pct = (x) => (100 * x / totalRows).toFixed(1) + '%'; console.log(`${o} → ${n}`.padEnd(12), (a.toLocaleString('en-US') + ' (' + pct(a) + ')').padStart(21), (b.toLocaleString('en-US') + ' (' + pct(b) + ')').padStart(18)); } JS node rebalance.mjs ``` ``` total loans in model: 2001046 transition direct modulo virtual bucket 8 → 9 1,793,865 (89.6%) 228,270 (11.4%) 8 → 12 1,337,553 (66.8%) 675,360 (33.8%) 8 → 16 1,006,912 (50.3%) 982,390 (49.1%) ``` Direct modulo — hashing the key and dividing by the shard count, then taking the remainder — moved 89.6 percent of records when going from eight to nine shards. Adding a single shard requires nearly all the data to shift, because when the divisor changes, the remainder changes for nearly every key too. The **virtual bucket (virtual bucket)** approach inserts a layer in between: the key is first reduced to a fixed number of buckets (here, 4,096), and buckets are assigned to shards. When shard count changes, the key-to-bucket mapping never changes; only the bucket-to-shard assignment is updated, and only the data of buckets handed to the new shard moves. Going from eight to nine, the moved ratio came out at 11.4 percent — very close to the theoretical lower bound of 1/9 ≈ 11.1 percent. The third row carries a warning. Going from eight to sixteen shards, the two methods come out equal (50.3 percent versus 49.1 percent), because doubling the shard count is direct modulo's luckiest case: every shard splits exactly in two. This makes doubling a valid growth strategy — but only for as long as it can keep doubling. When capacity planning is forced into that pattern, the cost is provisioning capacity that is not yet needed, paid for in advance. The moved ratio itself is not the final cost either. During the move, two copies live side by side for a while, reads must be answerable from either location, and writes to buckets in flight either have to be paused or written to both places at once. This is why rebalancing is a capability measured not by shard count but by whether the move can be carried out **without interruption**. #### When Sharding Is Not Yet Needed Sharding carries every cost counted above into the application's code. This is why the last question is whether the ways of avoiding it have run out. The three approaches measured in this course usually arrive first: the right indexes cut read cost by orders of magnitude, partitioning turned removing old data into a fixed cost, and read load can be spread out through replication. The one factor that truly makes sharding necessary is **write** load, and only once it exceeds a single engine's log and checkpoint capacity. That is the measure the decision is made by — not the measure of "the table is big." #### Summary - Sharding distributes data across independent database instances; the shard key decides which query stays within a single shard, and changing it later means moving all the data. - The scatter-gather pattern barely affects median time but wrecks the tail: in the model, the 99th percentile that was 6.0 ms at one shard became 100.9 ms at eight shards. - Joining between large tables stays cheap only when they are co-located; uniqueness can only be enforced within a shard; a transaction writing to two shards needs a separate protocol and carries the risk of hanging. - With direct modulo, going from eight to nine shards moved 89.6 percent of records; with a virtual bucket layer, the same transition dropped to 11.4 percent. - The factor that makes sharding necessary is write load exceeding a single engine's capacity; indexing, partitioning, and replication remain the cheaper paths up to that threshold. #### Next Step This topic has completed how data is organized and how it is read: index types, column order, covering, maintenance, partitioning, and sharding. Together with the Engine Architecture topic, where data sits and how it is found are now established — the system is operational. Being operational and being administered are different things. The next topic opens with that distinction, and its first question is the oldest one: who can access this data, and what can they do? The application at the loan desk needs to open a loan record; it does not need to delete a member's record. How that distinction is built at the privilege level is the next lesson's subject. ## Operations Roles and row-level security, backup types and point-in-time recovery, backup validation, physical and logical replication, failover, connection poolers, monitoring, bulk loading, and version upgrades. ### Roles and Privileges Source: https://academia.sh/en/courses/database-administration/roles-and-privileges Applying the principle of least privilege in the database: role-based access control, counting the excess privilege produced by role inheritance, a second line of defense with a read-only connection, and separating the application account from the maintenance account. The course so far has established where data sits and by which path it is read: page layout, the log, index types, partitioning, sharding patterns. Once layout and access paths are in place, the system becomes operational — but it does not yet count as operated. Operations is what keeps a running system secure, recoverable, and accessible over time. This topic's first question is the oldest one: who can access this data, and what can they do? The library loan records example makes it concrete. The circulation desk application needs to open loan records; it does not need to delete member records. The reporting tool needs to read loan counts; it does not need to change a row. Without these distinctions, the system can run for years without incident — until a single faulty query or one compromised application key charges, all at once, the price of the distinction that was never made. #### Authentication and Authorization Two questions get tangled together. **Authentication** is the question of who you are; it was introduced in the context of HTTPS in the How the Internet Works course. **Authorization** is the question of what you can do, once your identity is settled. Database engines keep the two in separate layers: identity is authenticated when the connection is established, and privilege is checked on every statement. Object-level privileges were introduced with the `GRANT` and `REVOKE` statements in the SQL Fundamentals course. That lesson taught the language; this one teaches how the same statements are organized in operations. The difference is scale: granting privileges one at a time works for ten tables and two users, and it fails at a hundred tables and thirty accounts. The structure that scales is the **role**. #### Role-Based Access Control **Role-based access control** ties privileges to duties, not people. The duty is defined first — reader, circulation clerk, cataloger — and privileges are granted to it. A person's account or an application account is then assigned to the role. In operations, this means that when an employee changes duties, or an application is retired, the only thing that has to change is the role assignment. If privileges were written onto accounts one at a time, closing off a departing employee's access becomes a search, and that search commonly comes up incomplete. The second property of roles is **role inheritance** — one role encompassing the privileges of another. The cataloger role inherits from the reader role, so a cataloger can do everything a reader can. Inheritance shortens the writing, but it has a side effect: the effective privilege set is no longer visible where it was written, and has to be computed. Seeing what an account can actually reach requires taking the closure of the inheritance chain. #### Computing Excess Privilege The script below models a privilege chart: roles, the roles each role inherits from, the role assignments of each account, and the privileges each account actually needs to do its job. The script follows inheritance to produce the effective privilege set and subtracts out what is more than required. This is a model, not the privilege chart of a real engine. What is modeled is the closure of inheritance and the difference calculation — these two steps are the same regardless of which engine is in use. ```bash cat > privileges.mjs <<'EOF' // Role -> directly granted privileges. Privilege format: "action:object". const ROLE_PRIVILEGES = { reader: ["select:book", "select:branch"], clerk: ["select:member", "select:loan", "insert:loan", "update:loan"], cataloger: ["insert:book", "update:book", "delete:book"], manager: ["delete:member", "delete:loan", "alter:*"], }; // Role -> roles it inherits from. const ROLE_INHERITANCE = { reader: [], clerk: ["reader"], cataloger: ["reader"], manager: ["clerk", "cataloger"], }; // Account -> assigned roles. const ACCOUNT_ROLES = { circulation_desk: ["clerk"], cataloging_team: ["cataloger"], reporting_tool: ["clerk"], maintenance_account: ["manager"], }; // Privileges the account actually needs to do its job. const REQUIRED = { circulation_desk: ["select:book", "select:member", "select:loan", "insert:loan", "update:loan"], cataloging_team: ["select:book", "insert:book", "update:book"], reporting_tool: ["select:book", "select:loan"], maintenance_account: ["select:book", "select:member", "select:loan", "alter:*"], }; function effectivePrivileges(role, seen = new Set()) { if (seen.has(role)) return new Set(); // guard against inheritance cycles seen.add(role); const set = new Set(ROLE_PRIVILEGES[role] ?? []); for (const parent of ROLE_INHERITANCE[role] ?? []) { for (const priv of effectivePrivileges(parent, seen)) set.add(priv); } return set; } function accountPrivileges(account) { const set = new Set(); for (const role of ACCOUNT_ROLES[account]) { for (const priv of effectivePrivileges(role)) set.add(priv); } return set; } const isWriter = (priv) => !priv.startsWith("select:"); console.log("account | effective | required | extra | extra write privileges"); console.log("---------------------|-----------|----------|-------|------------------------"); for (const account of Object.keys(ACCOUNT_ROLES)) { const effective = accountPrivileges(account); const required = new Set(REQUIRED[account]); const extra = [...effective].filter((i) => !required.has(i)).sort(); console.log( account.padEnd(20) + " | " + String(effective.size).padStart(9) + " | " + String(required.size).padStart(8) + " | " + String(extra.length).padStart(5) + " | " + extra.filter(isWriter).join(", ") ); } EOF node privileges.mjs ``` ```text account | effective | required | extra | extra write privileges ---------------------|-----------|----------|-------|------------------------ circulation_desk | 6 | 5 | 1 | cataloging_team | 5 | 3 | 2 | delete:book reporting_tool | 6 | 2 | 4 | insert:loan, update:loan maintenance_account | 12 | 4 | 8 | delete:book, delete:loan, delete:member, insert:book, insert:loan, update:book, update:loan ``` The table shows four separate problems at once. `circulation_desk` holds one privilege in excess, and it is a read privilege (`select:branch`) — a violation of least privilege, but a limited one: unavoidable residue from inheritance. `cataloging_team` holds the privilege to delete books, though deletion is not among what is required. A team that corrects catalog entries deleting a record by mistake is an accident that is expensive to repair afterward. `reporting_tool` is the clearest case. An account expected to only read holds the privilege to insert and update loan records, through the `clerk` role. This is the typical outcome of "there was a suitable role, so we granted it": the role's name described the job, but its privilege set was broader than the job. `maintenance_account` holds twelve privileges, four of which are required. A maintenance account being broad is expected; the problem is not its breadth but that account being used for day-to-day work. #### Read-Only Connections The privilege chart is not the only line of defense. The second layer is a **connection-level restriction**: the connection is opened without the capability to write. Regardless of which role the reporting tool carries, a write attempt on a connection opened read-only is rejected at the connection layer. ```bash rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT NOT NULL); INSERT INTO branch VALUES (1,'Central'),(2,'Shore'); SQL echo "--- writing from a read-only connection ---" sqlite3 -readonly library.db "INSERT INTO branch VALUES (3,'New');" echo "exit code: $?" echo "--- reading from the same connection ---" sqlite3 -readonly -box library.db "SELECT * FROM branch ORDER BY id;" rm -f library.db ``` ```text --- writing from a read-only connection --- Error: stepping, attempt to write a readonly database (8) exit code: 8 --- reading from the same connection --- ┌────┬─────────┐ │ id │ name │ ├────┼─────────┤ │ 1 │ Central │ │ 2 │ Shore │ └────┴─────────┘ ``` The `-readonly` option and the wording of the error message are specific to this engine; in server-based engines the counterpart is a connection parameter or a session setting. What holds regardless of engine is this: with the write capability switched off at connection time, a mistake in the privilege chart cannot turn into data loss on its own. The two layers exist together; neither substitutes for the other. #### Separating Accounts In operations, three classes of account are kept separate from each other. The **application account** performs the day-to-day work. It carries privileges only on the tables the application touches, and only for the actions it performs. It does not carry the privilege to change the schema — application code is not expected to alter table structure at run time. The **report account** only reads. Being separate is not only a matter of security: it also lets monitoring tell apart a slow report query consuming resources. The **maintenance account** is used for schema changes, backups, and recovery. Its breadth is a requirement of the job; its discipline lies in not being used for day-to-day connections. If it appears in the application's connection string, every defect in the application gains the capability to turn into schema loss. The separation has a testable consequence: a connection opened with the application account attempting to drop a table must return a privilege error. Making this attempt once, by hand, turns "we granted the privileges" into "we verified the privileges." #### Privilege Drift Over Time The privilege chart is correct on the day it is built; every following month it is a little more wrong. A report needs temporary access to one more table, the privilege is granted, the report is retired, the privilege remains. This accumulation is called **privilege creep**, and its only remedy is regular auditing. The audit's concrete form is what the script above did: extract the account's effective privilege set, compare it against what the job requires, and list the difference. The difference does not have to be zero — leftover privileges from inheritance can be acceptable. What is required is that the difference be **known**, and that no write privilege appear in it. The second audit question concerns access paths: while the privilege chart closes off a table, a view exposing that table's columns may have been left open. Views were introduced in the SQL Fundamentals course; on the operations side, a view must not become a path around privilege checking. The audit covers every accessible object, not just the list of tables. #### Summary - Authentication asks who you are; authorization asks what you can do. Engines check the two in separate layers. - Role-based access control ties privileges to duties, not people; when a duty changes, only the role assignment changes. - Role inheritance makes the effective privilege set invisible at the point where it was written; it is computed by taking the closure of the inheritance chain. - Auditing least privilege means computing the difference between the effective privilege set and what the job requires; no write privilege should appear in it. - A read-only connection is a second line of defense, independent of the privilege chart. - Application, report, and maintenance accounts are kept separate; regular auditing counters privilege creep. #### Next Step Every privilege in this lesson was object-level: an account could either read a table or it could not. In the library example, this is not enough — the Shore branch clerk needs to read the loan table, but only the rows of that branch. Showing different accounts a different row set on the same table cannot be expressed with an object-level privilege. The next lesson takes up row-level security, which defines with a policy expression the condition under which a row is visible, and shows how to count the rows a policy leaks. ### Row-Level Security Source: https://academia.sh/en/courses/database-administration/row-level-security Showing a different row set on the same table depending on the account: the policy expression, the session variable, counting the rows a policy leaks, auditing the write side, and the policy's interaction with the access path. The previous lesson built privileges at the object level: an account could either read the loan table or it could not. The library's real requirement is not met by this binary. The Shore branch clerk needs to read loan records, but only the rows of that branch. Seeing the Central branch's rows is not a requirement — it is a leak. This is a constraint that an object-level privilege cannot express. Rather than opening or closing access to the table wholesale, the solution attaches a **policy** to the table: the condition a row must satisfy to be visible. The engine adds this condition automatically to every query directed at that table. This is called **row-level security**. #### Policy Expression and Session Variable A policy is a predicate — a condition using the row's columns and a piece of information belonging to the session. Session information is a value fixed when the connection opens and readable from within a query: which account connected, which branch that account belongs to. This is called a **session variable**. The policy in the library example is this: `branch_id` must equal the session's branch number. The engine adds this condition even if the query writes nothing into its own `WHERE` clause; if the query does write its own condition, the two apply together. The example below models the policy through a view. Real row-level security attaches the policy to the base table and does not require a view; what is modeled here is how the policy expression narrows the row set, and that narrowing is the same either way. ```bash rm -f library.db sqlite3 -box -header library.db <<'SQL' CREATE TABLE loan( id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL); INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES (11,101,1,'2024-03-01','2024-03-14'),(12,102,1,'2024-03-03',NULL), (13,103,1,'2024-03-05',NULL),(14,104,2,'2024-03-02','2024-03-16'), (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL), (17,107,2,'2024-03-09','2024-03-20'),(18,108,3,'2024-03-04','2024-03-18'), (19,109,3,'2024-03-11',NULL); INSERT INTO session VALUES ('branch_id','2'); CREATE VIEW visible_loan AS SELECT * FROM loan WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id'); SELECT 'base table' AS source, COUNT(*) AS rows FROM loan UNION ALL SELECT 'after policy (branch 2)', COUNT(*) FROM visible_loan; UPDATE session SET value='1' WHERE key='branch_id'; SELECT 'after policy (branch 1)' AS source, COUNT(*) AS rows FROM visible_loan; SQL rm -f library.db ``` ```text ┌─────────────────────────┬──────┐ │ source │ rows │ ├─────────────────────────┼──────┤ │ base table │ 9 │ │ after policy (branch 2) │ 4 │ └─────────────────────────┴──────┘ ┌─────────────────────────┬──────┐ │ source │ rows │ ├─────────────────────────┼──────┤ │ after policy (branch 1) │ 3 │ └─────────────────────────┴──────┘ ``` The query text did not change; only the session value changed, and the result set dropped from 4 rows to 3. This is what the policy means: the same query returns a different row set depending on who runs it. Forgetting to write `WHERE branch_id = ?` in application code no longer produces a leak, because the engine adds the condition, not the application. #### Counting the Rows a Policy Leaks A policy is written once and stands for years. A condition correct on the day it was written can broaden through a "convenience" added later. A common broadening is this: a decision is made that returned records should "no longer count as hidden," and an `OR` is added to the policy. The effect of a change like this is not predicted, it is **counted**. The difference between the intended row set and the set the policy in force actually returns is the rows the policy leaks. ```bash rm -f policy.db sqlite3 -box -header policy.db <<'SQL' CREATE TABLE loan( id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES (11,101,1,'2024-03-01','2024-03-14'),(12,102,1,'2024-03-03',NULL), (13,103,1,'2024-03-05',NULL),(14,104,2,'2024-03-02','2024-03-16'), (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL), (17,107,2,'2024-03-09','2024-03-20'),(18,108,3,'2024-03-04','2024-03-18'), (19,109,3,'2024-03-11',NULL); WITH intended AS (SELECT id FROM loan WHERE branch_id = 2), broadened AS (SELECT id FROM loan WHERE branch_id = 2 OR returned IS NOT NULL) SELECT (SELECT COUNT(*) FROM loan) AS base, (SELECT COUNT(*) FROM intended) AS intended, (SELECT COUNT(*) FROM broadened) AS broadened_policy, (SELECT COUNT(*) FROM broadened WHERE id NOT IN (SELECT id FROM intended)) AS leaked; SELECT id, member_id, branch_id, returned FROM loan WHERE (branch_id = 2 OR returned IS NOT NULL) AND branch_id <> 2 ORDER BY id; SQL rm -f policy.db ``` ```text ┌──────┬──────────┬──────────────────┬────────┐ │ base │ intended │ broadened_policy │ leaked │ ├──────┼──────────┼──────────────────┼────────┤ │ 9 │ 4 │ 6 │ 2 │ └──────┴──────────┴──────────────────┴────────┘ ┌────┬───────────┬───────────┬────────────┐ │ id │ member_id │ branch_id │ returned │ ├────┼───────────┼───────────┼────────────┤ │ 1 │ 101 │ 1 │ 2024-03-14 │ │ 8 │ 108 │ 3 │ 2024-03-18 │ └────┴───────────┴───────────┴────────────┘ ``` The number of leaked rows is two, and which ones is listed: returned records from the Central and Hill branches, along with member numbers, were opened up to the Shore clerk. In a nine-row table this difference is visible to the eye; in a ten-million-row table it is visible only through this query. This query is an audit tool, and it is run on every change to the policy. Its shape can be turned into a template: put the intended condition into one common table expression, the policy in force into another, and count the difference. Any result greater than zero requires looking at the text of the policy. #### Auditing the Write Side A policy narrows reading. It does not automatically narrow writing. This distinction is the most commonly skipped side of row-level security: the Shore clerk can write a loan record belonging to the Hill branch, and then be unable to see the row afterward. The example below produces this situation. An insert made through the view reaches the base table, but the inserted row is missing from the view because it falls outside the policy: ```bash rm -f write.db sqlite3 -box -header write.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL); INSERT INTO session VALUES ('branch_id','2'); INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL); CREATE VIEW visible_loan AS SELECT * FROM loan WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id'); CREATE TRIGGER visible_loan_insert INSTEAD OF INSERT ON visible_loan BEGIN INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES (NEW.book_id, NEW.member_id, NEW.branch_id, NEW.pickup, NEW.returned); END; INSERT INTO visible_loan(book_id,member_id,branch_id,pickup,returned) VALUES (20,110,3,'2024-03-12',NULL); SELECT 'from the view' AS source, COUNT(*) AS rows FROM visible_loan UNION ALL SELECT 'base table', COUNT(*) FROM loan; SQL rm -f write.db ``` ```text ┌───────────────┬──────┐ │ source │ rows │ ├───────────────┼──────┤ │ from the view │ 2 │ │ base table │ 3 │ └───────────────┴──────┘ ``` The base table has three rows, the view has two. The third row was written and disappeared. From a data integrity standpoint, this is worse than leaking through reads: records that exist but nobody can see accumulate, counts stop adding up, and the cause is hard to find. The fix is to enforce the same condition on the write path as well. The trigger below rejects a row that does not conform to the policy before it is written: ```bash rm -f write2.db sqlite3 write2.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL); INSERT INTO session VALUES ('branch_id','2'); CREATE VIEW visible_loan AS SELECT * FROM loan WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id'); CREATE TRIGGER visible_loan_insert INSTEAD OF INSERT ON visible_loan BEGIN SELECT CASE WHEN NEW.branch_id <> (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id') THEN RAISE(ABORT, 'row outside the policy cannot be written') END; INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES (NEW.book_id, NEW.member_id, NEW.branch_id, NEW.pickup, NEW.returned); END; INSERT INTO visible_loan(book_id,member_id,branch_id,pickup,returned) VALUES (20,110,3,'2024-03-12',NULL); SQL echo "exit code: $?" rm -f write2.db ``` ```text Runtime error near line 19: row outside the policy cannot be written (19) exit code: 1 ``` Trigger syntax and the shape of the error message vary by engine; in engines that support row-level security natively, the same check is defined as a separate side of the policy. The rule that holds regardless is this: a policy's read side and write side are written separately, and their agreement is tested separately as well. #### Policy and Access Path The policy predicate is a condition added to the query's own condition; the planner sees both together. This has two consequences. The first is favorable: if a suitable index exists on the policy column, the policy does not slow the query down — on the contrary, it can speed it up by increasing selectivity. The index and partitioning decisions made in the previous topic pay off here; a table partitioned by branch number aligns naturally with a branch policy. The second requires attention: the evaluation order between the policy predicate and the query's own predicate is not always defined. The existence of a row outside the policy can be sensed indirectly, through a function running on that row erroring out or slowing down. Engines that guard against this kind of inference guarantee that the policy predicate is evaluated first. The responsibility on the operations side is to not allow user-defined functions as predicates on tables that carry a policy. #### Paths the Policy Does Not Cover Row-level security checks only access that passes through the table it is applied to. The audit checklist includes the following questions. **The table owner and broadly privileged accounts.** Engines generally exempt the table owner and the manager account from the policy. This is why the application account not being the table owner matters. **Copies without a policy.** Copying the same data into a report table, a materialized view, or an export file leaves the policy behind. The copy itself needs a policy too; if it does not, the copy must be produced in a way that narrows it to what the policy covers. **Inference through aggregate functions.** A policy hides rows; it may not hide counts. If there is a path where an account can take a sum or a count over rows it cannot see, information leaks even without row content. Verifying that aggregate queries run after the policy is part of the audit. **Backups.** Every protection built in this lesson belongs to the running system. A backup file is a copy without a policy, and it is protected separately — that is the subject of the next lesson. #### Summary - Row-level security, through a policy predicate attached to a table, makes the same query return a different row set depending on who runs it. - The policy uses a session variable; the engine adds the condition, not application code. - The effect of a policy change is not predicted, it is counted: the difference between the intended set and the set in force gives the leaked rows. - A read policy does not narrow writing; without a separate check on the write side, invisible rows accumulate. - The policy predicate enters the query plan; an index and partitioning choice suited to the policy column preserves performance. - The table owner, copies without a policy, inference through aggregates, and backup files are paths the policy does not cover. #### Next Step Access control keeps data from reaching the wrong person; it does not keep data from ceasing to exist. A disk failure, a mistakenly written `DELETE`, or a faulty schema change can render even the most carefully built privilege chart's database unusable. The next lesson moves to backups: what logical, physical, and incremental backups are, what they cost to take, and how to prove that a restored backup is equal to the source. ### Backup Types Source: https://academia.sh/en/courses/database-administration/backup-types The measured costs of logical, physical, and incremental backups: comparing file size and time to take, proving a restored copy's equality with the source through a content hash, and the limits of a watermark-based incremental backup. The previous lesson left one point open while building row-level security: all of that protection belonged to the running system. A backup file is a copy without a policy, and it is something more fundamental — the only guarantee that the database continues to exist. A disk failure, a mistakenly written `DELETE`, a faulty schema migration, or a deleted directory: none of these is prevented by a privilege chart. This lesson does not take up what a backup is, but the forms it is taken in and what each form costs. Three forms will be measured on the library database, and a restored copy being equal to the source will be shown with a query, not with words. #### Logical and Physical Backups A **logical backup** consists of statements that reproduce the database's content: `CREATE` and `INSERT` statements describing table definitions and rows. It is a text file — readable, editable, and loadable into a different engine or a different version. A **physical backup** is a copy of the data files themselves. It is a byte-for-byte copy at the page level; only the same engine can read its meaning. In exchange, it is cheap to take, because nothing is reproduced — bytes are moved. The choice between the two is not a matter of taste; it is a measurable trade-off. #### Measuring the Three Forms The block below sets up the library database, then measures four backup paths on the same data. Duration and size values depend on the machine, the file system, and the compression library; different numbers will come out on your machine. What matters is not the absolute values but the ratios between the columns. ```bash cat > setup.sql <<'SQL' CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); INSERT INTO branch VALUES (1,'Central'),(2,'Shore'),(3,'Hill'); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) INSERT INTO member(id,name,branch_id) SELECT n,'Member-'||n,(n%3)+1 FROM s; WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) INSERT INTO book(id,title,branch_id) SELECT n,'Book-'||n,(n%3)+1 FROM s; WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<300000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned) SELECT n,(n%50000)+1,(n%20000)+1,(n%3)+1, date('2023-01-01','+'||(n%900)||' day'), CASE WHEN n%4=0 THEN NULL ELSE date('2023-01-01','+'||((n%900)+14)||' day') END FROM s; CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_branch_pickup ON loan(branch_id, pickup); SQL rm -f library.db sqlite3 library.db < setup.sql cat > measure.mjs <<'EOF' import { execSync } from "node:child_process"; import { statSync, rmSync } from "node:fs"; const measure = (label, command, output) => { rmSync(output, { force: true }); const t0 = process.hrtime.bigint(); execSync(command); const ms = Number(process.hrtime.bigint() - t0) / 1e6; return { label, ms, bytes: statSync(output).size }; }; const source = statSync("library.db").size; const measurements = [ measure("logical (.dump)", "sqlite3 library.db .dump > logical.sql", "logical.sql"), measure("logical + compression", "sqlite3 library.db .dump | gzip -9 > logical.sql.gz", "logical.sql.gz"), measure("physical (file copy)", "cp library.db physical.db", "physical.db"), measure("physical (consistent snapshot)", "sqlite3 library.db \"VACUUM INTO 'snapshot.db'\"", "snapshot.db"), ]; console.log("source database: " + source.toLocaleString("en-US") + " bytes\n"); console.log("backup format | bytes | % of source | time (ms)"); console.log("---------------------------------|----------|-------------|----------"); for (const m of measurements) { console.log( m.label.padEnd(32) + " | " + String(m.bytes).padStart(8) + " | " + (100 * m.bytes / source).toFixed(1).padStart(11) + " | " + m.ms.toFixed(0).padStart(8) ); } EOF node measure.mjs ``` {/* mask: (?<=\|)[ ]+[0-9]+$ */} ```text source database: 21,692,416 bytes backup format | bytes | % of source | time (ms) ---------------------------------|----------|-------------|---------- logical (.dump) | 24281928 | 111.9 | 434 logical + compression | 3987422 | 18.4 | 900 physical (file copy) | 21692416 | 100.0 | 8 physical (consistent snapshot) | 21692416 | 100.0 | 43 ``` Four rows describe four separate decisions. The logical backup came out **larger** than the source. This looks counterintuitive, but the reason is plain: the binary representation of numbers and dates swells when converted to text, and the text of an insert statement is rewritten for every row. Indexes, by contrast, are not written to the file — only their definitions are — which partly balances this out. The compressed logical backup dropped below one-fifth of the source. A text backup compresses extremely well, because the same statement pattern repeats once per row. The cost of this is time: in this measurement, compression multiplied the time to take the backup several times over. The file copy is markedly faster than the logical backup. The difference between them is a matter of scale: the logical backup reads every row, converts it to text, and writes it; the file copy only moves bytes. #### The Physical Backup's Consistency Problem The table above has two physical rows, and their sizes are the same. The difference between them is not in size, it is in reliability. Copying a running database's file directly can produce an **inconsistent copy**. While the copy is in progress, the engine keeps writing to the beginning and the end of the file; the beginning of the copy belongs to one moment, the end to another. The resulting file corresponds to a state that never existed, and it looks corrupted when opened — or worse, it opens and returns wrong data. For this reason, a physical backup is taken either while the database is stopped, or with the engine's own consistent-snapshot tool. The fourth row in the table is that tool: the engine reads from a single consistent point and produces a new file. This is why it takes several times as long as a raw file copy in the measurement; the difference paid is the price of consistency. The tool's name and how it is invoked vary by engine. The rule that does not change: **a running database's file is not backed up with `cp`.** #### Incremental Backups A full backup moves the entire dataset every time. As the database grows, this becomes unsustainable in both time and storage. An **incremental backup** moves only the part that changed since the last backup. The simplest way to determine what changed is a **watermark**: the largest primary key value present in the last backup. The block below takes the full backup, then adds six thousand new loan records, then extracts only the rows after the watermark and compares the two files. The block also performs the restore. **A restore is a destructive operation:** it overwrites the target database. The `rm -f` and restore commands here operate only on temporary files produced within this block; in a real setup, the restore target is always a new, empty database, never an existing one. ```bash cat > setup.sql <<'SQL' CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); INSERT INTO branch VALUES (1,'Central'),(2,'Shore'),(3,'Hill'); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) INSERT INTO member(id,name,branch_id) SELECT n,'Member-'||n,(n%3)+1 FROM s; WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) INSERT INTO book(id,title,branch_id) SELECT n,'Book-'||n,(n%3)+1 FROM s; WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<300000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned) SELECT n,(n%50000)+1,(n%20000)+1,(n%3)+1, date('2023-01-01','+'||(n%900)||' day'), CASE WHEN n%4=0 THEN NULL ELSE date('2023-01-01','+'||((n%900)+14)||' day') END FROM s; CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_branch_pickup ON loan(branch_id, pickup); SQL rm -f library.db restored.db full.sql incremental.sql sqlite3 library.db < setup.sql sqlite3 library.db .dump > full.sql sqlite3 library.db "WITH RECURSIVE s(n) AS (SELECT 300001 UNION ALL SELECT n+1 FROM s WHERE n<306000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned) SELECT n,(n%50000)+1,(n%20000)+1,(n%3)+1,date('2025-06-01','+'||(n%30)||' day'),NULL FROM s;" sqlite3 library.db <<'ART' > incremental.sql .mode insert loan SELECT * FROM loan WHERE id > 300000 ORDER BY id; ART sqlite3 restored.db < full.sql sqlite3 restored.db < incremental.sql echo "full backup : $(wc -c < full.sql | tr -d ' ') bytes" echo "incremental backup : $(wc -c < incremental.sql | tr -d ' ') bytes" echo "source rows : $(sqlite3 library.db 'SELECT COUNT(*) FROM loan;')" echo "restored rows : $(sqlite3 restored.db 'SELECT COUNT(*) FROM loan;')" echo "source hash : $(sqlite3 library.db '.sha3sum --schema --sha3-256')" echo "restored hash : $(sqlite3 restored.db '.sha3sum --schema --sha3-256')" ``` ```text full backup : 24281928 bytes incremental backup : 375792 bytes source rows : 306000 restored rows : 306000 source hash : 84080fe4fe7e5bd8303e3ce48d974e9da1d7f82e762731c774e7cbf63049bbc0 restored hash : 84080fe4fe7e5bd8303e3ce48d974e9da1d7f82e762731c774e7cbf63049bbc0 ``` The incremental backup is a little over one and a half percent of the full backup. This ratio is the reason for taking a weekly full backup and a daily incremental one, rather than a daily full backup. #### Proof of Equality The last two lines are this lesson's actual point. The hash value is computed from the database's **content**: table names, column definitions, and row values. It is unaffected by the file's physical layout, its empty pages, or the order of rows on disk. Two hashes coming out equal shows that the restored database is logically identical to the source. Counting rows does not show this — the counts can match while values are corrupted. Looking at it by eye shows nothing at all. This habit has a name, and two of the following lessons are devoted to it: a backup's validity is known not at the moment it is taken, but at the moment it is restored. The statement "we take backups" is not a guarantee; the statement "we restore the backup and compare its hash" is a guarantee. #### What the Watermark Misses The incremental backup above captures only **inserted** rows. The watermark operates on the primary key, and the primary key produces increasing values; as a result: - If a row **before** the watermark was updated, it is not captured. - If a row before the watermark was deleted, it is not captured. - If the schema changed, it is not captured. In the library example, these three gaps are concrete. Returning a book updates the `returned` column of an old loan row — a row before the watermark, so it does not enter the incremental backup. In a database restored by combining the full backup with the incremental one, that book still appears on loan. Adding a column that carries the time of change captures updates but not deletions — the deleted row's change time is deleted along with it. The way to capture deletions is to keep a record not of rows, but of **operations**. That record already exists: the log the engine writes for durability. #### The Three Numbers of a Backup Plan A backup plan is described by three numbers. The **recovery point objective** is the maximum accepted duration of data loss. If a backup is taken once a day, this number is twenty-four hours: if a failure happens right before the backup, a day's worth of loan records is lost. The **recovery time objective** is the maximum accepted duration for the system to become operational again. This number is not about the backup's size but about restore duration — and in the measurement above, restoring took longer than taking the backup. The logical backup's cheap storage is paid for by an expensive restore. **Retention** is how long backups are kept. Keeping only the most recent backup is dangerous: if corruption was backed up before being noticed, the single backup is corrupted too. Backups are kept as a set drawn from different times and different media; at least one copy sits somewhere separate from the machine the database lives on. #### Summary - A logical backup consists of statements, is portable, and compresses well; a physical backup is a copy of files, is fast to take, and is tied to the same engine. - A logical backup can come out larger than the source; compression cuts its size by several times, at the cost of time. - A running database's file cannot be copied directly; a consistent physical backup is taken either from a stopped system or with the engine's snapshot tool. - An incremental backup takes up a small fraction of the full backup; a watermark-based incremental backup captures inserts and misses updates and deletes. - A restored copy's equality with the source is proven with a content hash; counting rows is not enough. - A backup plan is defined by the recovery point objective, the recovery time objective, and retention. #### Next Step The watermark-based incremental backup's three gaps — updates, deletes, and schema changes — all stemmed from the same cause: looking at the final state of rows does not tell what happened between them. A source that records every change in order already exists: the log the engine writes for durability. The next lesson takes up using that log together with a backup: applying log records on top of a full backup, up to a chosen moment, to bring the database back to its state at eleven yesterday morning. ### Point-in-Time Recovery Source: https://academia.sh/en/courses/database-administration/point-in-time-recovery Using a base backup together with a log archive: the recovery window, choosing the target moment, fast-forwarding the log to that target, the work lost after an incident, and computing a partial recovery. The previous lesson left three gaps in the watermark-based incremental backup: updates, deletes, and schema changes were not captured. All three shared the same cause — looking at the final state of rows does not tell what happened between them. A source that records every change in order already exists. To ensure durability, the engine writes every change to a **log** before writing it to the data files; this mechanism was covered in the Engine Architecture topic of this course. If that log is retained, what is on hand is not just two snapshots of the database, but every movement between them. **Point-in-time recovery** is this put to use: a base backup is restored, log records are applied on top of it up to a chosen moment, and the database returns to its state at that moment. #### The Recovery Window The method consists of two parts. A **base backup** is a full copy at a specific moment — the logical or physical backup from the previous lesson. It is the recovery's starting point. A **log archive** is the stored record of log entries from the moment the base backup was taken up to today. The engine keeps the log in its own working area for a limited time; archiving is copying filled log segments to a separate location. Together, the two define a **recovery window**: recovery can reach any point between the base backup's moment and the archive's last moment. The window's start is set by the base backup's age, its end by the archive's continuity. If archiving stops silently, the window stops growing, and the recovery capability quietly regresses to the base backup's moment without anyone noticing. #### An Incident and Its Recovery The block below re-enacts a day. A base backup is taken at nine in the morning. Loan records are opened and returns are processed throughout the day; every change is written to the log with a timestamp. At 11:15, an unconditional delete statement runs — every loan record from the Shore branch is gone. Nobody notices, and work continues until evening. Recovery means loading the base backup into a new database and applying the log up to **just before** 11:15. The `DELETE FROM loan WHERE branch_id=2;` statement in the block is deliberately destructive and is what this lesson is about. It operates only on temporary files produced within this block. In a real system, an unconditional or broadly conditioned delete statement is tested first by running a count query with the same condition; recovery does not substitute for that habit. ```bash rm -f library.db recovered.db base_snapshot.db base.sql changes.log sqlite3 library.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<2000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned) SELECT n,(n%500)+1,(n%300)+1,(n%3)+1,'2025-06-01',NULL FROM s; SQL # 09:00 -- base backup sqlite3 library.db .dump > base.sql # Log: each line "time|statement". There is an unconditional delete at 11:15. node - <<'EOF' > changes.log const lines = []; const time = (min) => new Date(Date.UTC(2025, 5, 10, 9, 0, 0) + min * 60000) .toISOString().slice(0, 19); for (let i = 1; i <= 300; i++) { const min = i * 0.44; if (i % 3 === 0) { lines.push(time(min) + "|UPDATE loan SET returned='2025-06-10' WHERE id=" + (i * 5) + ";"); } else { const id = 2000 + i; lines.push(time(min) + "|INSERT INTO loan VALUES(" + id + "," + ((id % 500) + 1) + "," + ((id % 300) + 1) + "," + ((id % 3) + 1) + ",'2025-06-10',NULL);"); } } lines.push("2025-06-10T11:15:00|DELETE FROM loan WHERE branch_id=2;"); for (let i = 1; i <= 200; i++) { const id = 3000 + i; lines.push(time(136 + i * 1.7) + "|INSERT INTO loan VALUES(" + id + "," + ((id % 500) + 1) + "," + ((id % 300) + 1) + "," + ((id % 3) + 1) + ",'2025-06-10',NULL);"); } console.log(lines.join("\n")); EOF # The full log is applied to the live database. awk -F'|' '{print $2}' changes.log | sqlite3 library.db # Recovery: base backup plus the portion of the log up to the target moment. TARGET='2025-06-10T11:15:00' sqlite3 recovered.db < base.sql awk -F'|' -v t="$TARGET" '$1 < t {print $2}' changes.log | sqlite3 recovered.db sqlite3 base_snapshot.db < base.sql echo "log entries : $(wc -l < changes.log | tr -d ' ')" echo "entries applied: $(awk -F'|' -v t="$TARGET" '$1 < t' changes.log | wc -l | tr -d ' ')" echo "entries skipped: $(awk -F'|' -v t="$TARGET" '$1 >= t' changes.log | wc -l | tr -d ' ')" sqlite3 -box -header library.db " ATTACH 'recovered.db' AS r; ATTACH 'base_snapshot.db' AS b; SELECT 'base backup (09:00)' AS state, COUNT(*) AS rows, SUM(branch_id=2) AS branch2, SUM(returned IS NOT NULL) AS returned FROM b.loan UNION ALL SELECT 'recovered (before 11:15)', COUNT(*), SUM(branch_id=2), SUM(returned IS NOT NULL) FROM r.loan UNION ALL SELECT 'live (17:00, after the incident)', COUNT(*), SUM(branch_id=2), SUM(returned IS NOT NULL) FROM main.loan; SELECT (SELECT COUNT(*) FROM r.loan WHERE id NOT IN (SELECT id FROM main.loan)) AS only_in_recovered, (SELECT COUNT(*) FROM main.loan WHERE id NOT IN (SELECT id FROM r.loan)) AS only_in_live;" ``` ```text log entries : 501 entries applied: 300 entries skipped: 201 ┌──────────────────────────────────┬──────┬─────────┬──────────┐ │ state │ rows │ branch2 │ returned │ ├──────────────────────────────────┼──────┼─────────┼──────────┤ │ base backup (09:00) │ 2000 │ 667 │ 0 │ │ recovered (before 11:15) │ 2200 │ 767 │ 100 │ │ live (17:00, after the incident) │ 1633 │ 67 │ 100 │ └──────────────────────────────────┴──────┴─────────┴──────────┘ ┌───────────────────┬──────────────┐ │ only_in_recovered │ only_in_live │ ├───────────────────┼──────────────┤ │ 767 │ 200 │ └───────────────────┴──────────────┘ ``` #### Reading the Results The first table places three states side by side. At the moment of the base backup, there were two thousand rows, and none had been returned. Returning to just this backup means erasing everything done after nine in the morning — two hundred new loan records and one hundred return transactions. The recovered copy is the state after three hundred log entries have been applied. The Shore branch's seven hundred sixty-seven records are in place, and one hundred returns have been processed. This is the actual state right before the incident, and it came from the base backup plus the log, not from the base backup alone. In the live database, only sixty-seven records from the Shore branch remain — all of them new records opened after the incident. Seven hundred records have been deleted. #### The Cost of Recovery The second table shows the real cost of recovery. The seven hundred sixty-seven rows present in the recovered copy but not in the live database are what the delete took. The two hundred rows present in the live database but not in the recovered copy are **what recovery would take**: ordinary work done after the incident. Putting the recovered copy directly in place of the live database means deleting those two hundred records. This is the side of point-in-time recovery most often overlooked: choosing the target moment means sacrificing everything after it. This cost grows as the time between the incident and its discovery stretches out. A deletion noticed six hours later produces two hundred records' worth of lost work; the same deletion noticed two days later produces two days' worth. For this reason, the preferred path in operations is not to put the recovered copy in place of the live one, but to put it **alongside** it. The recovered copy is opened as a separate database, the missing rows are pulled from it, and added to the live one. The second table's first column is exactly the input to this operation: which rows to move has already been counted. This path works only for deletions; a schema corruption or a widespread update error requires a full rollback. #### Choosing the Target Moment The recovery target can be given in two forms. **By timestamp.** As in the example above: "apply up to this moment." It is easy to read but not exact — several transactions may have finished within the same second, and all of them are either included or excluded together. **By transaction identifier.** With the sequence number or transaction ID engines write to the log: "apply up to this transaction, exclusive." It gives an exact boundary. Finding the faulty statement's identifier requires being able to read the log; most engines provide an inspection tool for this. In practice, the two steps are used together: the range is first narrowed roughly by timestamp, then the log entries in that range are read to find the faulty statement's exact boundary. Performing the recovery in a test environment first leaves room for a second attempt if the target was chosen wrong. #### Where This Model Actually Diverges The log above consists of statement text and is applied by re-executing it. This is a model; it correctly shows the concepts of the recovery window, fast-forwarding, and target selection, but real engines' logs do not look like this. Real logs record change at the physical level: what a given page's bytes became, or what values a given row moved to. The reason is **non-deterministic statements**. A statement that uses the current time, a random number, or a sequence generator produces a different result when re-executed. A recovery that replays statement text produces a database that has drifted from the source. The second difference is transaction boundaries. A real log carries which transaction each record belongs to, and whether that transaction committed. During recovery, the records of uncommitted transactions are rolled back. In the model above, each line is independent; there is no concept of a transaction. Three things remain conceptually unchanged: recovery starts from the base backup, the log is fast-forwarded in order, and it stops at the target moment. #### Summary - Point-in-time recovery is applying log records on top of a base backup up to a chosen moment. - The recovery window starts at the base backup's moment and ends at the log archive's last moment; if archiving stops, the window silently narrows. - Choosing the target moment means sacrificing all work done after it; the loss grows with how long the incident goes unnoticed. - Placing the recovered copy alongside the live one and moving the missing rows over prevents work loss in deletion incidents. - The target is given roughly by timestamp or exactly by transaction identifier. - Real logs record physical change, not statements; non-deterministic statements make recovery based on statement replay unreliable. #### Next Step Recovery worked in this lesson because the base backup was readable and the log was complete. That both are true is known only by attempting the recovery — and the moment that attempt happens should not be the moment of an actual failure. The next lesson takes up backup validation: checking a backup's integrity with a hash, catching a corrupted backup before it is restored, and what a regular recovery drill actually guarantees. ### Backup Validation Source: https://academia.sh/en/courses/database-administration/backup-validation Knowing a backup's validity before the moment of restore: integrity checking with a file hash, catching silent corruption with a content hash, the scope of a structural check, and setting up a regular recovery drill. The previous two lessons built taking a backup and restoring from one. Both rested on the same assumption: the backup file is readable and its content is correct. That assumption is taken as true until it is tested — right up to the moment of failure. The most expensive defect in backing up is not the backup that was never taken; it is **the backup believed to have been taken**. A file half-written because the disk filled up, a byte corrupted during copying, a backup job silently failing for weeks — all of these produce the same outcome: an unusable file in hand at the moment of recovery. This lesson builds ways to know a backup's validity **before the moment of restore**. There are three separate checks, and each catches something different. #### Three Checks, Three Scopes **File hash.** The moment the backup is taken, the file's hash is computed and stored alongside it. A comparison made later shows that not even a single byte of the file has changed. It catches corruption during transfer and storage; it does not show that the backup's **content** is correct. **Structural integrity check.** The engine's own check tests whether the database file's page structure, tree links, and indexes are consistent. It checks structure, not values. **Content hash.** A hash computed from the database's row values is recorded at the moment the backup is taken, and recomputed and compared after it is restored. This is the only check that catches corruption in a value. #### Two Forms of Corruption in a Logical Backup The block below takes a backup, corrupts it in two separate ways, and runs all three files through the same drill. The first corruption is a single byte changing — a loan record's book number turns into a different number. The second is truncating the file's last tenth, which is a half-written backup. The block operates only on temporary files it produces itself and never modifies a backup in place; the corrupted versions are written to separate files. A real drill works the same way: the backup file is never touched, and the work happens on a copy of it. ```bash rm -f library.db backup.sql corrupted.sql truncated.sql drill.db sqlite3 library.db <<'SQL' CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<5000) INSERT INTO member(id,name,branch_id) SELECT n,'Member-'||n,(n%3)+1 FROM s; WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<40000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned) SELECT n,(n%500)+1,(n%5000)+1,(n%3)+1,'2025-06-01',NULL FROM s; SQL sqlite3 library.db .dump > backup.sql shasum -a 256 backup.sql | awk '{print "backup hash (at the time it was taken): " substr($1,1,16)}' # Two corruption forms are produced: a single-byte change and file truncation. node -e ' const fs = require("node:fs"); const b = fs.readFileSync("backup.sql"); const at = b.indexOf("VALUES(20000,"); const pos = at + "VALUES(20000,".length; // first digit of the book_id field const c = Buffer.from(b); c[pos] = b[pos] === 0x39 ? 0x38 : b[pos] + 1; // change the digit fs.writeFileSync("corrupted.sql", c); fs.writeFileSync("truncated.sql", b.subarray(0, Math.floor(b.length * 0.9))); console.log("source : " + b.subarray(at, at + 37).toString()); console.log("corrupted: " + c.subarray(at, at + 37).toString()); ' source_hash=$(sqlite3 library.db '.sha3sum --schema --sha3-256') echo echo "backup | file hash | restore | loan rows | content hash" echo "--------------|-----------|---------|-----------|-------------" for file in backup.sql corrupted.sql truncated.sql; do rm -f drill.db file_hash=$(shasum -a 256 "$file" | awk '{print substr($1,1,8)}') if sqlite3 drill.db < "$file" 2>/dev/null; then restore="ok"; else restore="ERROR"; fi rows=$(sqlite3 drill.db 'SELECT COUNT(*) FROM loan;' 2>/dev/null || echo "none") content=$(sqlite3 drill.db '.sha3sum --schema --sha3-256' 2>/dev/null | tail -1) if [ "$content" = "$source_hash" ]; then equal="EQUAL"; else equal="DIFFERENT"; fi printf "%-13s | %-9s | %-7s | %9s | %s\n" "$file" "$file_hash" "$restore" "$rows" "$equal" done rm -f drill.db ``` ```text backup hash (at the time it was taken): b799626b828b7e06 source : VALUES(20000,1,1,3,'2025-06-01',NULL) corrupted: VALUES(20000,2,1,3,'2025-06-01',NULL) backup | file hash | restore | loan rows | content hash --------------|-----------|---------|-----------|------------- backup.sql | b799626b | ok | 40000 | EQUAL corrupted.sql | 68a2ee39 | ok | 40000 | DIFFERENT truncated.sql | a63b4da9 | ERROR | none | DIFFERENT ``` The middle row is the reason for this lesson. The corrupted backup **restored without error**. The row count came out the same as the source. Tables are in place, indexes are built, queries run. Only the content hash differs — and the difference amounts to a single number in one of forty thousand rows. This form of corruption does not announce itself. When one of forty thousand loan records has the wrong book number, no query returns an error; only the person searching for that book fails to find it. A drill that accepts a backup as "successfully restored" lets this defect through. The bottom row teaches something different. The truncated backup did not restore, and the table was not even created. The reason is that the logical backup is written as a single transaction, started at the beginning of the file and committed at the end. When the file is left half-written, the commit never arrives, and everything is rolled back. This is better than a partial restore — a half-populated database is more dangerous than an empty one, because it looks usable. The file hash column differs for all three files. Compared against the hash recorded at the moment the backup was taken, both corruptions would have been caught **without ever attempting a restore**. This is the cheapest check, and the one line of information that should be stored alongside every backup. #### Structural Checking in a Physical Backup In a physical backup, the situation is different: the file is not text, it is a structure made of pages. The engine can test this structure itself. The block below corrupts two copies of the same database at two separate locations — one at a page header, the other at a data value. ```bash rm -f physical.db sqlite3 physical.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<40000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup) SELECT n,(n%500)+1,(n%5000)+1,(n%3)+1,'2025-06-01' FROM s; CREATE INDEX loan_member ON loan(member_id); SQL source_hash=$(sqlite3 physical.db '.sha3sum --schema --sha3-256') # Two locations are each corrupted by a single byte: a page header and a data value. # The data value's position is found by searching the file for the first date string. data_offset=$(node -e ' const b = require("node:fs").readFileSync("physical.db"); console.log(b.indexOf("2025-06-01") + 5); ') corrupt() { cp physical.db "$1" node -e " const fs = require('node:fs'); const fd = fs.openSync('$1', 'r+'); const b = Buffer.alloc(1); fs.readSync(fd, b, 0, 1, $2); fs.writeSync(fd, Buffer.from([b[0] ^ 0xff]), 0, 1, $2); fs.closeSync(fd); " } corrupt corrupted-40960.db 40960 corrupt corrupted-data.db "$data_offset" echo "file | integrity_check | content hash" echo "--------------------|-----------------|-------------" for d in physical.db corrupted-40960.db corrupted-data.db; do ic=$(sqlite3 "$d" 'PRAGMA integrity_check;' 2>&1 | head -1) hash=$(sqlite3 "$d" '.sha3sum --schema --sha3-256' 2>/dev/null | tail -1) if [ "$hash" = "$source_hash" ]; then equal="EQUAL"; else equal="DIFFERENT"; fi printf "%-19s | %-15s | %s\n" "$d" "${ic:0:15}" "$equal" done rm -f corrupted-40960.db corrupted-data.db ``` ```text file | integrity_check | content hash --------------------|-----------------|------------- physical.db | ok | EQUAL corrupted-40960.db | Error: stepping | DIFFERENT corrupted-data.db | ok | DIFFERENT ``` When the corrupted byte is in a page header, the structural check catches it: the engine cannot read the page and reports the file as corrupted. When the corrupted byte is inside a data value, the structural check **says `ok`** — the structure is consistent, only the value is wrong. The page header's position (`40960`) depends on this engine's page size. The data value's position is not hard-coded: the block searches the file for the first date string and corrupts one of its bytes. A randomly chosen position could land on a record header, which would change the result. The result itself is not engine-specific: no tool that checks page structure can know whether a number inside a page is the correct number. This is the limit of a structural check, and it shows why a content hash is needed in addition. #### The Recovery Drill The two blocks above are parts of a drill. The whole of it consists of the following steps, and it runs at regular intervals, when nobody is in a hurry. **The backup file's hash is compared against the hash from when it was taken.** A mismatch means there is a problem in the storage or transfer layer; no restore is attempted. **The backup is restored into a non-production environment.** The target is always a new, empty database. The drill is never performed on top of a running database. **A structural integrity check is run.** For physical backups, this verifies page and index consistency. **The content hash is compared against the hash recorded when the backup was taken.** This step replaces comparing row counts — the counts can match while values are corrupted. **Duration is measured.** How long the restore takes is the one number that says whether the recovery time objective is achievable. This number grows as the data grows; until it is measured, the plan is an assumption. **The application connects to the recovered copy.** The backup having been restored does not mean the application will run: a missing index, an unsaved view, or a configuration table that never made it into the backup surfaces only at this step. The drill's output is not a report, it is a decision: the backup is valid or it is not. The in-between state of "probably fine" is the same thing as the backup being unvalidated. #### The Drill's Frequency and Scope Frequency is set together with the recovery point objective. Every day a backup goes unvalidated is a day of risk that it is unreliable. If backups are daily, the drill should be frequent too; if a full drill is expensive, a light integrity check runs on every backup, and a full restore drill runs less often. Scope covers every object that is backed up. If the loan table is backed up but the branch definitions sit in a separate configuration file, the drill does not bring the library up and running. A backup's scope is everything the application needs to run; the drill is how that gets found out. Finally, the drill itself leaves a record: which backup, on what date, how long the restore took, and whether the hashes matched. This record answers the question "when did we last try this" at the next failure. #### Summary - A backup's validity is known not at the moment it is taken, but at the moment it is validated; an unvalidated backup offers the same guarantee as no backup at all. - A file hash catches corruption without attempting a restore, and is stored alongside every backup. - A logical backup with a single changed byte can restore without error and the row count can match; only a content hash catches this corruption. - A structural integrity check tests page and index consistency, not the correctness of values. - A truncated logical backup produces an empty database, not a partial one, because it is written as a single transaction. - A recovery drill includes hash comparison, structural checking, duration measurement, and connecting the application; its output is a valid/invalid decision. #### Next Step Backup and recovery guarantee that data is not lost; they do not guarantee the system runs without interruption. A restore that takes forty minutes means the library cannot lend books for those forty minutes. Shortening the interruption means keeping a copy of the data already standing ready on another machine. The next lesson takes up physical replication: continuously streaming the primary server's log records to a standby server, the delay in that stream, and its effect on read consistency. ### Physical Replication Source: https://academia.sh/en/courses/database-administration/physical-replication Continuously streaming log frames to a standby server: the second copy that a base copy and the stream build together, measuring replication lag, lag showing up as stale reads and data loss, and the cost of synchronous commit. The previous lesson built validating a backup's validity. A validated backup guarantees against losing data, but it does not shorten an interruption: a restore that takes forty minutes means the library cannot lend books for those forty minutes. Shortening the interruption means keeping a copy of the data already standing ready on another machine. The most direct answer to how this copy is kept ready comes from the mechanism built in the Engine Architecture topic of this course. The engine writes every change to the log before writing it to the data files. The log carries the database's entire history in order. If that log is streamed to a second machine and applied there, the files on the second machine follow behind the first. This is **physical replication**: changes are carried at the page level, in the form of log records, and replayed on a standby server. #### Base Copy and Stream Physical replication consists of two parts, both familiar from the backup lessons. A **base copy** is a byte-for-byte copy of the data files at a specific moment — the physical backup itself. The standby server starts from here. The **log stream** is the uninterrupted sending of log records produced from the moment the base copy was taken onward. The standby server applies incoming records in order, closing in on the primary's state. It matters to notice that this is the same pair as the base backup and log archive from point-in-time recovery. The difference is purpose: in recovery, the log is applied **once**, up to a chosen moment; in replication, it is applied **continuously**, for as long as the stream keeps running. The same mechanism serves two different operational needs. The block below sets this up in a measurable way. In the engine used here, the log file consists of fixed-size **frames**; each frame carries a copy of a changed page and a header. The block opens five batches of loan records on the primary database, notes the log file's size after each batch, then produces each intermediate state of the standby server by applying only the first portion of the log to the base copy. Frame size and count are specific to this engine; other engines measure the log by byte position or sequence number. What stays the same is that the amount of unsent log is the measure of lag. ```bash rm -f primary.db primary.db-wal primary.db-shm base.db standby.db standby.db-wal standby.db-shm node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const crypto = require("node:crypto"); const PAGE = 4096, FRAME = PAGE + 24, LOG_HEADER = 32; const primary = new DatabaseSync("primary.db"); primary.exec("PRAGMA page_size=4096"); primary.exec("PRAGMA journal_mode=WAL"); primary.exec("PRAGMA wal_autocheckpoint=0"); primary.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL," + " member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)"); primary.exec("PRAGMA wal_checkpoint(TRUNCATE)"); // Base copy: the standby server starts from this file. fs.copyFileSync("primary.db", "base.db"); const insert = primary.prepare( "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); const cutoffs = []; for (let batch = 0; batch < 5; batch++) { primary.exec("BEGIN"); for (let i = 1; i <= 200; i++) insert.run(i % 500, i % 300, i % 3, "2025-06-01"); primary.exec("COMMIT"); cutoffs.push(fs.statSync("primary.db-wal").size); } const log = fs.readFileSync("primary.db-wal"); const totalFrames = (log.length - LOG_HEADER) / FRAME; const digest = (db) => crypto.createHash("sha256").update( db.prepare("SELECT id,book_id,member_id,branch_id FROM loan ORDER BY id").all() .map((r) => r.id + "," + r.book_id + "," + r.member_id + "," + r.branch_id).join("\n") ).digest("hex").slice(0, 8); const primaryDigest = digest(primary); console.log("primary: " + primary.prepare("SELECT COUNT(*) c FROM loan").get().c + " rows, " + totalFrames + " log frames, content digest " + primaryDigest); console.log(); console.log("frames sent | standby rows | lag | content digest | equality"); console.log("------------|--------------|-----|----------------|--------"); for (const size of cutoffs) { fs.rmSync("standby.db-wal", { force: true }); fs.rmSync("standby.db-shm", { force: true }); fs.copyFileSync("base.db", "standby.db"); fs.writeFileSync("standby.db-wal", log.subarray(0, size)); const standby = new DatabaseSync("standby.db"); const frames = (size - LOG_HEADER) / FRAME; const d = digest(standby); console.log(String(frames).padStart(11) + " | " + String(standby.prepare("SELECT COUNT(*) c FROM loan").get().c).padStart(12) + " | " + String(totalFrames - frames).padStart(3) + " | " + d.padStart(14) + " | " + (d === primaryDigest ? "EQUAL" : "BEHIND")); standby.close(); } EOF ``` ```text primary: 1000 rows, 20 log frames, content digest 0a8630f5 frames sent | standby rows | lag | content digest | equality ------------|--------------|-----|----------------|-------- 4 | 200 | 16 | e2b63ba7 | BEHIND 8 | 400 | 12 | 10a42e86 | BEHIND 12 | 600 | 8 | cc62491f | BEHIND 16 | 800 | 4 | 10142745 | BEHIND 20 | 1000 | 0 | 0a8630f5 | EQUAL ``` Each row of the table is a moment in time. The standby server is in the state where the frames sent up to that moment have been applied: consistent, queryable, but behind. In the last row, once lag drops to zero, the content digest matches the primary's — the copy is byte-for-byte equal at the page level. None of the intermediate rows is corrupted. The standby server never stops in the middle of a half-applied transaction; applying advances at transaction boundaries, so every intermediate state is a valid database. This is physical replication's basic guarantee: the standby can be behind, it cannot be inconsistent. #### Two Faces of Lag Replication lag looks harmless as a number. In operations, it shows up in two separate forms, and the two are different problems. The block below demonstrates this: while the standby server is caught up, the circulation desk opens three new records, and the stream stops at that moment. ```bash rm -f primary.db primary.db-wal primary.db-shm base.db standby.db standby.db-wal standby.db-shm node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const FRAME = 4096 + 24; const primary = new DatabaseSync("primary.db"); primary.exec("PRAGMA page_size=4096"); primary.exec("PRAGMA journal_mode=WAL"); primary.exec("PRAGMA wal_autocheckpoint=0"); primary.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL," + " member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)"); primary.exec("PRAGMA wal_checkpoint(TRUNCATE)"); fs.copyFileSync("primary.db", "base.db"); const insert = primary.prepare( "INSERT INTO loan(id,book_id,member_id,branch_id,pickup) VALUES(?,?,?,?,?)"); primary.exec("BEGIN"); for (let i = 1; i <= 1000; i++) insert.run(i, i % 500, i % 300, i % 3, "2025-06-01"); primary.exec("COMMIT"); // The standby server is caught up to this point. const sent = fs.statSync("primary.db-wal").size; fs.copyFileSync("base.db", "standby.db"); fs.writeFileSync("standby.db-wal", fs.readFileSync("primary.db-wal")); let standby = new DatabaseSync("standby.db"); console.log("caught-up standby, row count: " + standby.prepare("SELECT COUNT(*) c FROM loan").get().c); standby.close(); // The circulation desk opens three new records; the frames are not yet sent. primary.exec("BEGIN"); for (let i = 1001; i <= 1003; i++) insert.run(i, i % 500, i % 300, 2, "2025-06-10"); primary.exec("COMMIT"); const pending = (fs.statSync("primary.db-wal").size - sent) / FRAME; fs.rmSync("standby.db-shm", { force: true }); fs.copyFileSync("base.db", "standby.db"); fs.writeFileSync("standby.db-wal", fs.readFileSync("primary.db-wal").subarray(0, sent)); standby = new DatabaseSync("standby.db"); const countNew = (db) => db.prepare("SELECT COUNT(*) c FROM loan WHERE id>=1001").get().c; console.log(); console.log("pending frames : " + pending); console.log("new records on primary : " + countNew(primary)); console.log("new records on standby : " + countNew(standby)); console.log("report reading from standby: " + standby.prepare("SELECT COUNT(*) c FROM loan WHERE branch_id=2 AND pickup='2025-06-10'").get().c + " records"); standby.close(); // The pending frames are now sent as well. fs.rmSync("standby.db-shm", { force: true }); fs.copyFileSync("base.db", "standby.db"); fs.writeFileSync("standby.db-wal", fs.readFileSync("primary.db-wal")); standby = new DatabaseSync("standby.db"); console.log("after sending, on standby : " + countNew(standby) + " new records"); standby.close(); EOF ``` ```text caught-up standby, row count: 1000 pending frames : 4 new records on primary : 3 new records on standby : 0 report reading from standby: 0 records after sending, on standby : 3 new records ``` **The first face: stale reads.** The standby server is a read-only copy, and directing read load to it is replication's most commonly used benefit. Reports, search queries, and dashboards do not burden the primary. In exchange, the data read is as old as the lag. Above, the report sees zero records, while three have actually been opened. This is not a problem for every application. A report computing the monthly loan count does not notice a lag of a few seconds. It is different when the circulation clerk immediately queries the loan they just recorded: they cannot read what they wrote. The operating rule for avoiding this is simple — a session that just wrote goes to the primary for its next reads, and reads that can tolerate lag go to the standby. **The second face: data loss at failover.** If the primary server were lost at this moment, the standby would take over with 1000 rows; the three loan records in the four unsent frames would be lost. Lag is the counterpart of the recovery point objective: if the objective says "at most five seconds of data loss," lag not exceeding five seconds has to be monitored. Unmonitored lag is an unmeasured recovery point objective. #### The Cost of Synchronous Commit The loss above comes from replication being **asynchronous**: the primary server commits the transaction, tells the user "done," and sends the log afterward. The alternative is to tie the commit acknowledgment to the standby server reporting that it received the record — **synchronous commit**. In this case, loss is zero, because every acknowledged transaction is on two machines. The cost is a network round trip added to every transaction. The block below actually measures local commit duration, and **models** network latency: the round-trip times are given numbers, not measured. Local duration also depends on the machine, the file system, and the sync setting; it will come out different on your machine. What matters is the ratio between the columns. ```bash rm -f ack.db ack.db-wal ack.db-shm node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const db = new DatabaseSync("ack.db"); db.exec("PRAGMA journal_mode=WAL"); db.exec("PRAGMA synchronous=FULL"); db.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT)"); const insert = db.prepare("INSERT INTO loan(book_id,member_id) VALUES(?,?)"); // Local commit duration is measured: each record is its own transaction. const durations = []; for (let i = 0; i < 300; i++) { const t = process.hrtime.bigint(); db.exec("BEGIN"); insert.run(i % 500, i % 300); db.exec("COMMIT"); durations.push(Number(process.hrtime.bigint() - t) / 1e6); } durations.sort((a, b) => a - b); const local = durations[Math.floor(durations.length / 2)]; console.log("local commit (median): " + local.toFixed(3) + " ms"); console.log(); console.log("commit mode | network rtt | txn duration | txn/sec | loss risk"); console.log("------------------------------|-------------|--------------|---------|--------------"); const row = (mode, rtt, loss) => { const t = local + rtt; console.log(mode.padEnd(29) + " | " + (rtt ? rtt + " ms" : "-").padStart(11) + " | " + (t.toFixed(3) + " ms").padStart(12) + " | " + Math.round(1000 / t).toString().padStart(7) + " | " + loss); }; row("asynchronous", 0, "up to the lag"); row("synchronous (local network)", 1, "none"); row("synchronous (regions)", 10, "none"); row("synchronous (continents)", 60, "none"); EOF ``` {/* mask: (?<=median\): )[0-9.]+(?= ms) */} {/* mask: [ ]*[0-9]+\.[0-9]{3}(?= ms \|) */} {/* mask: [ ]*[0-9]+(?= \| [un]) */} ```text local commit (median): 0.004 ms commit mode | network rtt | txn duration | txn/sec | loss risk ------------------------------|-------------|--------------|---------|-------------- asynchronous | - | 0.004 ms | 226398 | up to the lag synchronous (local network) | 1 ms | 1.004 ms | 996 | none synchronous (regions) | 10 ms | 10.004 ms | 100 | none synchronous (continents) | 60 ms | 60.004 ms | 17 | none ``` The table gives one connection's sequential transaction rate; when multiple connections run in parallel, total throughput rises, but the time a single user waits does not change. In the library's context, this reads as follows: opening a record at the circulation desk takes on the order of a millisecond with a same-city standby; with a cross-continent standby, every loan transaction waits sixty milliseconds. The second produces an unworkable interface. For this reason, the common topology combines both: a nearby standby server is acknowledged synchronously (data loss is zero), and a distant standby server is fed asynchronously (a copy exists against a region-wide failure, without paying the latency price). Synchronous commit has a side effect that is easy to overlook: when the standby server stops responding, the primary server also becomes unable to write, because no transaction can be acknowledged. A mechanism built to raise availability lowers it when configured with a single standby server. Preventing this is done by tying the acknowledgment to **any one** of several standby servers, or by falling back to asynchronous mode when the standby stops responding; the second option means the risk of loss has returned, and it must be chosen knowingly. #### The Standby Server's Limits Physical replication copies pages. This directly produces three limits. **The copy is whole; it cannot be selective.** Replicating only the loan table is not possible; the whole database comes along. A mechanism that operates at the level of page numbers does not care which table a given page belongs to. **The copy is read-only.** Writing to the standby server would conflict with the incoming log stream. For this reason, the only writer on the standby server is the stream; applications only read from it. Completing a missing index or creating a temporary table on the standby server is not possible. **The copy requires the same version and the same layout.** Log records describe page structure; a version that interprets page structure differently cannot apply them. This is a decisive constraint in version upgrades, and it comes back in this topic's last lesson. There is one more limit, and it is the one most commonly experienced in operations: a long-running read query on the standby server can delay the application of incoming changes. The reason is the multiversion concurrency control built in the Engine Architecture topic of this course — the versions a reader sees have to be preserved. The result is that a report running for hours grows the replication lag. #### Summary - Physical replication is the combination of a base copy and a continuous log stream; the standby server follows the primary by applying incoming log records in order. - Every intermediate state of the standby server is consistent; lag is measured by the amount of unsent log, and at zero lag the two copies' content is byte-for-byte equal. - Lag shows up in two forms: reports reading from the standby see stale data, and unsent records are lost at failover. - Synchronous commit zeroes out data loss, and in exchange adds one network round trip to every transaction; with a distant standby, this can grow transaction duration by three orders of magnitude. - Synchronous commit tied to a single standby server leaves the primary unable to write when the standby stops responding. - A physical copy is whole, read-only, and requires the same page layout. #### Next Step All three of physical replication's limits come from the same place: what gets carried is not the row, it is the page. If the changed rows themselves were carried instead of the page, the copy could be selective — only the loan and member tables could be sent. The target table could be in a different layout, even a different engine version, because what gets applied would not be a page image but the information "this row moved to these values." The next lesson takes up logical replication: setting up selective copying, comparing the data volume sent against physical replication, writing to a target with a different schema, and the door this path opens for version transitions. ### Logical Replication Source: https://academia.sh/en/courses/database-administration/logical-replication Moving row-level changes for selected tables: publication and subscription, comparing the data volume sent against physical replication, writing to a target with a different schema, how conflict stops the stream, and the order in which schema changes apply. Physical replication's three limits all came from the same place: what moved was not a row but a page. If the changed rows themselves move instead of the page, all three limits lift. The copy becomes selective — only the loan and member tables could be sent. The table on the target could sit in a different layout, with a different set of indexes, or even on a different engine version, because what gets applied is not a page image but the information that "this row changed to these values." This is **logical replication**. The engine reads the log and extracts row-level events from the page changes inside it: which row was inserted into which table, which row was updated to which values, which row was deleted. This extraction step is called **logical decoding**. The result is a change stream that is independent of page numbers and meaningful on its own. #### Publication and Subscription Logical replication has two ends. **Publication** specifies, on the source server, which tables get replicated. Changes to tables outside the scope never enter the stream at all. **Subscription** is the side on the target server that reads and applies the stream. It stores how far it has applied as a position, and resumes from that position when the connection drops. Most engines also hold a **replication slot** on the source side, so that changes not yet applied are not deleted. The block below builds this on the library database. The source has four tables: member, book, loan, and session log. The publication covers only two of them. In a real engine the change stream is produced by decoding the log; here the same information is produced with **triggers** instead. That is the part of the model that is modeled — where the stream comes from. Its content, the way it is applied, and its scoping behavior match the real thing. ```bash rm -f primary.db subscriber.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const crypto = require("node:crypto"); const primary = new DatabaseSync("primary.db"); primary.exec(` CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, shelf TEXT); CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); CREATE TABLE session_log(id INTEGER PRIMARY KEY, account TEXT, moment TEXT); CREATE TABLE publication(seq INTEGER PRIMARY KEY, table_name TEXT, operation TEXT, key INT, row_text TEXT); `); // Publication scope: only member and loan. for (const [tableName, fields] of [ ["member", "'id',NEW.id,'name',NEW.name,'branch_id',NEW.branch_id"], ["loan", "'id',NEW.id,'book_id',NEW.book_id,'member_id',NEW.member_id," + "'branch_id',NEW.branch_id,'pickup',NEW.pickup,'returned',NEW.returned"]]) { for (const [event, code] of [["INSERT", "I"], ["UPDATE", "U"]]) { primary.exec(`CREATE TRIGGER pub_${tableName}_${code} AFTER ${event} ON ${tableName} BEGIN INSERT INTO publication(table_name,operation,key,row_text) VALUES('${tableName}','${code}',NEW.id,json_object(${fields})); END`); } primary.exec(`CREATE TRIGGER pub_${tableName}_D AFTER DELETE ON ${tableName} BEGIN INSERT INTO publication(table_name,operation,key,row_text) VALUES('${tableName}','D',OLD.id,NULL); END`); } // A day's work: member and book records, loan pickups, returns, deletions, session log. primary.exec("BEGIN"); const member = primary.prepare("INSERT INTO member(id,name,branch_id) VALUES(?,?,?)"); for (let i = 1; i <= 500; i++) member.run(i, "Member-" + i, (i % 3) + 1); const book = primary.prepare("INSERT INTO book(id,title,shelf) VALUES(?,?,?)"); for (let i = 1; i <= 300; i++) book.run(i, "Book-" + i, "R" + (i % 20)); const loan = primary.prepare( "INSERT INTO loan(id,book_id,member_id,branch_id,pickup) VALUES(?,?,?,?,?)"); for (let i = 1; i <= 2000; i++) loan.run(i, (i % 300) + 1, (i % 500) + 1, (i % 3) + 1, "2025-06-01"); const returnLoan = primary.prepare("UPDATE loan SET returned='2025-06-20' WHERE id=?"); for (let i = 1; i <= 400; i++) returnLoan.run(i * 3); const remove = primary.prepare("DELETE FROM loan WHERE id=?"); for (let i = 1; i <= 50; i++) remove.run(i * 37); const sessionLog = primary.prepare("INSERT INTO session_log(account,moment) VALUES(?,?)"); for (let i = 1; i <= 800; i++) sessionLog.run("loan_desk", "2025-06-10"); primary.exec("COMMIT"); // Only two tables are defined on the subscriber side. const subscriber = new DatabaseSync("subscriber.db"); subscriber.exec(` CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_id INT NOT NULL); CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); `); const apply = (db, rec) => { if (rec.operation === "D") { db.prepare(`DELETE FROM ${rec.table_name} WHERE id=?`).run(rec.key); return; } const row = JSON.parse(rec.row_text), columns = Object.keys(row); db.prepare(`INSERT OR REPLACE INTO ${rec.table_name}(${columns.join(",")})` + ` VALUES(${columns.map(() => "?").join(",")})`).run(...columns.map((c) => row[c])); }; const records = primary.prepare("SELECT * FROM publication ORDER BY seq").all(); subscriber.exec("BEGIN"); for (const rec of records) apply(subscriber, rec); subscriber.exec("COMMIT"); console.log("records in the publication stream:"); for (const row of primary.prepare("SELECT table_name, operation, COUNT(*) n FROM publication" + " GROUP BY table_name, operation ORDER BY table_name, operation").all()) console.log(" " + row.table_name.padEnd(6) + " " + row.operation + " : " + row.n); console.log(" total : " + records.length); const contentHash = (db, tbl, sel) => crypto.createHash("sha256").update( db.prepare(`SELECT ${sel} FROM ${tbl} ORDER BY id`).all() .map((r) => Object.values(r).join(",")).join("\n")).digest("hex").slice(0, 8); const count = (db, tbl) => { try { return db.prepare(`SELECT COUNT(*) c FROM ${tbl}`).get().c; } catch { return "no table"; } }; console.log(); console.log("table | primary | subscriber | content hash (primary / subscriber)"); console.log("-------------|----------|------------|------------------------------------"); for (const [tbl, sel] of [["member", "id,name,branch_id"], ["loan", "id,book_id,member_id,branch_id,pickup,returned"]]) console.log(tbl.padEnd(12) + " | " + String(count(primary, tbl)).padStart(8) + " | " + String(count(subscriber, tbl)).padStart(10) + " | " + contentHash(primary, tbl, sel) + " / " + contentHash(subscriber, tbl, sel)); for (const tbl of ["book", "session_log"]) console.log(tbl.padEnd(12) + " | " + String(count(primary, tbl)).padStart(8) + " | " + String(count(subscriber, tbl)).padStart(10) + " |"); EOF ``` ```text records in the publication stream: loan D : 50 loan I : 2000 loan U : 400 member I : 500 total : 2950 table | primary | subscriber | content hash (primary / subscriber) -------------|----------|------------|------------------------------------ member | 500 | 500 | 2e1cc525 / 2e1cc525 loan | 1950 | 1950 | 3ec52d07 / 3ec52d07 book | 300 | no table | session_log | 800 | no table | ``` The effect of scope shows up in the numbers. Four thousand fifty rows changed on the source; only two thousand nine hundred fifty of them entered the stream. The eleven hundred changes on the book and session log tables were never carried at all — that much less network traffic, target-side disk, and write work there. The content hash for both tables on the subscriber matches the source. This shows that all three operation types were applied correctly: inserts, four hundred return updates, and fifty deletions. Delete records carry only a key; the deleted row's other fields are unneeded. This means logical replication finds a row **by its key**, and explains why replication is harder on a table with no primary key: there is no criterion to select the row on the target. Engines require such a table to be assigned a **replica identity**. #### Data Volume Carried The most concrete difference between the two methods is the amount of data moved, and this difference changes by an order of magnitude with the workload. The block below runs the same workload on two separate databases: one where the log is measured, the physical side; the other where change records are measured, the logical side. The numbers depend on page size, row width, and batch size; yours will come out different. On the logical side only the change body is counted, not the per-record protocol overhead. What matters is the direction of the ratios. ```bash rm -f physical.db physical.db-wal physical.db-shm logical.db logical.db-wal logical.db-shm node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const SCHEMA = "CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL," + " member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT)"; function setup(file, trigger) { const db = new DatabaseSync(file); db.exec("PRAGMA page_size=4096"); db.exec("PRAGMA journal_mode=WAL"); db.exec("PRAGMA wal_autocheckpoint=0"); db.exec(SCHEMA); db.exec("CREATE TABLE change_log(seq INTEGER PRIMARY KEY, table_name TEXT, operation TEXT, row_text TEXT)"); const insert = db.prepare( "INSERT INTO loan(id,book_id,member_id,branch_id,pickup) VALUES(?,?,?,?,?)"); db.exec("BEGIN"); for (let i = 1; i <= 20000; i++) insert.run(i, i % 500, i % 300, i % 3, "2025-06-01"); db.exec("COMMIT"); if (trigger) { for (const [event, code] of [["UPDATE", "U"], ["INSERT", "I"]]) db.exec(`CREATE TRIGGER chg_${code} AFTER ${event} ON loan BEGIN INSERT INTO change_log(table_name,operation,row_text) VALUES('loan','${code}', json_object('id',NEW.id,'book_id',NEW.book_id,'member_id',NEW.member_id, 'branch_id',NEW.branch_id,'pickup',NEW.pickup,'returned',NEW.returned)); END`); } db.exec("PRAGMA wal_checkpoint(TRUNCATE)"); return db; } function measure(label, workload) { const physical = setup("physical.db", false); const logical = setup("logical.db", true); workload(physical); workload(logical); const physicalBytes = fs.statSync("physical.db-wal").size; const logicalStats = logical.prepare("SELECT IFNULL(SUM(LENGTH(row_text)),0) bytes, COUNT(*) n FROM change_log").get(); physical.close(); logical.close(); for (const d of ["physical.db", "physical.db-wal", "physical.db-shm", "logical.db", "logical.db-wal", "logical.db-shm"]) fs.rmSync(d, { force: true }); console.log(label.padEnd(20) + " | " + String(logicalStats.n).padStart(7) + " | " + String(physicalBytes).padStart(8) + " | " + String(logicalStats.bytes).padStart(9) + " | " + (physicalBytes / logicalStats.bytes).toFixed(1).padStart(5)); } console.log("workload".padEnd(20) + " | " + "changes".padStart(7) + " | " + "physical".padStart(8) + " | " + "logical".padStart(9) + " | ratio"); console.log("-".repeat(20) + "-|" + "-".repeat(9) + "|" + "-".repeat(10) + "|" + "-".repeat(11) + "|------"); measure("scattered update", (db) => { const update = db.prepare("UPDATE loan SET returned='2025-06-20' WHERE id=?"); for (let p = 0; p < 100; p++) { db.exec("BEGIN"); for (let i = 0; i < 20; i++) update.run(((p * 20 + i) * 7919) % 20000 + 1); db.exec("COMMIT"); } }); measure("sequential update", (db) => { const update = db.prepare("UPDATE loan SET returned='2025-06-20' WHERE id=?"); for (let p = 0; p < 100; p++) { db.exec("BEGIN"); for (let i = 0; i < 20; i++) update.run(p * 20 + i + 1); db.exec("COMMIT"); } }); measure("bulk insert", (db) => { const insert = db.prepare( "INSERT INTO loan(id,book_id,member_id,branch_id,pickup) VALUES(?,?,?,?,?)"); for (let p = 0; p < 100; p++) { db.exec("BEGIN"); for (let i = 0; i < 20; i++) { const n = 20001 + p * 20 + i; insert.run(n, n % 500, n % 300, n % 3, "2025-06-10"); } db.exec("COMMIT"); } }); EOF ``` ```text workload | changes | physical | logical | ratio ---------------------|---------|----------|-----------|------ scattered update | 2000 | 9014592 | 201716 | 44.7 sequential update | 2000 | 964112 | 199685 | 4.8 bulk insert | 2000 | 572712 | 186790 | 3.1 ``` All three rows carry two thousand changed rows; the amount of data carried varies by a factor of fifteen. The reason is the physical side's unit: however small the changed row is, the **entire page** containing that row gets sent. In the scattered update, each row sits on a separate page, so two thousand pages get carried; writing one date costs sending four kilobytes. In the sequential update, many rows within the same page change in a single batch, so the page is sent once and the ratio drops to five. In the bulk insert, the rows are already laid out next to each other; the physical side is at its most efficient. The logical side's hidden cost shows up at the target: every incoming row is applied as a normal write, indexes are updated, constraints are checked. On the physical side none of this exists; the page is put back in place unchanged. The method that moves less data does more work at the target. #### Different Schema, Conflict, and Schema Change Flexibility has three consequences; the block below shows each in turn: the target has an extra column and index, a local row conflicts with the stream, and a column is added on the source. ```bash rm -f source.db target.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const source = new DatabaseSync("source.db"); source.exec(` CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, pickup TEXT NOT NULL); CREATE TABLE publication(seq INTEGER PRIMARY KEY, row_text TEXT); CREATE TRIGGER pub_insert AFTER INSERT ON loan BEGIN INSERT INTO publication(row_text) VALUES(json_object('id',NEW.id,'book_id',NEW.book_id, 'member_id',NEW.member_id,'pickup',NEW.pickup)); END; `); const insert = source.prepare("INSERT INTO loan(id,book_id,member_id,pickup) VALUES(?,?,?,?)"); for (let i = 1; i <= 100; i++) insert.run(i, i % 50, i % 30, "2025-06-01"); // Target: a table with an extra column and an extra index. const target = new DatabaseSync("target.db"); target.exec(` CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, pickup TEXT NOT NULL, delay_days INT NOT NULL DEFAULT 0); CREATE INDEX loan_member ON loan(member_id); `); // A locally opened record on the target: this produces the conflict. target.prepare("INSERT INTO loan(id,book_id,member_id,pickup) VALUES(?,?,?,?)") .run(60, 9, 9, "2025-06-05"); const apply = (record) => { const row = JSON.parse(record.row_text), columns = Object.keys(row); target.prepare(`INSERT INTO loan(${columns.join(",")})` + ` VALUES(${columns.map(() => "?").join(",")})`).run(...columns.map((c) => row[c])); }; let position = 0; const advance = () => { for (const rec of source.prepare("SELECT seq,row_text FROM publication ORDER BY seq").all()) { if (rec.seq <= position) continue; try { apply(rec); position = rec.seq; } catch (err) { return "seq " + rec.seq + " stopped: " + err.message; } } return "stream finished"; }; const status = () => " applied record: " + position + ", rows in target: " + target.prepare("SELECT COUNT(*) c FROM loan").get().c; console.log("pass 1: " + advance()); console.log(status()); // Fix: the conflicting local row is removed, the stream resumes from where it left off. target.prepare("DELETE FROM loan WHERE id=?").run(60); console.log("pass 2: " + advance()); console.log(status()); console.log(" target-only column value: " + target.prepare("SELECT delay_days FROM loan WHERE id=1").get().delay_days); // Schema change on the source: the new column also enters the publication. source.exec("ALTER TABLE loan ADD COLUMN delay_notice TEXT"); source.exec("DROP TRIGGER pub_insert"); source.exec(`CREATE TRIGGER pub_insert AFTER INSERT ON loan BEGIN INSERT INTO publication(row_text) VALUES(json_object('id',NEW.id,'book_id',NEW.book_id, 'member_id',NEW.member_id,'pickup',NEW.pickup,'delay_notice',NEW.delay_notice)); END`); source.prepare("INSERT INTO loan(id,book_id,member_id,pickup,delay_notice)" + " VALUES(?,?,?,?,?)").run(101, 7, 7, "2025-06-11", "second notice"); console.log("pass 3: " + advance()); // The schema change is applied to the target first. target.exec("ALTER TABLE loan ADD COLUMN delay_notice TEXT"); console.log("pass 4: " + advance()); console.log(status()); EOF ``` ```text pass 1: seq 60 stopped: UNIQUE constraint failed: loan.id applied record: 59, rows in target: 60 pass 2: stream finished applied record: 100, rows in target: 100 target-only column value: 0 pass 3: seq 101 stopped: table loan has no column named delay_notice pass 4: stream finished applied record: 101, rows in target: 101 ``` **Extra columns and indexes are not a problem.** The target's `delay_days` column is not in the stream; it takes its default value. The target's index is not in the source either, and it is updated on every incoming row. This lets the same data be indexed for different access patterns on each side. **Conflict stops the stream.** The locally opened record numbered sixty on the target collided with the same-numbered record in the stream, and applying stopped at record fifty-nine. This is the deliberate way to avoid losing data: silently overwriting the conflicting record and silently skipping it would both quietly let the two sides drift apart. Stopping waits for a decision to be made. Once the decision was made and the conflicting row removed, the stream resumed from where it left off and all one hundred records were applied. The source of a conflict is most often the same thing: a local write made to the target. Logical replication's target is not read-only, and that is the flip side of the flexibility. The operating rule for avoiding conflict is that tables on the target are written only by the stream; if a local write is required, key ranges are partitioned off. **Schema change is not replicated.** The column added on the source entered the stream, and applying stopped because the target had no counterpart for it. Once the same column was added to the target, the stream resumed. This yields a single operating rule: **a column addition is applied to the target first, then to the source**; a column removal goes in the opposite order, from the source first. When the order gets mixed up, replication stops, and for as long as it stays stopped the changes accumulating on the source keep being retained. #### Its Role in Version Migration The sum of these three behaviors turns logical replication into a version migration tool. Physical replication requires the target to understand the same page layout; logical replication does not. An empty database set up on the new version can be subscribed to the database on the old version; the old version keeps running while data streams in. Once the two sides are caught up, applications are redirected to the new side. This path's cutover window is not the length of a restore, but only as long as the redirect takes. The same mechanism is also used to move a single table into a separate database or to keep a reporting server continuously fed. The whole of version upgrading is the subject of this topic's last lesson. #### Summary - Logical replication carries row-level changes decoded from the log; changes on tables outside publication scope never enter the stream at all. - Delete records carry only a key; since the row on the target must be found by that key, tables with no replica identity defined cause trouble. - The volume carried depends on the workload: on scattered small updates, physical replication sends nearly fifty times more data than logical; on bulk inserts the gap narrows. - The method that moves less data does more work at the target; incoming rows are applied as normal writes, and indexes and constraints are enforced. - The target may have extra columns and different indexes; conflict stops the stream and waits for a decision. - Schema change is not replicated: a column addition is applied to the target first, a column removal to the source first. #### Next Step Both forms of replication carry the same assumption: the answer to the question of who the primary server is, is known. When the primary is lost, that answer is lost too, and a new one has to be put in its place. Who decides, when do they decide, and what happens if two separate sides both say "I am the primary" at the same time? The next lesson covers high availability: the difference between failover and switchover, the cluster manager's role, what the quorum vote guarantees, and how the risk of split brain arises. ### High Availability and Failover Source: https://academia.sh/en/courses/database-administration/high-availability-and-failover The service continuing when the primary server is lost: the cluster manager's decision, how the majority rule prevents split brain, the data conflict two primaries produce, and the trade-off between detection time and false failover. The previous two lessons set up replication; both carried the same assumption: who the primary server is, is known. When the primary is lost, that answer is lost too, and a new one has to be put in its place. Having a second copy of the data ready on another machine does not mean that machine starts serving on its own — someone has to decide, promote the standby server, and redirect applications there. This lesson's subject is that decision: who makes it, on what information, and what happens if two separate sides say "I am the primary" at the same time. #### Failover, Switchover, and the Cluster Manager Two operations get confused with each other. **Switchover** is a deliberate exchange of the primary and standby roles. The primary server is still running: it stops accepting new writes, the standby server is allowed to catch up, and the roles are swapped. There is no data loss, and the outage is on the order of seconds. Maintenance, hardware replacement, and version upgrades are done this way. **Failover** is promoting the standby server to primary while the primary server is unreachable. There is no chance to wait for the primary to catch up; under asynchronous replication, every unsent change is lost. The amount lost is the replication lag measured in the physical replication lesson. The component that makes this decision is called the **cluster manager**. It runs outside the database engine, polls the nodes at regular intervals, and does three jobs: monitor the primary's reachability, promote a standby when needed, and redirect the address applications connect to toward the new primary. Without this last step, promotion is useless — applications still try to connect to the old address. #### The Majority Rule The cluster manager's hardest question is whether the primary is really dead or merely unreachable. The two look identical from the outside: no response comes back. But their consequences are opposite. If the primary is dead, the standby needs to be promoted; if the primary is running but invisible because of a network partition, promotion creates a second primary. The block below compares the two rules on the same set of partition scenarios. This is a model; no real cluster software runs — the rule that makes the decision is applied directly. ```bash node - <<'EOF' // The cluster splits into groups under a network partition; the primary is always node 1. const scenarios = [ [2, [[1], [2]]], [3, [[1, 2], [3]]], [3, [[1], [2, 3]]], [4, [[1, 2], [3, 4]]], [5, [[1, 2, 3], [4, 5]]], [5, [[1, 2], [3, 4, 5]]], [5, [[1, 2], [3, 4], [5]]], ]; const row = (a, b, c, d) => console.log(String(a).padStart(5) + " | " + b.padEnd(7) + " | " + c.padEnd(28) + " | " + d); console.log("MAJORITY RULE: a group accepts writes only if it brings together"); console.log("more than half of the nodes."); console.log(); row("nodes", "split", "majority", "result"); console.log("------|---------|------------------------------|--------------------------"); for (const [n, groups] of scenarios) { const majority = groups.find((g) => g.length * 2 > n); const hasPrimary = majority && majority.includes(1); row(n, groups.map((g) => g.length).join("+"), majority ? majority.join(",") + " group (" + majority.length + "/" + n + ")" : "none", majority ? (hasPrimary ? "primary stays in place" : "failover; old primary stops") : "no side accepts writes"); } console.log(); console.log('NO-QUORUM RULE: "if I cannot see the primary, it must be dead, so I become primary".'); console.log(); row("nodes", "split", "primary count", "result"); console.log("------|---------|------------------------------|--------------------------"); for (const [n, groups] of scenarios) row(n, groups.map((g) => g.length).join("+"), groups.length + " primaries", groups.length > 1 ? "split brain" : "no problem"); EOF ``` ```text MAJORITY RULE: a group accepts writes only if it brings together more than half of the nodes. nodes | split | majority | result ------|---------|------------------------------|-------------------------- 2 | 1+1 | none | no side accepts writes 3 | 2+1 | 1,2 group (2/3) | primary stays in place 3 | 1+2 | 2,3 group (2/3) | failover; old primary stops 4 | 2+2 | none | no side accepts writes 5 | 3+2 | 1,2,3 group (3/5) | primary stays in place 5 | 2+3 | 3,4,5 group (3/5) | failover; old primary stops 5 | 2+2+1 | none | no side accepts writes NO-QUORUM RULE: "if I cannot see the primary, it must be dead, so I become primary". nodes | split | primary count | result ------|---------|------------------------------|-------------------------- 2 | 1+1 | 2 primaries | split brain 3 | 2+1 | 2 primaries | split brain 3 | 1+2 | 2 primaries | split brain 4 | 2+2 | 2 primaries | split brain 5 | 3+2 | 2 primaries | split brain 5 | 2+3 | 2 primaries | split brain 5 | 2+2+1 | 3 primaries | split brain ``` The lower table shows that a rule which looks reasonable on its own produces two primaries on every partition. The problem is that a node cannot tell "the other side is dead" apart from "I cannot see the other side" using only local information. The **quorum** rule brings that distinction in from somewhere else: at most one group can bring together more than half of the nodes, so granting write rights only to that group makes two primaries impossible. The upper table has three readings. In the third row the primary ends up in the minority and failover happens on the majority side; the old primary must **stop writing on its own**. In the second and fourth rows no group is in the majority and writing stops entirely — the cluster makes itself unavailable to prevent a data conflict. Availability is sacrificed for consistency here; this is not a flaw but a deliberate choice. The four-node row gives the most practical conclusion about cluster size: four nodes add no partition tolerance over three. Both tolerate losing a single node, but four nodes stop completely under a 2+2 split. This is why cluster sizes are chosen odd. In a two-node setup, adding a third voter that carries no data — a **witness** — achieves the same result cheaply. #### The Cost of Two Primaries Split brain's damage is not abstract. The block below genuinely produces a split: two copies of the same database each accept writes separately, and when the split closes the two sides are compared. ```bash rm -f side-a.db side-b.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT)`; const seed = new DatabaseSync("side-a.db"); seed.exec(SCHEMA); const insertSeed = seed.prepare( "INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES(?,?,?,?,?)"); seed.exec("BEGIN"); for (let i = 1; i <= 1000; i++) insertSeed.run(i % 400 + 1, i % 250 + 1, i % 3 + 1, "2025-06-01", "2025-06-15"); seed.exec("COMMIT"); seed.close(); // Moment of the split: both sides start from the same state. fs.copyFileSync("side-a.db", "side-b.db"); const sideA = new DatabaseSync("side-a.db"); const sideB = new DatabaseSync("side-b.db"); const open = (db, book, member, branch) => db.prepare( "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)") .run(book, member, branch, "2025-06-16"); // Side A: desks at branches 1 and 2. Side B: branch 3. for (let i = 0; i < 40; i++) open(sideA, 101 + i, 10 + i, (i % 2) + 1); for (let i = 0; i < 25; i++) open(sideB, 131 + i, 60 + i, 3); const count = (db, s) => db.prepare(s).get().c; console.log("after the split"); console.log(" side A rows".padEnd(19) + ": " + count(sideA, "SELECT COUNT(*) c FROM loan")); console.log(" side B rows".padEnd(19) + ": " + count(sideB, "SELECT COUNT(*) c FROM loan")); // The split closes: the two sides' records are compared. sideA.exec("ATTACH 'side-b.db' AS b"); const sameIdDiffRow = count(sideA, `SELECT COUNT(*) c FROM main.loan m JOIN b.loan x USING(id) WHERE m.book_id <> x.book_id OR m.member_id <> x.member_id`); const doubleLoanedBook = count(sideA, `SELECT COUNT(*) c FROM (SELECT book_id FROM main.loan WHERE returned IS NULL INTERSECT SELECT book_id FROM b.loan WHERE returned IS NULL)`); console.log(); console.log(" same id, different record".padEnd(33) + ": " + sameIdDiffRow); console.log(" book loaned out on both sides".padEnd(33) + ": " + doubleLoanedBook); // Merge attempt: side B's new records are inserted into side A. let conflicts = 0, inserted = 0; for (const rec of sideA.prepare("SELECT * FROM b.loan WHERE id > 1000").all()) { try { sideA.prepare("INSERT INTO main.loan(id,book_id,member_id,branch_id,pickup,returned)" + " VALUES(?,?,?,?,?,?)") .run(rec.id, rec.book_id, rec.member_id, rec.branch_id, rec.pickup, rec.returned); inserted++; } catch { conflicts++; } } console.log(); console.log("merge attempt: " + inserted + " inserted, " + conflicts + " id conflicts"); EOF ``` ```text after the split side A rows : 1040 side B rows : 1025 same id, different record : 25 book loaned out on both sides : 10 merge attempt: 0 inserted, 25 id conflicts ``` The results show two different kinds of trouble. Twenty-five id conflicts are a **technical** problem: both sides handed out the same numbers from the same sequence generator. It can be solved by assigning new numbers to the records, but the receipts, external system records, and reports carrying those old numbers become invalid. Ten books appearing loaned out on both sides is a **non-technical** problem. The same book looks like it was handed to two different members, and the database has no way to know which one is really not on the shelf. This is the limit of automatic merging: deciding which of two conflicting realities is correct is not a data problem, it is a business problem. This is why the cost of split brain is not comparable to the cost of an outage — an outage ends; diverged data stays. The majority rule is the first layer against this. The second layer is **fencing**: the minority-side old primary's ability to write is blocked from the outside. The standard ways are shutting the server down over the network or revoking its access to shared storage. The reason is that the minority node cannot be trusted to stop itself: a stuck process is, by the time it would make that decision, already unable to respond. #### The Trade-Off Between Detection Time and False Failover The cluster manager's settings collapse onto a single axis: how fast a decision gets made. A fast decision shortens the outage and raises the odds of mistaking a transient stall for a real failure. The block below measures this. Responsiveness and stall durations are **modeled**; no real server is being polled. Promotion, redirection, and reconnection cost is taken as a fixed ten seconds. ```bash node - <<'EOF' // Model: a 3600-second window. The primary server occasionally does not respond for // 1-4 seconds (transient stall); at second 3000 it genuinely fails. const WINDOW = 3600, FAILURE = 3000, STALL_PROBABILITY = 0.004; let seed = 20250616; const random = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648; const responsive = new Array(WINDOW).fill(true); let stalls = 0; for (let t = 0; t < FAILURE; t++) { if (random() < STALL_PROBABILITY) { const length = 1 + Math.floor(random() * 4); stalls++; for (let i = 0; i < length && t + i < FAILURE; i++) responsive[t + i] = false; t += length; } } for (let t = FAILURE; t < WINDOW; t++) responsive[t] = false; // Promotion, redirection, and application reconnection (model value). const FAILOVER_COST = 10; console.log("transient stalls: " + stalls + " times, each 1-4 s. " + "Real failure: second " + FAILURE + "."); console.log(); console.log("check | consecutive | false | detection | outage"); console.log("interval | failures | failover | time | (RTO)"); console.log("---------|-------------|----------|-----------|--------"); for (const [interval, k] of [[1, 1], [1, 2], [1, 3], [2, 3], [5, 2], [5, 3], [10, 3], [30, 3]]) { let consecutive = 0, falseFailover = 0, detection = null, triggered = false; for (let t = interval; t < WINDOW; t += interval) { if (responsive[t]) { consecutive = 0; triggered = false; continue; } consecutive++; if (consecutive < k || triggered) continue; triggered = true; if (t < FAILURE) falseFailover++; else if (detection === null) detection = t - FAILURE; } console.log(String(interval + " s").padStart(8) + " | " + String(k).padStart(11) + " | " + String(falseFailover).padStart(8) + " | " + (detection === null ? "-" : detection + " s").padStart(9) + " | " + (detection === null ? "-" : detection + FAILOVER_COST + " s").padStart(7)); } EOF ``` ```text transient stalls: 10 times, each 1-4 s. Real failure: second 3000. check | consecutive | false | detection | outage interval | failures | failover | time | (RTO) ---------|-------------|----------|-----------|-------- 1 s | 1 | 10 | 0 s | 10 s 1 s | 2 | 10 | 1 s | 11 s 1 s | 3 | 4 | 2 s | 12 s 2 s | 3 | 0 | 4 s | 14 s 5 s | 2 | 0 | 5 s | 15 s 5 s | 3 | 0 | 10 s | 20 s 10 s | 3 | 0 | 20 s | 30 s 30 s | 3 | 0 | 60 s | 70 s ``` The columns run in opposite directions. As the check interval grows and the number of consecutive failures required grows, false failover drops to zero and the outage grows to seventy seconds. Each row's one-hour window contains ten transient stalls; the most aggressive setting counts all of them as failures and performs ten unnecessary failovers. It matters to see that an unnecessary failover is not harmless. Every failover means data loss equal to the replication lag under asynchronous replication, every connection dropping, and the work of the old primary rejoining the cluster. Taking down a server that was slow for one second can produce an outage larger than the one it was meant to prevent. The setting choice is tied to the recovery time objective. If the objective is "at most one minute," a ten-second check interval is sufficient and carries no false-failover risk. If the objective is on the order of seconds, the sources of stalling need to be reduced; an aggressive setting, built on top of an unstable system, magnifies the instability. One task remains after failover: returning the old primary to the cluster as a standby server. Because of unsent changes, the old primary's history has diverged from the new primary's; it cannot reconnect as it is, unchanged. Most engines provide a tool that finds this divergence and rewinds past it; if one is not provided, the path is to rebuild the old primary from scratch using a base copy taken from the new primary. #### Summary - Switchover is a deliberate, lossless exchange between roles; failover is promoting a standby in place of an unreachable primary, and it loses data equal to the replication lag under asynchronous replication. - A node cannot tell "dead" apart from "invisible" using only local information; the majority rule derives that distinction from node count and makes two primaries impossible. - An even-numbered cluster size adds no resilience; under a split, neither side may reach a majority. A witness node makes the vote count odd without carrying data. - The id conflicts split brain leaves behind can be resolved technically; reality that diverged on both sides cannot, and that is the limit of automatic merging. - Fencing blocks the minority-side old primary's writes from the outside; the node cannot be trusted to stop itself. - As health checks tighten, detection time shortens and the odds that a transient stall produces a false failover rise; the setting is chosen against the recovery time objective. #### Next Step Failover's last step was redirecting applications to the new primary. That means the application connects not directly to the database, but through a layer in between. The same layer solves a second problem, one encountered every day even without a failure: when every application process opens its own connection, a few hundred processes produce more connections than the database can carry. The next lesson covers connection poolers: the real cost of opening a connection, how pool size relates to queue time, and the difference between session-level and transaction-level pooling. ### Connection Poolers Source: https://academia.sh/en/courses/database-administration/connection-poolers Managing connection count: the measured cost of opening a connection, the difference between session-level and transaction-level pooling, how session state inherited from the pool can breach row-level security, and how pool size relates to queue time. Failover's last step was redirecting applications to the new primary; that means the application connects not directly to the database, but through a layer in between. The same layer solves a second problem encountered every day, even without a failure. The problem is this: as the number of application servers grows, so does the number of connections. When every application instance opens its own connections, ten instances at twenty connections each add up to two hundred connections. A connection is not a cheap object for a database engine — each one consumes memory, requires a separate process or thread on most engines, and its cost continues even while it sits idle. Once the connection count crosses a certain threshold, the engine slows down, then stops accepting new connections. #### The Cost of a Connection Opening a connection looks like a single operation from a distance. Inside it there are several steps: establishing the transport-layer connection, negotiating an encrypted session, authentication, starting a process or thread on the server side, preparing session state. None of these read any data. The block below measures the **lowest** form of this cost: a file-based engine has no network, no encryption, no authentication, and no server-side process is started. What gets measured is only opening the file and reading schema information. On a server-based engine, steps on top of this cost get added; the measurement gives a floor, not a ceiling. The timings are machine-dependent. {/* run: skip */} ```bash rm -f pool.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const QUERY = "SELECT COUNT(*) c FROM loan WHERE member_id=?"; const N = 3000; const setup = new DatabaseSync("pool.db"); setup.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT)"); setup.exec("CREATE INDEX loan_member ON loan(member_id)"); const insert = setup.prepare("INSERT INTO loan(book_id,member_id,pickup) VALUES(?,?,?)"); setup.exec("BEGIN"); for (let i = 1; i <= 20000; i++) insert.run(i % 400, i % 250, "2025-06-01"); setup.exec("COMMIT"); setup.close(); const measure = (label, task) => { const t = process.hrtime.bigint(); task(); const ms = Number(process.hrtime.bigint() - t) / 1e6; console.log(label.padEnd(43) + " | " + ms.toFixed(1).padStart(9) + " | " + (ms * 1000 / N).toFixed(1).padStart(11)); }; console.log("path".padEnd(43) + " | total ms | us per request"); console.log("-".repeat(43) + "-|-----------|--------------"); measure("new connection + statement each request", () => { for (let i = 0; i < N; i++) { const db = new DatabaseSync("pool.db"); db.prepare(QUERY).get(i % 250); db.close(); } }); const shared = new DatabaseSync("pool.db"); measure("shared connection, statement each request", () => { for (let i = 0; i < N; i++) shared.prepare(QUERY).get(i % 250); }); const prepared = shared.prepare(QUERY); measure("shared connection, prepared statement", () => { for (let i = 0; i < N; i++) prepared.get(i % 250); }); EOF ``` The output varies from machine to machine; one sample run: ```text path | total ms | us per request --------------------------------------------|-----------|-------------- new connection + statement each request | 91.5 | 30.5 shared connection, statement each request | 25.3 | 8.4 shared connection, prepared statement | 14.2 | 4.7 ``` The same query ran three ways, and in this run the per-request time changed by a factor of about six and a half; the ratio will differ from machine to machine. The query itself does the same work in all three rows; what differs is the setup surrounding the query. The gap between the second and third rows shows a separate point: when a **prepared statement** is reused, parsing and planning work is not repeated. This is the operating side of the same gain covered under plan caching in the Advanced SQL course. A **connection pool** carries this table from the first row to the third. The pool holds a set of already-open connections; when the application requests one, it borrows it from the pool and returns it when the work is done. The connection does not close — the next request finds it already there. #### Pooling Levels When the pool takes the connection back determines what the application has to give up. There are three levels. **Session-level pooling** hands the connection to the same client until the client connection closes. Nothing changes from the application's point of view; every session-bound feature works. Its cost is that a database connection is needed for as many connections as there are concurrent clients — the connection count problem is not fully solved. **Transaction-level pooling** hands out the connection only for the duration of a transaction; once the transaction ends the connection returns to the pool and can go to another client. This level is what lets a small number of connections serve a large number of clients. Its cost is that everything tied to the session becomes unreliable: temporary tables, session variables, session-level locks, and prepared statements attached to the connection. **Statement-level pooling** takes the connection back after every statement. In high-client-count setups it gives the highest sharing, but because a multi-statement transaction cannot be written, the application cannot use transactions. Transaction-level pooling's constraint is not merely a performance detail; it can produce a security consequence. The block below shows this on the mechanism from the Row-Level Security lesson: the policy looks at a session variable, while the connection changes hands between two requests. ```bash rm -f desk.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const db = new DatabaseSync("desk.db"); // the pool's only connection db.exec(` CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, branch_id INT, pickup TEXT); CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL); `); const insert = db.prepare("INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); db.exec("BEGIN"); for (let i = 1; i <= 900; i++) insert.run(i % 400, i % 250, i <= 500 ? 1 : (i <= 800 ? 2 : 3), "2025-06-01"); db.exec("COMMIT"); // The policy looks at a session variable (the mechanism from the Row-Level Security lesson). const visible = () => db.prepare( "SELECT COUNT(*) c FROM loan WHERE branch_id=" + "(SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id')").get().c; const setBranch = (branch) => db.prepare( "INSERT INTO session VALUES('branch_id',?)" + " ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(String(branch)); const actual = (b) => db.prepare("SELECT COUNT(*) c FROM loan WHERE branch_id=?").get(b).c; console.log("actual row count per branch: 1 -> " + actual(1) + ", 2 -> " + actual(2)); console.log(); // Request 1: branch 1's desk takes the connection. setBranch(1); console.log("request 1 (branch 1) visible rows".padEnd(38) + ": " + visible()); // The connection is returned to the pool. The pool does not clear session state. console.log("request 2 (branch 2), session not set".padEnd(38) + ": " + visible() + " <- branch 1's rows"); // Same order, but this time the pool clears session state on handoff. db.exec("DELETE FROM session"); console.log("request 2 (branch 2), pool cleared".padEnd(38) + ": " + visible() + " <- no rows visible"); setBranch(2); console.log("request 2 (branch 2), session set".padEnd(38) + ": " + visible()); EOF ``` ```text actual row count per branch: 1 -> 500, 2 -> 300 request 1 (branch 1) visible rows : 500 request 2 (branch 2), session not set : 500 <- branch 1's rows request 2 (branch 2), pool cleared : 0 <- no rows visible request 2 (branch 2), session set : 300 ``` The second line is the problem itself. Branch two's desk should see three hundred rows, but it sees five hundred; what it sees are another branch's loan records. The policy is written correctly, the query is correct, the data is correct — the only thing wrong is that the connection is carrying the previous user's session value. The third line shows the fix: the pool clears session state as it hands off the connection. In the cleared state, the policy returns no rows at all. This behavior is the intended one — **failing closed**: when information is missing, no data is shown. The application resetting the session value at the start of every request produces the correct result on the fourth line. The operating rule that follows applies to every setup using transaction-level pooling: no piece of session-bound information is written assuming it will survive across requests, and the pool's handoff cleanup is verified against its configuration. #### Pool Size and the Queue Pool size determines how many requests can enter the database at the same time. If it is too small, requests wait in a queue; if it is too large, the number of concurrently running transactions on the engine grows and the transactions slow each other down — because of lock contention, cache sharing, and scheduler overhead. The block below studies this by simulation. Arrivals, base transaction time, and the contention coefficient are **model values**; no real database is being run. The shape of the contention is also an assumption: as the number of concurrent transactions exceeds the core count, each transaction's time grows linearly. On a real system this curve is found by measurement. What the model shows is that the curve **has a best point**. ```bash node - <<'EOF' // Model: requests wait for a connection from the pool, then run against the database. // As the number of concurrent transactions exceeds the core count, every transaction // slows down (contention). const BASE = 10, CORES = 8, CONTENTION = 0.25; // ms, count, coefficient const RATE = 700, DURATION = 30000, STEP = 0.25; // requests/s, ms, ms const TIMEOUT = 200; // ms let seed = 20250616; const random = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648; const arrivals = []; for (let t = 0; t < DURATION; ) { t += -Math.log(1 - random()) * 1000 / RATE; if (t < DURATION) arrivals.push(t); } console.log("load: " + RATE + " req/s, base transaction time " + BASE + " ms, " + CORES + " cores, " + arrivals.length + " requests"); console.log(); console.log("pool | completed | queued | active | total | time"); console.log("size | requests | (avg ms) | (avg ms)| (avg ms)| outs"); console.log("------|-----------|----------|---------|---------|-------"); for (const poolSize of [2, 4, 8, 16, 32, 64]) { const queue = [], active = []; let nextIdx = 0, completed = 0, queueTotal = 0, transactionTotal = 0, timeouts = 0; for (let t = 0; t < DURATION * 1.5; t += STEP) { while (nextIdx < arrivals.length && arrivals[nextIdx] <= t) queue.push(arrivals[nextIdx++]); while (queue.length && active.length < poolSize) { const arrival = queue.shift(); if (t - arrival > TIMEOUT) { timeouts++; continue; } active.push({ wait: t - arrival, remaining: BASE, started: t }); } const slowdown = 1 + CONTENTION * Math.max(0, active.length - CORES); for (const tx of active) tx.remaining -= STEP / slowdown; for (let i = active.length - 1; i >= 0; i--) { if (active[i].remaining > 0) continue; queueTotal += active[i].wait; transactionTotal += t - active[i].started; completed++; active.splice(i, 1); } if (nextIdx >= arrivals.length && !queue.length && !active.length) break; } console.log(String(poolSize).padStart(5) + " | " + String(completed).padStart(9) + " | " + (queueTotal / completed).toFixed(1).padStart(8) + " | " + (transactionTotal / completed).toFixed(1).padStart(7) + " | " + ((queueTotal + transactionTotal) / completed).toFixed(1).padStart(7) + " | " + String(timeouts).padStart(6)); } EOF ``` ```text load: 700 req/s, base transaction time 10 ms, 8 cores, 21200 requests pool | completed | queued | active | total | time size | requests | (avg ms) | (avg ms)| (avg ms)| outs ------|-----------|----------|---------|---------|------- 2 | 6040 | 197.4 | 9.8 | 207.2 | 15160 4 | 12080 | 196.4 | 9.8 | 206.1 | 9120 8 | 21200 | 3.9 | 9.8 | 13.6 | 0 16 | 16116 | 192.9 | 29.7 | 222.6 | 5084 32 | 13823 | 193.5 | 69.6 | 263.1 | 7377 64 | 12919 | 190.9 | 149.2 | 340.1 | 8281 ``` The table shows two distinct failure modes in the same column. In the pools sized two and four, time spent active is at its lowest value; the problem is in the queue. What the pool lets through is less than what comes in, and most requests time out. This is **pool saturation**. At sixteen and above, the queue is just as bad, but for a different reason: time spent active has grown by a factor of three to fifteen. The pool let more requests in, they slowed each other down inside, and total completed work dropped. Growing the pool made things worse here. The pool sized eight is far from both extremes: the wait is four milliseconds, total response is thirteen milliseconds, and there are no timeouts. This number is the same as the core count, and that is not a coincidence — the best pool size sits close to the amount of work the engine can genuinely run in parallel. In disk-wait-dominated workloads this number runs above the core count; in CPU-dominated workloads it stays at the core count. The only way to find the right value is to measure it. Pool size is thought about not for a single application instance but for the system as a whole. Total connection count is the product of pool size and the number of application instances; in a ten-instance setup, twenty connections per instance arrives at the engine as two hundred connections. That application-side pools do not see this product is the main reason for keeping a single pooler in the middle. One last setting is idle connection lifetime. When load drops, shrinking the pool frees resources; reopening every time brings back the cost from the first table. The balance between the two extremes is set against how load varies over the course of a day. #### Summary - Opening a connection carries a setup cost independent of the query itself; in the sample run, the same query varied by a factor of about six and a half between the new connection and prepared statement paths. - The pool lends out already-open connections and removes this cost, capping the number of connections reaching the engine. - Session-level pooling does not constrain the application but does not reduce connection count; transaction-level pooling reduces it and makes everything session-bound unreliable. - A session variable inherited from the pool can let a row-level security policy show another account's rows; the pool's handoff cleanup must be verified. - If pool size is too small the queue grows; if too large, concurrent transactions slow each other down. The best value sits close to the amount of work the engine can run in parallel, and it is found by measurement. - Total connection count is the product of pool size and the number of application instances. #### Next Step Every decision in this lesson rested on a measurement: connection cost, queue time, concurrent transaction count, the share of requests that time out. None of these are visible on their own; pool saturation is felt on the application side only as "the database is slow" and is usually looked for in the wrong place. The next lesson covers monitoring: which magnitudes to collect, why the average is misleading, what percentiles show, catching slow queries with a threshold, how wait events name the bottleneck, and when an alert is meaningful. ### Monitoring and Alerting Source: https://academia.sh/en/courses/database-administration/monitoring-and-alerting Knowing the database's state through numbers: what the difference between wall-clock time and CPU time means, the distribution of query times and percentiles, what a threshold-based slow query log misses, measuring lock wait, and when an alert is meaningful. Every decision in the previous lesson rested on a measurement: connection cost, queue time, concurrent transaction count, the share of requests that time out. None of these are visible on their own. Pool saturation is felt on the application side only as "the database is slow," and it is usually looked for in the wrong place. Monitoring's job is to fill that gap: turning the system's state into numbers continuously, not only at the moment of failure. This lesson covers which numbers to collect, how to read them, and when an alert is meaningful. #### What a Query's Duration Measures The most basic magnitude is how long a statement takes; but "duration" is not a single number. The block below measures two queries on the same table. The timings depend on the machine and the filesystem; yours will come out different. ```bash rm -f measurement.db sqlite3 measurement.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<400000) INSERT INTO loan(id,book_id,member_id,branch_id,pickup) SELECT n,(n%400)+1,(n%900)+1,(n%3)+1,'2025-06-01' FROM s; CREATE INDEX loan_member ON loan(member_id); SQL sqlite3 measurement.db <<'SQL' .header on .timer on SELECT COUNT(*) AS indexed FROM loan WHERE member_id=42; SELECT COUNT(*) AS scanned FROM loan WHERE pickup LIKE '2025-06-01'; SQL ``` {/* mask: (?<=Run Time: real )[0-9.]+ user [0-9.]+ sys [0-9.]+ */} ```text indexed 445 Run Time: real 0.000 user 0.000000 sys 0.000104 scanned 400000 Run Time: real 0.025 user 0.024140 sys 0.001362 ``` Three numbers say three different things. **Wall-clock time** (`real`) is the time elapsed between the query's start and end — this is what the user waits for. **CPU time** (`user` and `sys`) is the time genuinely spent computing for that query. In the second query, wall-clock time is close to CPU time: the query was busy the whole time it read four hundred thousand rows, waiting for nothing. This gives monitoring its single most useful rule: **if wall-clock time is noticeably larger than CPU time, the difference is wait.** The wait could be data coming from disk, a lock being released, the log being written to disk, or a response coming over the network. Monitoring that looks only at CPU time cannot see the most common causes of slowness. Most engines surface this in more detail: which event each session is currently waiting on is recorded. Wait events are grouped into classes — lock wait, reading from a data file, writing the log to disk, waiting for the client to send data. The distribution across these classes names the bottleneck directly; guesses made without measuring are usually wrong. #### Average, Distribution, and Total Impact A single run's duration is one number; what operating requires is the distribution of thousands of runs. The block below simulates a day's workload: four different query shapes run with different call counts, and each run's duration is recorded. The member distribution is deliberately skewed: member number one is the library's bulk loan account and has a large number of records. Timings depend on the environment; what matters is the relationship between the columns. {/* run: skip */} ```bash rm -f monitoring.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const db = new DatabaseSync("monitoring.db"); db.exec(`CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT)`); // Member distribution is skewed: member number one is the library's bulk loan account. const insert = db.prepare("INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); db.exec("BEGIN"); for (let i = 1; i <= 200000; i++) insert.run(i % 400 + 1, i % 12 === 0 ? 1 : (i % 900) + 2, (i % 3) + 1, "2025-06-01"); db.exec("COMMIT"); db.exec("CREATE INDEX loan_member ON loan(member_id)"); const log = []; // (signature, duration) for each run const run = (signature, stmt, ...p) => { const t = process.hrtime.bigint(); stmt.all(...p); log.push([signature, Number(process.hrtime.bigint() - t) / 1e6]); }; const memberQuery = db.prepare("SELECT id,book_id,pickup FROM loan WHERE member_id=?"); const keyQuery = db.prepare("SELECT book_id,member_id FROM loan WHERE id=?"); const branchQuery = db.prepare("SELECT COUNT(*) c FROM loan WHERE branch_id=? AND returned IS NULL"); const reportQuery = db.prepare( "SELECT branch_id, COUNT(*) c FROM loan WHERE pickup>=? GROUP BY branch_id"); for (let i = 0; i < 2000; i++) run("member records", memberQuery, i % 12 === 0 ? 1 : i % 900 + 2); for (let i = 0; i < 20000; i++) run("lookup by key", keyQuery, i % 200000 + 1); for (let i = 0; i < 200; i++) run("branch count", branchQuery, (i % 3) + 1); for (let i = 0; i < 20; i++) run("daily report", reportQuery, "2025-01-01"); const percentile = (arr, p) => arr[Math.min(arr.length - 1, Math.floor(arr.length * p))]; const totalTime = log.reduce((t, k) => t + k[1], 0); console.log("signature | calls | average | p50 | p95 | p99 | total | share"); console.log("----------------|-------|----------|-------|-------|-------|---------|------"); for (const signature of [...new Set(log.map((k) => k[0]))]) { const durations = log.filter((k) => k[0] === signature).map((k) => k[1]).sort((a, b) => a - b); const sum = durations.reduce((t, v) => t + v, 0); console.log(signature.padEnd(15) + " | " + String(durations.length).padStart(5) + " | " + (sum / durations.length).toFixed(3).padStart(8) + " | " + percentile(durations, 0.5).toFixed(2).padStart(5) + " | " + percentile(durations, 0.95).toFixed(2).padStart(5) + " | " + percentile(durations, 0.99).toFixed(2).padStart(5) + " | " + (sum.toFixed(0) + " ms").padStart(7) + " | %" + (100 * sum / totalTime).toFixed(1).padStart(4)); } const THRESHOLD = 10; const slow = log.filter((k) => k[1] > THRESHOLD); const slowTime = slow.reduce((t, k) => t + k[1], 0); console.log(); console.log("total runs: " + log.length + ", total time: " + totalTime.toFixed(0) + " ms"); console.log(THRESHOLD + " ms threshold exceeded by: " + slow.length + " runs, " + slowTime.toFixed(0) + " ms (%" + (100 * slowTime / totalTime).toFixed(1) + ")"); EOF ``` The output varies from machine to machine; one sample run: ```text signature | calls | average | p50 | p95 | p99 | total | share ----------------|-------|----------|-------|-------|-------|---------|------ member records | 2000 | 0.653 | 0.09 | 6.31 | 7.26 | 1306 ms | %49.1 lookup by key | 20000 | 0.003 | 0.00 | 0.00 | 0.01 | 69 ms | % 2.6 branch count | 200 | 4.131 | 4.11 | 4.53 | 5.13 | 826 ms | %31.0 daily report | 20 | 23.051 | 22.92 | 24.14 | 24.14 | 461 ms | %17.3 total runs: 22220, total time: 2663 ms 10 ms threshold exceeded by: 22 runs, 483 ms (%18.1) ``` The table gives three separate lessons about monitoring at once. **Average does not describe the distribution.** The member records query's average is about two-thirds of a millisecond; its median is roughly a seventh of that. The gap comes from the fact that one call in twelve goes to the bulk loan account. Average blends these two separate behaviors into a single number and describes neither correctly. **Percentiles** preserve the distinction: p50 is what the ordinary user sees, p95 and p99 describe the tail. **Slow is not the same as expensive.** The daily report is the slowest query in a single run; it produces about a sixth of the total load. The member records query is fast on every run and produces almost half the total load. The first place to look when speeding up the system is not the query with the highest average duration, but the one with the highest **call count times duration**. **A threshold-based slow query log sees only one slice.** The ten-millisecond threshold caught about eighteen percent of the workload; the remaining roughly eighty-two percent stayed invisible. A threshold is valuable for finding statements that are pathological on their own; it does not tell where the workload's weight goes. For that, every run needs to be grouped by its **query fingerprint** — the statement text with its parameters stripped, which gathers every run of the same shape into a single line. #### Measuring the Wait A query's own duration is not the whole of the response time. The block below measures this directly: the same update statement runs first on an idle system, then while another process is holding a lock on the same table. The second duration depends on how long the process holding the lock runs. ```bash rm -f wait.db lock.flag node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const { spawn } = require("node:child_process"); const fs = require("node:fs"); const wait = (ms) => Atomics.wait(new Int32Array(new SharedArrayBuffer(4)), 0, 0, ms); const db = new DatabaseSync("wait.db"); db.exec("PRAGMA busy_timeout=5000"); db.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, returned TEXT)"); const insert = db.prepare("INSERT INTO loan(book_id,member_id) VALUES(?,?)"); db.exec("BEGIN"); for (let i = 1; i <= 5000; i++) insert.run(i % 400, i % 250); db.exec("COMMIT"); const write = () => { const t = process.hrtime.bigint(); db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE loan SET returned='2025-06-30' WHERE id=?").run(7); db.exec("COMMIT"); return Number(process.hrtime.bigint() - t) / 1e6; }; const uncontended = write(); // A second process holds a lock on the same table for 1200 ms. const childCode = ` const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const db = new DatabaseSync("wait.db"); db.exec("PRAGMA busy_timeout=5000"); db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE loan SET returned='2025-06-29' WHERE id=?").run(9); fs.writeFileSync("lock.flag", "1"); Atomics.wait(new Int32Array(new SharedArrayBuffer(4)), 0, 0, 1200); db.exec("COMMIT"); `; spawn(process.execPath, ["-e", childCode], { stdio: "ignore" }); while (!fs.existsSync("lock.flag")) wait(10); const contended = write(); console.log("uncontended write".padEnd(22) + ": " + uncontended.toFixed(1) + " ms"); console.log("same write under lock".padEnd(22) + ": " + contended.toFixed(1) + " ms"); console.log("share of the wait".padEnd(22) + ": %" + (100 * (contended - uncontended) / contended).toFixed(2)); fs.rmSync("lock.flag", { force: true }); EOF ``` {/* mask: (?<=: )[0-9.]+(?= ms) */} {/* mask: (?<=%)[0-9.]+$ */} ```text uncontended write : 0.0 ms same write under lock : 1196.8 ms share of the wait : %100.00 ``` Same statement, same row, same data; the duration grew by several orders of magnitude, and all of the growth came from waiting. No measurement collected about the statement itself can explain it: the query plan is the same, the number of rows read is the same, the CPU time is the same. The explanation lies only in the answer to "what was this session waiting for." In operation this gap shows up as: response time measured on the application side grows, while query durations on the database side are normal. The two sides point at each other. Monitoring wait events settles this argument — the time is being spent not on the side waiting for the lock, but in the long transaction **holding** it. #### When an Alert Is Meaningful Not every collected number should turn into an alert. A working alert satisfies three conditions. **It has a threshold, and the threshold is derived from a baseline.** "Query time is high" is not an alert; "p95 is three times its normal value over the last hour" is. The baseline is obtained by measuring during a period known to be healthy. **It has a persistence condition.** A momentary spike does not produce an alert; it has to hold for a given window. This is the same trade-off as the failover setting in the previous lesson: as sensitivity rises, the number of false alerts rises with it. **It corresponds to an action.** If there is no work to do in response, the alert only produces noise and, over time, gets ignored. Alert fatigue is the most common failure mode in monitoring setups: among hundreds of alerts, the real one goes unnoticed. A useful distinction for choosing which magnitudes to alert on is the difference between **symptom** and **cause**. A symptom is what the user feels: response time, error rate, the number of failed loan transactions. A cause is the internal state producing that symptom: replication lag, pool saturation, disk fullness, a long-running transaction. Alerts are built on symptoms — nobody is woken up when the user is unaffected. Causes are kept on dashboards; that is where to look once an alert arrives. Outside this distinction there is one more class: situations that call for an alert even though the user is not yet affected. The disk having two days left before it fills up, backups not having run for three days, replication lag exceeding the recovery point objective. These point to a resource running out, and need to be addressed before an effect forms. Finally, monitoring itself has a scope. Every lesson in this topic left behind a magnitude worth watching: the backup's age and validation date, how long the recovery drill takes, replication lag, cluster member state, idle connection count in the pool, and queue time. An unmonitored guarantee is not a guarantee. #### Summary - The difference between wall-clock time and CPU time is wait; monitoring that looks only at CPU time cannot see the most common causes of slowness. - Average hides skewed distributions; p50 describes the ordinary case, p95 and p99 describe the tail. - Where the workload's weight goes is found by grouping call count times duration by query fingerprint; the slowest query is usually not the most expensive one. - A threshold-based slow query log catches pathological statements on its own but misses most of the total load. - Lock wait never shows up in a statement's own measurements; the time is spent in the transaction holding the lock, and it is found only by monitoring wait events. - A meaningful alert has a threshold derived from a baseline, a persistence condition, and a corresponding action; alerts are built on symptoms, dashboards on causes. #### Next Step Monitoring shows what the system does under its ordinary load. Some work sits outside that ordinary load and deliberately stresses the system: importing an archive, loading a membership list from another institution, exporting a year's loan records. This work moves millions of rows at once and takes hours when done with row-by-row writing habits. The next lesson covers bulk data loading: how the transaction boundary affects duration, the cost of indexes during load, comparing a prepared statement against an import command, and extracting bad rows without stopping the load. ### Bulk Data Loading Source: https://academia.sh/en/courses/database-administration/bulk-data-loading The cost of high-volume import and export: the effect of the transaction boundary on load speed, whether indexes are built during or after loading, extracting bad rows with a staging table, and the shape of export. Monitoring shows what the system does under its ordinary load. Some work sits outside that ordinary load and deliberately stresses the system: importing an archive, loading a membership list from another institution, exporting a year's loan records. This work moves millions of rows at once. The point where bulk loading departs from ordinary writing is this: in ordinary writing the cost of a single row is negligible; in bulk loading, every per-row cost gets multiplied by the row count. This lesson covers where those multipliers come from and how they are removed. #### The Cost of the Transaction Boundary Every commit requires the engine to make the change durable: the log is written to disk, and the write is verified to have genuinely completed. This step was covered in the course's Engine Architecture topic, presented there as a single row's guarantee. In bulk loading, that same step turns into a cost repeated once per row. The block below loads the same thirty thousand rows with five different batch sizes. Absolute durations depend on the machine, the disk, and the filesystem; what matters is the ratio between the rows. {/* run: skip */} ```bash rm -f loading.db loading.db-journal node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const N = 30000; const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)`; const measure = (label, batch) => { fs.rmSync("loading.db", { force: true }); fs.rmSync("loading.db-journal", { force: true }); const db = new DatabaseSync("loading.db"); db.exec("PRAGMA synchronous=FULL"); db.exec(SCHEMA); const insert = db.prepare( "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); const t = process.hrtime.bigint(); for (let i = 0; i < N; i += batch) { if (batch > 1) db.exec("BEGIN"); for (let j = i; j < Math.min(i + batch, N); j++) insert.run(j % 400 + 1, j % 900 + 1, j % 3 + 1, "2025-06-01"); if (batch > 1) db.exec("COMMIT"); } const ms = Number(process.hrtime.bigint() - t) / 1e6; db.close(); console.log(label.padEnd(26) + " | " + String(Math.ceil(N / batch)).padStart(6) + " | " + (ms.toFixed(0) + " ms").padStart(9) + " | " + Math.round(N / (ms / 1000)).toLocaleString("en-US").padStart(11)); }; console.log("path | txns | duration | rows/second"); console.log("---------------------------|--------|-----------|------------"); measure("one transaction per row", 1); measure("batches of 100 rows", 100); measure("batches of 1,000 rows", 1000); measure("batches of 10,000 rows", 10000); measure("single transaction", N); EOF ``` The output varies from machine to machine; one sample run: ```text path | txns | duration | rows/second ---------------------------|--------|-----------|------------ one transaction per row | 30000 | 501 ms | 59,869 batches of 100 rows | 300 | 15 ms | 1,956,543 batches of 1,000 rows | 30 | 9 ms | 3,184,615 batches of 10,000 rows | 3 | 9 ms | 3,234,211 single transaction | 1 | 9 ms | 3,184,390 ``` The gap between one transaction per row and a single transaction is more than fifty times in this run; the ratio varies widely with the disk and the filesystem. This run was taken on a filesystem that lives in memory; there, a commit does not wait for the write to reach disk, so the gap on a real disk can be larger than the table shows. The data loaded is the same, the rows written are the same, the work done is the same — the only difference is how many times a commit happened. The shape of the curve also matters. Most of the gain is obtained in the first step, the move to batches of a hundred; anything past a thousand gives a small further improvement. This does not mean the batch size should be chosen as large as possible. A single giant transaction carries three costs: if it fails, everything restarts from the beginning, rollback information accumulates for the duration of the transaction, and a long-running write delays the dead-row cleanup covered in the course's Engine Architecture topic. A batch size somewhere between one hundred thousand and one million rows gives nearly all of the measured gain while bounding all three costs. The batch boundary is also a progress point. At the end of every batch, how many rows have loaded is known; if loading is interrupted, it can resume from where it left off. This only requires the incoming data's order to be deterministic and the last loaded key to be recorded. #### The Cost of Indexes During Loading The second multiplier is indexes. Every row added to the table also adds an entry to every index, and that entry has to be placed at the right position in the tree. When rows do not arrive sorted by the index key, every insert touches a random location in the tree. The alternative is building indexes after loading finishes: the engine then sorts all the keys at once and builds the tree from the bottom up, in order. ```bash rm -f indexed.db indexed.db-journal node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const fs = require("node:fs"); const N = 200000; const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)`; const INDEXES = ["CREATE INDEX loan_member ON loan(member_id)", "CREATE INDEX loan_book ON loan(book_id)", "CREATE INDEX loan_branch_pickup ON loan(branch_id, pickup)"]; const elapsed = (task) => { const t = process.hrtime.bigint(); task(); return Number(process.hrtime.bigint() - t) / 1e6; }; const measure = (label, indexFirst) => { fs.rmSync("indexed.db", { force: true }); fs.rmSync("indexed.db-journal", { force: true }); const db = new DatabaseSync("indexed.db"); db.exec(SCHEMA); if (indexFirst) for (const d of INDEXES) db.exec(d); const insert = db.prepare( "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); const loadTime = elapsed(() => { db.exec("BEGIN"); for (let i = 0; i < N; i++) insert.run(i % 400 + 1, i % 900 + 1, i % 3 + 1, "2025-06-01"); db.exec("COMMIT"); }); const indexTime = indexFirst ? 0 : elapsed(() => { for (const d of INDEXES) db.exec(d); }); db.close(); console.log(label.padEnd(24) + " | " + (loadTime.toFixed(0) + " ms").padStart(9) + " | " + (indexTime ? indexTime.toFixed(0) + " ms" : "-").padStart(9) + " | " + ((loadTime + indexTime).toFixed(0) + " ms").padStart(9) + " | " + (Math.round(fs.statSync("indexed.db").size / 1024) + " KB").padStart(9)); }; console.log(N.toLocaleString("en-US") + " rows, three indexes"); console.log(); console.log("path | load | index | total | file"); console.log("-------------------------|-----------|-----------|-----------|----------"); measure("indexes exist beforehand", true); measure("indexes added afterward", false); EOF ``` {/* mask: [ ]*[0-9]+(?= ms) */} ```text 200,000 rows, three indexes path | load | index | total | file -------------------------|-----------|-----------|-----------|---------- indexes exist beforehand | 384 ms | - | 384 ms | 14128 KB indexes added afterward | 58 ms | 82 ms | 140 ms | 13352 KB ``` Load time dropped by more than six-fold in this run; even with index-building time added in, the total fell to about a third of what it was. The file also came out smaller: an index built in bulk fills its pages tightly, while an index grown row by row leaves gaps behind because of page splits. This gap is the same thing covered under bloat in this course's Indexes and Partitioning topic, in the lesson on index maintenance. The cost of this method is that the table's read performance drops for as long as the indexes are absent. On an empty table receiving its first load, this cost does not exist. When a large transfer is made into a table that already exists and is in use, dropping the indexes is not an option; in that case the batch size is tuned instead, and the load is scheduled for a quiet hour. The same reasoning applies to constraints. A foreign key check performs a lookup on every row; a uniqueness constraint requires a check on every row. Most engines allow constraints to be deferred to the end of the transaction; a deferred check is not done per row, it is done once. #### Staging Table and Bad Rows Data coming from outside does not arrive clean. A single bad row stops a load that writes directly to the target table in the middle of the operation, and the work done up to that point is rolled back. A load that stops at row one thousand three hundred of twenty thousand both wastes time and does not show the whole of the problem — the bad rows still further down have not been seen yet. The standard solution is a **staging table**: the incoming file is taken in as is, into a table where every column is text and no constraint is applied. Validation happens by query after the data is inside the database. Valid rows are moved to the target; invalid ones are set aside along with the reason. ```bash rm -f transfer.db loans_incoming.csv clean.csv # Incoming file: a 20,000-row archive transfer, containing bad rows. node - <<'EOF' > loans_incoming.csv console.log("id,book_id,member_id,branch_id,pickup"); for (let i = 1; i <= 20000; i++) { if (i === 77) { console.log("seventyseven,12,34,1,2025-05-04"); continue; } // not a number if (i === 512) { console.log("512,12,34,1,04.05.2025"); continue; } // date format if (i === 900) { console.log("900,12,99999,1,2025-05-04"); continue; } // nonexistent member if (i === 1301) { console.log("1301,12,34,1"); continue; } // missing field console.log([i, (i % 400) + 1, (i % 900) + 1, (i % 3) + 1, "2025-05-04"].join(",")); } EOF sqlite3 transfer.db <<'SQL' CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL); WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<900) INSERT INTO member(id,name) SELECT n,'Member-'||n FROM s; CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL REFERENCES member(id), branch_id INT NOT NULL, pickup TEXT NOT NULL); -- Staging table: every column is text, no constraints. CREATE TABLE staging(id TEXT, book_id TEXT, member_id TEXT, branch_id TEXT, pickup TEXT); CREATE TABLE rejected(row_text TEXT, reason TEXT); SQL sqlite3 transfer.db ".import --csv --skip 1 loans_incoming.csv staging" echo "rows loaded into staging: $(sqlite3 transfer.db 'SELECT COUNT(*) FROM staging;')" sqlite3 -box -header transfer.db <<'SQL' INSERT INTO rejected(row_text, reason) SELECT id || ',' || IFNULL(book_id,'') || ',' || IFNULL(member_id,'') || ',' || IFNULL(branch_id,'') || ',' || IFNULL(pickup,''), CASE WHEN id GLOB '*[^0-9]*' OR member_id GLOB '*[^0-9]*' THEN 'non-numeric field' WHEN pickup IS NULL OR pickup NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' THEN 'date format' WHEN NOT EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER)) THEN 'nonexistent member' END FROM staging WHERE id GLOB '*[^0-9]*' OR member_id GLOB '*[^0-9]*' OR pickup IS NULL OR pickup NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' OR NOT EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER)); INSERT INTO loan(id,book_id,member_id,branch_id,pickup) SELECT CAST(id AS INTEGER), CAST(book_id AS INTEGER), CAST(member_id AS INTEGER), CAST(branch_id AS INTEGER), pickup FROM staging WHERE id NOT GLOB '*[^0-9]*' AND member_id NOT GLOB '*[^0-9]*' AND pickup GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' AND EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER)); SELECT (SELECT COUNT(*) FROM staging) AS incoming, (SELECT COUNT(*) FROM loan) AS accepted, (SELECT COUNT(*) FROM rejected) AS set_aside; SELECT reason, COUNT(*) AS rows FROM rejected GROUP BY reason ORDER BY rows DESC; SQL # Export: a file in the same format is produced. sqlite3 transfer.db <<'SQL' .mode csv .headers on .output clean.csv SELECT id, book_id, member_id, branch_id, pickup FROM loan WHERE branch_id=1; SQL echo "rows written out: $(($(wc -l < clean.csv) - 1))" head -2 clean.csv ``` ```text loans_incoming.csv:1302: expected 5 columns but found 4 - filling the rest with NULL rows loaded into staging: 20000 ┌──────────┬──────────┬───────────┐ │ incoming │ accepted │ set_aside │ ├──────────┼──────────┼───────────┤ │ 20000 │ 19996 │ 4 │ └──────────┴──────────┴───────────┘ ┌────────────────────┬──────┐ │ reason │ rows │ ├────────────────────┼──────┤ │ date format │ 2 │ │ nonexistent member │ 1 │ │ non-numeric field │ 1 │ └────────────────────┴──────┘ rows written out: 6665 id,book_id,member_id,branch_id,pickup 3,4,4,1,2025-05-04 ``` The load did not stop. All twenty thousand rows entered the database, nineteen thousand nine hundred ninety-six made it to the target, and four rows were set aside along with a reason. The list of set-aside rows can be corrected and reloaded; this is far cheaper than repeating the entire transfer over one bad row. The output's first line is instructive on its own. The row with the missing field was met with a warning by the import tool and the missing column was filled with a null value — meaning it was not silently rejected, it was silently **accepted**. Without the validation layer, this row would have entered the target with an empty pickup date. How import tools handle a malformed row varies by engine and by tool; which behavior applies is never assumed, it is tested. Export is the same contract in the opposite direction: column order, delimiter, header row, and date format are set for the other side. If both directions of a transfer are bound to the same format definition, the file handed out can be tested by loading it back into its own staging table. #### Other Decisions During Loading Three more settings determine load time, and all three carry the same trade-off: temporarily reducing durability. **Log setting.** Some engines offer a mode that writes less to the log during bulk loading. The gain is large; in exchange, if a crash happens during loading, the table can end up unrecoverable and the load has to be redone from scratch. This is acceptable for a first load into an empty table, not when appending to existing data. **Sync setting.** A setting that does not wait for the disk write to complete on commit makes a large difference on small batches. It has to be returned to its previous value once loading finishes; forgetting this step silently leaves the system at risk for months afterward. **Copy command.** Most engines provide a command that takes data as a stream instead of row-by-row inserts. The import above is one example of this. Its gain is that statement parsing and per-record processing overhead disappear. Finally, statistics need to be refreshed after loading. The planner knows how many rows a table has and how values are distributed from statistics it collected itself; this information, covered in the Advanced SQL course, loses its connection to reality after a bulk load. Left unrefreshed, the planner scans a table it thinks is empty, and queries run unexpectedly slow after the load. #### Summary - Commit count is bulk loading's first cost; the sample run showed a gap of more than fifty times between one transaction per row and batched loading. - Most of the gain is obtained in the first few hundred-row batches; very large batches grow restart cost and the amount of rollback information. - Indexes require per-row maintenance during loading; built in bulk afterward instead, both duration drops and the index takes up less space. - A staging table takes incoming data in without constraints and moves validation to a query; bad rows are set aside along with a reason without stopping the load. - Import tools can silently accept malformed rows; the behavior is never assumed, it is tested. - Statistics need to be refreshed after loading; left unrefreshed, the planner works with stale information. #### Next Step The lessons in this topic set up every task a database needs to keep on living: authorization, backup, recovery, replication, availability, connection management, monitoring, and bulk transfer. One remains, and it concerns all of them at once. The engine's version changes over time; security fixes, bug fixes, and end of support eventually make an upgrade unavoidable. Upgrading means replacing the layer underneath a running system: it requires an outage window, a compatibility check, a rollback plan, and the application being able to run against two versions at once. The last lesson covers this. ### Version Upgrades Source: https://academia.sh/en/courses/database-administration/version-upgrades Replacing the layer underneath a running system: the outage window of each upgrade path, a compatibility check done by pulling the schema from the catalog and comparing it, which application version runs at each step of an expand-contract migration, and the rollback plan. The lessons in this topic set up the work a database needs to keep on living: authorization, backup, recovery, replication, availability, connection management, monitoring, and bulk transfer. One remains, and it concerns all of them at once. The engine's version changes over time. Security fixes, bugs getting fixed, and support running out eventually make an upgrade unavoidable. Upgrading means replacing the layer underneath a running system; it is done while the application on top keeps running, and there has to be a way back if something goes wrong. #### Upgrade Paths and the Outage Window There are three paths, and what separates them is how long the outage lasts. **In-place upgrade** replaces the engine software and starts it against the same data files. The outage is only as long as the server takes to stop and restart. Its condition is that the new version can read the old file format; if it cannot, the files have to be converted, and the outage becomes tied to the data's size. **Dump and restore** takes the data out as a logical backup and loads it into the new version. It works between any two versions, because what moves is statements, not files. Its outage is the total time of the backup and the restore. **Migration via logical replication** subscribes a database set up on the new version to the old one. The old version keeps running while data streams in; the outage is only as long as redirecting applications takes. This path was set up in the Logical Replication lesson. The block below measures the second path's outage: the same database is dumped and restored at two sizes. The durations depend on the machine and the disk; what matters is the ratio between the two rows. ```bash rm -f old.db new.db dump.sql node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const { spawnSync } = require("node:child_process"); const fs = require("node:fs"); const setup = (file, n) => { fs.rmSync(file, { force: true }); const db = new DatabaseSync(file); db.exec(`CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT)`); const insert = db.prepare("INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)"); db.exec("BEGIN"); for (let i = 0; i < n; i++) insert.run(i % 400 + 1, i % 900 + 1, i % 3 + 1, "2025-06-01"); db.exec("COMMIT"); db.exec("CREATE INDEX loan_member ON loan(member_id)"); db.exec("CREATE INDEX loan_branch_pickup ON loan(branch_id, pickup)"); db.close(); }; const elapsed = (task) => { const t = process.hrtime.bigint(); task(); return Number(process.hrtime.bigint() - t) / 1e6; }; console.log("rows | file | dump | restore | outage"); console.log("----------|----------|---------|--------------|--------"); for (const n of [100000, 400000]) { setup("old.db", n); const size = fs.statSync("old.db").size; const dump = elapsed(() => { const c = spawnSync("sqlite3", ["old.db", ".dump"], { maxBuffer: 1 << 30 }); fs.writeFileSync("dump.sql", c.stdout); }); fs.rmSync("new.db", { force: true }); const restore = elapsed(() => { spawnSync("sqlite3", ["new.db"], { input: fs.readFileSync("dump.sql"), maxBuffer: 1 << 30 }); }); const count = new DatabaseSync("new.db").prepare("SELECT COUNT(*) c FROM loan").get().c; console.log(n.toLocaleString("en-US").padStart(9) + " | " + (Math.round(size / 1024) + " KB").padStart(8) + " | " + (dump.toFixed(0) + " ms").padStart(7) + " | " + (restore.toFixed(0) + " ms").padStart(12) + " | " + ((dump + restore).toFixed(0) + " ms").padStart(7) + " (" + count + " rows)"); } EOF ``` {/* mask: [ ]*[0-9]+(?= ms) */} ```text rows | file | dump | restore | outage ----------|----------|---------|--------------|-------- 100,000 | 5712 KB | 207 ms | 170 ms | 377 ms (100000 rows) 400,000 | 23040 KB | 781 ms | 690 ms | 1471 ms (400000 rows) ``` Four times the data, roughly four times the duration. Dump and restore's outage is directly proportional to data size, and this is the basis for planning (durations vary by machine): the duration measured on a small database is scaled up to production size to estimate the outage window. That the ratio stays linear at large sizes is an assumption; the real window is found by testing against a copy at production size. Restore takes a time of the same order as the dump. The reason is what got measured in the previous lesson: the dump only reads, while the restore rewrites the rows and rebuilds the indexes. When calculating the outage window, look at the sum of both sides. #### Compatibility Check What an upgrade breaks is usually not the engine but the contract between the application and the database. If the schema shipped with the new version differs from what the application expects, the application does not work. This difference has to be found **before** the upgrade, by reading it from the catalog. The system catalog was covered in this course's Engine Architecture topic. The block below uses it as an auditing tool: the schema in production and the candidate schema are compared with the same query. ```bash rm -f production.db candidate.db # Schema in production. sqlite3 production.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT, pickup TEXT NOT NULL, returned TEXT); CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_pickup ON loan(pickup); SQL # Candidate schema shipped with the new version. sqlite3 candidate.db <<'SQL' CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, branch_id INT NOT NULL, pickup_date TEXT NOT NULL, returned TEXT, delay_days INT DEFAULT 0); CREATE INDEX loan_member ON loan(member_id); CREATE INDEX loan_branch_pickup_date ON loan(branch_id, pickup_date); SQL sqlite3 -box -header production.db <<'SQL' ATTACH 'candidate.db' AS candidate; WITH p AS MATERIALIZED (SELECT cid, name, type, "notnull" FROM pragma_table_info('loan','main')), c AS MATERIALIZED (SELECT cid, name, type, "notnull" FROM pragma_table_info('loan','candidate')), diff AS ( SELECT p.name AS column_name, p.cid AS position, p.type || CASE WHEN p."notnull" THEN ' NOT NULL' ELSE '' END AS production, IFNULL(c.type || CASE WHEN c."notnull" THEN ' NOT NULL' ELSE '' END, '-') AS candidate, CASE WHEN c.name IS NULL THEN 'removed' WHEN p.type <> c.type OR p."notnull" <> c."notnull" THEN 'changed' ELSE 'same' END AS status FROM p LEFT JOIN c ON c.name = p.name UNION ALL SELECT c.name, 100 + c.cid, '-', c.type || CASE WHEN c."notnull" THEN ' NOT NULL' ELSE '' END, 'added' FROM c WHERE c.name NOT IN (SELECT name FROM p)) SELECT column_name, production, candidate, status FROM diff ORDER BY position; SELECT IFNULL(p.name, c.name) AS index_name, CASE WHEN p.name IS NULL THEN 'added' WHEN c.name IS NULL THEN 'removed' ELSE 'same' END AS status FROM (SELECT name FROM main.sqlite_schema WHERE type='index' AND sql IS NOT NULL) p FULL OUTER JOIN (SELECT name FROM candidate.sqlite_schema WHERE type='index' AND sql IS NOT NULL) c USING(name) ORDER BY 1; SQL ``` ```text ┌─────────────┬───────────────┬───────────────┬─────────┐ │ column_name │ production │ candidate │ status │ ├─────────────┼───────────────┼───────────────┼─────────┤ │ id │ INTEGER │ INTEGER │ same │ │ book_id │ INT NOT NULL │ INT NOT NULL │ same │ │ member_id │ INT NOT NULL │ INT NOT NULL │ same │ │ branch_id │ INT │ INT NOT NULL │ changed │ │ pickup │ TEXT NOT NULL │ - │ removed │ │ returned │ TEXT │ TEXT │ same │ │ pickup_date │ - │ TEXT NOT NULL │ added │ │ delay_days │ - │ INT │ added │ └─────────────┴───────────────┴───────────────┴─────────┘ ┌─────────────────────────┬─────────┐ │ index_name │ status │ ├─────────────────────────┼─────────┤ │ loan_branch_pickup_date │ added │ │ loan_member │ same │ │ loan_pickup │ removed │ └─────────────────────────┴─────────┘ ``` Differences fall into three classes, and each carries a different risk. **Additions are harmless.** A new column with a default value does not affect the old application; the old version does not know the column exists at all. **Removals are breaking.** A removed column breaks every statement that reads or writes it. A removed index does not produce a syntax error; instead, queries that depended on it silently slow down. The second case is more dangerous, because the upgrade looks "successful" and the problem only shows up once load arrives. **Changes are conditional.** A column that was not required becoming required breaks every insert statement that leaves it blank. A type narrowing behaves the same way. This check's output is a list; what makes the decision is comparing that list against the application code. If no query touches the removed column, there is no risk; if one does, the upgrade cannot be done without changing that query first. #### Expand and Contract Compressing a schema change into the same moment as the upgrade unavoidably produces an outage: the application's old version and the new schema do not work together. An **expand-contract migration** splits that moment into two and opens a window in between where both sides run. The block below shows this on a column rename. Three application versions are defined: the old one writes and reads the old column, the transition one writes to both columns and reads the new one, and the last one uses only the new column. At every stage all three are tried and which one works gets recorded. ```bash rm -f migration.db node - <<'EOF' const { DatabaseSync } = require("node:sqlite"); const db = new DatabaseSync("migration.db"); db.exec(`CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL, pickup TEXT NOT NULL)`); db.exec("INSERT INTO loan(book_id,member_id,pickup) VALUES(1,1,'2025-06-01')"); let seq = 100; // Three application versions: each does one write and one read. const oldVersion = () => { db.prepare("INSERT INTO loan(id,book_id,member_id,pickup) VALUES(?,2,2,'2025-06-02')").run(seq++); db.prepare("SELECT pickup FROM loan WHERE id=1").get().pickup; }; const transitionVersion = () => { // dual write: writes to both columns, reads the new one db.prepare("INSERT INTO loan(id,book_id,member_id,pickup,pickup_date)" + " VALUES(?,3,3,'2025-06-03','2025-06-03')").run(seq++); db.prepare("SELECT pickup_date FROM loan WHERE id=1").get().pickup_date; }; const finalVersion = () => { db.prepare("INSERT INTO loan(id,book_id,member_id,pickup_date) VALUES(?,4,4,'2025-06-04')").run(seq++); db.prepare("SELECT pickup_date FROM loan WHERE id=1").get().pickup_date; }; const attempt = (task) => { try { task(); return "works"; } catch { return "ERROR"; } }; const status = (stage) => console.log(stage.padEnd(34) + " | " + attempt(oldVersion).padEnd(10) + " | " + attempt(transitionVersion).padEnd(12) + " | " + attempt(finalVersion)); console.log("stage".padEnd(34) + " | old version | transition | final version"); console.log("-".repeat(34) + "-|-------------|--------------|--------------"); status("0. start"); // Direct path: the column is renamed. db.exec("ALTER TABLE loan RENAME COLUMN pickup TO pickup_date"); status("direct rename"); // Reset to the start and follow the expand-contract path. db.exec("ALTER TABLE loan RENAME COLUMN pickup_date TO pickup"); db.exec("ALTER TABLE loan ADD COLUMN pickup_date TEXT"); db.exec("UPDATE loan SET pickup_date = pickup WHERE pickup_date IS NULL"); db.exec(`CREATE TRIGGER migrate_backfill AFTER INSERT ON loan WHEN NEW.pickup_date IS NULL BEGIN UPDATE loan SET pickup_date = NEW.pickup WHERE id = NEW.id; END`); status("1. expand (column + backfill)"); // The old column is removed after every instance has moved to the transition version. db.exec("DROP TRIGGER migrate_backfill"); db.exec("ALTER TABLE loan DROP COLUMN pickup"); status("2. contract (old column removed)"); console.log(); console.log("new column left empty on rows written by the old version: " + db.prepare("SELECT COUNT(*) c FROM loan WHERE pickup_date IS NULL").get().c); EOF ``` ```text stage | old version | transition | final version -----------------------------------|-------------|--------------|-------------- 0. start | works | ERROR | ERROR direct rename | ERROR | ERROR | works 1. expand (column + backfill) | works | works | ERROR 2. contract (old column removed) | ERROR | ERROR | works new column left empty on rows written by the old version: 0 ``` What to look for in the table is how many versions work at each row. In the direct-rename row, only the final version works. Every second that passes between the schema change and the application's new version rolling out is an outage; and there is no way back, either, because reverting to the old version would require reverting the schema too. In the expand row, two versions work at once. This means the rollout can be done gradually: instances are moved to the transition version one at a time, and if a problem shows up, they can fall back to the old version. The schema change itself is not reverted, because the added column does not bother the old version. The contract row is applied only **after** every instance has moved to the transition version. Once this step is taken, going back requires bringing the removed column back; that is why contracting is the upgrade's last and most careful step. The waiting period in between is however long it takes for the odds of needing to roll back to drop to an acceptable level. The last line shows the backfill working: on rows the old version wrote using only the old column, the new column did not come out empty. Without this backfill, every row the old version writes during the migration window would be left missing in the new column, and the gap would silently lose data at contract time. #### The Work Surrounding an Upgrade The upgrade decision is where every lesson in this topic comes together. **Testing.** An upgrade is done first against a copy of production. That copy's source is a validated backup; the backup validation drill is therefore a precondition for upgrading. What testing needs to measure is not only "does it work," but whether query plans have changed. The planner changes along with the version; a query that ran well can pick a different plan on the new version. This risk is measured by replaying production's workload against the copy. **Rollback plan.** An upgrade needs a way back, and that cannot just be the sentence "we restore from backup" — restoring from backup erases the work done after the upgrade. For an in-place upgrade, the way back is a file copy taken before the upgrade; for a migration via logical replication, it is keeping the old version current by setting up replication in the reverse direction. **Order.** In a replicated setup, upgrade order matters. Standby servers are upgraded first, the primary last, so the target is ready if failover is needed. Because physical replication requires both sides to run the same version, this order can only be followed with logical replication or with a short outage. **After the upgrade.** Statistics are refreshed, monitored values are compared against before the upgrade, and the first busy hour is watched. An upgrade counts as successful not because the version changed, but because the monitored magnitudes returned to their baseline. #### Summary - Upgrade paths separate by outage window: in-place upgrade takes about as long as a restart, dump and restore is proportional to data size, migration via logical replication takes only as long as redirecting traffic. - In dump and restore, the restore runs noticeably longer than the dump; that is the number to look at when calculating the outage. - A compatibility check is done from the catalog: added columns are harmless, removed ones are breaking, columns whose requiredness changed are conditional; a removed index produces silent slowdown, not an error. - An expand-contract migration splits a schema change into two steps, opening a window where the old and new application versions run together; gradual rollout and rollback are possible in that window. - The contract step is applied only after every instance has moved to the new version, and it is the most expensive step to roll back. - Query plans can change along with the version; an upgrade is tested by replaying production's workload against a copy. #### Course Wrap-Up This course looked at a database engine from three separate angles. The **Engine Architecture** topic set up where data sits and how durability is guaranteed: process and memory layout, page structure, the write-ahead log, checkpoints, multiversion concurrency control, dead-row cleanup, and the system catalog. This topic's single idea — that the log carries every change in order — carried half of the two topics that followed: both point-in-time recovery and physical replication are that same log put to other uses. The **Indexes and Partitioning** topic set up how data gets found: index types, compound and partial indexes, covering indexes, index maintenance, table partitioning, and sharding patterns. The trade-off here kept reappearing throughout operations — every index speeds up reads and slows down writes; the bulk-loading measurement was the clearest evidence of that. The **Operations** topic set up what keeps a running system safe, recoverable, and available over time: roles and row-level security, backup types, point-in-time recovery, backup validation, physical and logical replication, high availability, connection poolers, monitoring, bulk loading, and version upgrades. These twelve lessons carried one claim in common: **in operations, a guarantee comes from nothing that has not been measured.** An unvalidated backup is not a backup, unmonitored replication lag is not a recovery point objective, an unmeasured pool size is an assumption, and an untested upgrade is a hope. Throughout the library loan records example, the same question was asked in every section: how do we know this? The answer was, every time, a measurement or a drill. Throughout the course, the relational model itself was never questioned: tables, rows, schema, transactions, and SQL were taken as given. There are requirements this model cannot carry, too. Documents whose shape keeps changing, enormous key-value sets accessed by a single key, graphs where the relationships themselves are the subject of the query, and time series writing millions of measurements per second all make the relational model's strengths pay a cost without using them. The next course — **Non-Relational Data Models** — looks at these families: key-value stores, document databases, wide-column stores, graph and time-series systems; the choice between embedding and referencing in the document model; and the questions distributed behavior makes unavoidable — replica sets, sharding, read and write concerns, consistency models. The replication, failover, quorum, and consistency concepts learned in this course will come back there under the same names; what changes is which guarantee gets traded away for which performance.