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 <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> 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/
