pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] discussion #877: Full table scan of default partition during partman maintenance (ATTACH PARTITION)
3+ messages / 2 participants
[nested] [flat]

* [pgpartman/pg_partman] discussion #877: Full table scan of default partition during partman maintenance (ATTACH PARTITION)
@ 2026-09-18 06:26 "sanjay-motorq (@sanjay-motorq)" <noreply+sanjay-motorq@github.com>
  0 siblings, 0 replies; 3+ messages in thread

From: sanjay-motorq (@sanjay-motorq) @ 2026-09-18 06:26 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

Problem: We have a usecase where we partition our tables by timestamp and there are cases where data can arrive late. This data, if it lies outside the range of our partitions will go to the default partition. We cannot afford to drop this data immediately or change the partition key.
We have a way to clean up the default partition once the data is no longer required.

The issue here is that, partman maintenance takes too long due to the amount of data in default partition. Since this locks the table, our queries fail during this time window.

Proposal: If the goal is to check the default table for any conflicting rows (that might belong in the new partition), why cant we use an index scan instead of a full table scan ?
What are the blockers to go with this approach ?

^ permalink  raw  [nested|flat] 3+ messages in thread

* Re: [pgpartman/pg_partman] discussion #877: Full table scan of default partition during partman maintenance (ATTACH PARTITION)
@ 2026-10-29 17:29 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  1 sibling, 0 replies; 3+ messages in thread

From: keithf4 (@keithf4) @ 2026-10-29 17:29 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

You're saying here that the data is arriving late. I would assume this means the data has timestamps that are in the past, not the future? If that's the case, are you dropping older tables via retention? If so, ensure your retention window is longer than the window of time you expect late data. If this is data that is even older than you started your partition set for, then I'd just see about making as many older tables manually as you'd need to cover that window.

If you're talking about timestamp values in the the future, then set your premake window to account for the expected window of future data.

You could also re-evaluate your partition interval as well to make it larger so each table can cover a larger period of time.

If you can give an example of your table's schema along with the `part_config` entry for it, I might be able to help a bit more.

^ permalink  raw  [nested|flat] 3+ messages in thread

* Re: [pgpartman/pg_partman] discussion #877: Full table scan of default partition during partman maintenance (ATTACH PARTITION)
@ 2026-10-29 17:36 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  1 sibling, 0 replies; 3+ messages in thread

From: keithf4 (@keithf4) @ 2026-10-29 17:36 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

As for your proposal, whether something uses an index or sequential scan when pulling the table out of the default, that's really down to how much of the table it's considering to move. If it's over 40-50% of the table that it's scanning, PG will typically default to a seq scan because it actually ends up being cheaper (avoids the index lookup overhead). To force index scans in that case, you'd have to reduce the size of the batches being moved. If it needs to be smaller than the partition interval, you'll have to look into the async partitioning introduced in v5.3.0. But carefully read the caveats that come with this method (data goes "missing" while it's moved).

If you're talking about what PG itself is doing when deciding whether to put something in the default, or what happens when you add a new child table and it's scanning the default table, that's entirely due to PG internals and I don't really have any control over that in pg_partman.

^ permalink  raw  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-10-29 17:36 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 06:26 [pgpartman/pg_partman] discussion #877: Full table scan of default partition during partman maintenance (ATTACH PARTITION) "sanjay-motorq (@sanjay-motorq)" <noreply+sanjay-motorq@github.com>
2026-10-29 17:29 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-10-29 17:36 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox