Re: About inheritance - Mailing list pgsql-advocacy

This is the case where I've found inheritance to be useful too.
Years worth of data, divided into child tables.  It is imporant
to be able to query effectively *both* on the whole data set
and on the currently active table.  The distribution of indexes
spend up the queries considerably in the most common case
of the current table and pretty darn good in a date qualified
over all query.

I believe the distributed indexes are useful and are probably
a justification for the implementation.  I could probably
dig up some history on it if we really want to know.

I strongly suggest a more cautious approach than "ripping
things out."  Ripping out time travel got us row space
but lost point in time recovery.  And is pushing us to
implement more traditionally logging.  It was an OK trade
in the long run, but it took us a long time to get around to pitr.

--elein

On Tue, Jun 29, 2004 at 09:07:54PM -0700, 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.
>
> I'll keep you posted when we get to full load testing (probably several
> weeks out -- we've waiting on hardware).
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org