当数据表数据量较大时,pg数据库的存取性能会有较大的下降,因此官方建议当一个数据表的大小接近物理内存大小的时候,就应该采取分表操作。
pg数据库官方支持两种分表方式,都是讲逻辑上的一个数据表,在磁盘上分区存储,一种是pg数据库自己维护的声明式分区,另一种自行维护的继承式分区。
声明式分区不需要自行维护分区界限,不需要自己编写插入逻辑,支持范围分区、列表分区、哈希分区,支持子分区,支持将现有数据表挂接为一个分区表的分区,
-
创建分区表
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate) ;
-
创建分区
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); ... CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'); CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01') TABLESPACE fasttablespace; CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01') WITH (parallel_workers = 4) TABLESPACE fasttablespace;
-
打开分区优化开关
enable_partition_pruning配置参数在postgresql.conf中没有被禁用
-
分区表维护:新增分区、删除分区…
继承式分区需要自行维护分区界限,需要自己通过触发器实现插入逻辑
- 创建主表
- 创建继承表
- 创建插入触发器 .
详见官方文档:
http://www.postgres.cn/docs/11/ddl-partitioning.html