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

Re: About inheritance

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-advocacy(at)postgresql(dot)org
Subject: Re: About inheritance
Date: 2004-06-30 12:29:04
Message-ID: m3r7rx5iwf.fsf@wolfe.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-advocacy
Martha Stewart called it a Good Thing when mail(at)joeconway(dot)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/>

In response to

Responses

pgsql-advocacy by date

Next:From: Anastasios HatzisDate: 2004-06-30 13:13:49
Subject: PgSQL driver for .Net?
Previous:From: Thomas HallgrenDate: 2004-06-30 07:53:53
Subject: Re: About inheritance

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