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
hash poc 如下
```
Hi all,
Now we have a declarative partitioning, but hash partitioning is not
implemented yet. Attached is a POC patch to add the hash partitioning
feature. I know we will need more discussions about the syntax and other
specifications before going ahead the project, but I think this runnable
code might help to discuss what and how we implement this.
- Description
In this patch, the hash partitioning implementation is basically based
on the list partitioning mechanism. However, partition bounds cannot be
specified explicitly, but this is used internally as hash partition
index, which is calculated when a partition is created or attached.
The tentative syntax to create a partitioned table is as bellow;
CREATE TABLE h (i int) PARTITION BY HASH(i) PARTITIONS 3 USING hashint4;
The number of partitions is specified by PARTITIONS, which is currently
constant and cannot be changed, but I think this is needed to be changed in
some manner. A hash function is specified by USING. Maybe, specifying hash
function may be ommitted, and in this case, a default hash function
corresponding to key type will be used.
A partition table can be create as bellow;
CREATE TABLE h1 PARTITION OF h;
CREATE TABLE h2 PARTITION OF h;
CREATE TABLE h3 PARTITION OF h;
FOR VALUES clause cannot be used, and the partition bound is
calclulated automatically as partition index of single integer value.
When trying create partitions more than the number specified
by PARTITIONS, it gets an error.
postgres=# create table h4 partition of h;
ERROR: cannot create hash partition more than 3 for h
An inserted record is stored in a partition whose index equals
abs(hashfunc(key)) %
example, this is abs(hashint4(i))%3.
postgres=# insert into h (select generate_series(0,20));
INSERT 0 21
postgres=# select *,tableoid::regclass from h;
i | tableoid
----+----------
0 | h1
1 | h1
2 | h1
4 | h1
8 | h1
10 | h1
11 | h1
14 | h1
15 | h1
17 | h1
20 | h1
5 | h2
12 | h2
13 | h2
16 | h2
19 | h2
3 | h3
6 | h3
7 | h3
9 | h3
18 | h3
(21 rows)
- Todo / discussions
In this patch, we cannot change the number of partitions specified
by PARTITIONS. I we can change this, the partitioning rule
(
is also changed and then we need reallocatiing records between
partitions.
In this patch, user can specify a hash function USING. However,
we migth need default hash functions which are useful and
proper for hash partitioning.
Currently, even when we issue SELECT query with a condition,
postgres looks into all partitions regardless of each partition's
constraint, because this is complicated such like "abs(hashint4(i))%3 = 0".
postgres=# explain select * from h where i = 10;
QUERY PLAN
Append (cost=0.00..125.62 rows=40 width=4)
-> Seq Scan on h (cost=0.00..0.00 rows=1 width=4)
Filter: (i = 10)
-> Seq Scan on h1 (cost=0.00..41.88 rows=13 width=4)
Filter: (i = 10)
-> Seq Scan on h2 (cost=0.00..41.88 rows=13 width=4)
Filter: (i = 10)
-> Seq Scan on h3 (cost=0.00..41.88 rows=13 width=4)
Filter: (i = 10)
(9 rows)
However, if we modify a condition into a same expression
as the partitions constraint, postgres can exclude unrelated
table from search targets. So, we might avoid the problem
by converting the qual properly before calling predicate_refuted_by().
postgres=# explain select * from h where abs(hashint4(i))%3 = abs(hashint4(10))%3;
QUERY PLAN
Append (cost=0.00..61.00 rows=14 width=4)
-> Seq Scan on h (cost=0.00..0.00 rows=1 width=4)
Filter: ((abs(hashint4(i)) % 3) = 2)
-> Seq Scan on h3 (cost=0.00..61.00 rows=13 width=4)
Filter: ((abs(hashint4(i)) % 3) = 2)
(5 rows)
Best regards,
Yugo Nagata
--
Yugo Nagata
```
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://commitfest.postgresql.org/13/1059/
https://www.postgresql.org/message-id/flat/20170228233313.fc14d8b6.nagata@sraoss.co.jp#20170228233313.fc14d8b6.nagata@sraoss.co.jp
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
