Backup Types

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.

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

source database: 21,782,528 bytes

backup format                    |    bytes | % of source | time (ms)
---------------------------------|----------|-------------|----------
logical (.dump)                  | 24281928 |       111.5 |      200
logical + compression            |  3972854 |        18.2 |      808
physical (file copy)             | 21782528 |       100.0 |        9
physical (consistent snapshot)   | 21782528 |       100.0 |       57

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.

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')"

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.