Database re-examination update — Part 1

This is the hinge. I’m checking PostgreSQL 16.14’s actual executor/heap code rather than relying on current-development discussions, because HeapDetermineColumnsInfo does compare tuple values in some paths—but the question is whether ordinary executor UPDATEs in PostgreSQL 16 use that comparison for HOT eligibility or pass a SET-list attribute bitmap instead.

The version-specific check resolves it: Claude’s description matches proposed newer work, not PostgreSQL 16’s ordinary executor path. In 16, an indexed attribute named in the UPDATE target list blocks HOT without comparing whether the replacement value is byte-identical; HeapDetermineColumnsInfo’s value comparison is being applied to the wrong path. I’m drafting the correction, including why n_tup_newpage_upd is not itself the non-HOT counter.

:::writing{variant="standard" id="18453"}
The HOT premise does not hold for ordinary PostgreSQL 16 executor updates.

HeapDetermineColumnsInfo does compare old and new tuple values, but that comparison is being attributed to the wrong update path. In PostgreSQL 16, ordinary SQL UPDATE/ON CONFLICT DO UPDATE receives an updated-column bitmap derived from the update target list. If an attribute referenced by a non-summarizing index appears in that bitmap, HOT is blocked without first proving that the resulting indexed value changed byte-for-byte.

Here the UPSERT target list contains:

SET text = EXCLUDED.text,
    token_count = EXCLUDED.token_count

And text is an input to the expression index:

CREATE INDEX chunks_text_trgm
ON content.chunks
USING gin (COALESCE(text, '') gin_trgm_ops);

Therefore text is HOT-blocking in PostgreSQL 16 even when the incoming text equals the stored text.

This exact limitation is the subject of newer PostgreSQL development work proposing value-aware HOT/selective-index decisions. The PostgreSQL developers’ discussion describes the existing behavior as taking the “indexed attribute updated” path even when the indexed value did not change. That proposed machinery is not part of PostgreSQL 16. PostgreSQL hackers discussion

PostgreSQL 16’s documented rule remains that HOT is possible only when the update does not modify columns referenced by indexes and the replacement tuple fits on the same page. PostgreSQL 16 HOT documentation

So the original physical contradiction remains:

• Approximately 9.14M conflict updates occurred.
• They named an indexed text column in the update.
• They were not HOT-eligible.
• They generated replacement heap versions and successor index entries.
• Current heap and HNSW sizes still imply later cleanup or rebuilding.

The proposed statistics query remains useful, but n_tup_newpage_upd needs a narrower interpretation.

The relevant relationships are:

n_tup_upd
├── n_tup_hot_upd
└── non-HOT updates
    ├── same-page non-HOT
    └── n_tup_newpage_upd

n_tup_newpage_upd counts updates whose successor landed on another page. It does not count every non-HOT update. A non-HOT successor can still fit on the original page while requiring new index entries.

Therefore:

non-HOT total = n_tup_upd - n_tup_hot_upd

n_tup_newpage_upd then tells us how many of those non-HOT updates also moved pages.

For this workload, if March counters survived, the prediction is:

n_tup_upd - n_tup_hot_upd ≈ 9.14M
n_tup_hot_upd ≈ 0 for these duplicate chunk updates

If n_tup_hot_upd is unexpectedly in the millions, that would be a finding requiring explanation—such as a different index definition during the rerun—not confirmation of identical-value HOT under the inspected PostgreSQL 16 schema.

One qualification to the “5.8 TB of reading” statement: 216 scans over a 27 GB heap represent approximately 5.8 TB of logical heap-page scanning, but not necessarily 5.8 TB of physical storage reads because the operating-system and PostgreSQL caches may satisfy many accesses.

Those scans could prune HOT chains encountered on visited pages. They could not prune away the non-HOT versions at issue while their independent index entries still required cleanup. Thus the repeated COUNT(*) scans explain elapsed time, but not disappearance of millions of non-HOT update versions.

The compaction question remains real:

> What happened after the March 2026 rerun that removed approximately 9.14M non-HOT heap versions and their successor index debris?

The query should still be run exactly as proposed:

SELECT
    n_tup_upd,
    n_tup_hot_upd,
    n_tup_newpage_upd,
    n_tup_ins,
    n_tup_del,
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'content'
  AND relname = 'chunks';

Interpretation:

• n_tup_upd around 9.14M or higher: March update history survived.
• n_tup_upd near zero: cumulative history was reset or discarded.
• n_tup_hot_upd near zero: expected under the March schema.
• n_tup_newpage_upd large: many replacements moved to other pages.
• n_tup_upd - n_tup_hot_upd large: the actual non-HOT population.
• Current counters alone still cannot identify the cleanup operation if they reset on July 31.

So the proposed “no compaction required” resolution is withdrawn. It depends on value-aware HOT eligibility that PostgreSQL 16 does not provide for this ordinary UPSERT path.
:::

That source-level distinction is specific enough that it deserves a direct code reading. I’m checking the actual heap_update() call chain and pruning semantics now—especially whether non-HOT tuple bodies can become LP_DEAD without index cleanup, because that second claim determines whether the 27 GB heap really stops being evidence.

I’m checking the pruning claim at the source-code level before answering, because it now determines whether there is any heap-size contradiction left. I’m also using the writing-block skill so the result is cleanly pasteable back to Claude.

The source check resolves one of Claude’s stated uncertainties: on-access pruning does record newly useful free space in the free-space map in the relevant code path, so reuse is not merely speculative. I’m narrowing the remaining issue to what PostgreSQL 16 counts after a tuple becomes LP_DEAD, and whether the repeated scans could actually drive that transition.

:::writing{variant="chat_message" id="58314"}
The HOT correction is yours. I checked PostgreSQL 16.14 itself, and my target-list argument was wrong.

heap_update() constructs modified_attrs through HeapDetermineColumnsInfo(), which compares the old and new datums using heap_attr_equals(). HOT is then selected when the successor fits on the same page and modified_attrs does not overlap the HOT-blocking index attributes. ExecGetUpdatedCols() is not that eligibility gate. PostgreSQL 16 heapam.c

One qualification: this is binary datum equality, not semantic text equality. UPDATE t SET indexed_col = indexed_col normally preserves the identical physical datum. This script supplies EXCLUDED.text reconstructed from client input. If the stored value is compressed or externally TOASTed while the incoming value has another representation, logically identical text can compare unequal. For ordinary inline text with the same bytes, your conclusion holds.

Your LP_DEAD point is also correct: opportunistic pruning can remove the body of a dead non-HOT tuple while retaining its four-byte line pointer for outstanding index references. So “nine million old versions necessarily occupy another 14 GB now” is wrong.

But the heap-size contradiction does not dissolve completely, because PostgreSQL 16 settles your free-space uncertainty in the unfavorable direction. Immediately after on-access pruning, heap_page_prune_opt() deliberately does not update the free-space map:

> “We avoid reuse of any free space created on the page by unrelated UPDATEs/INSERTs … The free space should be reused by UPDATEs to this page.”

PostgreSQL 16 pruneheap.c

That matters sharply for this script:

• All updates for one file occur in one transaction, so their old versions cannot become globally removable while that transaction is still running.
• The transaction commits.
• The following full COUNT(*) can prune that file’s old versions.
• But the next file advances to different document pages, and the freed space from the preceding file was not entered into the FSM.

Therefore those repeated scans can leave the original pages largely empty except for LP_DEAD stubs while the non-HOT successors remain in newly allocated pages. Pruning after each commit is too late to let most of those same updates reuse the recovered space. Substantial non-HOT movement should still have raised the heap’s high-water mark, unless later updates revisited the same pages or most successors fitted on-page and were HOT.

The low dead count also remains a discriminator. PostgreSQL 16’s ANALYZE code explicitly counts every LP_DEAD item as one dead row because VACUUM is still required to remove it. PostgreSQL 16 heapam_handler.c So if approximately nine million committed non-HOT updates were pruned into stubs before the July ANALYZE, n_dead_tup should reflect them. Pruning does not make that evidence disappear. Conversely, pruned HOT-chain members become LP_UNUSED, and PostgreSQL adjusts the dead-tuple counter downward; a low count is entirely natural if HOT dominated.

So the clean split is now:

• Mostly HOT: compact heap, compact indexes, and low dead count all follow without maintenance.
• Mostly non-HOT/new-page: index debris, heap extension, and millions of LP_DEAD items remain expected; some later cleanup event is still required.
• Counters reset: the cumulative update columns cannot decide the historical branch, and physical inspection must carry more weight.

I agree that the query should decide before either of us explains its result. Add the database reset timestamp so the counters date themselves:

SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd,
       n_tup_ins, n_tup_del,
       n_live_tup, n_dead_tup, n_mod_since_analyze,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'content' AND relname = 'chunks';

SELECT stats_reset
FROM pg_stat_database
WHERE datname = current_database();

One final precision: the sequence gap includes any last, uncommitted file whose transaction rolled back, whereas n_tup_upd records committed updates. So the expected update total may be somewhat below 9.14M even with intact counters. It should still be in the same multi-million range if the rerun reached file 216.

Corrected state: HOT eligibility is value-based; heap-body reclamation by pruning is real; but PostgreSQL 16’s deliberate non-update of the FSM and ANALYZE’s explicit counting of LP_DEAD prevent the non-HOT branch from becoming physically invisible.
:::

Understood. We’ll pause here; the entire technical chain remains in this conversation, including the latest corrected PostgreSQL/HOT analysis and the exact queries waiting to be run.

An “authorization failure” at Anthropic is usually a session/account-side problem rather than anything caused by this exchange. When Claude becomes accessible again, paste my latest response and we’ll resume from its reply—no reconstruction needed.

Most likely, no—we will not need to recreate the whole database.

What we have found so far looks more like an inefficient duplicate ingestion run than corrupted data. The exact live chunk count agrees through two independent measurements, and sequence gaps are harmless in PostgreSQL. Even substantial table/index bloat can usually be repaired without discarding the logical contents.

There are three separable layers:

• The 13.7 million text chunks: probably worth preserving.
• Embeddings and indexes: can be rebuilt independently if necessary.
• The memory architecture/schema: can usually be added or migrated without reingesting the corpus.

We would start fresh only if we establish that the chunk contents are wrong, the corpus needs a different chunking strategy, or repairing the existing structure would be more expensive and uncertain than rebuilding it. None of that has been established.

If a rebuild eventually is the cleanest answer, I would recommend creating a new database beside the existing one, validating it, and retaining the original until the replacement is proven complete. We won’t erase the current database on a theory.

So the current working answer is: preserve it, finish the audit, repair or migrate in place where practical, and rebuild only the derived portions that actually require it. Your willingness to start fresh gives us freedom, but it isn’t yet necessary.