---
title: 'Version Upgrades'
source: 'https://academia.sh/en/courses/database-administration/version-upgrades'
course: 'Relational Database Administration'
language: en
updated: '2026-08-23T07:00:40+00:00'
license: 'CC BY-SA 4.0'
---

# 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
```

```text
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.

```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;

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
```

```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.