Skip site navigation (1) Skip section navigation (2)

Re: Daily Rotated Insertion

From: Haifeng Liu <liuhaifeng(at)live(dot)com>
To: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Daily Rotated Insertion
Date: 2012-03-27 06:02:11
Message-ID: BLU0-SMTP4434590A1672562321D21ACB94A0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-admin
On Mar 27, 2012, at 12:42 PM, Sergey Konoplev wrote:

> Hi,
> 
> On Mon, Mar 26, 2012 at 9:39 AM, Haifeng Liu <liuhaifeng(at)live(dot)com> wrote:
>> Well, I can create a trigger to redirect insertion to 'yesterday', 'today' and 'tomorrow', but I am still expecting a more simple and more fast solution.
> 
> You can use partitioning by date
> http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html.

Yes, that's what I am doing. There are many ways to implement partitioned table, I just want to find an elegant one. Bulk create a certain number of partitions is not good in my opinion. What I am trying now is let the trigger which switches insertions to catch exceptions and create new partitions on demand, meanwhile. update the trigger itself to use the new switch rule.

This idea can use static statement to switch insertions, dynamic statements are only used to create partition and update the trigger, this should be good for performance. And compare to the static partition example given in the document, this solution can auto-update itself, there is no need for cron jobs to maintenance partition.

I am not sure if this idea work, still trying now.

> 
>> 
>> Any idea is appreciated. Thanks in advance.
>> 
>> 
>> Best regards.
>> liuhaifeng
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
> 
> 
> 
> -- 
> Sergey Konoplev
> 
> Blog: http://gray-hemp.blogspot.com
> LinkedIn: http://ru.linkedin.com/in/grayhemp
> JID/GTalk: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp
> 
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
> 


In response to

Responses

pgsql-admin by date

Next:From: Sergey KonoplevDate: 2012-03-27 07:33:10
Subject: Re: Daily Rotated Insertion
Previous:From: Sergey KonoplevDate: 2012-03-27 04:42:45
Subject: Re: Daily Rotated Insertion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group