Re: Read performance on Large Table

On May 21, 2015, at 9:21 AM, Scott Marlowe <scott.marlowe@gmail.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
particulardate 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
whilethe application was actively inserting new data. The same basic technique can be adapted to other situations, but
thetrigger function is slightly more complicated, and the function to create the trigger function would be kind of ugly
andhard to follow. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice