Обсуждение: Partioning in postgres
Hi, We are planning on implementing partioning in our postgres production database.=20 In my test system I created a table called eventlog which has 7 partitions. I have created rules for data to be inserted in respective partitions. 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 ? 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. Any workaround, recommedation or help will be highly appreciated. Thanks Paramjeet Bhella
On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella@motorola.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
Thanks for the response Richard. We're trying to simulate rolling window concept in postgres with partitions, without impacting application which does concurrentinserts into partitioned tables. We basically need to roll off data from older partition say 7 days older justto avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the flyfor 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.broersma@gmail.com] Sent: Monday, June 29, 2009 12:36 PM To: Bhella Paramjeet-PFCW67 Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Partioning in postgres On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella@motorola.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 thisfeature 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
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 does concurrentinserts into partitioned tables. We basically need to roll off data from older partition say 7 days older justto avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the flyfor 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. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
We used a rule that inserted into a partition mod the date.
Then we truncated the partitions before we reused them. This
allowed us to avoid changing the actual trigger. Here is the
trigger we are using for a two month rotation:
DECLARE
table_name text;
table_id_num int;
BEGIN
table_name := TG_ARGV[0];
SELECT INTO table_id_num mod(CAST(EXTRACT(epoch FROM NEW.date) AS INT),63*86400)/86400;
IF table_name = 'table' THEN
IF table_id_num <= 21 THEN
IF table_id_num <= 7 THEN
IF table_id_num = 0 THEN
INSERT INTO table_0 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 1 THEN
INSERT INTO table_1 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 2 THEN
INSERT INTO table_2 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 3 THEN
INSERT INTO table_3 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 4 THEN
INSERT INTO table_4 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 5 THEN
INSERT INTO table_5 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 6 THEN
INSERT INTO table_6 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 7 THEN
INSERT INTO table_7 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 14 THEN
IF table_id_num = 8 THEN
INSERT INTO table_8 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 9 THEN
INSERT INTO table_9 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 10 THEN
INSERT INTO table_10 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 11 THEN
INSERT INTO table_11 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 12 THEN
INSERT INTO table_12 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 13 THEN
INSERT INTO table_13 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 14 THEN
INSERT INTO table_14 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 21 THEN
IF table_id_num = 15 THEN
INSERT INTO table_15 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 16 THEN
INSERT INTO table_16 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 17 THEN
INSERT INTO table_17 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 18 THEN
INSERT INTO table_18 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 19 THEN
INSERT INTO table_19 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 20 THEN
INSERT INTO table_20 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 21 THEN
INSERT INTO table_21 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
IF table_id_num <= 42 THEN
IF table_id_num <= 28 THEN
IF table_id_num = 22 THEN
INSERT INTO table_22 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 23 THEN
INSERT INTO table_23 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 24 THEN
INSERT INTO table_24 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 25 THEN
INSERT INTO table_25 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 26 THEN
INSERT INTO table_26 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 27 THEN
INSERT INTO table_27 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 28 THEN
INSERT INTO table_28 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 35 THEN
IF table_id_num = 29 THEN
INSERT INTO table_29 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 30 THEN
INSERT INTO table_30 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 31 THEN
INSERT INTO table_31 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 32 THEN
INSERT INTO table_32 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 33 THEN
INSERT INTO table_33 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 34 THEN
INSERT INTO table_34 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 35 THEN
INSERT INTO table_35 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 42 THEN
IF table_id_num = 36 THEN
INSERT INTO table_36 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 37 THEN
INSERT INTO table_37 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 38 THEN
INSERT INTO table_38 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 39 THEN
INSERT INTO table_39 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 40 THEN
INSERT INTO table_40 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 41 THEN
INSERT INTO table_41 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 42 THEN
INSERT INTO table_42 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
IF table_id_num <= 63 THEN
IF table_id_num <= 49 THEN
IF table_id_num = 43 THEN
INSERT INTO table_43 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 44 THEN
INSERT INTO table_44 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 45 THEN
INSERT INTO table_45 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 46 THEN
INSERT INTO table_46 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 47 THEN
INSERT INTO table_47 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 48 THEN
INSERT INTO table_48 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 49 THEN
INSERT INTO table_49 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 56 THEN
IF table_id_num = 50 THEN
INSERT INTO table_50 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 51 THEN
INSERT INTO table_51 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 52 THEN
INSERT INTO table_52 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 53 THEN
INSERT INTO table_53 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 54 THEN
INSERT INTO table_54 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 55 THEN
INSERT INTO table_55 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 56 THEN
INSERT INTO table_56 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 63 THEN
IF table_id_num = 57 THEN
INSERT INTO table_57 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 58 THEN
INSERT INTO table_58 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 59 THEN
INSERT INTO table_59 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 60 THEN
INSERT INTO table_60 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 61 THEN
INSERT INTO table_61 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 62 THEN
INSERT INTO table_62 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 63 THEN
INSERT INTO table_63 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
END IF;
RAISE EXCEPTION 'No matching partition for table_name = (%) table_id_num(%)', table_name, table_id_num;
RETURN NULL;
END;
)
Maybe something like this would work for you.
Regards,
Ken
On Tue, Jun 30, 2009 at 01:26:23PM -0400, Bhella Paramjeet-PFCW67 wrote:
> Thanks for the response Richard.
>
> We're trying to simulate rolling window concept in postgres with partitions, without impacting application which does
concurrentinserts into partitioned tables. We basically need to roll off data from older partition say 7 days older
justto avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the
flyfor 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.broersma@gmail.com]
> Sent: Monday, June 29, 2009 12:36 PM
> To: Bhella Paramjeet-PFCW67
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Partioning in postgres
>
> On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella@motorola.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
thisfeature 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
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
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.