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