记录一下PG库分表实践

先上文档 http://www.postgres.cn/docs/10/ddl-partitioning.html

简单贴一下PG分区表的概念特点

pg本身的分区表功能，支持基本的表划分，划分值将逻辑上的一个大表分成一些小的物理上的片，优势如下：

• 在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
• 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
• 如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。 执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE 删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
• 很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。

PG支持两种分区方式：

1. 声明式

2. 继承式

---

本次记录一下继承方式的分区方法。

继承的特点：

• 分区强制执行一条规则，即所有分区必须与父表具有完全相同的一组列， 但表继承允许子表具有不在父表中的额外列。
• 表继承允许多重继承。
• 声明式分区仅支持列表和范围分区，而表继承允许按照用户选择的方式分割数据。 （但是，请注意，如果约束排除无法有效地修剪分区，查询性能将会很差。）
• 有些操作在使用声明式分区时需要比使用表继承时更强的锁定。例如， 向分区表添加或删除分区需要在父表上使用ACCESS EXCLUSIVE锁， 而在普通继承的情况下SHARE UPDATE EXCLUSIVE锁就足够了。

实践开始

创建主表

所有的分区都将继承主表。 这个表将不会包含任何数据。不要在这个表上定义任何检查约束， 除非准备将它们应用到所有分区。同样也不需要定义任何索引或者唯一约束。

CREATE TABLE student (
"id" VARCHAR(10) not null,
"name" VARCHAR(20) not null,
"age" int4,
"class" VARCHAR(10)
)

创建分区表

CREATE TABLE student_1 (CHECK ("class" = '1')) INHERITS (student);
CREATE TABLE student_2 (CHECK ("class" = '2')) INHERITS (student);

注：确保每个分区表的CHECK约束没有重叠，比如class in ('1','2')和class in ('2','3')

创建索引

CREATE INDEX index_student_1_class ON student_1 ("class");
CREATE INDEX index_student_2_class ON student_2 ("class");

创建触发器

为主表附加一个合适的触发器，能够在insert into student…时将数据重定向到对应的分区表。

先创建一个函数，每一个IF必须匹配对应分区表的CHECK约束

CREATE OR REPLACE FUNCTION student_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
		IF (NEW."class" = '1') THEN
			INSERT INTO student_1 VALUES (NEW.*);
		ELSEIF (NEW."class" = '2') THEN
			INSERT INTO student_2 VALUES (NEW.*);
		ELSE
			RAISE EXCEPTION 'class out of range';
		END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

然后创建一个调用该函数的触发器

CREATE TRIGGER insert_student_trigger
    BEFORE INSERT ON student
    FOR EACH ROW EXECUTE PROCEDURE student_insert_trigger();

维护

ALTER TABLE student ADD "remark" VARCHAR(255);

例如增加字段，只需要修改主表即可。

至此继承方式的分区表创建基本就OK了，可以自行验证一下

注：主表中是没有数据的SELECT * from only student;，如果insert数据的class字段的值不是1或2，则会抛出触发器函数中的异常class out of range