Re: Partioning in postgres

Thanks for the response Richard.

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.

Thanks
Paramjeet kaur

-----Original Message-----
From: Richard Broersma [mailto:richard(dot)broersma(at)gmail(dot)com]
Sent: Monday, June 29, 2009 12:36 PM
To: Bhella Paramjeet-PFCW67
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Partioning in postgres

On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella(at)motorola(dot)com> wrote:

> I tested a scenario where in one terminal window I insert rows in the
> eventlog table in the BEGIN END loop and do not commit the
> transaction. In another terminal window I create a new partition but
> when I create a rule for the new partition it hangs until I commit the
> transaction(insert) in the first terminal window.  Just want to know
> if this is a limitation in postgres partitioning ?

Actually I would say that transactional ddl is a feature of PostgreSQL rather than a limitation. But is sounds like this feature isn't playing well with your current design.

> Is there a work around
> for this problem? In our production environment we want to  be able to
> create new partitions and rule for new partition while data is being
> inserted into the eventlog table.

You probably need break up your insert transactions into smaller segments that coincide DDL changes.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug