Re: About inheritance

Martha Stewart called it a Good Thing when mail@joeconway.com (Joe Conway) wrote:
> 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.

We ran into the problem that "self-joins are evil."

A "rotor" table that is comprised of 10 tables turns a self-join into
a 100-way join, which is very much NOT painless.  :-(
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/advocacy.html
Rules of the  Evil Overlord #128. "I will not  employ robots as agents
of  destruction  if  there  is  any  possible way  that  they  can  be
re-programmed  or if their  battery packs  are externally  mounted and
easily removable." <http://www.eviloverlord.com/>