From: Greg Burd <greg@burd.me>
To: pgsql-hackers@postgresql.org
Subject: Re: Tepid: selective index updates for heap relations
In-Reply-To: <ODJlxLVOyurrUHvH-gJ3D5MNxuOiQB8fI3QqCZfgoDwePEGb83YCteB90wuw4CNKt6OK6G_Wr73Pyon1hpvhxDhiTngz5zJ8o4ExSkhpAhM=@burd.me>
References: <af02b486-6adc-494c-a357-1dd72f655dcf@app.fastmail.com>
 <ODJlxLVOyurrUHvH-gJ3D5MNxuOiQB8fI3QqCZfgoDwePEGb83YCteB90wuw4CNKt6OK6G_Wr73Pyon1hpvhxDhiTngz5zJ8o4ExSkhpAhM=@burd.me>

Attached is v72, rebased through upstream 9e7d1524. The series now has
26 patches, excluding my local development setup. I have kept the
locator/executor interfaces separate from the heap implementation.

I also ran the benchmarks I promised in v71. The first campaign found
wrong results, which I fixed before running the second campaign. There
are useful update gains, but also read and CLUSTER regressions that I
don't want to hide behind an update-throughput number.

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 did not obtain FreeBSD pressure-I/O results: the host failed its
recovery deadline after a planned native memory-cap reboot, before any
of the 18 I/O trials started. I collected the completed phases first,
then terminated the instance and removed the dedicated resources.
Also, the sampled read-latency logs contain zero-microsecond anomalies;
I am reporting throughput checked against transactions and elapsed time,
not claiming validated read-latency percentiles.

Attachments and remaining work
------------------------------

tepid-v72.tar.gz contains the patches, base information and validation
summary. tepid-v72-results-and-harness.tar.gz contains the measurement
harnesses, reports and paired results. I also prepared a larger
tepid-v72-benchmarks.tar.gz with selected per-trial evidence; I can make
that and the remaining raw archives available separately. The bundles
keep the unfixed Debian and repaired FreeBSD results separate and
record their source revisions.

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.
