On 8/8/07, Simon Riggs <[EMAIL PROTECTED]> 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 ?