Re: Measuring relation free space

On Mon, Nov 14, 2011 at 2:02 PM, Jaime Casanova <jaime(at)2ndquadrant(dot)com> wrote:
> On Wed, Nov 9, 2011 at 7:58 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>>
>> Excerpts from Jaime Casanova's message of mar nov 08 18:12:25 -0300 2011:
>>> On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>> >
>>> > Looks pretty useful.
>>>
>>> thanks for the review, attached is a new version of it
>>
>> Note that AFAIK you shouldn't update the 1.0 extension script ... you
>> have to create a 1.1 version (or whatever), update the default version
>> in the control file, and create an 1.0--1.1 script to upgrade from the
>> original version to 1.1.
>>
>
> good point... fixed that...
> a question i have is: are we supposed to let the old script (1.0) around?

Since the syntax to install a non-default version is supported, I
would argue the old script should be kept.
CREATE extension pageinspect with version "1.0"

This patch applies and builds cleanly. It works either for "CREATE
EXTENSION" from scratch, or for updating from the prior version with
"ALTER EXTENSION..UPDATE".

It seems to be using the buffer ring strategy as advertised.

It reports space that is free exclusively for updates as being free.
In other words, it considers space free even if it is reserved against
inserts in deference to fillfactor. This is in contrast to
pg_freespace, which only reports space available for inserts as being
available. I think this is reasonable behavior, but it is subtle and
should perhaps be documented. (Is it common to use fill factors other
than the default in the first place? Do we assume that people using
fillfactor are sophisticated enough not to shot themselves in the
foot?)

As noted by Greg, the documentation calls it "total amount of free
free [sic] space" when that is not what is reported. However, it also
is not reporting a percentage, but rather a decimal fraction. The
reported value should be multiplied by 100, especially if the docs are
going to be changed to call it a percentage.

Unless I am missing something, all indexes are handled via a procedure
designed for BTree indices, "GetBTRelationFreeSpace". I don't know
that the ultimate behavior of this is wrong, but it seems unusual. If
I get some more time, I will try to explore what is actually going on
when called on other types of indexes.

I have no insight into how to handle toast tables, or non-superusers.
I had thought that toast tables had names of their own which could be
used, but I could not figure out how to do that.

Even if there are other ways to get approximately the same
information, this functionality seems to be a natural thing to have in
the pageinspect extension.

Cheers,

Jeff