最近项目中用到PG的分区表,记录一下学习过程
项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,postgresql提供了分区表的功能。
分区实际上是把逻辑上的一个大表分割成物理上的几个小块,提供了很多好处:
1.查询性能大幅提升
2.删除历史数据更快
3.可将不常用的历史数据使用表空间技术转移到低成本的存储介质上
什么时候改使用分区表呢?
官方给出的指导意见是:当表的大小超过了数据库服务器的物理内存大小,则应当使用分区表。
创建分区表的步骤:
1.创建父表
2.创建n个子表,每个子表都继承于父表
3.定义一个规则(rule)或触发器(Trigger),把对主表的数据插入重定向到合适的分区表
1.创建父表
CREATE TABLE users ( uid int not null primary key, name varchar(20));
2.创建n个子表,必须继承父表,加上索引和约束
CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users);
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);
## 在分区表上建立索引,其实这步可以省略的哦
CREATE INDEX users_0_uidindex on users_0(uid);
CREATE INDEX users_1_uidindex on users_1(uid);
3.把数据插入到合适的分区表
CREATE RULE users_insert_0 AS
ON INSERT TO users WHERE
(uid >= 0 and uid < 100)
DO INSTEAD
INSERT INTO users_0 VALUES (NEW.uid,NEW.name);
CREATE RULE users_insert_1 AS
ON INSERT TO users WHERE
(uid >= 100)
DO INSTEAD
INSERT INTO users_1 VALUES (NEW.uid,NEW.name);