Re: Could ANALYZE estimate bloat?

* Josh Berkus (josh@agliodbs.com) wrote:
>  Also, live vs. dead tuples doesn't tell me how much free *space* is
> available on pages.

I'm not really sure that you'd get much better from ANALYZE than you get
from tracking the inserted/updated/deleted tuples.  Collecting that
information when VACUUM'ing the table would certainly provide much more
accurate results, which could possibly be stored in a page-level bitmap
of "completely empty pages" at the beginning of each 1G segment.
Alternatively, the bitmap could be updated during processing instead of
waiting for a VACUUM.

Greg and I hypothesized that such a bitmap might be used to truncate
individual 1G segments in the middle of a relation rather than only at
the end, perhaps all the way down to a point where only a header plus
the page-level bitmap in the 1G segment are left.  This was discussed in
context of a VACUUM which used the try-to-elevate-the-lock approach
already used to truncate the last 1G segment of the relations, though
I've also wondered if it could take page-level locks starting at the end
of the 1G segment and walking backwards until it's unable to acquire a
lock or a non-empty page is found.

Of course, we're aware of the issues around the storage management
system and interfaces which might make this entirely unrealistic but
it's getting to a point where, I think (not sure about Greg), we need to
deal with that in some way to improve on issues like this.
Thanks,
    Stephen