On 8/8/07, Simon Riggs <simon@2ndquadrant.com> wrote:
So pruning removes dead hot updated tuples, while defragging will remove
dead cold updated tuples and deletes, as well as rearranging space.
No, pruning removes all dead tuples, irrespective of whether they are
HOT or COLD updated and whether they are heap-only or not. It
handles line pointer redirection and marks
all dead tuples as ~LP_USED. Defragging just repairs the fragmentation
by rearranging LP_USED tuples.
It's easily possible that 100% of the cold updates and deletes are
removed by defragging because of HOT updates. It would be pointless to
trigger a VACUUM when it might find nothing to clear up. Its also
possible that the deletes are at one end of the table and the updates at
the other. So we really need to keep track of the effects of defragging
dead cold updates and deletes, so they can be subtracted from the cold
update + deletes.
The argument for including HOT updates is weaker, but I'm still thinking
that they should eventually cause a VACUUM to take place. A very slowly
updated table with spread out updates might bloat a table to 200 times
its size, even with HOT, in the worst case. Perhaps we should count each
HOT update as 1/5th of a row for autovacuum purposes, or some other
discounting factor?
I can't see how a table might bloat to 200 times its size assuming only
HOT updates and without long running transactions. And even if the table is
really bloating that way, it must be because of COLD updates and they
are discounted in triggering autovac. Am I missing something ?
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB
http://www.enterprisedb.com