Re: Postgres table size

Hi
Thanks. Ill post it on the mailing list when I get the results.
Im trying to calculate and see how the tablesize works for a simple
table.

I have a table with 10 cols
5 varchars _ it is declared as varchar(40) but contains data of length 3
5 numeric - declared as numeric(22,0) but contains data of precision 10
There are 10000 rows

select * from pg_relation_size gives 1548288 bytes as the table's size
select relpages from pg_class for that table gives 189 pages

Calculation
varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes
*for 5 varchar
cols = 5*7 = 35 bytes

numeric
(according to manual--- The actual storage requirement is two bytes for
each group of four decimal digits, plus eight bytes overhead )
numeric = ( 10/4)*2 +8 = 13 bytes
*for 5 numeric cols = 13 *5 = 65

*row overhead = 32

So
Bytes per row = 35 + 65 +32 = 132 bytes
for 10000
rows = 1320000

The manual says ---The first 20 bytes of each page consists of a page header (PageHeaderData).
There are 189 pages
*page header cost = 20*189 = 3780 bytes

Therefore Total = 1320000 (row cost) + 3780 (page header cost)=1323780 bytes for this table

Is this calculation right? But the size of the table according to pg_relation_size is 1548288 bytes

What am I missing in my calculation?

Thanks for your help.

Thanks
sharmila

----- Original Message ----
From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: SHARMILA JOTHIRAJAH <sharmi_jo(at)yahoo(dot)com>
Sent: Friday, November 16, 2007 2:12:46 PM
Subject: Re: [GENERAL] Postgres table size

On Fri, 2007-11-16 at 07:36 -0800, SHARMILA JOTHIRAJAH wrote:
> Hi,
>
> You were right. I installed beta2 and the table size now is
> 4682817536. Thanks
>
> How do you estimate the table size generally., ie.,
> what is the storage size of varchar, numeric and timestamp columns
and
> What is the row overhead?
>
> For example, If I have a table (in postgres8.2.3 version) with 10
rows
> and 3 columns (varchar,numeric(22,0),timestamp) , how do I estimate
> their storage size. How does 8.3Beta-2 handle this?
> this will be very helpful for me for allocating the space properly
> Thanks again for your help
> sharmila
>

That's good news. Please post to pgsql-advocacy to show the reduction
in
table size that 8.3 brings for you, and the performance difference that
means for you. If it helps you to choose PostgreSQL instead of some
other database that would be great to mention too (however, some
databases don't like you to publish benchmarks, so be careful not to
violate your license).

The official docs are here:
http://developer.postgresql.org/pgdocs/postgres/storage.html (8.3)
http://www.postgresql.org/docs/8.2/static/storage.html (8.2)

The way I think about it is simple:

In 8.2:
* 32 bytes of row overhead: 28 byte row header + 4 byte line pointer
* 4 bytes of overhead for every variable-width type: to store length

In 8.3:
* 28 bytes of row overhead: 24 byte row header + 4 byte line pointer
* 1-4 bytes of overhead for every variable-width type: only one byte of
overhead if length < 127 bytes, up to 4 bytes if it is longer.

Regards,
Jeff Davis

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/