pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Read performance on Large Table
Date: Thu, 21 May 2015 09:41:28 -0600
Message-ID: <1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com> (raw)
In-Reply-To: <CAOR=d=08=D-nui40rhsk7dM36L8L99ocvHXJ75Agyvd-_hazpQ@mail.gmail.com>
References: <CA+ebzukaZJVOjw2Vqg0cuCUqbaEEHHuCa-pNbsX6CWAZyga3Fw@mail.gmail.com>
	<CAOR=d=3Ft9x+r0N30a_ASgLtSDFNA_m-H7_-sdFuOqD3FRyjPg@mail.gmail.com>
	<580E17C5-363E-4D24-B381-80A1F4C83C81@elevated-dev.com>
	<CAOR=d=08=D-nui40rhsk7dM36L8L99ocvHXJ75Agyvd-_hazpQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



view thread (12+ messages)  latest in thread

Message-ID: <1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com>
Permalink:  ../1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com/
Also on:    postgresql.org/message-id/1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: scott_ribe@elevated-dev.com, scott.marlowe@gmail.com
  Subject: Re: Read performance on Large Table
  In-Reply-To: <1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox