Re: Read performance on Large Table

If you're just going to do basic time series partitioning, I've written a
tool that manages most of it for you. Also does retention management as
well and can dump out the old partitions automatically.

https://github.com/keithf4/pg_partman

I'm in the process of getting v2.0.0 out that has a lot of new work done,
but will only be compatible with Postgres 9.4 (since it uses background
workers to have scheduling built in). So if you want to wait, I should have
that out soon.

On Thu, May 21, 2015 at 11:41 AM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
wrote:

> On May 21, 2015, at 9:21 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
> >
> > I use an interesting method to setup partitioning. I setup my
> > triggers, then insert the data in chunks from the master table to
> > itself.
> >
> > insert into master_table select * from only master_table limit 10000;
> >
> > and run that over and over. The data is all in the same "table" to the
> > application. But it's slowly moving to the partitions without
> > interruption.
>
> Ah, good idea—I’ll have to remember that.
>
> In my case the table is write-mostly, read-rarely, and update-never, so
> insert into partition select * from master where… with millions of rows at
> a time was not a problem.
>
> The one thing I did differently from the documentation, is write a pgSQL
> procedure to create a partition for a particular date range and the
> function and trigger, where the trigger function checks the date against
> the current “head” partition, and if the date is newer, calls the function
> which creates the partition and replaces the trigger function. I hate
> having to remember those things which only have to be done once a year ;-)
>
> And yes, that particular implementation depends on the fact that the
> criteria on which I’m partitioning increases monotonically. I expect it
> would create a performance nightmare if I tried to insert old data into the
> master table while the application was actively inserting new data. The
> same basic technique can be adapted to other situations, but the trigger
> function is slightly more complicated, and the function to create the
> trigger function would be kind of ugly and hard to follow.
>
> --
> Scott Ribe
> scott_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
>
> --
> 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
>