On Monday, October 26th, 2026 at 11:49 AM, Greg Burd <[email protected]> wrote:
> Hey David,
>
> I built the overflow-signal prototype and measured it, including the two
> adversarial cases you flagged. Short version: it recovers the scaling,
> and the delete-heavy and high-duplicate cases behave better than either
> of us feared, not worse.
>
> Design: an insert that fits without allocating a new overflow page
> doesn't touch the metapage at all, it just bumps a per-backend pending
> count. Only an insert that adds an overflow page flushes that pending
> count into hashm_ntuples and asks for a split. The flush path already
> holds the metapage exclusive lock (that's where _hash_addovflpage
> allocated the page), so it costs nothing extra there.
> _hash_expandtable()'s own threshold re-check stays as the split
> decision, which turns out to matter (see the duplicate case below). The
> batched delta rides in the insert WAL record so a standby advances
> hashm_ntuples in the same steps.
>
> Box: c7i.8xlarge, 32 vCPU, one NUMA node, data on tmpfs, master built -O2.
>
> Scaling (pgbench, single-row INSERT, 2M preload, synchronous_commit off, 20s,
> best of 3):
>
> clients 1 16 32 64
> master 38810 361819 288961 262614
> overflow 40743 487632 558926 588115
>
> Same shape as the batched POC: master peaks at 16 clients and declines,
> overflow keeps scaling, 2.24x at 64 and no decline.
>
> > deletes will leave partially filled pages
>
> Delete-heavy (insert 2M, delete 90%, insert 2M more):
>
> master: 18692 pages, 13030 buckets, ntuples 4000000
> overflow: 16695 pages, 11980 buckets, ntuples 3898197
>
> It doesn't over-split after deletes, it splits *less*, because a split
> only fires when a bucket actually overflows, and deletes that free space
> mean fewer overflow events. So the direction of the concern is
> backwards: the local signal reacts to real fullness, where the global
> counter keeps climbing across deletes until VACUUM corrects it.
> (ntuples runs ~2.5% low from the unflushed pending counts; harmless, it
> only times splits and VACUUM recomputes it.)
>
> > splitting overflow pages full of unique values won't cut down on the number
> > of overflow pages when splitting
>
> This was the one I thought could loop, and it doesn't. High-duplicate
> load, 2M rows in 50 distinct keys:
>
> master: 12070 pages, 6515 buckets
> overflow: 10044 pages, 4931 buckets
>
> and as a direct split-loop check, 500k inserts of a *single* key:
>
> overflow: maxbucket = 1229
>
> 1229 is the normal bucket count for 500k tuples at the fill factor, not
> an explosion. If an overflow-triggered split were looping on the
> duplicates, maxbucket would run away. It doesn't, because
> _hash_expandtable() re-checks hashm_ntuples against the threshold and
> declines a split the count doesn't warrant. A bucket that overflows
> purely on duplicate keys asks for a split and gets told no. Master
> actually over-splits more here, testing the threshold on every insert.
>
> Correctness: mixed unique/duplicate/delete load gives identical row
> counts to master (596572), and index probes match seqscan on both unique
> and heavy- duplicate values. A streaming standby replays clean and ends
> at the same row count as the primary, with index probes matching.
>
> One thing worth separating out: with wal_consistency_checking='hash' the
> standby fails with "incorrect resource manager data checksum". That is
> *not* this change, unpatched master fails the same way under the same
> setting. It's the REGBUF_NO_CHANGE full-page-image bug from the other
> thread ("WAL record CRC calculated incorrectly because of underlying
> buffer modification") [0]. The two are orthogonal; with consistency
> checking off, this change replays cleanly.
>
> So I think the overflow signal is the better answer, for the reason you
> gave: it replaces a global counter on the hot path with a local signal
> the inserter already has, and the threshold re-check in
> _hash_expandtable() is a natural guard against the duplicate-key
> pathology.
>
> I'll clean the prototype into a real patch, the pending-count storage is
> still a fixed backend-local array that loses its count on backend exit,
> which is fine for correctness but I want to look at whether a relcache
> home is worth it. Prototype diff attached for reference (not submission
> quality yet).
>
> best,
>
> -greg
>
> [0] https://postgr.es/m/ats4pi-RM0Uom0T9%40floki
I forgot to attach it, lol. :)
-greg
diff --git a/src/backend/access/hash/hash_xlog.c b/src/backend/access/hash/hash_xlog.c
index e9a2b9aa9a..edd13b9f51 100644
--- a/src/backend/access/hash/hash_xlog.c
+++ b/src/backend/access/hash/hash_xlog.c
@@ -122,8 +122,10 @@ hash_xlog_insert(XLogReaderState *record)
}
if (BufferIsValid(buffer))
UnlockReleaseBuffer(buffer);
+ buffer = InvalidBuffer;
- if (XLogReadBufferForRedo(record, 1, &buffer) == BLK_NEEDS_REDO)
+ if (XLogRecHasBlockRef(record, 1) &&
+ XLogReadBufferForRedo(record, 1, &buffer) == BLK_NEEDS_REDO)
{
/*
* Note: in normal operation, we'd update the metapage while still
@@ -133,7 +135,7 @@ hash_xlog_insert(XLogReaderState *record)
*/
page = BufferGetPage(buffer);
metap = HashPageGetMeta(page);
- metap->hashm_ntuples += 1;
+ metap->hashm_ntuples += (double) xlrec->ntup_delta;
PageSetLSN(page, lsn);
MarkBufferDirty(buffer);
diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c
index 3395bbc13f..d37c37fd4b 100644
--- a/src/backend/access/hash/hashinsert.c
+++ b/src/backend/access/hash/hashinsert.c
@@ -22,6 +22,49 @@
#include "storage/predicate.h"
#include "utils/rel.h"
+/*
+ * Overflow-signal prototype (hash metapage contention). Every insert used to
+ * exclusive-lock the metapage to bump hashm_ntuples and test the split
+ * threshold, serialising all inserters on one buffer. Instead each backend
+ * accumulates a pending per-index count and only touches the metapage when an
+ * insert had to add an overflow page -- the local signal a bucket is
+ * over-full. Then it flushes the pending count into hashm_ntuples (the
+ * metapage is already locked by _hash_addovflpage on that path) and asks for a
+ * split. _hash_expandtable() re-checks the threshold, so a split that cannot
+ * relieve overflow (duplicate keys) is declined there. The batched delta is
+ * carried in the insert WAL record so the standby advances hashm_ntuples in
+ * the same steps. A pending count lost at backend exit is harmless:
+ * hashm_ntuples only drives split timing and VACUUM recomputes it.
+ */
+typedef struct HashPendingCount
+{
+ Oid relid;
+ uint32 pending;
+} HashPendingCount;
+
+#define HASH_PENDING_SLOTS 64
+static HashPendingCount hash_pending[HASH_PENDING_SLOTS];
+
+static uint32 *
+hash_pending_slot(Oid relid)
+{
+ int i;
+ int free_slot = -1;
+
+ for (i = 0; i < HASH_PENDING_SLOTS; i++)
+ {
+ if (hash_pending[i].relid == relid)
+ return &hash_pending[i].pending;
+ if (free_slot < 0 && hash_pending[i].relid == InvalidOid)
+ free_slot = i;
+ }
+ if (free_slot < 0)
+ free_slot = (int) (relid % HASH_PENDING_SLOTS);
+ hash_pending[free_slot].relid = relid;
+ hash_pending[free_slot].pending = 0;
+ return &hash_pending[free_slot].pending;
+}
+
static void _hash_vacuum_one_page(Relation rel, Relation hrel,
Buffer metabuf, Buffer buf);
@@ -47,6 +90,8 @@ _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel, bool sorted)
HashPageOpaque pageopaque;
Size itemsz;
bool do_expand;
+ bool added_ovfl = false;
+ uint32 ntup_delta = 0;
uint32 hashkey;
Bucket bucket;
OffsetNumber itup_off;
@@ -181,6 +226,7 @@ restart_insert:
/* chain to a new overflow page */
buf = _hash_addovflpage(rel, metabuf, buf, (buf == bucket_buf));
page = BufferGetPage(buf);
+ added_ovfl = true;
/* should fit now, given test above */
Assert(PageGetFreeSpace(page) >= itemsz);
@@ -191,10 +237,15 @@ restart_insert:
}
/*
- * Write-lock the metapage so we can increment the tuple count. After
- * incrementing it, check to see if it's time for a split.
+ * Overflow-signal prototype: on the common path (tuple fit without a new
+ * overflow page) do not lock or touch the metapage; just accumulate a
+ * pending count. Only when this insert added an overflow page do we flush
+ * the pending count into hashm_ntuples and ask for a split. On that path
+ * _hash_addovflpage already exclusive-locked and released the metapage, so
+ * re-locking is the same cost that path already paid.
*/
- LockBuffer(metabuf, BUFFER_LOCK_EXCLUSIVE);
+ if (added_ovfl)
+ LockBuffer(metabuf, BUFFER_LOCK_EXCLUSIVE);
/* Do the update. No ereport(ERROR) until changes are logged */
START_CRIT_SECTION();
@@ -203,15 +254,27 @@ restart_insert:
itup_off = _hash_pgaddtup(rel, buf, itemsz, itup, sorted);
MarkBufferDirty(buf);
- /* metapage operations */
- metap = HashPageGetMeta(metapage);
- metap->hashm_ntuples += 1;
+ do_expand = false;
+ {
+ uint32 *pending = hash_pending_slot(RelationGetRelid(rel));
+
+ if (added_ovfl)
+ {
+ ntup_delta = *pending + 1;
+ *pending = 0;
- /* Make sure this stays in sync with _hash_expandtable() */
- do_expand = metap->hashm_ntuples >
- (double) metap->hashm_ffactor * (metap->hashm_maxbucket + 1);
+ metap = HashPageGetMeta(metapage);
+ metap->hashm_ntuples += (double) ntup_delta;
- MarkBufferDirty(metabuf);
+ /* Make sure this stays in sync with _hash_expandtable() */
+ do_expand = metap->hashm_ntuples >
+ (double) metap->hashm_ffactor * (metap->hashm_maxbucket + 1);
+
+ MarkBufferDirty(metabuf);
+ }
+ else
+ (*pending)++;
+ }
/* XLOG stuff */
if (RelationNeedsWAL(rel))
@@ -219,11 +282,13 @@ restart_insert:
xl_hash_insert xlrec;
xlrec.offnum = itup_off;
+ xlrec.ntup_delta = ntup_delta; /* 0 unless this record flushed */
XLogBeginInsert();
XLogRegisterData(&xlrec, SizeOfHashInsert);
- XLogRegisterBuffer(1, metabuf, REGBUF_STANDARD);
+ if (added_ovfl)
+ XLogRegisterBuffer(1, metabuf, REGBUF_STANDARD);
XLogRegisterBuffer(0, buf, REGBUF_STANDARD);
XLogRegisterBufData(0, itup, IndexTupleSize(itup));
@@ -234,12 +299,13 @@ restart_insert:
recptr = XLogGetFakeLSN(rel);
PageSetLSN(BufferGetPage(buf), recptr);
- PageSetLSN(BufferGetPage(metabuf), recptr);
+ if (added_ovfl)
+ PageSetLSN(BufferGetPage(metabuf), recptr);
END_CRIT_SECTION();
- /* drop lock on metapage, but keep pin */
- LockBuffer(metabuf, BUFFER_LOCK_UNLOCK);
+ if (added_ovfl)
+ LockBuffer(metabuf, BUFFER_LOCK_UNLOCK);
/*
* Release the modified page and ensure to release the pin on primary
diff --git a/src/include/access/hash_xlog.h b/src/include/access/hash_xlog.h
index 149aed69dc..a0e51560db 100644
--- a/src/include/access/hash_xlog.h
+++ b/src/include/access/hash_xlog.h
@@ -62,9 +62,10 @@
typedef struct xl_hash_insert
{
OffsetNumber offnum;
+ uint32 ntup_delta; /* batched hashm_ntuples increment (0 if none) */
} xl_hash_insert;
-#define SizeOfHashInsert (offsetof(xl_hash_insert, offnum) + sizeof(OffsetNumber))
+#define SizeOfHashInsert (offsetof(xl_hash_insert, ntup_delta) + sizeof(uint32))
/*
* This is what we need to know about addition of overflow page.
