Skip site navigation (1) Skip section navigation (2)

Re: About inheritance

From: Joe Conway <mail(at)joeconway(dot)com>
To: Rod Taylor <rbt(at)rbt(dot)ca>
Cc: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>,Josh Berkus <josh(at)agliodbs(dot)com>, Diogo Biazus <diogob(at)gmail(dot)com>,Postgresql Advocacy <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: About inheritance
Date: 2004-06-30 04:07:54
Message-ID: 40E23C9A.9030100@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-advocacy
Rod Taylor wrote:
>>I hope not -- I think the underlying infrastructure could become the 
>>basis of table partitioning. I have a project going on right now in 
>>which we're porting ~700GB of data (forecast to become multi-TB over the 
>>next year or so) from partitioned vendor-O tables to inherited Postgres 
>>tables.
> 
> Tell me how that works out. I have a few tables with more than 100M
> records in them but only the last 5M (by time -- so it's well clustered)
> or so are in active use.
> 
> Looked at inheritance, but it seems to do a select against the structure
> anyway. Using partial indexes with a common datastore seems to work much
> better, until VACUUM runs...

Right -- vacuum is an issue. So is loading new data, and purging old. 
Say we want 12 months rolling data -- once a month we create a new 
"partition", and drop the oldest "partition". Using individual tables 
makes this relatively painless (or that's the theory anyway).

Selects do hit all the inherited tables, but a query that uses the index 
on each of the tables, and only has hits in the most recent month, will 
not spend much time on the non-applicable tables relative to the overall 
query.

I'll keep you posted when we get to full load testing (probably several 
weeks out -- we've waiting on hardware).

Joe

In response to

Responses

pgsql-advocacy by date

Next:From: Joe ConwayDate: 2004-06-30 04:11:49
Subject: Re: About inheritance
Previous:From: Josh BerkusDate: 2004-06-30 03:53:28
Subject: Re: About inheritance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group