Re: Vacuum stats interpreted?

At some point in time, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:

>Jeff Boes <jboes(at)qtm(dot)net> writes:
>> The "Keep" and "UnUsed" numbers seem high to me, compared to other tables. Can
>> anyone interpret these and tell me anything about what we could do with this
>> table to make it "look" better?
>
>"Keep" is the number of rows that are committed dead but had to be kept
>anyway, because there are open transactions old enough to still
>potentially see them. The only way to reduce that is to not have old
>transactions hanging 'round while you vacuum.
>
>UnUsed is the number of empty line-pointer slots. At 4 bytes apiece,
>this would have to vastly exceed the number of live tuples before you
>should worry much.

For which values of "vastly"? I have a small table (1-2k rows) which has a ratio
of UnUsed:Tuples of 50-500.

The table in question has a ratio of about 10 or 11:1.

For some tables (not this one), we find that it significantly improves
performance (of non-indexed queries) to pg_dump and reload the table
periodically. I've been asked to try to quantify (from these vacuum numbers)
when we can predict that a dump-and-reload would be valuable.