Re: Monthly table partitioning for fast purges?

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 <kleptog(at)svana(dot)org> 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