Re: MERGE/SPLIT PARTITIONS issues/questions

On Tue, Aug 25, 2026 at 1:17 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Aug 24, 2026 at 6:40 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> > The attached patch (based on v6) is very rough; I will polish it later.
>
> I hate to be a downer here, but I don't really understand why this
> feature got committed (or, well, re-committed) in the first place, and
> I don't understand why it hasn't been reverted yet. What does this
> actually do that anyone would want or find advantageous?
>
> I admit to being a skeptic of this kind of feature on general
> principle, so take everything I have to say here with an
> appropriately-sized amount of salt. But it seems to me that the major
> arguments for a feature like this would be if it either (1) makes the
> new partitions that it creates really good clones of the original
> partition or (2) does something to minimize data movement or (3) finds
> clever ways to reduce the amount of locking required. As to (1), even
> Alexander seems not entirely satisfied with the current behavior and
> proposes that it be changed in a future release, but that's a
> backward-incompatibility that we should be reluctant to introduce. As
> to (2), the source partitions are always copied in their entirety to
> new partitions, which is probably a pretty fair strategy when
> splitting a partition into equal parts or merging roughly equal-size
> partitions, but very non-optimal when the splits or merges are very
> lopsided. The point here isn't that the strategy is horrible but that
> there's no particular intelligence here; you can easily do the same
> thing by hand. As to (3), the patch takes AccessExclusiveLock on the
> partition parent for the entire duration of the operation. This seems
> non-viable in practice. I suspect that essentially 100% of users will
> prefer to quiesce writes to the partition to be split or merged,
> create new partitions with the same data, and then use ATTACH/DETACH
> CONCURRENTLY to do the swap.

Yes, I agree. Even the first implementation shouldn't be worse than
what user can manually do: block writers only most of the time, and
block writers and readers only for the short window to swap the
tables. And there should be the clear way to make it work like REPACK
CONCURRENTLY to allow both readers and writers most of the time.

> To go into a little more detail about (1), I asked Claude to analyze,
> in the current code, which partition properties are set from the
> original partition vs. which ones are set as they would be from a new
> partition. Basically, it says that [A] ownership is copied from the
> source partition(s), apparently in response to my 2024 complaint, and
> [B] DEPENDS ON EXTENSION markers on indexes are copied from source
> partitions. According to Claude, everything else is identical to what
> you would get with CREATE TABLE ... PARTITION OF, except that when the
> parent has no AM set, the default is heap rather than
> default_table_access_method, which is a bug. This seems like a very
> disappointing state of affairs, not so much because of the bug, but
> just because it doesn't seem at all principled. Like, why those two
> things, and nothing else? The ALTER INDEX .. DEPENDS ON EXTENSION
> thing was introduced by 713e553e321 and is a result of the fact that
> the indexes are re-cloned from the parent rather than the source
> partition -- but there is also ALTER TRIGGER ... DEPENDS ON EXTENSION,
> which wasn't changed. I think what almost everyone has said is that
> they want clone-of-source-partition behavior, not
> new-partition-of-parent behavior, but
> almost-new-partition-of-parent-but-with-a-few-random-exceptions seems
> almost worse. Those exceptions aren't curing the basic design problem
> here; they're only obscuring it.

You're right, too many design question arise, and too late to resolve
them. Reverted.

------
Regards,
Alexander Korotkov
Supabase