Thread

1. Monthly table partitioning for fast purges?

   Roger Hand <rhand@ragingnet.com> — 2003-08-01T20:46:54Z

   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
   
   

2. Re: Monthly table partitioning for fast purges?

   Martijn van Oosterhout <kleptog@svana.org> — 2003-08-02T03:25:58Z

   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@svana.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
   

3. Re: Monthly table partitioning for fast purges?

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-08-03T11:31:28Z

   On 1 Aug 2003 at 13:46, Roger Hand wrote:  
   > 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. 
   
   Well, you need to emulate this feature on postgresql, as pointed out already. 
   It is possible in postgresql but is not provided out of the box.
   
   Here is what you can do.
   
   * Create a empty base table.
   * Create a trigger the directs the select on timestamps accordingly
   * Write a schedule to create a new table at the start of every month and update 
   the trigger accordingly
   * Create and maintain rules to insert into base table depending upon the 
   timestamp.
   
   This is just a rough idea.
   
   There might be fair amount of work to get this working but surely it is not 
   imposible.
   
   HTH
   
   Bye
    Shridhar
   
   --
   Justice, n.:	A decision in your favor.
   
   
   

4. Re: Monthly table partitioning for fast purges?

   Ron Johnson <ron.l.johnson@cox.net> — 2003-08-03T22:49:54Z

   On Sun, 2003-08-03 at 06:31, Shridhar Daithankar wrote:
   > On 1 Aug 2003 at 13:46, Roger Hand wrote:  
   [snip]
   > Here is what you can do.
   > 
   > * Create a empty base table.
   > * Create a trigger the directs the select on timestamps accordingly
   > * Write a schedule to create a new table at the start of every month and update 
   > the trigger accordingly
   > * Create and maintain rules to insert into base table depending upon the 
   > timestamp.
   > 
   > This is just a rough idea.
   > 
   > There might be fair amount of work to get this working but surely it is not 
   > imposible.
   
   And you get a big performance hit when all those records are moved.
   
   Partitioning "should" be put on the TODO list soon after tablespaces
   (or DBA-defined directories) is implemented.
   
   -- 
   +-----------------------------------------------------------------+
   | Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
   | Jefferson, LA  USA                                              |
   |                                                                 |
   | "I'm not a vegetarian because I love animals, I'm a vegetarian  |
   |  because I hate vegetables!"                                    |
   |    unknown                                                      |
   +-----------------------------------------------------------------+
   
   
   
   

5. Re: Monthly table partitioning for fast purges?

   scott.marlowe <scott.marlowe@ihs.com> — 2003-08-04T15:09:45Z

   On Fri, 1 Aug 2003, 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. 
   
   There are two approaches I can think of, and you should test each one to 
   see how it holds up to your usage.
   
   Approach the first:  Using scripts, create a table for each month.  Create 
   a view that combines all of these months.  When a month goes out of date, 
   simply remove it from the view.  Deleting the month can be done at your 
   leisure, as it only saves disk space at this point, but since it isn't in 
   the view, it doesn't slow you down to keep them.  
   
   Approach the second:  Use partial indexes to make it look like the table 
   is partitioned.  I.e. every month create a new partial index like:
   
   create index on bigoldtable (datefield) where datefield >=1stofmonth and 
   datefield<=lastofmonth.
   
   Then include the "where date >=firstofmonth AND date <= lastofmonth
   
   This should then hit the partial index, which will be small compared to 
   the master table with all the rows, or the main index, which will index 
   all fields.
   
   
   

6. Re: Monthly table partitioning for fast purges?

   Jean-Luc Lachance <jllachan@nsd.ca> — 2003-08-05T15:28:27Z

   I second that.
   I have discussed adding partitioning tables almost a year ago...
   No need to partition a functional index or anything like that.
   Just partition on a specific field.
   
   
   Ron Johnson wrote:
   >[...]
   > Partitioning "should" be put on the TODO list soon after tablespaces
   > (or DBA-defined directories) is implemented.
   > 
   > --
   > +-----------------------------------------------------------------+
   > | Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
   > | Jefferson, LA  USA                                              |
   > |                                                                 |
   > | "I'm not a vegetarian because I love animals, I'm a vegetarian  |
   > |  because I hate vegetables!"                                    |
   > |    unknown                                                      |
   > +-----------------------------------------------------------------+
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   
   

7. Re: Monthly table partitioning for fast purges?

   Ron Johnson <ron.l.johnson@cox.net> — 2003-08-05T16:30:03Z

   On Tue, 2003-08-05 at 10:28, Jean-Luc Lachance wrote:
   > I second that.
   > I have discussed adding partitioning tables almost a year ago...
   > No need to partition a functional index or anything like that.
   > Just partition on a specific field.
   
   Well.....
   
   If you're going to partition a *huge* table by, say, a date field,
   and there's also an index on that date field, partitioning the
   index on that same field may have some advantages.
   
   > Ron Johnson wrote:
   > >[...]
   > > Partitioning "should" be put on the TODO list soon after tablespaces
   > > (or DBA-defined directories) is implemented.
   
   -- 
   +-----------------------------------------------------------------+
   | Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
   | Jefferson, LA  USA                                              |
   |                                                                 |
   | "I'm not a vegetarian because I love animals, I'm a vegetarian  |
   |  because I hate vegetables!"                                    |
   |    unknown                                                      |
   +-----------------------------------------------------------------+