Version Upgrades

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.

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

rows      | file     | dump    | restore      | outage
----------|----------|---------|--------------|--------
  100,000 |  5712 KB |   53 ms |       156 ms |  210 ms   (100000 rows)
  400,000 | 23040 KB |  194 ms |       639 ms |  834 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: 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 runs about three times longer than 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. The number to look at when calculating the outage window is the restore side.

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.

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;

SELECT IFNULL(p.name, c.name) AS column_name,
       IFNULL(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 p.name IS NULL THEN 'added'
            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 pragma_table_info('loan','main') p
FULL OUTER JOIN pragma_table_info('loan','candidate') c USING(name)
ORDER BY IFNULL(p.cid, 100 + c.cid);

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

┌─────────────┬───────────────┬───────────────┬─────────┐
│ 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.

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

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.