Message-ID: <47E4C231.20302@postnewspapers.com.au>
Date: Sat, 22 Mar 2008 17:24:17 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: sathiya psql <sathiya.psql@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Having MANY MANY empty columns in database
References: <f966c2ee0803212110n2d84585eqf5d9bf67cdba0898@mail.gmail.com>
In-Reply-To: <f966c2ee0803212110n2d84585eqf5d9bf67cdba0898@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> In a database which we are having we have nearly 100 tables, and in 75% of
> the tables we have 6 columns ( INT ) as standard columns. What is standard
> columns, if you create a table in this database you should have some default
> 6 columns in there they should maintain
>     1. who is the owner of that read
>     2. when it is added
>     3. who is updating the record
>     4. when it is updated .... and other columns....

OK, so your tables all have the same fields (columns), as if you used 
CREATE TABLE new_table ( LIKE some_template_table ) ?

> But many of the users are not doing anything with those columns, they are
> all empty always....

meaning that they contain NULL values in that field for every record?

> If you drop those columns we will gain any performance or not.....

The best way to find that out is to test it. I'd be surprised if it 
didn't make *some* performance difference, but the question is whether 
it will be enough to be worth caring about.

However, I recall hearing that PostgreSQL keeps a null bitmap and 
doesn't use any storage for null fields. If that is correct then you 
probably won't be paying much of a price in disk I/O, but there might 
still be other costs.

I can't help wondering why you have all those useless columns in the 
first place, and why you have so many identically structured tables.

--
Craig Ringer