PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可以提供若干好处:
多大数据该使用分区表?一般取决于具体的应用,不过也有个基本的简单原则,即表的大小超过了数据库服务器的物理内存大小则应该使用。
在使用继承实现的分区表时,一般会让父表为空,数据都存储在子表中。
建分区表的步骤如下:
1.创建父表,所有分区都从它继承。这个表中没有数据,不要在这个表上定义任何检查约束,除非你希望约束所有分区。同意,在其上定义任何索引或唯一约束也没有意义。
2.创建几个子表,每个表都是从主表上继承。通常,这些表不会增加任何字段。我们把子表称作分区,实际上他们就是普通的PostgreSQL表。
3.给分区表增加约束,定义每个分区的键值。
4.对于每个分区,在关键字字段上创建一个索引,也可创建其他你想创建的索引。严格来说,关键字字段索引并非必须的,但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的,那么应该总是给每个分区创建一个唯一约束或主键约束。
5.定义一个规则或者触发器,把对主表的数据插入重定向到合适的分区表。
6.确保constraint_exclusion里的配置参数postgresql.conf是打开的。打开后,如果查询中WHERE子句过滤条件与分区的约束条件匹配,那么这个查询会智能地只查询这个分区,而不会查询其他分区。
实现分区表的例子
假设有一张销售明细表,定义如下:create table sales_detail( product_id int not null, -- 产品编号 price numeric(12,2), -- 单价 amount int not null, -- 数量 sale_date date not null, -- 销售日期 buyer varchar(40), -- 买家名称 buyer_contact text -- 买家联系方式);先建主表,主表就是上面的sales_detail表。现在按销售日期进行分区,每个月一个分区,建各个分区的语句如下:create table sales_detail_y2021m01(CHECK (sale_date >= DATE '2021-01-01' AND sale_date < DATE '2021-02-01')) INHERITS(sales_detail);create table sales_detail_y2021m02(CHECK (sale_date >= DATE '2021-02-01' AND sale_date < DATE '2021-03-01')) INHERITS(sales_detail);create table sales_detail_y2021m03(CHECK (sale_date >= DATE '2021-03-01' AND sale_date < DATE '2021-04-01')) INHERITS(sales_detail);create table sales_detail_y2021m04(CHECK (sale_date >= DATE '2021-04-01' AND sale_date < DATE '2021-05-01')) INHERITS(sales_detail);每个分区实际上都是一张完整的表,只不过他们是从sales_detail表继承定义的。父表sales_detail中实际是不存数据的。以后若要删除旧数据,只需要删除最早月份的表即可。不知大家是否注意到了每个分区表中都加了一个约束,这表示只允许插入本月内的数据。一般情况下,还可以在分区键"sale_date"上建索引:CREATE INDEX sale_detail_y2021m01_sale_date ON sales_detail_y2021m01 (sale_date);CREATE INDEX sale_detail_y2021m02_sale_date ON sales_detail_y2021m02 (sale_date);CREATE INDEX sale_detail_y2021m03_sale_date ON sales_detail_y2021m03 (sale_date);CREATE INDEX sale_detail_y2021m04_sale_date ON sales_detail_y2021m04 (sale_date);当然如果有需要,还可以在其他字段上建索引。目前还有一个插入数据的问题没有解决,在往sale_detail表中插入数据时,怎么能自动且正确地把数据插入到正确的分区呢?可能有人已经想到了,使用触发器,那么,接下来就建一个触发器:CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()RETURNS TRIGGER AS $$BEGIN IF (NEW.sale_date >= DATE '2021-01-01' AND NEW.sale_date < DATE '2021-02-01') THEN INSERT INTO sales_detail_y2021m01 VALUES(NEW.*); ELSIF (NEW.sale_date >= DATE '2021-02-01' AND NEW.sale_date < DATE '2021-03-01') THEN INSERT INTO sales_detail_y2021m02 VALUES(NEW.*); ELSIF (NEW.sale_date >= DATE '2021-03-01' AND NEW.sale_date < DATE '2021-04-01') THEN INSERT INTO sales_detail_y2021m03 VALUES(NEW.*); ELSIF (NEW.sale_date >= DATE '2021-04-01' AND NEW.sale_date < DATE '2021-05-01') THEN INSERT INTO sales_detail_y2021m04 VALUES(NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the sale_detail_insert_trigger () function!'; END IF; RETURN NULL;END;$$LANGUAGE plpgsql;CREATE TRIGGER insert_sale_detail_trigger BEFORE INSERT ON sales_detail FOR EACH ROW EXECUTE PROCEDURE sale_detail_insert_trigger();至此分区表就建好了。试试插入数据insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');testdb=# select * from sales_detail; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+----------+--------------- 1 | 43.12 | 1 | 2021-01-02 | 塞尔达 | 海拉鲁 2 | 8.88 | 1 | 2021-03-07 | 林克 | 海拉鲁保安部 3 | 28.88 | 1 | 2021-04-15 | 达尔克尔 | 咚鼓族 testdb=# insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');ERROR: Date out of range. Fix the sale_detail_insert_trigger () function!CONTEXT: PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE不过需要注意的是,上面的分区表只包括2021年1月到4月的分区表,如果日期到达2021年5月1日,再往表sale_detail中插入数据,就会报错,所以在2021年5月之前,就应该把所有新的分区表加上去,同时还要改调触发器中的内容。在PostgreSQL中,删除表sales_detail_y2021m01并不会导致触发器函数失效。验证如下:drop table sales_detail_y2021m01;insert into sales_detail values(4,7.99,1,date '2021-04-08','米法','卓尔领地');可以看出,分区表是使用触发器来把插入的数据重新定位到相应的分区中,实际上可以使用PostgreSQL中的"规则”来实现上面的函数触发器功能。连接一个新库chinese,然后创建主表sales_detail,以及子表sales_detail_y2021m01 sales_detail_y2021m02 sales_detail_y2021m03等。CREATE RULE sales_detail_insert_y2021m01 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-01-01' AND sale_date < DATE '2021-02-01') DO INSTEAD INSERT INTO sales_detail_y2021m01 VALUES(NEW.*);CREATE RULE sales_detail_insert_y2021m02 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-02-01' AND sale_date < DATE '2021-03-01') DO INSTEAD INSERT INTO sales_detail_y2021m02 VALUES(NEW.*);CREATE RULE sales_detail_insert_y2021m03 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-03-01' AND sale_date < DATE '2021-04-01') DO INSTEAD INSERT INTO sales_detail_y2021m03 VALUES(NEW.*);CREATE RULE sales_detail_insert_y2021m04 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-04-01' AND sale_date < DATE '2021-05-01') DO INSTEAD INSERT INTO sales_detail_y2021m04 VALUES(NEW.*);但该规则有如下的缺点:1.相比于触发器,该规则有显著的开销,而且每次检查时都会有此开销。不过,批量插入时只会有一次开销,所以再批量插入的情况下,其相对于触发器更有优势。然而再更多的情况下,触发器的方法更好一些;2.如果想用COPY插入数据,由于COPY不会触发"规则",因此先得把要复制得数据直接COPY到分区表(而不是主表)。不过,COPY时回触发触发器得,所以用触发器得方法就可以正常使用;3.如果插入数据时在规则设置范围之外得,比如2021年5月1日之后得数据,那么将会插入到主表中。如果此时希望直接报错,而不是把数据插入到主表中,使用规则时无法实现的。insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');chinese=# select * from sales_detail; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+----------+--------------- 1 | 43.12 | 1 | 2021-01-02 | 塞尔达 | 海拉鲁 2 | 8.88 | 1 | 2021-03-07 | 林克 | 海拉鲁保安部 3 | 28.88 | 1 | 2021-04-15 | 达尔克尔 | 咚鼓族 insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');chinese=# select * from sales_detail; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+----------+--------------- 4 | 7.99 | 1 | 2021-05-02 | 米法 | 卓尔领地 1 | 43.12 | 1 | 2021-01-02 | 塞尔达 | 海拉鲁 2 | 8.88 | 1 | 2021-03-07 | 林克 | 海拉鲁保安部 3 | 28.88 | 1 | 2021-04-15 | 达尔克尔 | 咚鼓族 chinese=# select * from sales_detail_y2021m01; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+--------+--------------- 1 | 43.12 | 1 | 2021-01-02 | 塞尔达 | 海拉鲁(1 row)chinese=# select * from sales_detail_y2021m02; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+-----------+-------+---------------(0 rows)chinese=# select * from sales_detail_y2021m03; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+-------+--------------- 2 | 8.88 | 1 | 2021-03-07 | 林克 | 海拉鲁保安部(1 row)chinese=# select * from sales_detail_y2021m04; product_id | price | amount | sale_date | buyer | buyer_contact------------+-------+--------+------------+----------+--------------- 3 | 28.88 | 1 | 2021-04-15 | 达尔克尔 | 咚鼓族(1 row)查看主表和子表,可以看出5月份的数据,实际是插入到了主表里。
分区优化的技巧
打开约束排除(constraint_exclusion)是一种查询优化技巧,它改进了用上面的方法定义的表分区的性能。在PostgreSQL9.2.4中,参数"constraint_exclusion"默认就是"partition",如果采用默认值,在SQL查询中将WHERE语句的过滤条件与表上的CHECK条件进行对比,就可得知不需要扫描的分区,从而跳过相应的分区表,性能也就得到了提高。
testdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01'; QUERY PLAN--------------------------------------------------------------------------------------------------- Aggregate (cost=32.90..32.91 rows=1 width=8) -> Append (cost=0.00..32.19 rows=287 width=0) -> Seq Scan on sales_detail sales_detail_1 (cost=0.00..0.00 rows=1 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m03 sales_detail_2 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m04 sales_detail_3 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date)(8 rows)这里将WHERE条件中的"sale_date >= DATE '2021-03-01'"与各个分区子表上的CHECK条件进行对比,就知道了只需要扫描主表和sales_detail_y2021m03 sales_detail_y2021m04,而不需要扫描其他的分区子表。如果把参数"constraint_exclusion"设置成off,则会扫描每张分区子表,如下:testdb=# set constraint_exclusion='off';SETtestdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01'; QUERY PLAN--------------------------------------------------------------------------------------------------- Aggregate (cost=67.43..67.44 rows=1 width=8) -> Append (cost=0.00..65.97 rows=586 width=0) -> Seq Scan on sales_detail sales_detail_1 (cost=0.00..1.54 rows=14 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m01 sales_detail_2 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m02 sales_detail_3 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m03 sales_detail_4 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date) -> Seq Scan on sales_detail_y2021m04 sales_detail_5 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2021-03-01'::date)(12 rows)