We know that HOT can cause line pointer bloat because of redirect dead
line pointers. In the worst case there could be MaxHeapTuplesPerPage
redirect-dead line pointers in a page. VACUUM can reclaim these line
pointers and mark them ~LP_USED (what is now called LP_UNUSED).
But we don't reclaim the space used by unused line pointers during
repairing page fragmentation, and hence we would never be able to
remove the line pointer bloat completely. Fundamentally we should
be able to reclaim the unused line pointers at the end of the lp array
(i.e. unused line pointers immediate to pd_lower)
I had earlier tried to repair the bloat by reclaiming the space used
by LP_UNUSED line pointers at the end of the array. But it doesn't work
well with VACUUM FULL which tracks unused line pointers for moving
tuples. Its not that we can not fix that issue, but I am reluctant to spend
time on that right now because many of us feel that VACUUM FULL is
near its EOL.
How about passing a boolean to PageRepairFragmentation to
command it to reclaim unused line pointers ? We pass "true" at all
places except in the VACUUM FULL code path. IOW we reclaim unused
line pointers in defragmentation and LAZY VACUUM. We would need
to WAL log this information in xl_heap_clean so that we redo the same
during recovery. I have a patch ready since I had already implemented
this few weeks back.