postgresql 表分区

背景

一个大表查询性能往往不高, 其原因在于数据加载 data load,扫描数据 table scans ,内存交换 memory swap 等等操作的时间成本会随着数据量的增加而上升.

从 PG 11 开始, 支持较为简洁的声明表分区. 为提高大表性能提供了一种解决方案.

什么是表分区

表分区(Partitioning) 指的是: 将一个逻辑上的大表拆分成多个物理上的小表. 是一种单体数据库表的水平分表.

表分区能带来以下好处:

• 在特定情形下, 查询性能的大幅度提升. 尤其是在绝大多数查询集中于某个分区上时.
• 当查询或者更新一个大百分比的单一分区时, 性能可能通过分区的顺序扫描替代索引扫描来提高.
• 批量加载或者删除可以通过添加或者减少分区来完成. 这比直接 delete 要快得多.
• 极少使用的数据可以迁移到更加便宜的存储介质中

通常只有当一个表非常大(超过数据库服务器的物理内存)时，这些好处才有价值, 但表从分区中获益的确切时间取决于应用程序和使用场景。

如何进行表分区

pg 提供以下表分区的形式

• Range 范围分区
  • 通过值的范围来进行分区, 例如时间, 数字等. 不过要注意数据范围是左闭右开. 例如:[10, 100)表示从 10 到 99.
• List 列表分区
  • 通过固定列表里的值进行分区
• Hash 哈希分区
  • 根据 hash 算法进行分区
• Multi-level 多级分区
  • 分区的分区, 可以将上述 3 种分区方式混合使用. 例如: 先按照时间分区, 再按照状态再次分区.

-- 查看表 分区信息
select
	d.relname, c.relname
from
	pg_class c
	join pg_inherits i on i.inhrelid = c. oid
	join pg_class d on d.oid = i.inhparent
where
	d.relname = '表名';

Range 范围分区

-- 创建分区表, 并以列 arr 进行分区
CREATE TABLE range_customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
-- 分区表
CREATE TABLE cust_arr_small PARTITION OF range_customers FOR VALUES FROM (MINVALUE) TO (25);
CREATE TABLE cust_arr_medium PARTITION OF range_customers FOR VALUES FROM (25) TO (75);
CREATE TABLE cust_arr_large PARTITION OF range_customers FOR VALUES FROM (75) TO (MAXVALUE);
-- 插入测试数据
INSERT INTO range_customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
-- 查看分区结果
SELECT tableoid::regclass,* FROM range_customers;

注意: pg 11 不支持自动创建分区. 例如: 以 创建时间 每一个月的数据自动生成分区表, pg 原生不支持.
但是可以通过插件 pg_partman 或者定时任务 scheduler pg_agent 自动插入.

• 具体情况:stackoverflow automatic-partitioning-by-day-postgresql

Oracle 表分区支持自动创建.

List 列表分区

-- 创建表, 根据列 status的值进行分区
CREATE TABLE list_customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
-- 分区表
CREATE TABLE cust_active PARTITION OF list_customers FOR VALUES IN ('ACTIVE');
CREATE TABLE cust_archived PARTITION OF list_customers FOR VALUES IN ('EXPIRED');
-- 其它值默认给到 cust_others表
CREATE TABLE cust_others PARTITION OF list_customers DEFAULT;
-- 插入测试数据
INSERT INTO list_customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
-- 查看分区结果
SELECT tableoid::regclass,* FROM list_customers;

Hash 哈希分区

-- 创建表 并根据id进行hash
CREATE TABLE hash_customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY HASH(id);
-- 分区表 根据模运算取余的结果分区
CREATE TABLE cust_part1 PARTITION OF hash_customers FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE cust_part2 PARTITION OF hash_customers FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE cust_part3 PARTITION OF hash_customers FOR VALUES WITH (modulus 3, remainder 2);
-- 插入数据
INSERT INTO hash_customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
-- 查看数据分区结果
SELECT tableoid::regclass,* FROM hash_customers;

Multi-level 多级分区

-- 第一级: 根据列 status 按照 LIST 进行分区
CREATE TABLE multi_level_customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
-- status 的表分区
CREATE TABLE multi_cust_active PARTITION OF multi_level_customers FOR
VALUES
  IN ('ACTIVE', 'RECURRING', 'REACTIVATED') PARTITION BY RANGE(arr);

CREATE TABLE multi_cust_others PARTITION OF multi_level_customers DEFAULT;

-- 第二级: 对分区表 multi_cust_active 的列 arr Range分区
CREATE TABLE multi_cust_arr_small PARTITION OF multi_cust_active FOR
VALUES
FROM
  (MINVALUE) TO (101);

CREATE TABLE multi_cust_arr_big PARTITION OF multi_cust_active FOR
VALUES
FROM
  (101) TO (MAXVALUE);
-- 插入测试数据
INSERT INTO multi_level_customers VALUES (1,'ACTIVE',100), (2,'RECURRING',200), (3,'REACTIVATED',38), (4,'EXPIRED',144);
-- 查看数据分区结果
SELECT tableoid::regclass,* FROM multi_level_customers;

限制 Limitations

• 唯一约束(包括主键) 在分区表中必须包括分区的列. 因为: 分区的表仅能限制自己分区部分的数据唯一性.

create unique index list_customers_pk on list_customers(id);
-- ERROR: 错误:  unique constraint on partitioned table must include all partitioning columns
-- DETAIL:  表"list_customers"上的约束UNIQUE缺少列"status"，该列是分区键的一部分.

• 没有办法创建跨越整个分区表的排除约束( exclusion constraint )。只能将这样的约束单独地放在每个叶分区上。同样，这种限制源于不能执行跨分区限制。
• 虽然分区表上支持主键，但不支持引用分区表的外键。(支持从分区表到其他表的外键引用。)

表分区最佳实践

怎么进行表分区应该谨慎思考的; 否则, 由于错误的设计, 表分区反而会降低查询性能.

• 选择那一列作为分区的列?
  • 根据使用场景, 一般来说是 where 语句中最常使用的字段.
• 分区表的数量多少个合适?
  • 需要依据使用场景和对未来的数据增长的判断来决定.
  • 过少: 可能意味着索引仍然太大，数据局部性仍然很差，这可能导致较低的缓存命中率。
  • 过多: 过多的分区可能意味着更长的查询规划时间和查询规划和执行期间更高的内存消耗。

参考

• https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql
• https://www.postgresql.org/docs/11/ddl-partitioning.html
• 三分钟理解分库分表
• PostgreSQL 分区表如何支持多列唯一约束
• PostgreSQL exclude 排它约束详解