Reputation: 347
I am unaware of the best practices involved in the automation of the addition of future partitions to a table. So the situation is like this: In the month of say, Dec 2021, we want to create partitions for the next year (2022) for some tables in Postgres. We can obviously do it manually, but we want to automate this. So far, I could think of (and found by researching, talking to some people, etc.) the following ways: -
Is there any other way that I am missing? If not, what would be the best way of the above to automate the addition of future partitions to a table in Postgres.
Also, please point out if this is not the right platform for such questions (it would be great if you could direct me to the right one).
Thank you for reading this.
Upvotes: 3
Views: 2798
Reputation: 866
All of your proposed methods are sub-optimal either in terms of managing the scheduling or in terms of performance.
The best solution is likely to use the pg_partman
extension which comes packaged with a scheduler that once enabled manages creating new child partitions for you. This avoids having to add any additional custom logic with an external cron job (or internal with pg_cron
/pg_timetable
).
Upvotes: 0
Reputation: 248305
Option 1 is not sufficient, because you need a way to run the code automatically (that's the hard part). It doesn't matter much if you use PL/pgSQL or a client side language for the procedural parts of the operation.
Option 3 is not easy to achieve, and certainly not in an efficient fashion.
I would say that the best way is to schedule a job for partition creation, either with the operating system scheduler (cron
) or with a PostgreSQL extension like pg_timetable
or pg_cron
.
Upvotes: 2