On Tue, May 31, 2022 at 5:33 AM Dmitry Koval <d.ko...@postgrespro.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