PostgreSQL 表分区实践

Published: 2019-06-24

Tags: PostgreSQL

本文总阅读量次

随着时间增长，数据量会越来越多，当数据达到一定规模，索引会过大，导致查询/更新/删除的缓慢

如果将这个大表创建为一个分区表，关联有多个分区，插入数据的时候，数据根据一定规则存入不同的分区内，查询的时候既可以到指定分区查询，也可以直接查询分区表，如果使用 PostgreSQL 内置的几种分区规则，如根据时间划分的分区，带有时间条件的检索，则 PostgreSQL 在查询的时默认只关联相关的表，不在时间条件里的表不会参与搜索，这称为 分区剪枝，无疑，这样能够大幅提升查询速度

在 PostgreSQL 中使用分区表有两种方式，<内置声明分区> 与 <个体分区>，前者由系统提供，也就是上一段提及到的方式，它可以根据定义的规则自动将数据存入不同的分区中，并且拥有更好的性能优化，后者的实现基于继承功能，由用户创建触发器的方式来存储数据，拥有更好的灵活性

创建分区表并初始化分区后，向分区表存储数据会根据路由规则保存在不同的分区下，例如，插入到"t_logs"分区表的日志会被自动保存在"t_logs_y2019m02", "t_logs_y2019m03"... 这样的分区内

数据分区后，删除历史数据或者存档变得容易，以往导出数据，删除记录需要针对百万条记录操作，分区后，直接删除相应表或整表导出即可

虽然使用了分区能够很大程度的提升检索速度，但应避免无范围的全表检索，这样的话表分区与大表检索没什么区别，性能得不到改善，应该合理的设置数据检索条件确定检索数据的范围

内置声明分区

范围划分

适合于线性增长的列，如时间，不断增长的数值


-- 创建分区表
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- 创建分区
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

-- 创建索引
CREATE INDEX ON measurement (logdate);

-- 插入数据到分区
INSERT into measurement (city_id, logdate, peaktemp, unitsales) values (230000, '2006-02-01', '23', 92374);
INSERT into measurement (city_id, logdate, peaktemp, unitsales) values (230000, '2006-03-06', '36', 17281);

列表划分

针对特定字段值的表分区，比如不同系统，可以根据系统ID来分别存入不同的分区内

-- 加载uuid扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 创建表
CREATE TABLE message (
    uuid uuid DEFAULT uuid_generate_v4 (),
    nodeid TEXT NOT NULL,
    systemid TEXT NOT NULL,
    time TEXT NOT NULL,
    jdoc jsonb NOT NULL
) partition by list (systemid);

CREATE TABLE message_109001 PARTITION OF message FOR VALUES in ('109001');
CREATE TABLE message_109002 PARTITION OF message FOR VALUES in ('109002');
CREATE TABLE message_109003 PARTITION OF message FOR VALUES in ('109003');
CREATE TABLE message_109004 PARTITION OF message FOR VALUES in ('109004');
CREATE TABLE message_109005 PARTITION OF message FOR VALUES in ('109005');
CREATE TABLE message_109006 PARTITION OF message FOR VALUES in ('109006');

CREATE INDEX ON message (nodeid);
CREATE INDEX ON message (systemid);
CREATE INDEX ON message (time);


-- 插入数据
INSERT into message (nodeid, systemid, time, jdoc) values ('111111', '109004', '2019-06-13 19:05:11', '{"hello": "AA系统"}');
INSERT into message (nodeid, systemid, time, jdoc) values ('222222', '109005', '2019-06-13 19:05:12', '{"hello": "BB系统"}');
INSERT into message (nodeid, systemid, time, jdoc) values ('333333', '109006', '2019-06-13 19:05:13', '{"hello": "CC系统"}');

-- 验证查询
EXPLAIN SELECT * FROM message WHERE systemid = '109005';

PostgreSQL 系统内置的分区方案已经可以处理不少的分区场景，当内置的方案不能解决业务的问题时，可以使用集成的方式，用触发器来创建规则

个体分区

使用继承方式实现的个体分区可以有更多的定制化功能

-- SQL 示例

-- 使用继承实现表分区
CREATE TABLE measurement2 (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);
-- 创建子表
CREATE TABLE measurement2_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement2);

CREATE TABLE measurement2_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement2);

-- 创建索引
CREATE INDEX measurement2_y2006m02_logdate ON measurement2_y2006m02 (logdate);
CREATE INDEX measurement2_y2006m03_logdate ON measurement2_y2006m03 (logdate);

-- 定义触发器函数
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement2_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement2_y2006m03 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- 创建一个调用该触发器函数的触发器
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement2
    FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

-- 插入数据到分区
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-02-07', '23', 1111);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-02-07', '21', 0000);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2006-03-07', '36', 2222);
INSERT into measurement2 (city_id, logdate, peaktemp, unitsales) values (100000, '2018-11-11', '36', 4444);

select * from measurement2;

查看搜索详情

分区剪枝 功能可以针对 内置声明分区 进行性能优化

enable_partition_pruning 设置为是否启用内置声明分区的 分区剪枝 功能

关闭后，搜索会关联所有的分区，开启后，则会智能的剔除无关分区

可以观察SQL查询的解释来进行对比

SET enable_partition_pruning = on;   -- off
EXPLAIN SELECT count(*) FROM measurement WHERE logdate > DATE '2006-03-15';

生成分区脚本

文件名：generate_partition_sql_datetime.js

const moment = require("moment")

if (process.argv.length != 5) {
  console.log("Usage: node script.js table_name 2019(start) 5(years)")
  return;
}

TABLE_NAME = process.argv[2]
START_Y = process.argv[3]
TOTAL_Y = process.argv[4]
LOOP_COUNT = TOTAL_Y * 4

START_STRING = START_Y + "-01-01"
START_YMD = moment(START_STRING)

console.log("-- start --")

from = START_STRING
for (let i=0; i<LOOP_COUNT; i++) {
  let tmp_date = START_YMD.add(3, 'months')
  let tmp_date_string = tmp_date.format("YYYY-MM-DD")
  let to = tmp_date.format("YYYY-MM-DD")
  let from_Y = from.split("-")[0]
  let from_M = from.split("-")[1]
  console.log("CREATE TABLE " + TABLE_NAME + "_y" + from_Y + "m" + from_M + " PARTITION OF " + TABLE_NAME + " FOR VALUES FROM ('" + from + "') TO ('" + to + "');")
  from = to
}

console.log("-- end --")

使用及输出：

# 首先安装依赖
npm install moment

执行结果：

生成分区

生成从2019年开始，一个季度间隔，总时长五年的分区创建SQL（适用于内置声明分区）

参考：

5.10. 表分区