UPDATE Write Benchmark

ReferencePerformanceBenchmarks

The 10M-Row Query Benchmark never writes. This page covers the other half: what an UPDATE costs, measured across four table sizes, together with the correctness and metadata invariants that must hold after one.

The headline is a property of the current storage engine that anyone sizing a write workload needs to know before they design around it:

UPDATE cost is set by table size, not by how many rows match

Updating one row of a 5-million-row table costs about the same as updating all five million. Every UPDATE is a durable rewrite of the entire table. Plan write workloads accordingly — see What this means in practice.

Run identity

Build	AetheriusDB 0.1.105 — release profile, dedicated daemon on a fresh data directory
Host	Apple M4 (10 cores: 4 performance + 6 efficiency), 16 GB unified memory
OS	macOS 26.6, arm64
Client	aesql 0.1.105, native binary protocol, loopback, single client
Date	2026-08-26
Load	Idle host, no concurrent queries
Harness	black-box-testing/benchmarks/update_bench.py

The daemon is a dedicated one on a fresh data directory, not the installed service, so no co-tenant background work contends with the measurement.

Harness precondition

update_bench.py expects its --db (default updbench) to already exist — it never issues CREATE DATABASE. Against a fresh data directory every check fails with database 'updbench' not found, and the harness reports its unparsed-result sentinel -1 rather than a clear error. Create the database first. This is a harness gap, not an engine one.

What is measured

Four questions the query suite cannot answer, because it only reads:

1. How long does an UPDATE take — for one row, for a group, for the whole table?
2. Are the updated values sustained — does reading them back agree with what was written?
3. Does the table survive — row count, and the values of rows the predicate did not match?
4. Does each cube’s header metadata stay in sync — does _sys_cube_residency still describe the table after a rewrite, with no cube leaked?

Method

A four-column table is created and seeded from scratch at each size:

CREATE TABLE t (id BIGINT, grp BIGINT, v BIGINT, tag TEXT);
-- seeded with id = 1..N, grp = id % 100, v = id, tag = 'x<id>'

Then three statements run in order, each timed as a full client round trip, with every invariant re-checked between them:

-- U1  one row
UPDATE t SET v = v + 1 WHERE id = 1;

-- U2  one group, ~1% of the table
UPDATE t SET v = v + 1000 WHERE grp = 5;

-- U3  every row
UPDATE t SET v = v + 7;

This benchmark never touches the ecom dataset

It works in its own database (updbench) and drops its table first. The 31-query suite compares result checksums against a recorded 0.1.70 baseline; a stray UPDATE against ecom would invalidate that comparison permanently and silently, so nothing here connects to ecom at all.

Results

Every invariant passed at every size — matched count, SUM(v) after each mutation, the updated value read back, an untouched neighbour unchanged, row count preserved, no cube leaked.

Table rows	U1 — 1 row	U2 — one group	U3 — all rows	Cube chain
100,000	112 ms	115 ms (1,000)	129 ms (100,000)	10 → 1
500,000	217 ms	241 ms (5,000)	258 ms (500,000)	50 → 1
1,000,000	349 ms	368 ms (10,000)	412 ms (1,000,000)	100 → 1
5,000,000	1,508 ms	1,594 ms (50,000)	2,719 ms (5,000,000)	500 → 1

Against the previous published run (0.1.86, 2026-08-03)

Same harness, same host, same four sizes. Every size is faster on 0.1.105, and the shape of the finding is unchanged.

Table rows	0.1.86 U1	0.1.105 U1	0.1.86 U3	0.1.105 U3
100,000	177 ms	112 ms	135 ms	129 ms
500,000	349 ms	217 ms	245 ms	258 ms
1,000,000	457 ms	349 ms	425 ms	412 ms
5,000,000	2,376 ms	1,508 ms	3,122 ms	2,719 ms

The single-row case improved most — 37 % faster at 5 M rows — but it is still an O(table) rewrite, so the caution at the top of this page stands unchanged.

Read the table across, then down.

Across a row, the matched-row count varies by up to five orders of magnitude and the time barely moves. At 1,000,000 rows, matching one row costs 349 ms and matching all one million costs 412 ms — a 1,000,000× change in matched rows buys an 18 % change in time.

Down the table, holding the matched set fixed at a single row, U1 goes from 112 ms to 1,508 ms — 13× slower for a 50× larger table, while the work the statement was asked to do never changed.

This differed on 0.1.86

On the previous run U1 was more expensive than U3 at 100 K–1 M (457 ms vs 425 ms at 1 M), because the first UPDATE also pays the one-time cube-chain collapse described below. On 0.1.105 that collapse is cheap enough that the ordering is no longer inverted. The O(table) conclusion is unaffected — it rests on the down axis, not the across one.

That is the signature of a whole-table rewrite, and it is exactly what the code does. AcubeStorageAdapter::update_where is scan-and-rebuild: it materialises every row, builds a second complete row set, replaces the table’s rows wholesale, and then durably flushes the table. The matched-set size never enters into it.

Separating the two effects

The raw sweep has a confound worth removing, because it makes the result look stranger than it is. U1 runs first, against a table that still has its full cube chain (10, 50, 100 or 500 cubes), and the rewrite collapses that chain to one. U2 and U3 then run against an already-collapsed table. So U1 pays a one-time cost the other two do not, which is why it appears to be the most expensive statement at the smaller sizes.

Re-running on the surviving tables, with every statement starting from identical single-cube geometry (these figures are from the 0.1.86 run and were not re-measured on 0.1.105; the main table above was):

Table rows	1 row matched	All rows matched
1,000,000	330 / 279 / 279 ms	329 ms
5,000,000	2,013 / 1,512 / 1,458 ms	2,795 ms

At 1,000,000 rows the matched set varies by six orders of magnitude and the time does not move at all. That is the clean form of the claim, and it rules out any reading in which matched-row count is the dominant term.

Where the time goes

The three effects separate cleanly:

Component	at 1,000,000 rows	at 5,000,000 rows
Whole-table rewrite floor	~280 ms	~1,460–1,510 ms
Cube-chain collapse (first UPDATE only)	~180 ms	~900 ms
Per-matched-row modification	~50 ms for 1 M rows	~1,300 ms for 5 M rows

The floor dominates at every size measured. The per-matched-row term is real but so small relative to the floor that it is invisible below roughly a million rows — which is why U3 is cheaper than U1 at 100 K–1 M (U1 is paying the collapse, U3 is not), and only at 5 M does modifying five million rows finally clear the floor and make U3 the most expensive statement.

So the precise statement is O(table) with a weak O(matched) term, not pure O(table).

A correction to how this result was first recorded

The first recording of this sweep read “one row costs more than fifty thousand” as an intrinsic property of single-row UPDATEs. It is not — it is the first-UPDATE cube-collapse cost, as the identical-geometry re-runs above show. The O(table) conclusion is unchanged and, if anything, better supported.

Cube-chain collapse and what it costs you later

Every UPDATE rebuilds the surviving rows into a single fresh cube. A table loaded as 500 cubes comes back as one.

The metadata stays fully coherent. Across all four sizes, _sys_cube_residency reported resident_bytes identical before and after all three rewrites (3,438,895 / 17,638,895 / 35,388,896 / 181,388,896) and cubes_spilled stayed at 0. Nothing leaked and nothing was lost — the metadata simply describes a coarser table.

The consequence is on the read side. Zonemap, chrono-stripe and PK-bloom skipping all work per cube. Once a table is a single cube it is a single skip unit, so range pruning cannot eliminate anything on it until it is recompacted:

A written-to table loses its skip granularity

This directly bounds what cube pruning can do for you on any table that takes writes. The BETWEEN zonemap fix in 0.1.86 makes range predicates able to prune a cube for the first time — but on a table that has taken an UPDATE there is only one cube left to prune, so the mechanism has nothing to work with until the Loom compactor rebuilds the chain.

What this means in practice

Size write cost by the table, not by the statement. A single-row UPDATE against a large table is not a cheap operation, and batching a thousand single-row updates into a thousand statements costs a thousand full table rewrites. Where the workload allows it, prefer one UPDATE with a predicate matching all the affected rows — the cost is essentially identical to updating one of them.

Extrapolating to a real table: by row count alone, one UPDATE … WHERE order_item_id = ? on the 10M benchmark dataset’s order_items (6,468,831 rows) would rewrite the whole table for roughly 2–3 s. Treat that as a lower bound: the benchmark table has four narrow columns and a 181 MB resident footprint, where order_items has eleven columns and 420 MB, and the rewrite cost tracks bytes as well as rows.

Expect pruning to degrade after writes. A table that is loaded, then queried with range predicates, will prune well. The same table after an UPDATE will not, until it is recompacted.

What this benchmark does not establish

1. Restart survival is not measured here. Every invariant above is checked against a running daemon. Verifying that updated values outlive a process restart requires operator privileges the harness deliberately does not take; the harness has a --verify-only mode intended to be run after an operator restart, and that check has not been recorded on this build.
2. Single client, no concurrency. No concurrent readers or writers, so nothing here characterises contention or isolation behaviour under load.
3. Warm cache, data fits in memory. As with the query suite, the working set fits comfortably on this host. Behaviour when the table exceeds RAM is not characterised.
4. One table shape. Four columns, three of them BIGINT. Wider tables and different type mixes are not covered, and the rewrite cost tracks bytes, so they will differ.
5. UPDATE only. DELETE and mixed read/write workloads are not measured.

10M-Row Query Benchmark →

The read side — 31 analytical queries over 10 million rows.

Benchmarks (earlier runs) →

Ingest throughput and the earlier 500 K-row suite.

Memory Compaction →

The Loom compactor that rebuilds a collapsed cube chain.

Multi-Tier Residency →

What _sys_cube_residency reports and how cubes spill.