Re: estimating table size

Given a table, foo, created in a database but not populated, is there a
procedure that will return an estimate of the size of a given tuple in
that table? It looks like pgstattuple reports on actual table pages; I'm
looking for something that reads the lengths of each row, and knows what
the storage overhead would be (extra 4 bytes for varchar, for instance).

Alternatively, is there documentation somewhere that can give me a good
rule of thumb for this? I can add up the lengths of the columns and make
my own judgements about averages for varchar columns. The "hidden"
storage overhead is what I'm wondering about, I guess.

Thanks.

- DAP
======================================================
David Parker Tazz Networks (401) 709-5130

On Mon, 26 Jul 2004 18:14:06 -0400, David Parker
<dparker(at)tazznetworks(dot)com> wrote:
> Given a table, foo, created in a database but not populated, is there a
> procedure that will return an estimate of the size of a given tuple in
> that table? It looks like pgstattuple reports on actual table pages; I'm
> looking for something that reads the lengths of each row, and knows what
> the storage overhead would be (extra 4 bytes for varchar, for instance).
>
> Alternatively, is there documentation somewhere that can give me a good
> rule of thumb for this? I can add up the lengths of the columns and make
> my own judgements about averages for varchar columns. The "hidden"
> storage overhead is what I'm wondering about, I guess.

There is a little info in the FAQ:
http://www.postgresql.org/docs/faqs/FAQ.html

particularly sections 4.6 and 4.14

Ian Barwick
barwick(at)gmail(dot)com

Ian Barwick <barwick(at)gmail(dot)com> writes:
> There is a little info in the FAQ:
> http://www.postgresql.org/docs/faqs/FAQ.html
> particularly sections 4.6 and 4.14

I think the calculation in section 4.6 is out of date --- it's been
awhile since row headers were 36 bytes. The more correct number is
between 24 and 32 bytes depending on whether you created the table
WITH OIDS and whether your machine requires 4- or 8-byte alignment.

regards, tom lane

I just updated the FAQ to suggest 32 as the header size (I am assuming
OID's and 4-byte alignment). I am also assuming 7.5 which will loose
the cmin/cmax compression.

---------------------------------------------------------------------------

Tom Lane wrote:
> Ian Barwick <barwick(at)gmail(dot)com> writes:
> > There is a little info in the FAQ:
> > http://www.postgresql.org/docs/faqs/FAQ.html
> > particularly sections 4.6 and 4.14
>
> I think the calculation in section 4.6 is out of date --- it's been
> awhile since row headers were 36 bytes. The more correct number is
> between 24 and 32 bytes depending on whether you created the table
> WITH OIDS and whether your machine requires 4- or 8-byte alignment.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073