On May 21, 2015, at 9:45 AM, Keith <keith@keithf4.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. :)