Index bloat and B-tree page merge, part 1

session summary

This B-tree page-merge session moved from automated REINDEX toward fixing the cause of persistent bloat. The group defined the target as partially empty leaf pages that vacuum cannot reclaim, then had an agent build a logical leaf-page scan that reports adjacent pages fitting into one block. On a randomized 90%-deleted workload it found widespread merge opportunities. They did not implement merging: backward-scan visibility, transaction horizons, WAL replay, lock coupling, and undetectable lightweight-lock deadlocks were identified as the core design problems.

timecodes

1. 00:30 From automated REINDEX to B-tree page merge
2. 08:00 Defining the persistent bloat worth fixing
3. 17:30 Planning a logical scan for merge candidates
4. 24:30 Agent-assisted prototype of the merge checker
5. 54:30 Correcting physical scan to logical leaf order
6. 01:00:30 The logical adjacency scanner works
7. 01:08:00 Randomized deletion reveals widespread candidates
8. 01:14:00 Backward scans, WAL, and lock-coupling hazards