PostgreSQL 11 新特性之哈希分区_sql

概述本文介绍 PostgreSQL 11 新增的分区类型：哈希分区（hash partitioning）。

文章目录

Postgresql 10 引入了声明式分区（declarative partitioning）特性，但是实现的功能有限。Postgresql 11 为此带来了许多关于分区的增强功能。首先就是增加了 HASH 分区。哈希分区基于分区键的哈希值计算数据所在的分区。

CREATE table htable (c1 bigint, c2 VARCHAR(10)) PARTITION BY HASH(c1);

为哈希分区表创建分区时，使用 FOR VALUES WITH 子句指定分区的计算方法，其中的 MODulUS 子句用于指定除数，REMAINDER 子句用于指定哈希值被除后的余数。

CREATE table htable_p0 PARTITION OF htable FOR VALUES WITH (MODulUS 4, REMAINDER 0);CREATE table htable_p1 PARTITION OF htable FOR VALUES WITH (MODulUS 4, REMAINDER 1);CREATE table htable_p2 PARTITION OF htable FOR VALUES WITH (MODulUS 4, REMAINDER 2);CREATE table htable_p3 PARTITION OF htable FOR VALUES WITH (MODulUS 4, REMAINDER 3);

REMAINDER 子句需要指定一个小于 MODulUS 子句的值。如果指定的分区数量少于 MODulES 子句的值，将会导致无法插入某些数据，因为没有用于存储这些值的分区。

\d+ htable                                          table "public.htable" Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description --------+-----------------------+-----------+----------+---------+----------+--------------+------------- c1     | bigint                |           |          |         | plain    |              |  c2     | character varying(10) |           |          |         | extended |              | Partition key: HASH (c1)Partitions: htable_p0 FOR VALUES WITH (modulus 4, remainder 0),            htable_p1 FOR VALUES WITH (modulus 4, remainder 1),            htable_p2 FOR VALUES WITH (modulus 4, remainder 2),            htable_p3 FOR VALUES WITH (modulus 4, remainder 3)

为表 htable 生成一些数据，查看数据在各个分区的分布是否均匀：

INSERT INTO htable SELECT val,'val:'||val FROM generate_serIEs(1,100000) val;INSERT 0 100000SELECT COUNT(*) FROM htable_p0; count ------- 25126(1 row)SELECT COUNT(*) FROM htable_p1; count ------- 24978(1 row)SELECT COUNT(*) FROM htable_p2; count ------- 24971(1 row)SELECT COUNT(*) FROM htable_p3; count ------- 24925(1 row)

每个分区大概包含四分之一（25000）的数据。

对于哈希分区，同样支持分区裁剪（Partition Pruning）：

show enable_partition_pruning; enable_partition_pruning -------------------------- onEXPLAIN SELECT * FROM htable where c1 = 200;                            query PLAN                            ------------------------------------------------------------------ Append  (cost=0.00..470.57 rows=1 wIDth=17)   ->  Seq Scan on htable_p3  (cost=0.00..470.56 rows=1 wIDth=17)         Filter: (c1 = 200)(3 rows)