High Availability and Failover

The previous two lessons set up replication; both carried the same assumption: 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. Having a second copy of the data ready on another machine does not mean that machine starts serving on its own — someone has to decide, promote the standby server, and redirect applications there.

This lesson’s subject is that decision: who makes it, on what information, and what happens if two separate sides say “I am the primary” at the same time.

Failover, Switchover, and the Cluster Manager

Two operations get confused with each other.

Switchover is a deliberate exchange of the primary and standby roles. The primary server is still running: it stops accepting new writes, the standby server is allowed to catch up, and the roles are swapped. There is no data loss, and the outage is on the order of seconds. Maintenance, hardware replacement, and version upgrades are done this way.

Failover is promoting the standby server to primary while the primary server is unreachable. There is no chance to wait for the primary to catch up; under asynchronous replication, every unsent change is lost. The amount lost is the replication lag measured in the previous lesson.

The component that makes this decision is called the cluster manager. It runs outside the database engine, polls the nodes at regular intervals, and does three jobs: monitor the primary’s reachability, promote a standby when needed, and redirect the address applications connect to toward the new primary. Without this last step, promotion is useless — applications still try to connect to the old address.

The Majority Rule

The cluster manager’s hardest question is whether the primary is really dead or merely unreachable. The two look identical from the outside: no response comes back. But their consequences are opposite. If the primary is dead, the standby needs to be promoted; if the primary is running but invisible because of a network partition, promotion creates a second primary.

The block below compares the two rules on the same set of partition scenarios. This is a model; no real cluster software runs — the rule that makes the decision is applied directly.

node - <<'EOF'
// The cluster splits into groups under a network partition; the primary is always node 1.
const scenarios = [
  [2, [[1], [2]]],
  [3, [[1, 2], [3]]],
  [3, [[1], [2, 3]]],
  [4, [[1, 2], [3, 4]]],
  [5, [[1, 2, 3], [4, 5]]],
  [5, [[1, 2], [3, 4, 5]]],
  [5, [[1, 2], [3, 4], [5]]],
];
const row = (a, b, c, d) => console.log(String(a).padStart(5) + " | " + b.padEnd(7) +
  " | " + c.padEnd(28) + " | " + d);

console.log("MAJORITY RULE: a group accepts writes only if it brings together");
console.log("more than half of the nodes.");
console.log();
row("nodes", "split", "majority", "result");
console.log("------|---------|------------------------------|--------------------------");
for (const [n, groups] of scenarios) {
  const majority = groups.find((g) => g.length * 2 > n);
  const hasPrimary = majority && majority.includes(1);
  row(n, groups.map((g) => g.length).join("+"),
    majority ? majority.join(",") + " group (" + majority.length + "/" + n + ")" : "none",
    majority ? (hasPrimary ? "primary stays in place" : "failover; old primary stops")
             : "no side accepts writes");
}

console.log();
console.log('NO-QUORUM RULE: "if I cannot see the primary, it must be dead, so I become primary".');
console.log();
row("nodes", "split", "primary count", "result");
console.log("------|---------|------------------------------|--------------------------");
for (const [n, groups] of scenarios)
  row(n, groups.map((g) => g.length).join("+"), groups.length + " primaries",
    groups.length > 1 ? "split brain" : "no problem");
EOF

MAJORITY RULE: a group accepts writes only if it brings together
more than half of the nodes.

nodes | split   | majority                     | result
------|---------|------------------------------|--------------------------
    2 | 1+1     | none                         | no side accepts writes
    3 | 2+1     | 1,2 group (2/3)              | primary stays in place
    3 | 1+2     | 2,3 group (2/3)              | failover; old primary stops
    4 | 2+2     | none                         | no side accepts writes
    5 | 3+2     | 1,2,3 group (3/5)            | primary stays in place
    5 | 2+3     | 3,4,5 group (3/5)            | failover; old primary stops
    5 | 2+2+1   | none                         | no side accepts writes

NO-QUORUM RULE: "if I cannot see the primary, it must be dead, so I become primary".

nodes | split   | primary count                | result
------|---------|------------------------------|--------------------------
    2 | 1+1     | 2 primaries                  | split brain
    3 | 2+1     | 2 primaries                  | split brain
    3 | 1+2     | 2 primaries                  | split brain
    4 | 2+2     | 2 primaries                  | split brain
    5 | 3+2     | 2 primaries                  | split brain
    5 | 2+3     | 2 primaries                  | split brain
    5 | 2+2+1   | 3 primaries                  | split brain

The lower table shows that a rule which looks reasonable on its own produces two primaries on every partition. The problem is that a node cannot tell “the other side is dead” apart from “I cannot see the other side” using only local information. The quorum rule brings that distinction in from somewhere else: at most one group can bring together more than half of the nodes, so granting write rights only to that group makes two primaries impossible.

The upper table has three readings. In the third row the primary ends up in the minority and failover happens on the majority side; the old primary must stop writing on its own. In the second and fourth rows no group is in the majority and writing stops entirely — the cluster makes itself unavailable to prevent a data conflict. Availability is sacrificed for consistency here; this is not a flaw but a deliberate choice.

The four-node row gives the most practical conclusion about cluster size: four nodes add no partition tolerance over three. Both tolerate losing a single node, but four nodes stop completely under a 2+2 split. This is why cluster sizes are chosen odd. In a two-node setup, adding a third voter that carries no data — a witness — achieves the same result cheaply.

The Cost of Two Primaries

Split brain’s damage is not abstract. The block below genuinely produces a split: two copies of the same database each accept writes separately, and when the split closes the two sides are compared.

rm -f side-a.db side-b.db

node - <<'EOF'
const { DatabaseSync } = require("node:sqlite");
const fs = require("node:fs");

const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INT NOT NULL,
                member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT)`;
const seed = new DatabaseSync("side-a.db");
seed.exec(SCHEMA);
const insertSeed = seed.prepare(
  "INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES(?,?,?,?,?)");
seed.exec("BEGIN");
for (let i = 1; i <= 1000; i++)
  insertSeed.run(i % 400 + 1, i % 250 + 1, i % 3 + 1, "2025-06-01", "2025-06-15");
seed.exec("COMMIT");
seed.close();

// Moment of the split: both sides start from the same state.
fs.copyFileSync("side-a.db", "side-b.db");
const sideA = new DatabaseSync("side-a.db");
const sideB = new DatabaseSync("side-b.db");
const open = (db, book, member, branch) => db.prepare(
  "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)")
  .run(book, member, branch, "2025-06-16");
// Side A: desks at branches 1 and 2. Side B: branch 3.
for (let i = 0; i < 40; i++) open(sideA, 101 + i, 10 + i, (i % 2) + 1);
for (let i = 0; i < 25; i++) open(sideB, 131 + i, 60 + i, 3);

const count = (db, s) => db.prepare(s).get().c;
console.log("after the split");
console.log("  side A rows".padEnd(19) + ": " + count(sideA, "SELECT COUNT(*) c FROM loan"));
console.log("  side B rows".padEnd(19) + ": " + count(sideB, "SELECT COUNT(*) c FROM loan"));

// The split closes: the two sides' records are compared.
sideA.exec("ATTACH 'side-b.db' AS b");
const sameIdDiffRow = count(sideA, `SELECT COUNT(*) c FROM main.loan m JOIN b.loan x USING(id)
                            WHERE m.book_id <> x.book_id OR m.member_id <> x.member_id`);
const doubleLoanedBook = count(sideA, `SELECT COUNT(*) c FROM
  (SELECT book_id FROM main.loan WHERE returned IS NULL
   INTERSECT SELECT book_id FROM b.loan WHERE returned IS NULL)`);
console.log();
console.log("  same id, different record".padEnd(33) + ": " + sameIdDiffRow);
console.log("  book loaned out on both sides".padEnd(33) + ": " + doubleLoanedBook);

// Merge attempt: side B's new records are inserted into side A.
let conflicts = 0, inserted = 0;
for (const rec of sideA.prepare("SELECT * FROM b.loan WHERE id > 1000").all()) {
  try {
    sideA.prepare("INSERT INTO main.loan(id,book_id,member_id,branch_id,pickup,returned)" +
                " VALUES(?,?,?,?,?,?)")
       .run(rec.id, rec.book_id, rec.member_id, rec.branch_id, rec.pickup, rec.returned);
    inserted++;
  } catch { conflicts++; }
}
console.log();
console.log("merge attempt: " + inserted + " inserted, " + conflicts + " id conflicts");
EOF

after the split
  side A rows      : 1040
  side B rows      : 1025

  same id, different record      : 25
  book loaned out on both sides  : 10

merge attempt: 0 inserted, 25 id conflicts

The results show two different kinds of trouble. Twenty-five id conflicts are a technical problem: both sides handed out the same numbers from the same sequence generator. It can be solved by assigning new numbers to the records, but the receipts, external system records, and reports carrying those old numbers become invalid.

Ten books appearing loaned out on both sides is a non-technical problem. The same book looks like it was handed to two different members, and the database has no way to know which one is really not on the shelf. This is the limit of automatic merging: deciding which of two conflicting realities is correct is not a data problem, it is a business problem. This is why the cost of split brain is not comparable to the cost of an outage — an outage ends; diverged data stays.

The majority rule is the first layer against this. The second layer is fencing: the minority-side old primary’s ability to write is blocked from the outside. The standard ways are shutting the server down over the network or revoking its access to shared storage. The reason is that the minority node cannot be trusted to stop itself: a stuck process is, by the time it would make that decision, already unable to respond.

The Trade-Off Between Detection Time and False Failover

The cluster manager’s settings collapse onto a single axis: how fast a decision gets made. A fast decision shortens the outage and raises the odds of mistaking a transient stall for a real failure. The block below measures this. Responsiveness and stall durations are modeled; no real server is being polled. Promotion, redirection, and reconnection cost is taken as a fixed ten seconds.

node - <<'EOF'
// Model: a 3600-second window. The primary server occasionally does not respond for
// 1-4 seconds (transient stall); at second 3000 it genuinely fails.
const WINDOW = 3600, FAILURE = 3000, STALL_PROBABILITY = 0.004;
let seed = 20250616;
const random = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648;

const responsive = new Array(WINDOW).fill(true);
let stalls = 0;
for (let t = 0; t < FAILURE; t++) {
  if (random() < STALL_PROBABILITY) {
    const length = 1 + Math.floor(random() * 4);
    stalls++;
    for (let i = 0; i < length && t + i < FAILURE; i++) responsive[t + i] = false;
    t += length;
  }
}
for (let t = FAILURE; t < WINDOW; t++) responsive[t] = false;

// Promotion, redirection, and application reconnection (model value).
const FAILOVER_COST = 10;
console.log("transient stalls: " + stalls + " times, each 1-4 s. " +
            "Real failure: second " + FAILURE + ".");
console.log();
console.log("check    | consecutive | false    | detection | outage");
console.log("interval | failures    | failover | time      | (RTO)");
console.log("---------|-------------|----------|-----------|--------");
for (const [interval, k] of [[1, 1], [1, 2], [1, 3], [2, 3], [5, 2], [5, 3], [10, 3], [30, 3]]) {
  let consecutive = 0, falseFailover = 0, detection = null, triggered = false;
  for (let t = interval; t < WINDOW; t += interval) {
    if (responsive[t]) { consecutive = 0; triggered = false; continue; }
    consecutive++;
    if (consecutive < k || triggered) continue;
    triggered = true;
    if (t < FAILURE) falseFailover++;
    else if (detection === null) detection = t - FAILURE;
  }
  console.log(String(interval + " s").padStart(8) + " | " + String(k).padStart(11) + " | " +
    String(falseFailover).padStart(8) + " | " +
    (detection === null ? "-" : detection + " s").padStart(9) + " | " +
    (detection === null ? "-" : detection + FAILOVER_COST + " s").padStart(7));
}
EOF

transient stalls: 10 times, each 1-4 s. Real failure: second 3000.

check    | consecutive | false    | detection | outage
interval | failures    | failover | time      | (RTO)
---------|-------------|----------|-----------|--------
     1 s |           1 |       10 |       0 s |    10 s
     1 s |           2 |       10 |       1 s |    11 s
     1 s |           3 |        4 |       2 s |    12 s
     2 s |           3 |        0 |       4 s |    14 s
     5 s |           2 |        0 |       5 s |    15 s
     5 s |           3 |        0 |      10 s |    20 s
    10 s |           3 |        0 |      20 s |    30 s
    30 s |           3 |        0 |      60 s |    70 s

The columns run in opposite directions. As the check interval grows and the number of consecutive failures required grows, false failover drops to zero and the outage grows to seventy seconds. Each row’s one-hour window contains ten transient stalls; the most aggressive setting counts all of them as failures and performs ten unnecessary failovers.

It matters to see that an unnecessary failover is not harmless. Every failover means data loss equal to the replication lag under asynchronous replication, every connection dropping, and the work of the old primary rejoining the cluster. Taking down a server that was slow for one second can produce an outage larger than the one it was meant to prevent.

The setting choice is tied to the recovery time objective. If the objective is “at most one minute,” a ten-second check interval is sufficient and carries no false-failover risk. If the objective is on the order of seconds, the sources of stalling need to be reduced; an aggressive setting, built on top of an unstable system, magnifies the instability.

One task remains after failover: returning the old primary to the cluster as a standby server. Because of unsent changes, the old primary’s history has diverged from the new primary’s; it cannot reconnect as it is, unchanged. Most engines provide a tool that finds this divergence and rewinds past it; if one is not provided, the path is to rebuild the old primary from scratch using a base copy taken from the new primary.

Summary

• Switchover is a deliberate, lossless exchange between roles; failover is promoting a standby in place of an unreachable primary, and it loses data equal to the replication lag under asynchronous replication.
• A node cannot tell “dead” apart from “invisible” using only local information; the majority rule derives that distinction from node count and makes two primaries impossible.
• An even-numbered cluster size adds no resilience; under a split, neither side may reach a majority. A witness node makes the vote count odd without carrying data.
• The id conflicts split brain leaves behind can be resolved technically; reality that diverged on both sides cannot, and that is the limit of automatic merging.
• Fencing blocks the minority-side old primary’s writes from the outside; the node cannot be trusted to stop itself.
• As health checks tighten, detection time shortens and the odds that a transient stall produces a false failover rise; the setting is chosen against the recovery time objective.

Next Step

Failover’s last step was redirecting applications to the new primary. That means the application connects not directly to the database, but through a layer in between. The same layer solves a second problem, one encountered every day even without a failure: when every application process opens its own connection, a few hundred processes produce more connections than the database can carry. The next lesson covers connection poolers: the real cost of opening a connection, how pool size relates to queue time, and the difference between session-level and transaction-level pooling.