pg_partman does not create new time-based partitions when tables are created far in the future #347
Comments
|
It would be great if |
|
Additionally, we found that |
|
So I'll explain how pg_partman works as of the latest version... Yes this has changed compared to how things worked back in 1.x and 2.x. It was changed so it works better, and in a more predictable way, for most cases. Maintenance looks at the "newest" data that exists in the partition set, no matter if it's in the child tables or the parent/default. It checks both the latest child and the parent/default and takes the higher value. See - https://github.com/pgpartman/pg_partman/blob/master/sql/functions/run_maintenance.sql#L228 It then makes the next partition based on that new data. This was done specifically due to maintenance issues that were encountered by other users when pg_partman made new tables based only on the child table values. Yes, if you have data that is far into the future that gets inserted, that means any new partitions that are made will also be far into the future and you will have a gap. But that data will not be lost, it will go into either the parent or default. Because of this, it is highly recommended that you have close monitoring on data going into the parent/default. This is actually critically important for native partitioning since excessive data in the parent can greatly affect maintenance performance. (see #262 (comment)). As soon as you see any alerts for data going into the parent/default, that data should be handled ASAP to either discard it or partition it out as needed. You can monitor for data in the parent/default by using the If you end up having gaps due to far future data, I did add a function to automatically fill those gaps. See If you know for a fact that you will be getting data far into the future within a specific time frame, please set your premake appropriately to handle your expected data range. PG12+ native partitioning can handle larger amounts of child tables much more efficiently, so if the large child table count is causing issues for you, please plan to upgrade. Also, if you're still using trigger-based partitioning, it is highly recommended to migrate to native unless you're using some specific feature that is not yet supported. I have written several guides in the The While it would be nice to have maintenance automatically check for gaps and fill them in, I likely won't be doing that for several reasons. One, I believe that could impact the performance of the maintenance process the more partition sets it is managing. Second, if you accidentally get far future or past data and you didn't intend to, you will have a bunch of child tables created that you don't need and have to clean that up. So, in summary, set your premake high enough to handle your expected data range and closely monitor for data going into the parent/default. Hopefully that helps explains how things work and lets you address the issues you've encountered. If you still have any questions, please let me know. |
|
Thank you for this fantastic explanation. I was unaware of |
|
A flag would be nice, maybe. But I'm honestly trying to avoid anymore configuration options unless they are absolutely necessary since the config table is getting a bit excessive. |
|
Understood. Thank you for the help! |
We recently upgraded from an old version of pg_partman to 4.4.1. We noticed that one of our partitioned tables (
readingsin this example) was no longer creating new partitions and data was piling up in the parent table, causing slow performance.The table is partitioned by month. We noticed we were missing some recent partitions after our upgrade at the end of 2020. We also noticed that some of our partitions were created far into the future but we're not sure about why, though we do rarely have a need to write to a table in the future. We have not yet migrated to native partitioning for this table.
When calling
run_maintenance_proc(0, true, false, true)we saw:No new tables were being created. As an experiment, I decided to move all the data from those future partitions (>2021_03) into a temp table, delete the future partitions, and reinsert the data into the parent table. When calling
run_maintenance_procafter that, it was correctly creating the new tables (we have premake=4):Since then, we've run
partition_data_timeagainst the parent to fill in the missing past parititions and clear out the parent table.Looking into how
run_maintenanceworks, it seems that it will only premake the tables if the latest table is within the premake range. We suspect this behavior must have changed between our old version (we were on either 1.8.7 or 2.3.3, though these versions may not be exact) since we only noticed this after upgrading PG10->PG12 and pgpartman along with it. Unfortunately the extension was not upgraded inside the DB at the same time which may be why we have some gaps in the past (but does not account for the findings above)The text was updated successfully, but these errors were encountered: