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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
