TAG 13
作者
digoal
日期
2017-03-13
标签
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
同时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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
