postgresql hash分区，partition by hash

What & Why

• What

什么是分表： 把一个大表分成若干分区，使其物理上分散，逻辑上连续。各分区继承一个母表，对母表进行的操作会自动同步到各分区（包括插入数据时按照分表方式自动导入不同分区、建立索引等）。

• Why

单表数据量过大（对于博主8GRAM的商务本来说，千万级），影响查询性能。
分表的优点：

显著提升查询性能，尤其是当频繁访问的记录在同一个分区或少数几个分区中时。
分区索引在一定程度上替代了传统的索引，可以减小索引占用的空间。
批量读取和删除可以以分区为单位完成，例如detach partition这种操作速度远远快于bulk delete操作。

PostgreSQL分表方式

1. Range Partitioning
   将某列（或某几列）的值划分成互不重叠的区间，如时间按月划分。
2. List Partitioning
   按枚举出的一些值分区。
3. Hash Partitioning
   指定一个模数（modulus），将hash值对模取余（remainder），余数相同的分到同一分区。
   range、list方式可能会出现数据不均匀的情况，如冰棍销售情况按月分表，夏天销量远高于冬天。

创建分区表

create table dept (id  int primary key) partition by hash(id) ; 
1

创建10个分区
创建分区（以10为模数，按余数分10个区）

create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);

1
2
3
4
5
6
7
8
9
10
11

插入测试数据

insert into dept (select generate_series(0,200000));
1

创建索引
对母表进行操作即可，已存在的分区和后续创建的分区上也会包含该索引。

CREATE INDEX ON dept (id);
1