PostgreSQL 10.0 preview 功能增强 - 分区表(list default)

标签

PostgreSQL , 10.0 , 分区表 , 默认分区

背景

PostgreSQL 10.0将支持range,list分区表，同时hash分区处于POC阶段（同时还有一些需要改进的地方，例如优化器部分）。

如果你使用的是10.0以前的版本，可以使用pg_pathman插件实现分区,pg_pathman已经非常的完美。

PostgreSQL支持伪表作为分区，例如外部表，物化视图。伪表作为分区有很多可以适合的使用场景，例如将外部表作为分区，则可以实现sharding场景。

分区表用法

https://www.postgresql.org/docs/devel/static/sql-createtable.html

《PostgreSQL 10.0 内置分区表》

同时list分区将添加default语法，没有在list中的数据，将写入default分区。

Hello,  

Currently inserting the data into a partitioned table that does not fit into
any of its partitions is not allowed.  

The attached patch provides a capability to add a default partition to a
list
partitioned table as follows.  

postgres=# CREATE TABLE list_partitioned (
    a int
) PARTITION BY LIST (a);
CREATE TABLE  

postgres=# CREATE TABLE part_default PARTITION OF list_partitioned FOR
VALUES IN (DEFAULT);
CREATE TABLE  

postgres=# CREATE TABLE part_1 PARTITION OF list_partitioned FOR VALUES IN
(4,5);
CREATE TABLE  

postgres=# insert into list_partitioned values (9);
INSERT 0 1  

postgres=# select * from part_default;
 a
---
 9
(1 row)  

The attached patch is in a  preliminary stage and has following ToDos:
1. Adding pg_dump support.
2. Documentation
3. Handling adding a new partition to a partitioned table
   with default partition.
   This will require moving tuples from existing default partition to
  newly created partition if they satisfy its partition bound.
4. Handling of update of partition key in a default partition. As per
current design it should throw an error if the update requires the tuple to
be moved to any other partition. But this can changed by the following
proposal.  

https://www.postgresql.org/message-id/CAJ3gD9do9o2ccQ7j7+tSgiE1REY65XRiMb=
yJO3u3QhyP8EEPQ(at)mail(dot)gmail(dot)com  

I am adding it to the current commitfest with the status Waiting on Author
as I will submit an updated patch with above ToDos.
Kindly give your suggestions.  

Thank you,
Rahila Syed

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/1066/

https://www.postgresql.org/message-id/flat/CAH2L28tbN4SYyhS7YV1YBWcitkqbhSWfQCy0G=apRcC_PEO-bg@mail.gmail.com#CAH2L28tbN4SYyhS7YV1YBWcitkqbhSWfQCy0G=apRcC_PEO-bg@mail.gmail.com