Re: Postgres table size - Mailing list pgsql-general
| From | SHARMILA JOTHIRAJAH |
|---|---|
| Subject | Re: Postgres table size |
| Date | |
| Msg-id | 372553.22052.qm@web31104.mail.mud.yahoo.com Whole thread Raw |
| In response to | Postgres table size (SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>) |
| Responses |
Re: Postgres table size
(Erik Jones <erik@myemma.com>)
|
| List | pgsql-general |
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
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@j-davis.com>
To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.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
From: Jeff Davis <pgsql@j-davis.com>
To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.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 sports nut! Let your teams follow you with Yahoo Mobile. Try it now.
pgsql-general by date: