Re: postgresql storage and performance questions

Thanks Filip.
I have 2 tables with 2 cols each( 1 numeric(8,0) and 1 varchar(3) ).
In table1 both the cols are filled and in table2 the varchar colm is null

So when I checked the tablesize for these two tables (using pg_relation_size)
table1 - 57344 bytes (no null columns)
table2 - 49152 bytes (varchar colm is null)

There is not much difference between the two sizes.So even if a column
is null postgresql still has lots of overhead.
Does postgres occupy space even when the column is NULL?

This is not a spam.... I posted it twice becoz my question didnot
show up the first time in the mailing list even after 30 minutes. So i
tried again and then both showed up...kind of strange though!

Thanks again
Josh

On Nov 19, 2007 1:37 PM, Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> wrote:
> 2007/11/19, Josh Harrison <joshques(at)gmail(dot)com>:
> > Hi,
> > I have a few questions about the storage and performance
> >
> > 1. How do you estimate the table size in postgresql?
> > For example if I have a table 'Dummy' with 1 varchar (40) & 1
> > numeric(22,0) fields and 1000 rows, what is the tablesize estimate for
> > this (including the row overhead etc)? How many pages will this
> > occupy?
> >
> > 2. Also if the table contains null columns, does postgres allocates
> > the same space for these nulls columns? How does it handle 'nulls' in
> > terms of storage?
>
> Try these:
> http://www.postgresql.org/docs/current/static/datatype-numeric.html
> http://www.postgresql.org/docs/current/static/datatype-character.html
> http://www.postgresql.org/docs/current/static/storage-page-layout.html
>
> ad.1 ) There is a fixed-size header (occupying 27 bytes on most
> machines) for each tuple
>
> so you will have about 27 + 1 + varchar data + numeric data per row,
> plus some overhaed for block headers
>
> ad.2 ) there is a null bitmap for each tuple which has nullable fields
> - so every 8 NULLable columns occupy one byte bitmap.
>
>
> PS. why do you post same thing many times? this is kinda.. spam?
>
> --
> Filip Rembiałkowski
>