Re: Read performance on Large Table

On Thu, May 21, 2015 at 12:01 PM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
wrote:

> On May 21, 2015, at 9:45 AM, Keith <keith(at)keithf4(dot)com> wrote:
> >
> > If you're just going to do basic time series partitioning, I've written
> a tool that manages most of it for you. Also does retention management as
> well and can dump out the old partitions automatically.
>
> Yep, make some partitions ahead of time, plus cron to periodically run in
> order to stay ahead, is an alternative approach.
>

This is exactly what the extension does.

>
> > I'm in the process of getting v2.0.0 out that has a lot of new work
> done, but will only be compatible with Postgres 9.4 (since it uses
> background workers to have scheduling built in). So if you want to wait, I
> should have that out soon.
>
> I wouldn’t mind that—I did it the way I did partly because I wanted it all
> self-contained in PG without external (cron or launchd or…) configuration
> dependencies.
>

The problem with creating partitions on the fly via triggers, even ahead of
when they're needed, is you run into BIG contention issues when you have
high insert rates. One session will see it needs to create a new table, the
next one comes in before the table is made but either gets put into a wait
state or outright fails when it goes to try and make the new table that's
already there. The serial partitioning method in pg_partman does do this by
default (creates new future partitions when current is at 50%) because for
serial, scheduling maintenance can be tricky because you may not be able to
predict how often it needs to run. But several people brought up issues
with the contention problems this caused so I made it optional to use the
scheduled maintenance run instead.

It may not be an issue for you, but it's definitely something to keep in
mind if you notice performance issues. :)

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