Ideal number of partitions for data purge

[kushagk]

[Use case]: I need to purge Pgres data older than 30 days as a part of a compliance step.
[Issue]: Say, if I partition my table monthly using timestamp, use retention as 1 month in partman.part_config and use the default cron bgw.interval (hourly). Now, on 1st Feb, if there is a db entry with timestamp = 1st Jan, it will detach the partition of January month from main table and remove the entries made on 31st Jan. So, I end up deleting data which is one day old.

If I partition the table daily, then my goal is achieved but the number of partitions become 30.

[Questions]

1. What would be recommended solution here ?
2. How many partitions are a lot of partitions ? My service has a peak load of 150 Requests per minute. Is it advisable to go for daily partitions here ?
3. What is the maximum number of partitions that are allowed in PGres. I couldn't find a value online. Few posts online do say to not go beyond 1000 partitions.

[keithf4]

As long as you're on PG version 12 or higher, you can have a quite a high number of partitions (several 1000) without noticing much of an issue.

If you want to try and keep the partition count lower, I'd say do monthly but instead of a 30 day/1 month retention do 60 days/2months. Unless there's some business requirement that no data older than 30 days can exist, there's no harm in keeping one extra partition around to ensure you keep your minimum data amount.

If you must purge older data, then you'd have to go with the daily option.

[kushagk]

How about this approach: (Table1 --(FK1)--> Table2 --(FK2)--> Table3)

1. partition the table with equal duration.
2. Set retention value of T3 as 28 days, T2 as 29 days, T1 as 30 days. So, T3 will be deleted first, T2 next and when T1 is dropped both T2 and T3's partitions would be gone.

[keithf4]

You'd have to try it and see :)

[kushagk]

@keithf4 : Yes, I tried and it did not work :(

This is what I am going for next:

1. keep retention_keep_table = true: So the older data will not come up while querying the parent table.
2. Now, the detached partitions are free from the foreign key's barriers and can be dropped.
3. Write a procedure to delete the detached partitioned tables since the table name follows a timestamp regular expression.
   Something like: https://stackoverflow.com/questions/13110225/drop-tables-using-regex-mysql#:~:text=The%20revised%20solution%20would%20be,GROUP_CONCAT(table_name))%20FROM%20information_schema.

Please opine.

[keithf4]

It may not even let you detach them depending on the FK data

[kushagk]

@keithf4

It does; I tested it.

1. I detached the the table(retention_keep_table = true) in the order of the foreign keys. The lower most table in the hierarchy had the least retention duration.
2. Post detaching, it allows to drop the detached table if done in the right order. [This as of now is still a manual step for me and I am looking for the best ways].
3. Did not ask to break any FK relations amongst the parent table.

This behaviour is very special compared to below:
In case of drop (retention_keep_table = false), the partitions are not dropped even if there is no data in it.