Physical Replication

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.

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

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.

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

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.

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

local commit (median): 0.033 ms

commit mode                  | network rtt | txn duration | txn/sec | loss risk
------------------------------|-------------|--------------|---------|--------------
asynchronous                  |           - |     0.033 ms |   30189 | up to the lag
synchronous (local network)   |        1 ms |     1.033 ms |     968 | none
synchronous (regions)         |       10 ms |    10.033 ms |     100 | none
synchronous (continents)      |       60 ms |    60.033 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.