List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:August 6 2010 7:47am
Subject:Re: Possible tricks to ALTER on huge tables?
View as plain text  
Daevid Vincent wrote:
> We currently have some tables that are approaching 1 BILLION rows (real
> Billion, with nine zeros, not that silly six zero version). Trying to do an
> "ALTER" on them to add a column can sometimes take hours.

A few years ago I have tested possible table structures for an 
application which had to store at least a million profiles of persons. 
Because we expected that properties would be added (and/or removed) from 
the database quite often I also tested a structure where the properties 
of a single profile were stored in tables based on the data type.

So we had tables with integers, strings, dates, etc. and used a record 
for each property; columns were like: id, property name, value, and a 
few other relevant things to handle and display the data.

Most select queries were about as fast as they would be with a single 
table. Database size was approximately the same because not all profiles 
used all properties, so we only needed to store the properties a certain 
  profile would use.
The only limitation at that time was 31 joins, but I don't think we've 
ever hit that limit.

Adding properties was easy, just adding them to the configuration of the 
application was enough.

It really depends on the situation of your application which table 
structure is the most suitable. Test the performance of all kinds of 
operations you need to do with realistic data and various amounts of 
data to see how it scales.

-- 
Jigal van Hemert
Thread
Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Buford Tannen6 Aug
  • Re: Possible tricks to ALTER on huge tables?Rob Wultsch6 Aug
    • RE: Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Jigal van Hemert6 Aug
    • Re: Possible tricks to ALTER on huge tables?Andy Wallace6 Aug