From: | "Roger Hand" <rhand(at)ragingnet(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Monthly table partitioning for fast purges? |
Date: | 2003-08-01 20:46:54 |
Message-ID: | 1329118CFAAABD42BDF304DA2BABA58F2424DC@berkeley.ragingnet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thanks in advance,
-Roger
From | Date | Subject | |
---|---|---|---|
Next Message | elein | 2003-08-01 20:59:13 | Re: CREATE TABLE with a column of type {table name} |
Previous Message | Claudio Lapidus | 2003-08-01 20:17:01 | changing column type |