CODEWITHSUNDEEP
postgresqldatabase-partitioning
Prabhatika Vij
Prabhatika Vij

Reputation: 347

Best way to automate addition of future partitions to a postgres table

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: -

  1. Using PL/pgSQL (In my opinion, there could be issues related to version control and deployment here)
  2. Writing a script (in Python, say) and executing it as a cron job annually
  3. Adding the partitioning logic to the code (that inserts records in the database) i.e., whenever a record is inserted in a table, you check whether the partition corresponding to the record exists, if not, you create the partition (fetching the metadata of the table with each incoming record can be expensive, but we can try and optimize it in my opinion)

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

Answers (2)

Timaayy
Timaayy

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

Laurenz Albe
Laurenz Albe

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

Related Questions