estimating table size

[David Parker]

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 [phone]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ian Barwick]

On Mon, 26 Jul 2004 18:14:06 -0400, David Parker
<dp*****@tazznetworks.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
ba*****@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Ian Barwick <ba*****@gmail.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruce Momjian]

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 <ba*****@gmail.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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend