Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2098\))
Subject: Re: Read performance on Large Table
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAOR=d=08=D-nui40rhsk7dM36L8L99ocvHXJ75Agyvd-_hazpQ@mail.gmail.com>
Date: Thu, 21 May 2015 09:41:28 -0600
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <1295589F-361A-4C3E-BA25-3A4F34C3CAB2@elevated-dev.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>
To: Scott Marlowe <scott.marlowe@gmail.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On May 21, 2015, at 9:21 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>=20
> I use an interesting method to setup partitioning. I setup my
> triggers, then insert the data in chunks from the master table to
> itself.
>=20
> insert into master_table select * from only master_table limit 10000;
>=20
> 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=E2=80=94I=E2=80=99ll have to remember that.

In my case the table is write-mostly, read-rarely, and update-never, so ins=
ert into partition select * from master where=E2=80=A6 with millions of row=
s at a time was not a problem.

The one thing I did differently from the documentation, is write a pgSQL pr=
ocedure to create a partition for a particular date range and the function =
and trigger, where the trigger function checks the date against the current=
 =E2=80=9Chead=E2=80=9D partition, and if the date is newer, calls the func=
tion 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 criter=
ia on which I=E2=80=99m partitioning increases monotonically. I expect it w=
ould create a performance nightmare if I tried to insert old data into the =
master table while the application was actively inserting new data. The sam=
e basic technique can be adapted to other situations, but the trigger funct=
ion is slightly more complicated, and the function to create the trigger fu=
nction would be kind of ugly and hard to follow.

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


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