On Fri, Aug 01, 2003 at 01:46:54PM -0700, Roger Hand wrote: > We are moving an application from Oracle 8i to Postgres and I've run into > a problem attempting to duplicate a feature we currently use. > > In Oracle you can divide a table into partitions. We use this feature to > break up the data by month. Each month we store several tens of millions > of rows in a particular table, and each month we drop the partition that's > a year old. In other words, we always keep the last 12 months of data (12 > partitions). This is clean and fast. Since the partition is by a timestamp > column, it also gives us a certain amount of automatic indexing. > > Postgres doesn't support table partitions (correct me if I'm wrong!) so > the only option appears to be to dump everything into one big table. What > I'm worried about is the purging of the data from 12 months ago ... I'm > worried that this will be a slow and expensive operation. > > Does anyone have any advice for how best to handle this?
I feel your pain! No, PortgreSQL doesn't support this. There were some proposals recently on -hackers but there didn't seem to be a great deal of interest. The best solution I've come up with is by creating base tables for each year by hand and using a view to combine them. You can create RULEs to automatically move new data to various tables. As long as you're not doing UPDATEs you can avoid a lot of the complexity. Similar effects can be acheived using inheritance. Good luck! -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
pgp00000.pgp
Description: PGP signature