Re: UNDO with FILEOPS, and the FLUX table AM

On Thursday, October 15th, 2026 at 8:26 PM, Manu <manuelreyesbravo(at)gmail(dot)com> wrote:

> Hi Greg,

Hi Manu,

Apologies for the length of this email but there was a lot of churn and
this isn't a small set of changes.

> I think the wrong tarball went out: undo-v6.tar.gz is v3 with a new
> version number.

You're right, sorry about that, and thanks for checking before spending
time on it. Not sure how I did that...

> > v6, attached, fixes both of them, along with the cfbot failures
> > from v4.

so, hopefully I've delivered v7 with:

0001 cluster-wide UNDO engine (records in the WAL stream)
0002 transactional filesystem operations (FILEOPS) backed by UNDO
0003 route filesystem mutations through FILEOPS
0004 per-backend UNDO logs
0005 the ATM, sLog, and logical revert worker
0006 nbtree: mark aborted index inserts dead via UNDO
0007 hash: mark aborted index inserts dead via UNDO
0008 nbtree: in-place delete-marking for stable-TID UPDATE
0009 tableam: let an AM declare that UPDATE keeps a row's TID
0010 executor: keep the pre-update row of an in-place UPDATE
0011 EvalPlanQual: fetch an in-place AM's reference rows with the query snapshot
0012 AFTER triggers: queue an in-place AM's row images with the event
0013 logical replication: pre-check unique keys an in-place UPDATE changes
0014 tableam: SO_RETURN_DELETED, and update_deleted detection for any AM
0015 tableam: let a block AM's size estimate use its own default fillfactor
0016 tableam: let REPACK (CONCURRENTLY) accept any AM whose changes are decoded
0017 vacuum: add parallel_vacuum_set_index_stats() and a bulkdel injection point
0018 pg_visibility: refuse tables of other table AMs in the tuple checks
0019 the FLUX in-place-MVCC table access method
0020 benchmarks: heap vs FLUX

The 0009-0018 commits are heap-neutral preparatory changes to the table-AM,
executor, trigger, EvalPlanQual, logical-replication, VACUUM and pg_visibility
interfaces; each is a no-op for heap and for any AM that does not opt in, and
all are ordered before 0019 (FLUX), which is the first commit that uses them.

As much as I want UNDO/FILEOPS/FLUX I really think the 0009-0018 series is
something we need to think over. I've been running into other
table/index/executor issues over in my Tepid [1] thread, maybe the union of
these should be it's own thread?

The TL;DR for the benchmarks is that for OLTP workloads FLUX is at
0.83-0.98 of HEAP, row locking at parity, analytics 1.6-2x slower,
mostly because FLUX tables are 1.3-1.5x larger. The main reason for
the size is a FLUX defect I'll fix next: it keeps varlenas' 4-byte
headers on the page where heap shortens them to one byte, that should
be easily adopted by FLUX code.

> Thanks, the fixes you describe are what I hoped for, and I was about to
> measure them. But I think the wrong tarball went out: undo-v6.tar.gz is v3
> with a new version number. Diffing each v6-00NN against v3-00NN, the only
> line that differs in all 20 files is the "[PATCH v6 NN/20]" in the Subject;
> the commit ids in the "From <sha>" lines are v3's (0001 is b7667c1ca7bf).
> There is no cover letter, no test_fileops/t/017, slog_num_partitions is still
> in 0005, and 0004 still adds 073-075 etc. It also lacks v4's record
> alignment fix, so cfbot will be testing v3 again.
>
> Could you resend the real v6? I'll run 017 on the three paths, the chmod
> repro against v4 on the same machine, and the per-prefix check as soon as it
> is out.
>
> Regards,
> Manu

So, v7 has everything I'd promised in v6's email plus it also fixes the two
things I said I'd do next, and a few bugs that a benchmark on bare-metal
hosts turned up since.

- recovery/137's "undo record pointer mismatch" is fixed. It was the
insert-pointer side of the checkpoint race, as I suspected, but not
only that. A FLUX writer reserves its undo space before its critical
section and inserts the forward WAL record after. A checkpoint whose
redo point falls in between, and whose undo-log snapshot is taken
after the reservation, leaves recovery with a log that is already past
the record. Whether a META record puts it back depends on which redo
point the writer saw. The forward record now carries the record's
whole placement, and redo installs it before re-running the insert.
recovery/154 forces the window with an injection point: on v6 it fails
in all four shapes it tries, and a heap control passes. Under
EXEC_BACKEND with CPU load, 137 failed 3 times in 192 runs on v6 and
0 times in 384 with the fix.

- FileOpsRename is now immediate and rolled back, like the other
constructive operations, rather than deferred to commit. The new name
exists inside the transaction, which is what your harness expected,
and a crash right after commit no longer loses it. It refuses to
replace an existing destination. storage/file/README now opens with a
table of when each operation takes effect and what undoes it.

- The pgbench row-lock table was described as "scale 100". Its custom
scripts used :scale without passing it, so they only drew rows from the
first 100,000 accounts. The letter says so now, and the new metal
benchmarks use the whole table.

- The metal benchmark (HammerDB TPROC-C and TPROC-H, pgbench, the
ledger benchmark; x86_64 and aarch64) found a lost update in FLUX's
same-size UPDATE path: it asked CLOG before the proc array whether the
row's last writer had committed, so a writer that committed between
the two questions was overwritten. It showed up as a duplicate TPROC-C
order id at 256 users on 128 cores. Fixed, with a deterministic test
(recovery/160). It also found a checkpoint that failed on an undo
segment the discard worker had just removed (fixed as md.c does,
recovery/161), and two memory problems in an earlier candidate.

- Rollbacks in the undo workers and the logical revert worker can now be
interrupted, so a fast shutdown no longer waits for a large rollback,
and the logical revert worker no longer PANICs on an ordinary error
for an entry older than the last checkpoint.

Shihao, Matthias,

Thanks for the careful reviews. All three of your reports were real
bugs that led to a much larger round of stabilization than I expected.
Hence the delay responding.

Your reports:

1. Patch-split mistakes (059/061 setting a GUC that only 0005 added; test
modules only built in 0005). Fixed: tests live with the feature they
exercise, and every prefix of the series builds and passes its own tests.

2. CREATE DATABASE crash leaves the directory, for both wal_log and
file_copy. Fixed; the destination tree is now owned by FILEOPS and removed
on abort or crash (recovery/090). Following it up also found that a loser
whose UNDO predated the last checkpoint was never rolled back; the
checkpoint now records in-flight UNDO chains and recovery starts from them
(test_fileops/005).

3. An ordinary filesystem error turning into a PANIC inside a critical
section. Your diagnosis was exact and applied to every immediate
operation. The syscall now runs before the critical section, so a failure
is an ordinary ERROR with the usual message (test_fileops/004 / 016).

Correcting my earlier CTR claim... <face, palm>

Earlier in this thread I said the ATM lets recovery accept connections without
replaying UNDO. That is not entirely accurate.

- FLUX table and index UNDO: a live rollback applies the UNDO inline before
ROLLBACK returns, unless the chain exceeds undo_rollback_overflow_size
(16 MB) and an undo apply worker has room. After a crash, recovery queues
the losers and the per-backend undo workers revert them AFTER the database
is open, not the ATM.
- FILEOPS: cluster-wide UNDO for a crash loser is applied BEFORE the database
opens, linear in the loser's UNDO.

So "no UNDO replay before open" is true only for FLUX table/index UNDO,
via the per-backend workers, and not for FILEOPS. The cluster-wide
engine keeps UNDO in the WAL stream, so a long transaction pins WAL and
recovery walks loser chains.

"Constant time recovery" (CTR) in the SQL Server sense (a persistent
version store, O(1) abort, bounded recovery) was inspirational, but
recovery as of this version doesn't match that implementation or
claim as yet.

FILEOPS: what it is and is not

FILEOPS makes the filesystem mutations the server does outside the
buffer manager (CREATE/DROP DATABASE and TABLESPACE directories,
copydir) participate in transactions and in crash recovery, at wal_level
>= replica. It is NOT a replacement for smgr, and it is NOT a
self-contained crash-atomic resource manager yet, and I'm not sure that
should ever be the goal, but feel free to disagree with me on that.

Right now here are the honest limitations (also in the README and the
commit message):

- a commit-time operation (delete/rmdir at commit) is logged after the
commit record, so a crash in between can lose it (test_fileops/007 TODO);
rename is no longer one of them (it is immediate since v7);
- the syscall-before-WAL window;
- per-transaction, not per-operation, ownership of a crash loser's inverses;
- nothing for wal_level = minimal.

I've considered integration into the resource manager (self-contained
redo/undo from common WAL, durable intent before commit, per-operation
ownership) but I'm not sure where I'm going to take that idea next. I'm
open to ideas here.

FLUX: a new table AM that uses UNDO (not a HEAP replacement)

FLUX is an in-place-MVCC table access method here not as competition
with HEAP but more an illustration/research/proof-of-concept for an
UNDO-based table AM, not meant for production use (at this time).

Attempts to "make HEAP better by using UNDO" were all unsuccessful; HEAP
represents a set of trade-offs that are proven, well optimized, and
understood by all who use Postgres. The first rule of Database Club
is: "Never talk about Database Club", the second is "Don't break the
database." (or don't break what works). :-)

In FLUX, a row keeps its TID for its lifetime; the header carries
heap-shaped xmin/xmax/cid; older versions are reconstructed from the
per-backend UNDO log; row locks live in a shared, on-demand sLog (never
on the tuple).

Two FLUX notes worth stating up front:
- index-only scans are not used on a FLUX table (TODO). A delete-marked
index entry can carry a key that no longer matches the row until VACUUM;
- FLUX must never/will never depend on VACUUM for correctness; VACUUM
reclaims space and freezes for wraparound.

The sLog that holds row locks allocates nothing at startup (a cluster that
never uses FLUX pays almost nothing) and grows on demand.

Measured on a quiet 64-vCPU metal host, release builds, a pgbench scale-100
database, all at 64 concurrent clients; each number is the median of five
interleaved 60-second runs, in thousands of transactions per second (higher
is better). The custom scripts (all rows but -N) draw their rows from the
first 100,000 accounts only, a 1% hot set: they use :scale without passing
it, and pgbench reads the scale from the database only for its built-in
scripts. The comparison is like for like, but it is a hot-set measurement;
the metal results below use the whole table. Three FLUX builds are compared
against each other, with heap on the same v3 build as a reference:

- FLUX/v2 : FLUX as posted in v2 (the old fixed-size row-lock table)
- FLUX/v7 : FLUX in this series (the on-demand row-lock sLog)
- heap/v3 : the heap AM on the v3 build, for reference

workload (what each pgbench transaction does) FLUX/v2 FLUX/v7 heap/v3 v7 vs v2
--------------------------------------------- ------- ------- ------- --------
SELECT FOR UPDATE one row, then UPDATE it 293.0 306.5 359.0 +4.6%
INSERT a child row (foreign-key check on parent) 497.1 505.3 529.9 +1.6%
plain UPDATE (pgbench -N, no row locks) 229.3 243.6 291.7 +6.2%
SELECT 10 rows FOR UPDATE in one transaction 273.8 306.6 285.7 +12.0%
SELECT 100 rows FOR UPDATE in one transaction 20.4 33.0 35.1 +62.1%

So FLUX's row-lock path is at or above the v2 fixed-table store
everywhere, and now beats it by 12% (10-row) and 62% (100-row) at 64
clients.

What's in each patch?

0001 Add a cluster-wide UNDO engine with records in the WAL stream
The UNDO substrate: records live in the common WAL stream; crash recovery
reverts loser transactions' structural changes through a recovery pass.
Changes since v2: 6 items (unused AM prepare hook removed; headerscheck
skips undormgrlist.h; recovery-model docs made accurate; buffer content
locks released before inline abort UNDO; failed-abort WAL-hold claim
corrected; UNDO docs promoted to a chapter).

0002 Add transactional filesystem operations (FILEOPS) backed by UNDO
WAL resource manager (RM_FILEOPS_ID) making structural filesystem
mutations crash-safe and rollback-safe; AtPrepare path for 2PC.
Changes since v2: report apply failures as failures; remove obsolete UNDO
file cleanup. Tests: drop logical_revert_naptime in 063/061; show a crash
right after commit loses deferred ops.

0003 Route filesystem mutations through FILEOPS
Wire in-tree DDL/storage callers (CREATE/DROP DATABASE, TABLESPACE,
copydir) through the FILEOPS API. (No "Changes since v2" block.)

0004 Add per-backend UNDO logs
Per-backend UNDO logs and the undo apply/discard workers; the engine FLUX
table/index UNDO reverts crash losers from, after the database opens.
Changes since v2: 38 items (2PC rollback chains; subxact undo dispatch;
non-permanent/temp undo handling; 3-page record spanning; FPIs on undo
pages; snapshot-limited recovery start; project formatting + pgperltidy).

0005 Add the ATM, sLog, and logical revert worker
The aborted-transaction map, the on-demand sLog row-lock store, and the
logical revert worker.
Changes since v2: 24 items + 3 tests (retention report; inline UNDO always
for non-index; new-database dir removed on abort/crash; roll back
in-flight UNDO preceding the checkpoint; prepared row locks restored
without clobbering owners; "a cluster that never uses UNDO costs what
master does").

0006 nbtree: mark aborted index inserts dead via UNDO
Changes since v2: 22 items + 2 tests (flush index UNDO before commit
record; newest-first apply in crash recovery; CLR redo consistency-checked;
index UNDO applied inline only).

0007 hash: mark aborted index inserts dead via UNDO
Changes since v2: 14 items + 1 test (restore UNDO abort ordering; persist
FILEOPS ownership for deferred crash losers; dead-tuples hint on hash
rollback; PANIC only when an inverse cannot be made durable).

0008 nbtree: support in-place delete-marking for stable-TID UPDATE
Changes since v2: 11 items + 1 test (uniqueness check asks the AM about a
delete-marked entry; atomic delete-mark of a full leaf via replace split;
amcheck handles delete-marked entries; tombstone trailer room).

0009 [prep] tableam: let an AM declare that UPDATE keeps a row's TID
0010 [prep] executor: keep the pre-update row of an in-place UPDATE
0011 [prep] EvalPlanQual: fetch an in-place AM's reference rows with the query
snapshot
0012 [prep] AFTER triggers: queue an in-place AM's row images with the event
0013 [prep] logical replication: pre-check unique keys an in-place UPDATE changes
0014 [prep] tableam: SO_RETURN_DELETED, and update_deleted detection for any AM
0015 [prep] tableam: let a block AM's size estimate use its own default fillfactor
0016 [prep] tableam: let REPACK (CONCURRENTLY) accept any AM whose changes are
decoded
(adds TableAmRoutine.am_logical_decoding; REPACK tests it instead of
naming heap.)
0017 [prep] vacuum: add parallel_vacuum_set_index_stats() and a bulkdel injection
point
0018 [prep] pg_visibility: refuse tables of other table AMs in the tuple checks
(0009-0018 have no "Changes since v2" block except 0016; they are the new
heap-neutral interface preparation split out of the FLUX commit.)

0019 Add the FLUX in-place-MVCC table access method
In-place MVCC table AM: a row keeps its TID; heap-shaped xmin/xmax/cid in
the header; older versions from the per-backend UNDO log; row locks in the
shared on-demand sLog, never on the tuple.
Changes since v2: 60+ items (invisible aborted writers; idempotent UNDO
keyed on version; helping; TOAST supersede; VACUUM reclaims aborted-INSERT
rows and truncates the whole empty tail; tombstone-removal horizon
reasoning; scan slots own a copy under a writer-excluding lock; VACUUM
never moves a live row; row locks not held per-row until commit;
user_catalog_table refused; project formatting + pgperltidy).

0020 Benchmarks: HEAP vs FLUX
Benchmark scaffolding comparing heap and FLUX. (No "Changes since v2".)

Benchmarks: HEAP vs FLUX on metal

x86_64 c6id.metal (128 vCPU, 2 NUMA nodes) and aarch64 m7gd.metal (64
vCPU), 251 GiB, NVMe RAID0; external load drivers in the same placement
group; release builds; shared_buffers = 128 GB (50% of RAM); datasets ~2x
shared_buffers on HEAP. HEAP and FLUX on the same binary (only
default_table_access_method differs). Runs interleaved, starting AM
alternating by round, >= 10 minutes measured each, one golden restore per
run. Ratio of medians FLUX/HEAP with a 95% bootstrap CI; above 1 is
better for FLUX, except TPROC-H power, which is time. These were measured
on the v7 candidate BEFORE the lost-update and checkpoint fixes above;
TPROC-C was re-measured on the final tree (<TPCC-REMEASURE>).

x86_64 aarch64
HammerDB TPROC-C, 2680 WH
0.25x vCPU users 0.956 (0.928-0.984) 0.863 (0.853-0.873)
0.5x 0.952 (0.902-1.005) 0.890 (0.863-0.918)
1x 0.941 (0.872-1.017) 0.828 (0.811-0.845)
2x 0.896 (one run) 0.836 (0.806-0.866)
HammerDB TPROC-H SF100
power, geomean query time 1.60x slower 2.08x slower
throughput (vCPU/8 streams) 0.230 0.192
pgbench row-lock set, whole
table (scale 17000), 64/128
clients 0.89-0.98 (x86 only)
pgbench row-lock set, 1% hot
set (scale 100), 64 clients 0.90-1.03 (x86 only)
ledger benchmark, 50M accounts
mixed, 1x vCPU clients 0.852 0.854
hot - 1.053

The series costs HEAP little net new overhead, on the whole-table row-lock
et upstream runs 1.006-1.032x the series' HEAP (0.6-3.2%, all ten points;
the series adds the per-backend UNDO workers and the hooks of 0009-0018).

What this says about FLUX:

- OLTP that fits in memory runs at 0.83-0.98 of heap. Row locking on a
hot set is at parity or ahead (FLUX writes no lock into the tuple:
locking 100 rows writes 16.5 kB of WAL per commit vs heap's 37.7 kB).
Plain updates write more WAL (pgbench -N 1,047 vs 894 bytes/commit).

- FLUX scales with connections a little worse than heap: from 32 to 256
TPROC-C users heap gains 1.37x and FLUX 1.28x on x86; pgbench -N from
64 to 128 clients heap 1.51x, FLUX 1.44x.

- Analytics is where FLUX loses, and mostly because its tables are
1.3-1.5x larger for the same rows (indexes are the same size). At
SF100 heap's 226 GB nearly fits in shared_buffers plus the page cache
and FLUX's 304 GB does not, so the same plans read from the device:
Q14 reads 138M blocks vs heap's 16M, and with read I/O taken out runs
1.5x heap. The larger relpages also change three plans (Q7, Q17, Q18).
The size has three causes, in order: FLUX stores varlenas with their
4-byte header where heap converts them to 1-byte short headers (a
FLUX defect, to be fixed); a 36-byte tuple header against heap's 23
(the version-chain pointer and FLUX's own cmin/cmax); and a default
fillfactor of 90, kept so that a row can grow in place.

The full report (per-query TPROC-H times, latency percentiles, WAL and
undo bytes per transaction, the plans of every query > 2x heap, settings
and method) is attached as v7-benchmark-results.md in the .tgz with the
patches. It's early days for FLUX (and soon also RECNO) so much more
optimization to be had, and don't forget the missing bloat vs HEAP.

best.

-greg

[1] https://postgr.es/m/10eD4qtjEu2S7fepnPrBqxg73zuMbBCHN1MOEsbrwlPgOoGVzpzOchAF1GqaU8tKrIVCykGm7HVl6EUUonIheJNr9OQ2C5uajOLd1WLThlI=@burd.me