ClickHouse HA on a Mac, part 3: insert_quorum, the no-loss proof, and taking it to production

Series: 1 build · 2 when nodes die · 3 insert_quorum, no loss, production · 4 MEMORY_LIMIT_EXCEEDED at 8 MiB

People coming from MySQL usually ask me the same thing about ClickHouse replication: is there a semi-sync? The closest thing is insert_quorum. I wanted to see what it does when a replica dies mid-insert, and then answer the question that really matters after a post of killing things. Did anything the application was told had succeeded go missing? If the answer was no, the last question was what I’d have to change before trusting this shape outside a laptop.

A quorum insert, then a dead replica

By default an insert is acknowledged once the part is on the server you talked to, and the other replica fetches it a moment later. insert_quorum = 2 makes the server wait until two replicas have the part, which with two replicas means both.

I sent these straight to ch-1, ten rows each, with a distinct batch number so I could find them later:

INSERT INTO lab.events SETTINGS insert_quorum = 2, insert_quorum_timeout = 5000
SELECT now64(3), 900001, number, 1, 1 FROM numbers(10)

With both replicas up, and Keeper back to three nodes after part 2, it returned in 0.162 seconds.

Fine.

At 19:06:06.753 (WET, 31 October) I killed ch-2. One second later I sent batch 900002 the same way. It took 5.088 seconds to come back, and this is what came back:

Code: 319. DB::Exception: Received from localhost:9000. DB::Exception: Unknown quorum status.
The data was inserted in the local replica but we could not verify quorum.
Reason: Timeout while waiting for quorum. (UNKNOWN_STATUS_OF_INSERT)

Five seconds is the insert_quorum_timeout I set. Read the message carefully, because it’s unusually honest: the rows are on ch-1, and ClickHouse just can’t promise you they’re anywhere else. So is that a failure or a success? Neither, and that’s the problem, because your application now has to decide what to do with an answer of “maybe”.

Then I waited 35 seconds, longer than the 30-second Keeper session, so Keeper would stop counting ch-2 as alive. Batch 900003:

Code: 285. DB::Exception: Received from localhost:9000. DB::Exception:
Number of alive replicas (1) is less than requested quorum (2/2).. (TOO_FEW_LIVE_REPLICAS)

That one failed in 0.068 seconds, because by now ClickHouse knows ch-2 is gone and refuses the insert up front. It’s a cleaner answer, but look at what it means: with two replicas and a quorum of two, losing either replica stops every quorum insert.

The plain loader, without quorum, barely cared. It went three seconds without a successful insert, logged one insert error plus two select errors, then carried on at about 36 a second on ch-1.

What to do with “maybe”

The safe answer to UNKNOWN_STATUS_OF_INSERT is to send the same block again. Replicated tables remember a hash of each recently inserted block and drop an identical one. Identical is the catch, though. My loader uses now64(3), so every attempt is a new block, and a retry of it would never be recognised. For this test I used a fixed timestamp:

INSERT INTO lab.events SETTINGS insert_quorum = 2, insert_quorum_timeout = 5000
SELECT toDateTime64('2026-10-29 23:00:00.000', 3), 900010, number, 1, 1 FROM numbers(10)

I started ch-2, waited for it, killed it again at 19:06:59.189 and sent that block. It came back after 5.090 seconds with UNKNOWN_STATUS_OF_INSERT, as before. Then I started ch-2 again and sent the exact same statement, and it succeeded in 0.083 seconds.

How many rows of batch 900010 ended up on each replica? Ten, not twenty:

ch-1 quorum/dedup batches: [(900001,10),(900002,10),(900010,10)]
ch-2 quorum/dedup batches: [(900001,10),(900002,10),(900010,10)]

Deduplication dropped the retry, which is exactly what you’d want. There’s a second detail in there I like. Batch 900002, the “maybe” from earlier, is on both replicas, because ch-2 fetched it when it came back. Batch 900003, the one refused with TOO_FEW_LIVE_REPLICAS, is nowhere.

Did anything acknowledged go missing?

Now the stricter question for the whole run, with every kill from part 2 included. Every time the loader’s writer got “inserted” back, it wrote the batch number to acked.txt. At the end I stopped the loader and pulled the list of loader batches from each replica:

SELECT DISTINCT batch FROM lab.events WHERE batch BETWEEN 1 AND 899999

Then I compared the lists with comm, after a byte-order sort. I also took a row count and a fingerprint per replica: sum(cityHash64(ts, batch, id, user_id, value)) over the whole table, which comes out the same for two replicas with the same rows in different parts.

ch-1: 6906030 5910 5311470202214891452
ch-2: 6906030 5910 5311470202214891452
acked:     5906  ch-1 batches:     5906  ch-2 batches:     5906  highest batch sent: 5913
in ch-1 not ch-2:        0   in ch-2 not ch-1:        0
acked but missing on ch-2:        0   on ch-2 but not acked:
MEMORY_LIMIT_EXCEEDED: reader 0  writer 0

Every one of the 5,906 acknowledged batches is on both replicas.

No batch is on one replica only, and no batch is stored that the application was told had failed. The row count adds up exactly: a million seed rows, 5,906 batches of 1,000, and 30 rows from the three quorum batches. The writer sent 5,913 batches, so seven weren’t acknowledged. That’s exactly the seven insert errors in writer.err, five connection resets from the replica kills and two TABLE_IS_READ_ONLY from the quorum loss, and none of the seven were stored.

The two earlier full runs on this Mac gave the same answer, even though each sent a different number of batches and hit its errors at slightly different moments. The table at the end of this post has all three.

What carries over, and what I’d change

The shape is right.

Two or more replicas that all take writes, a three-node Keeper, a proxy in front: that’s what a replicated ClickHouse shard looks like on real servers too. The behaviour I measured is a property of that shape, not of the laptop. What won’t carry over are the timings, because they come straight from settings I picked to make a laptop lab fast and readable.

HAProxy here checks every second and gives up after two failures, and the Keeper session times out after 30 seconds. That’s why a dead replica cost me only about three seconds. Production values are usually longer, so measure the stall with your own settings rather than quoting mine.

All six containers share one laptop, which makes any single-host failure a total outage. I’d put the three Keepers on three separate hosts, ideally in different zones, never on the ClickHouse servers themselves. Part 2 shows what losing two of them looks like, and it isn’t pretty.

The load is an anti-pattern on purpose. Roughly 20 to 40 inserts of 1,000 rows a second means a new part for every insert and a lot of merges, which is why old parts mattered in part 4. In production I’d batch on the client or let the server do it with async_insert.

Two replicas with insert_quorum = 2 is fragile, as you saw above. Three replicas with a quorum of two survive a loss.

Do you want inserts to hang or to fail when Keeper loses its majority? Decide on purpose. The default insert_keeper_max_retries made each insert wait 142 to 143 seconds in my runs, and setting it to 0 made the same insert fail in well under a tenth of a second.

One HAProxy is a single point of failure of its own. In production I’d run at least two behind a virtual IP or DNS, or let the client library spread connections across the replicas. Plain-text passwords and the missing TLS are laptop shortcuts too, and so are the 4 GB memory cap and the 10-second part lifetime. A real server takes its cap from its RAM and keeps the default part lifetime.

When something goes wrong

These are the failures I actually hit while building and breaking the lab on the Mac, and what fixed them.

The Mac results, three runs

I ran the whole sequence three times on the same Mac (Apple M4 Pro, Docker Desktop 29.7.2 with a 12-CPU, 15.6 GiB VM), with tools/run-experiments.sh, which runs everything from deploy to teardown, including verify and the no-loss check. The 31 October 18:59 run is the main one in this series.

The settings-driven numbers barely moved: the 142 to 143 seconds, the five-second quorum timeout, the dedup retry. The replica kill moved by about a second, which is the health-check interval. The Keeper leader kill is the one I’d treat with care, anywhere from 1 to 12 seconds, and I still can’t say why.

To clean up after your own run, ./scripts/lab.sh teardown removes the six containers with their volumes, plus the ch-ha network. After every one of my runs it reported left over: 0 containers, 0 volumes.

Verdict

In my runs ReplicatedMergeTree never lost an acknowledged insert and never stored one it had rejected, through every failure in this series. I’d still be careful with insert_quorum = 2 on two replicas, because it turns the loss of either server into failed quorum inserts. Three replicas with a quorum of two is the setup I’d actually run, and whatever you choose, give each block deterministic content so that an UNKNOWN_STATUS_OF_INSERT can be resent safely.

If I had to pick one thing to take from this series to production, it’s an alert on is_readonly, because when Keeper loses its majority your inserts hang for over two minutes and your health check won’t tell you.

Everything I used is in the lab folder 20261029-clickhouse-replicated-ha. Rebuild it and break it. Then tell me if your numbers match mine.

There’s one more post. In part 4 I go back to the very first attempt at this lab, the one where a query that needs about 8 MiB kept failing with a memory error.