Re: Read performance on Large Table - Mailing list pgsql-admin

From Scott Ribe
Subject Re: Read performance on Large Table
Date
Msg-id 1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com
Whole thread Raw
In response to Re: Read performance on Large Table  (Scott Marlowe <scott.marlowe@gmail.com>)
Responses Re: Read performance on Large Table
List pgsql-admin
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







pgsql-admin by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Read performance on Large Table
Next
From: Keith
Date:
Subject: Re: Read performance on Large Table