PG10.12的表分区实践

---

背景

数据库的应用中，老大难的问题主要有：

1. 连接
2. 速度
3. 存储

一、常用的解决方法

• 重启
• SQL与索引
• 表与存储引擎
• 数据库与应用架构（redis/loadCache/读写分离/分库分表）
• 数据库与操作系统配置（PG缓存连接数配置）
• 硬件

从上到下，成本越来越高，收益越来越小

ps.《阿里巴巴开发手册》中 单表超过500W行或单表容量超过2G

二、分区

本文仅关注在PG10.12版本中分区的功能实现性能优化的实践。

1.概念

PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。主要优点有：

• 某些类型的查询性能可以得到极大提升
• 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问
• 批量删除可以用简单的删除某个分区来实现
• 可以将很少用的数据移动到便宜的、转速慢的存储介质上

PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

2.实操

PG的分区类型有种，分别是LIST和RANGE：

• 范围分区（Range Partitioning）

The table is partitioned into “ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects.

• 列表分区（List Partitioning）

The table is partitioned by explicitly listing which key values appear in each partition.

本人业务中使用的是列表分区，由于业务中端口表的数据量过大，所以考虑针对端口表进行分区，分区键是端口的类型(modelType)，因为目前端口的类型已知且相对固定。

2.1创建主表

建表语句：

CREATE TABLE table_name (  ...  )
[ PARTITION BY { RANGE | LIST }  (  { column_name |  ( expression )  }
 CREATE TABLE table_name
PARTITION OF parent_table [  (
)  ] FOR VALUES partition_bound_spec

示例：

CREATE TABLE public.openans_ncc_topo_default_ltp (
	id varchar(512) NOT NULL,
	from_ int4 NULL,
	from_id varchar(512) NULL,
	"name" varchar(256) NULL,
	layer int4 NULL,
	control_domain varchar(100) NULL,
	i18n_label varchar(2560) NULL,
	manage_domain varchar(100) NULL,
	"_key" varchar(256) NULL,
	model_type int4 NULL,
	moc varchar(100) NULL,
	display_name varchar(2560) NULL,
	topo_id varchar(256) NULL,
	node_id varchar(256) NULL,
	cli_name varchar(256) NULL,
	port_type int4 NULL,
	if_index varchar(512) NULL,
	board_id varchar(1024) NULL,
	delete_time_stamp int8 NULL,
	md5 varchar(256) NULL
) partition by LIST(model_type);

2.2创建分区表

创建子表，针对类型2和3建分区表，语法：
Range

CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
FROM{ ( 表达式 [, ...] ) | MINVALUE } [, ...]
TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...] [ TABLESPACE 表空间名 ];

参数说明:

• FROM … TO 表示分区的起始值和结束值（UNBOUNDED）
• MINVALUE / MAXVALUE 表示无限小值和无限大值
• 默认FROM后面的值是包括值分区的约束内，TO后面的值不包括

LIST

CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
IN ( 表达式 [, ...] ) [ TABLESPACE 表空间名 ];

示例：

CREATE TABLE ltp_l2 PARTITION OF openans_ncc_topo_default_ltp FOR VALUES in (2);
CREATE TABLE ltp_l3 PARTITION OF openans_ncc_topo_default_ltp FOR VALUES in (3);

2.3创建索引约束主键

创建分区表上的索引（不在主表上创建）

CREATE INDEX ltp_l2_model_type_idx ON public.ltp_l2 (model_type) ;
ALTER TABLE public.ltp_l2 ADD CONSTRAINT ltp_l2_pk PRIMARY KEY (id) ;

ps.每张分区表都需要单独建立.

效果如图：

三、测试

执行数据插入

INSERT INTO public.openans_ncc_topo_default_ltp (id,  model_type, display_name) VALUES('id2', 2, 'ltp-2');
INSERT INTO public.openans_ncc_topo_default_ltp(id,  model_type, display_name) VALUES('id3', 3, 'ltp-3');

执行结果：

RANGE类型的分区，可以对字段以区间进行分区，参考
https://postgres.fun/20170521123452.html

四、性能

1.数据插入

数据导入时性能相差大约是一半（与不分区相比），所以大数据量导入时最好提前分区直接导进对应分区表中

INSERT INTO ltp_l3(id,  model_type, display_name) VALUES('id4', 3, 'ltp-31');

ps.注意插入时要受要分区约束限制，如ltp_l3只能存储model_type为3的数据

2.查询

对外提供查询时，尽量提供按指定分片键过滤接口，最大限度利用分区表的优点，自然分片键的选择则十分重要，可以根据具体业务特点选定。