Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

On Tue, May 31, 2022 at 5:33 AM Dmitry Koval <d(dot)koval(at)postgrespro(dot)ru> wrote:
> There are not many commands in PostgreSQL for working with partitioned
> tables. This is an obstacle to their widespread use.
> Adding SPLIT PARTITION/MERGE PARTITIONS operations can make easier to
> use partitioned tables in PostgreSQL.
> (This is especially important when migrating projects from ORACLE DBMS.)
>
> SPLIT PARTITION/MERGE PARTITIONS commands are supported for range
> partitioning (BY RANGE) and for list partitioning (BY LIST).
> For hash partitioning (BY HASH) these operations are not supported.

This may be a good idea, but I would like to point out one
disadvantage of this approach.

If you know that a certain partition is not changing, and you would
like to split it, you can create two or more new standalone tables and
populate them from the original partition using INSERT .. SELECT. Then
you can BEGIN a transaction, DETACH the existing partitions, and
ATTACH the replacement ones. By doing this, you take an ACCESS
EXCLUSIVE lock on the partitioned table only for a brief period. The
same kind of idea can be used to merge partitions.

It seems hard to do something comparable with built-in DDL for SPLIT
PARTITION and MERGE PARTITION. You could start by taking e.g. SHARE
lock on the existing partition(s) and then wait until the end to take
ACCESS EXCLUSIVE lock on the partitions, but we typically avoid such
coding patterns, because the lock upgrade might deadlock and then a
lot of work would be wasted. So most likely with the approach you
propose here you will end up acquiring ACCESS EXCLUSIVE lock at the
beginning of the operation and then shuffle a lot of data around while
still holding it, which is pretty painful.

Because of this problem, I find it hard to believe that these commands
would get much use, except perhaps on small tables or in
non-production environments, unless people just didn't know about the
alternatives. That's not to say that something like this has no value.
As a convenience feature, it's fine. It's just hard for me to see it
as any more than that.

--
Robert Haas
EDB: http://www.enterprisedb.com