Re: Tepid: selective index updates for heap relations

Hello,

Attached is v72, rebased onto 9e7d1524. The series now has 26 patches,
more than I'd anticipated and hard for anyone to commit to a review at
that scale. Apologies, perhapes I'll break out the locator contract
into a separate thread next.

I ran the benchmarks I promised in the v71 email which turned up a few
bugs, which I've fixed. It's always good idea to test at scale to find
issues.

TL;DR
-----

The anticipated wins are in this series, the other shapes showed almost
no change of significance.

Mixed 80/20 read/update +32.26%
Update one of eight indexed columns +51.56%

The worst impact:
Plain-index scan after churn -5.94%

Changes since v71
-----------------

The first addition is the TM_FailureData abstraction I deferred
in v71. Patch 03 moves ctid, xmax and cmax into HeapTupleFailureData.
Table-AM callers instead receive same-command, current-transaction and
partition-move results. The adapter initializes the semantic outputs on
each call and reads heap fields only for results that define them.
retargeted accumulates across retries within one lock call. Executor,
trigger and replication callers no longer interpret raw heap metadata.
Tests cover the command/transaction distinctions, including MERGE and
subtransactions.

The other five additions fix problems found during testing:

* Repeated pruning could bypass a preserved changed-attribute bitmap
stub. An old index entry then looked valid for the current tuple. A
20-row example returned 24 rows through a unique index, and WHERE u=1
could return a row whose current value was -1. Pruning now preserves
the hop information and forwarding links. Tests cover repeated and
opportunistic pruning, ordering, offset reuse and WAL replay.

* amcheck's heapallindexed check confused a valid old chain anchor with
the live tuple's own TID. It now compares coverage through the visible
tuple while retaining the stored index key and rejecting stale anchors
for that index. It validates physical chain structure before using the
heap visibility walker, so corrupt links do not turn the checker into
an assertion failure. This is separate from the wrong-query fix.

* HOT-indexed redo did not initialize a new visibility-map page when no
applicable full-page image existed. It now initializes the page before
setting the locator-split bit. Recovery tests cover full_page_writes
settings and VM tail truncation. I kept the existing WAL consistency
masking rules; strict bitmap comparison would reject legitimate
unlogged truncation changes.

* Concurrent index validation could insert a second entry for a tuple
that already had a valid per-index chain anchor. Validation now checks
those anchors under its snapshot before adding an entry. The tests
include REINDEX CONCURRENTLY and changed, unchanged and A-to-B-to-A
keys.

* Unique checks requested a key recheck after any indexed-attribute hop,
even when the arriving index's attributes had not changed. That could
hit an assertion on an unchanged expression index. The existing
fetch-check interface now accepts the index relation and reports
possible staleness for that index. This avoids evaluating expressions
while holding a btree leaf lock.

The patch order is:

01-08 Locator contract, semantic failures, version naming, triggers
and bitmap hooks
09 Existing HOT behavior tests
10-13 Attribute comparison and selective index maintenance
14-16 Heap format, visibility map, update and prune/vacuum support
17-19 Statistics, amcheck and replication apply option
20-21 [DO NOT MERGE] Test AMs and in-tree benchmark harness
22-26 The five correctness fixes described above

I have left the fixes as separate patches to make the differences from
v71 reviewable. The complete testable series includes all 26 patches;
the two [DO NOT MERGE] patches are not the end of this version's series.
The VM remains four bits per heap block. This version does not add
native variable-width locator transport or an in-place table AM.

Testing
-------

The final rebased tip, 40e568cb, passed a build with assertions,
injection points and -Werror, then fresh test setup and world testing:
405 passed, 0 failed, 36 skipped. The extra pass versus the earlier
404-test result is the new upstream hash-index regression suite.
I also reran the original wrong-results reproducer and strict amcheck
on the final installation.

Each of the 26 submitted commits passed a warning-free build, fresh
setup and 239 core regression tests. Isolation counts were 134 for the
first 16 patches and 135 thereafter. I rebuilt and retested the five
rewritten tail commits after folding the test dependencies below.

I fixed two missing GNU Make recovery-test dependencies during export:
pageinspect in patch 22 and pg_buffercache in patch 24. Fresh temporary
installs first reproduced each missing-extension failure, then passed
all 125 assertions with the dependencies present. Those two Makefile
lines are the only changes beyond replaying the existing series onto
the new base.

Before the rebase, the integrated repair build passed the local default
world suite: 404 passed, 0 failed, 36 skipped. The original reproducer
returned 20 rows through each of sequential, plain-index and bitmap
scans, with no obsolete-key match. Two 65536-row, 160-step stress runs,
with checksums on and off, passed result comparisons and strict amcheck
checks before and after crash recovery.

On FreeBSD the default world suite recorded 401 passed, 1 failed and 31
skipped. The failure was psql filename completion; I reproduced the same
timeout on the baseline. I am not counting that run as a world pass.
All four native database gates and all completed benchmark correctness
checks passed. Those checks include exact row multisets, plan checks,
feature counters and heapallindexed verification. The first FreeBSD
harness attempt failed before timing because it checked feature counters
after a deliberate crash reset; I corrected it to check before the crash
and retained that failed attempt.

Performance
-----------

I ran separate Debian 13 and FreeBSD 15.1-p4 campaigns on EC2
m6id.metal: two sockets, 64 cores/128 threads, 512 GiB RAM and four
local NVMe drives. The FreeBSD run used UFS over a GEOM stripe and
eight physical cores on one NUMA domain. CPU pinning is not a claim of
memory binding. Builds, durability settings and placement matched within
each campaign. Each trial used a fresh fixture, and only one workload
ran on the host at a time.

I compared the branch base, the foundations through the bitmap hooks,
and the full series. The FreeBSD measurements used repaired commit
23b76c05, baseline 5fc25dec and foundations 7e5ef84e. They precede this
rebase. I have not rerun throughput on the new base, and I am not
comparing FreeBSD TPS directly with Debian TPS.

FreeBSD paired-median TPS changes for full versus baseline were:

Workload Change
------------------------------------- -------
No-op update, eight secondary indexes -0.99%
Ordinary HOT update -0.46%
Update all eight indexed columns +0.74%
Pristine plain-index scan -1.78%
Pristine index-only scan -1.93%
Mixed 80/20 read/update +32.26%
Exact bitmap scan -2.01%
Lossy bitmap scan +0.31%
Update one of eight indexed columns +51.56%
Plain-index scan after churn -5.94%
Lossy bitmap scan after churn +3.54%

These are medians of three matched-repeat ratios, with 60-second
comparison runs and alternating arms. The two phases completed 78 and 33
trials, including their A/A controls. Maximum absolute A/A paired biases
were 0.634% and 0.092%. That does not establish precision for all of the
small differences above.

The one-column update gain ranged from 51.29% to 52.26%. About 83% of
its timed updates were HOT-indexed. The churned plain-index scan was
slower in all three pairs, from 3.57% to 7.21%. Churn produced different
index layouts between arms, so this measures the resulting feature
state, not equal-layout scan overhead. I did not complete a five-repeat
confirmation of that read regression. After the five-update churn, index
main-fork bytes were 47.5 MB baseline versus 38.4 MB full, with equal
heap main-fork sizes.

For the one-column update, median WAL bytes per transaction fell from
884.86 to 384.20 (LSN delta, including any background WAL). Full ended
with larger heap and index forks because it performed more updates in
the fixed interval. I do not have an equal-work bloat conclusion.

The foundations-only core results ranged from about -1.01% to +0.58%
versus baseline. This addresses part of the interface-overhead question
from v71, but does not establish zero overhead for other workloads.

CLUSTER shows a larger cost. With 300k rows and four balanced repeats,
full/baseline statement time increased 61.9% for pristine multi-index
fixtures and 54.4% after churn. Single-index increases were 2.7% and
3.0%. These were short, instrumented warm-cache statements; I have not
profiled the remaining cost into a source-level attribution.

I also ran 128 CLUSTER/trigger/relation-context cases, 32 warm PG17
upgrade cells and eight separate upgrade cells containing VM states 0, 1
and 3. All passed their checks. At 256 relations I measured about 2.4 kB
more backend context memory per relation after commit in full. That
includes catalog and index state, not just locator descriptors. The
wrapper AM tests exercise image/spill/rollback machinery, not a real
overwriting AM. With work_mem=64kB the deferred wrapper case held about
7.1 MB of used backend context and 23.0 MB of live temp storage before
commit. At 64MB it held about 38.2 MB in memory without spill; after
commit, used context was about 2.8 MB and live temp storage was
zero. work_mem does not bound total event/backend memory, and logical
rollback need not shrink a physical temp file. I did not sum backend RSS
or infer a memory leak.

The upgrade cells cover link/copy, checksums on/off, few/many relations
and dense/sparse VM layouts. Exact rows and logical VM maps survived.
Total upgrade times were about 1.73-2.33 seconds. One pair per
configuration and roughly 50 ms timing granularity are not enough to
claim an upgrade performance difference. Cold-cache FreeBSD upgrades
remain untested.

The earlier Debian run used the unfixed candidate 85e75674. I retained
its failures and results, but do not offer its active-update timings as
safe gains. Its five-repeat pristine-index confirmation showed a -3.25%
paired median. A separate 12 GiB fixture measured VM growth from 49 to
97 pages, or 393216 bytes, with other captured forks unchanged. That
follow-up did not retroactively fix the original I/O run's failed size
comparison gate. In those 18 pristine read-only trials, warm median TPS
was 130770 baseline versus 130949 full; under memory pressure it was
18700 versus 18705. Pressure trials recorded about 144 GB of device
reads per 60 seconds, while warm trials recorded none. These remain
descriptive observations with a failed publication gate.

Debian also completed 192 PG17 upgrade cells with three matched pairs
per configuration. All passed their integrity checks. All 12
cold-VM/many-relations/link full trials took longer, by 0.050-0.151
seconds (median 0.100 seconds; configuration paired medians about 6%).
The roughly 50 ms timing granularity and 1.416-2.218 second total calls
do not isolate VM conversion cost. Cold here means verified VM-page
residency, not a cold whole cluster. Conversion also pads allocated VM
forks: 128 small tables grew from 1 MiB of VM storage to 2 MiB even
though each table's entries fit within one page in either format.

The unfixed Debian multi-index CLUSTER runs took 39.4% longer pristine
and 35.4% longer after churn, by ratios of arm medians, with an extra
spilling tuple sort of about 86.6 MiB. Four full CLUSTER executions had
failed correctness status; completed statements do not make those runs
green. The repaired FreeBSD results above provide the separate checked
comparison. The attached reports also retain the Debian foundations
controls, trigger/resource measurements and initial performance screen;
I have not pooled those repeats with the later confirmation.

I would appreciate review of the semantic failure interface and the heap
anchor/pruning rules. The VM trade-off, slower churned reads and
multi-index CLUSTER cost still need scrutiny. I think the update results
justify continuing, but they do not settle those trade-offs.

best.

-greg