From: Craig Ringer <craig@postnewspapers.com.au>
To: sathiya psql <sathiya.psql@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Having MANY MANY empty columns in database
Date: Sat, 22 Mar 2008 17:24:17 +0900
Message-ID: <47E4C231.20302@postnewspapers.com.au> (raw)
In-Reply-To: <f966c2ee0803212110n2d84585eqf5d9bf67cdba0898@mail.gmail.com>
References: <f966c2ee0803212110n2d84585eqf5d9bf67cdba0898@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, sathiya.psql@gmail.com
Subject: Re: Having MANY MANY empty columns in database
In-Reply-To: <47E4C231.20302@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox