pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving MANY MANY empty columns in database
3+ messages / 2 participants
[nested] [flat]
* Having MANY MANY empty columns in database
@ 2008-03-22 04:10 sathiya psql <sathiya.psql@gmail.com>
2008-03-22 08:24 ` Re: Having MANY MANY empty columns in database Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: sathiya psql @ 2008-03-22 04:10 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I want some clarification in the following,
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....
But many of the users are not doing anything with those columns, they are
all empty always....
Say in that 75 % of tables, 60 % table contains nearly 1000 records
always...
and other 10% of tables contains less than 10000 records
and 5% of table contain records nealy 5 lakh.....
What i need is???
If you drop those columns we will gain any performance or not.....
Definitely i know having that columns is not useful, but i want some
clarification that having empty columns will make performance degradation or
not....
Am using Debian, and having 1 GB RAM...
I want this informations in both postgres 7.4 and 8.1 ( don't ask me to use
8.3 please, i want info in 7.4 and 8.1 )...
Regards
SathiyaMoorthy
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having MANY MANY empty columns in database
2008-03-22 04:10 Having MANY MANY empty columns in database sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-22 08:24 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-22 09:08 ` Re: Having MANY MANY empty columns in database sathiya psql <sathiya.psql@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Craig Ringer @ 2008-03-22 08:24 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having MANY MANY empty columns in database
2008-03-22 04:10 Having MANY MANY empty columns in database sathiya psql <sathiya.psql@gmail.com>
2008-03-22 08:24 ` Re: Having MANY MANY empty columns in database Craig Ringer <craig@postnewspapers.com.au>
@ 2008-03-22 09:08 ` sathiya psql <sathiya.psql@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: sathiya psql @ 2008-03-22 09:08 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
>
> OK, so your tables all have the same fields (columns), as if you used
> CREATE TABLE new_table ( LIKE some_template_table ) ?
It will contain some other unique columns for each table.
> meaning that they contain NULL values in that field for every record?
what is the value it may contain i don't know ?? we are not filling any
value !!
>
> > If you drop those columns we will gain any performance or not.....
>
I need to test... HOW to test the overall performance of database..
> 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.
>
if it is sure that it will not make disk I/O then it is ok
>
> 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.
>
these are not useless columns... it should be used to update the owner of
the record, updated time, created and other stuffs, but nobody is using now.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-03-22 09:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-22 04:10 Having MANY MANY empty columns in database sathiya psql <sathiya.psql@gmail.com>
2008-03-22 08:24 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-22 09:08 ` sathiya psql <sathiya.psql@gmail.com>
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