Re: Daily Rotated Insertion

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
>