On 9/8/07, Bruce Momjian <bruce@momjian.us> wrote:
Would someone tell us exactly when pruning and defragmenting happens in
the current version of the patch? If we don't nail this issue down soon
PostgreSQL 8.3 is going to sail without this patch.
I guess you already have the answers by now, but let me repeat here:
Until patch version 14, defragmentation and pruning were separate
operations, though we used to invoke both at the same time. The only
difference was that pruning can work with a simple exclusive lock
whereas defragmentation needs vacuum-strength lock. Tom argued
that its not worth the complexity, so I changed that and clubbed
both the operations together. What it means: pruning also now needs
vacuum-strength lock and is always followed by defragmentation.
So as the patch stands (version 15), we call heap_page_prune_defrag()
inside heap_release_fetch() and heapgetpage(), apart from the vacuum
loop. It checks for PageIsPrunable() before proceeding. PageIsPrunable
is set when a tuple is updated/deleted. So for read-mostly workloads
heap_page_prune_defrag will mostly be no-op.
If PageIsPrunable is set, cleanup lock is available (exclusive lock is tried
conditionally, so we don't wait if there is contention) and we are running
low on free space (right now if free space is less than 1.2 times the average
tuple size or less than BLCKSZ/8), the entire page is pruned and fragmentation
is repaired.
We also prune-defrag is vacuum lazy and vacuum full. But I assume we
are not worried about these maintenance activities.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB
http://www.enterprisedb.com