On Tue, Jun 30, 2009 at 11:45 AM, Richard
Broersma<richard.broersma@gmail.com> wrote:
> On Tue, Jun 30, 2009 at 10:26 AM, Bhella
> Paramjeet-PFCW67<PBhella@motorola.com> wrote:
>
>> We're trying to simulate rolling window concept in postgres with partitions, without impacting application which
doesconcurrent inserts into partitioned tables. We basically need to roll off data from older partition say 7 days
olderjust to avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules
onthe 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.