Re: Read performance on Large Table

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