Re: Partioning in postgres

On Tue, Jun 30, 2009 at 11:45 AM, Richard
Broersma<richard(dot)broersma(at)gmail(dot)com> wrote:
> On Tue, Jun 30, 2009 at 10:26 AM, Bhella
> Paramjeet-PFCW67<PBhella(at)motorola(dot)com> wrote:
>
>> We're trying to simulate rolling window concept in postgres with partitions, without impacting application which does concurrent inserts into partitioned tables. We basically need to roll off data from older partition say 7 days older just to avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the fly for future day partitions blocks rows from being inserted into current day partition.
>>
>> Anyone implemented similar concept in postgres.
>
> I'm not an expert on this, but I will tell you what I know.  From what
> I understand, partitioned tables and associated triggers are created
> for date ranges ahead of time (before the date when the partition data
> come into existence).  Table creation is handled through CRON jobs and
> scripts.
>
> The following archived thread illustrates what one person did to
> develop automatic creation of table partitions:
> http://archives.postgresql.org/pgsql-general/2009-04/msg01077.php
>
>
> The above mentioned email may give you a workable solution.

I've since changed to using a much more primitive but quite functional
method, I posted it in the last month or so, a complete example of how
I create the triggers and partitions. Just dig around the archives
for the last month or so on my name and partitioning for it.