On Mon, May 24, 2004 at 11:15:07AM -0700, Joe Conway wrote:
> Greg Stark wrote:
> >Well this was actually under Oracle, but I can extrapolate to Postgres
> >given
> >my experience.
> >
> >The idea tool for the job is a feature that Postgres has discussed but
> >hasn't
> >implemented yet, "partitioned tables". Under Oracle with partitioned
> >tables we
> >were able to drop entire partitions virtually instantaneously. It also made
> >copying the data out to near-line backups much more efficient than index
> >scanning as well.
>
> I think you can get a similar effect by using inherited tables. Create
> one "master" table, and then inherit individual "partition" tables from
> that. Then you can easily create or drop a "partition", while still
> being able to query the "master" and see all the rows.
I've done this, in production, and it works fairly well. It's not as
clean as true partitioned tables (as a lot of things don't inherit)
but you can localise the nastiness in a pretty small bit of
application code.
Any query ends up looking like a long union of selects, which'll slow
things down somewhat, but I found that most of my queries had date range
selection on them so I could take advantage of that in the application
code to only query some subset of the inherited tables for most of the
application generated queries, while I could still do ad-hoc work from
the psql commandline using the parent table.
Cheers,
Steve