Thread: 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@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 barwick@gmail.com
Ian Barwick <barwick@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
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@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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073