Too much data ingress for partman to succeed?

[MaaHeebTrackbee]

I'm having issues lately, we run pg_partman on a table that ingresses around ~5k records every minute and this volume makes either run_maintenance_proc and partition_data_proc both fail as, what i think, data gets inserted before pg_partman can finish its routine.

This snippet is the error i get:
pq: updated partition constraint for default partition "redacted_default" would be violated by some row CONTEXT: SQL statement "ALTER TABLE public.redacted ATTACH PARTITION public.redacted_p20251104 FOR VALUES FROM ('2025-11-04 00:00:00+00') TO ('2025-11-05 00:00:00+00')" PL/pgSQL function create_partition_time(text,timestamp with time zone[],text) line 197 at EXECUTE PL/pgSQL function partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 243 at assignment SQL statement "SELECT public.partition_data_time (p_parent_table := 'public.redacted', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)" PL/pgSQL function partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean) line 86

How do you handle these type of situations? Is there a golden standard i probably don't follow?
Love to hear back from any of you.

[keithf4]

This error means there is data in the default that would match data that would go into the new child table that it is trying to attach. So that means you are inserting data that does not yet have a child table for it. First thing would be to figure out what the window of time you expect your data ingestion to cover relative to the partition column's value. Once you know that, then set the "premake" value in "part_config" to stay ahead of your expected data ingestion window and make sure maintenance is running often enough to keep up with it.

Note that partman partition creation is only for future data. If you're still getting data into the default for past data without child tables, then you'll have to manage migrating that out manually. The partition_data_proc() procedure along with the partition_gap_fill() function can help there. Past data should not be causing this error, but you want to keep data out of the default to avoid maintenance performance issues.

[keithf4]

If the data in the default is valid data, again the partition_data_proc() and partition_gap_fill() functions can be used to resolve the error you're getting as well. If the data is invalid, then just delete it.

[MaaHeebTrackbee]

All data is valid, the premake is 7 days and run_maintenance_proc runs every 12hrs, where partition_data_proc runs every 10 minutes. We partition by day.

We have lots of tables partitioned and running great, where pg_partman premakes all partitions nicely and the default stays empty.

Running partition_data_proc also just gives this error which makes me to believe that in the process of pg_partman trying to move everything from the default to a new partition, between taking the data from default and creating the actual partition there is new data being inserted hindering the creation of the partition (which totally makes sense if that is the case). But these are guesses, have not checked on what locks pg_partman takes on for example.

[keithf4]

Ok yeah, partition_data_proc() is not going to work if it's actively trying to partition out data within a window that is still being ingested. You'll have to wait until that data ingestion time window passes before you can move that data out. But data shouldn't be going into the default if there is a child table for it, so something isn't lining up here.

I'm assuming this is a time based partition set, not numerical, right? Ideally you shouldn't need to be running partition_data_proc() as part of normal maintenance unless your data ingestion window is just any possible data at any possible time value.

Is the data that's in the default that it's trying to parse out in the past or the future? If it's in the future, then your ingesting data outside of the premake's expected window so you'll need to increase it. To fully fix it then, you may have to wait a bit for the data ingestion time window to fully pass to get all of the old data out of the default then too. If it's in the past, you'll have to figure out a time that you can partition it out when data within that time period isn't being ingested anymore.

[MaaHeebTrackbee]

Thanks, it's time based, daily to be precise. Data thats ingested is always for the current day, and we don't have ingestion windows, it just keeps on coming, making the whole maintenance thing a little more complicated.
We in fact do have premake set at 3 days and on initialization it is nicely created, but along the line something goes haywire and running maintenance just doesn't premake anymore leading us to not noticing it and starting the dump all data to default eventually.
As we run our database in Google Cloud SQL, i don't really have fine grained log control on the engine side for me to figure out what is going wrong.
Are there any common failures for the maintenance function regarding high ingress tables?
I'm also trying to figure stuff out in the meantime and have created some partitions manually for tomorrow onwards so i can re-enable maintenance again and so i can flush out the default table in batches.

[keithf4]

When i say ingestion window, I didn't mean a period of time that data is being inserted. I mean the window of the values being inserted. For you that sounds like it's one day, so a premake of 3 should normally be fine. The default is 4 and before you said it was 7. There's nothing wrong with setting this value higher and it will only increase the number of partitions being made that first initial run. After that it's only making 1 new partition every time it's actually needed as long as it's run often enough, which every 12 hours is for you.

The only scenario I know of where partitions are not made is when no data is being inserted. By default pg_partman does not make new partitions if there's no new data. This is to keep it from making new children on an inactive table forever and to be consistent with the number based partitioning. So say your premake was 3 and you had 4 days of no data, then the necessary child table would not be there. For that situation, there's the infinite_time_partitions setting in part_config. That causes it to keep making time-based partitions based on "now" even if there's no new data. In this situation maintenance runs typically start throwing errors as well, but since you're also running partition_data_proc() so often it's moving that data out to the proper child table instead. You may see gaps in the series of child tables as well when this happens. The partition_gap_fill() function can fix that.
So, try setting infinite_time_partitions to true for the given partition sets having problems.

Otherwise, I'm not aware of anything else that would cause the child tables to fail creation without throwing some sort of error (deadlock, syntax error, etc).

[MaaHeebTrackbee]

Thanks for thinking along Keith. Will have a check coming monday.