Logical Replication

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.

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

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.

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

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.

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

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 an order of magnitude 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.