Postgres 表分区
当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。
PostgreSQL对下列分区形式提供了内建支持：
- 范围划分，根据一个关键列或一组列划分为“范围”，例如日期
- 列表划分，显式地列出每一个分区中出现的键值来划分表
- 哈希分区，为每个分区指定模数和余数来对表进行分区
无法把一个常规表转换成分区表，反之亦然。不过，可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的表。
声明式分区
在 v10 中，引入了新的专用分区语法。
假定我们正在为一个大型的冰激凌公司构建数据库。该公司每天测量最高温度以及每个区域的冰激凌销售情况。
1
2
3
4
5
6
|
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
|
我们知道大部分查询只会访问上周的、上月的或者上季度的数据，因为这个表的主要用途是为管理层准备在线报告。为了减少需要被存放的旧数据量，我们决定只保留最近3年的数据。在每个月的开始我们将去除掉最早的那个月的数据。在这种情况下我们可以使用分区技术来帮助我们满足对measurement表的所有不同需求。
要在这种情况下使用声明式分区，可采用下面的步骤：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
-- 创建表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
-- 创建分区
CREATE TABLE measurement_y2022m04 PARTITION OF measurement
FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE measurement_y2022m05 PARTITION OF measurement
FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');
-- 为分区表键列创建索引
-- 这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引
CREATE INDEX ON measurement (logdate);
-- 测试添加
INSERT INTO measurement VALUES(1,now(),27,99999);
INSERT INTO measurement VALUES(2,'2022-05-13 20:26:42',23,11111);
INSERT INTO measurement VALUES(3,'2022-04-13 20:26:47',21,33333);
-- 没有对应日期的子表，会出现错误
INSERT INTO measurement VALUES(4,'2022-03-13 20:26:54',25,33412);
-- 测试更新
UPDATE measurement SET peaktemp=18 WHERE city_id=3
|
在上面的例子中，我们会每个月创建一个新分区，因此写一个脚本来自动生成所需的DDL会更好。
Hash 分区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- hash 分区
create table measurement(
id int not null,
logdata date not null,
peaktemp int,
unitsales int
) partition by hash (unitsales);
-- 创建子表
create table measurement_0 partition of measurement
for values with (modulus 4, remainder 0);
create table measurement_1 partition of measurement
for values with (modulus 4, remainder 1);
create table measurement_2 partition of measurement
for values with (modulus 4, remainder 2);
create table measurement_3 partition of measurement
for values with (modulus 4, remainder 3);
|
分区维护
移除旧数据最简单的选择是删除掉不再需要的分区：
1
|
DROP TABLE measurement_y2022m04;
|
另一种通常更好的选项是把分区从分区表中移除，但是保留它作为一个独立的表：
1
|
ALTER TABLE measurement DETACH PARTITION measurement_y2022m04;
|
限制
- 没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。
- 分区表上的惟一约束必须包括所有分区键列。存在此限制是因为PostgreSQL只能每个分区中分别强制实施唯一性。
分区剪枝
分区剪枝是一种提升声明式分区表性能的查询优化技术。例如
1
2
|
SET enable_partition_pruning = on; -− the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
|
如果没有分区剪枝，上面的查询将会扫描measurement表的每一个分区。如果启用了分区剪枝，规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的行而无需扫描。
分区剪枝仅由分区键隐式定义的约束所驱动，而不是由索引的存在驱动。因此，没有必要在键列上定义索引。是否需要为一个给定分区创建索引取决于预期的查询扫描该分区时会扫描大部分还是小部分。后一种情况下索引的帮助会比前者大。
旧的方法
从 v8.1 开始，Postgres 提供约束排除的优化器功能实现区分
使用表继承建立表的层次结构，在每个子表使用 check 约束描述其分区范围，并在父表使用触发器执行数据分配
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create table measurement(
id int not null,
logdata date not null,
peaktemp int,
unitsales int
);
create table measurement_y2020m01
(check (logdata >= '2020-02-1' AND logdata < '2020-02-01') )
INHERITS (measurement)
create table measurement_y2020m02
(check (logdata >= '2020-02-1' AND logdata < '2020-03-01') )
INHERITS (measurement)
|
在父表定义触发器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
create trigger insert_measurement_trigger
before insert on measurement
for each row execute procedure
measurement_insert_trigger();
create or replace function measurement_insert_trigger()
returns trigger as $$
begin
if (NEW.logdate >= DATE '2021-01-01' AND
NEW.logdata < DATE '2020-02-01') THEN
INSERT INTO measurement_y2020m01 VALUES(NEW.*)
ELSIF NEW.logdate >= DATE '2021-02-01' AND
NEW.logdata < DATE '2020-03-01') THEN
INSERT INTO measurement_y2020m02 VALUES(NEW.*)
ELSE RAISE EXCEPTION 'Date out of range. fix the ';
END IF;
RETURN NULL:
END;
$$
LANGUAGE PLPGSQL;
|
参考
PostgreSQL 官方文档