Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Do not create partitions that are not to be retained #649

Closed
nicolagi opened this issue Apr 11, 2024 · 4 comments
Closed

Do not create partitions that are not to be retained #649

nicolagi opened this issue Apr 11, 2024 · 4 comments

Comments

@nicolagi
Copy link
Contributor

Suppose retention is set to 4 months for a monthly partitioned table foo.

Suppose run_maintenance fails to run for whatever reason and as a consequence foo has only a very old partition, say, foo_p20010101.
When run_maintenance runs again in 2024 it will create lots of partitions.
The vast majority of them will be expired at the subsequent run.

Would it make sense to not create those in the first place?
Perhaps the timestamp to start creating partitions from should be something like MAX(NOW() - retention, last_partition_timestamp)?

nicolagi added a commit to nicolagi/pg_partman that referenced this issue Apr 11, 2024
I realized this requires tests and possibly other changes, but it's just a start to make the proposal from the pgpartman#649 more concrete.
I did try this and works for my use case.
@keithf4 keithf4 self-assigned this Apr 11, 2024
@keithf4
Copy link
Collaborator

keithf4 commented Jun 10, 2024

I'll look into seeing if this feature can be added when I return to development of the next release. Thank you!

nicolagi added a commit to nicolagi/pg_partman that referenced this issue Sep 1, 2024
I realize this requires tests and possibly other changes, but it's just a start to make the proposal from the pgpartman#649 more concrete.
I did try this and works for my use case.
nicolagi added a commit to nicolagi/pg_partman that referenced this issue Sep 1, 2024
I realize this requires tests and possibly other changes, but it's just a start to make the proposal from the pgpartman#649 more concrete.
I did try this and works for my use case.
keithf4 pushed a commit that referenced this issue Sep 12, 2024
I realize this requires tests and possibly other changes, but it's just a start to make the proposal from the #649 more concrete.
I did try this and works for my use case.
@keithf4 keithf4 added this to the 5.2 milestone Oct 16, 2024
@keithf4
Copy link
Collaborator

keithf4 commented Nov 19, 2024

Just FYI, had to do some adjustment with the PR for this. While testing your scenario where a single old child table was left, the tables that were created the next maintenance run would not have the proper lower-bound set. For example, if partitioned monthly, the child tables would normally lower-bound on the 1st of the month. However, since the raw CURRENT_TIMESTAMP value was being used, when it would run, it would be basing the lower-bound on the current day. So for my testing today (Nov 19th) was resulting in all the child tables having a lower-bound of the 19th for all months.

For a moment I thought I'd have to revert this PR, but thankfully I'd accounted for this! The calculate_time_partition_info() function can properly truncate the interval to the desired value.

9cd69e0

@nicolagi
Copy link
Contributor Author

nicolagi commented Nov 20, 2024 via email

@keithf4
Copy link
Collaborator

keithf4 commented Nov 22, 2024

Version 5.2.0 has been released. Thank you for reporting the bug and working on fixing it with me!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants