Create range partitions in advance

[tarvip]

First of all, thanks for great work so far.

I wonder is there a way to create range partitions in advance (yes, I could do that manually). I have noticed that if there is a need to create a new partition then that particular insert, that triggers creation of new partition, is a bit slower.
I would prefer to have next partition ready and have constant insert durations.

Initially it would be nice to have a function that can be called to create next partition.

Ultimately I would like to set a number of partitions to have ready. And when inserts start going to last partition then bgw ensures that there is next(empty) partition ready and main transaction is unaffected by delay of creating new partition. I guess this example is more valid for tables that are partitioned by date ranges.

[funbringer]

Hi @tarvip,

Thank you for your feedback!

I have noticed that if there is a need to create a new partition then that particular insert, that triggers creation of new partition, is a bit slower.

That's true, but is it such a problem? It only happens once in a while. I don't pretend to have a complete understanding of your project, but there are various use-cases that don't suffer from this particular implementation. You can also use set_auto() if you'd like to restrict automatic partition creation.

Initially it would be nice to have a function that can be called to create next partition.

Such a function exists, and it's called append_range_partition(). There's also a sibling function prepend_range_partition(). Keep in mind that they're only suitable for partitioning by range, though.

Ultimately I would like to set a number of partitions to have ready. And when inserts start going to last partition then bgw ensures that there is next(empty) partition ready and main transaction is unaffected by delay of creating new partition.

I'm sorry, but we're not going to implement this. There are several reasons:

• Right now we're actively working on features that are crucial for sharding, and we don't have much spare time.
• You could create a cron task calling append_range_partition() (at least if you're using a UNIX-like OS).
• PostgresPro Enterprise features a task scheduler that's perfectly suited for such a problem. It's not free, but you have to understand that we can't interfere with our own commercial solutions.

Anyway, we're willing to help if you have any questions.

[tarvip]

That's true, but is it such a problem?

Will observe it, maybe it is not a problem.

Ok, great at least there is function already available.

Closing this issue as I got my questions answered.

Thank you.