PostgreSql分表技术试验

在PostgreSql数据库中，表添加了btree索引在1.8亿数据量的基础上插入数据的速度已经非常慢，由于插入需要构建索引树而树的高度越来越高，导致插入性能急速下降。

此篇文章通过PostgreSql的表分区来做分表优化。

PostgreSQL版本：PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit

删除主表、子表和序列

DROP TABLE IF EXISTS test_table CASCADE ;
DROP SEQUENCE IF EXISTS test_table_seq;

创建主表与序列,主表中不需要添加索引，主表不存放实际数据

CREATE SEQUENCE test_table_seq MINVALUE 1 MAXVALUE 999999999999;
CREATE TABLE test_table (
  "id" int8 NOT NULL DEFAULT nextval('test_table_seq'::regclass),
  "data_id" numeric,
  "logtime" numeric,
  "data_value" numeric
);

logtime为毫秒时间戳，根据logtime对表进行表分区，将logtime转化为年月，按月分为不同的表

创建主表触发器函数

CREATE OR REPLACE FUNCTION test_table_trigger()
  RETURNS TRIGGER AS $$
Declare
	now_data VARCHAR;
	test_table_name VARCHAR;
	sql_text VARCHAR;
BEGIN
  now_data = substring(cast(to_timestamp(NEW.logtime/1000) as varchar),1,10);
	now_data = REPLACE(now_data, '-', '_');
	test_table_name = 'test_table_' || SUBSTRING(now_data,7); 
	sql_text = 'INSERT INTO ' || test_table_name || ' VALUES ( $1.* )';
	EXECUTE sql_text USING NEW;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

主表设置触发器

CREATE TRIGGER test_table_trigger
BEFORE INSERT ON test_table
FOR EACH ROW EXECUTE PROCEDURE test_table_trigger();

创建子表创建函数，按照年份批量生成该年的所有子表，子表加入check检查logtime的范围并继承主表，然后为子表添加索引

Create or replace function creat_test_table(table_year integer) RETURNS BOOLEAN as
$BODY$
Declare
	test_table_name VARCHAR;
	test_table_index_name VARCHAR;
	start_time_text VARCHAR;
	end_time numeric;
	start_time numeric;
	time1 VARCHAR;
	time2 VARCHAR;
	sql_text VARCHAR;
	row_result RECORD;
BEGIN
	start_time_text = table_year || '-01-01';
	start_time = EXTRACT(epoch FROM CAST(start_time_text AS TIMESTAMP)) * 1000 -28800000;
  time1 = table_year || '-02-01';
	time2 = (table_year+1) || '-01-01';
  sql_text = 'select EXTRACT(epoch FROM cast(generate_series as TIMESTAMP))*1000-28800000 as bb from generate_series(to_date('||quote_literal(time1)||','||quote_literal('yyyy-mm-dd')||'),to_date('||quote_literal(time2)||', '||quote_literal('yyyy-mm-dd')||'),'||quote_literal('1 month')||' ::interval)';
	FOR row_result IN EXECUTE sql_text LOOP
		end_time = row_result.bb;
		test_table_name = 'test_table_'||SUBSTRING(replace(to_char(to_timestamp(start_time/1000), 'yyyy-mm-dd'), '-', '_'),0,8);
		raise notice 'creat table %',test_table_name;
		IF (select count(*) from pg_class where relname = test_table_name) !=0 THEN
			CONTINUE;
		end if;	
		test_table_index_name = test_table_name || '_idx';
		sql_text = 'CREATE Table '||test_table_name||' (CHECK(logtime>= ' || start_time || ' 
	  and logtime< ' ||end_time || ')) INHERITS(test_table);';
		EXECUTE sql_text;
		sql_text = 'CREATE UNIQUE INDEX '||test_table_index_name||' ON ' ||test_table_name||' USING btree (
		"data_id" ASC ,
		"logtime" ASC
		); ';
		EXECUTE sql_text;
		start_time = end_time;
	END LOOP;
	RETURN TRUE;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

打开约束排除、维护分区

set constraint_exclusion = on;
set enable_partition_pruning = on;

运行子表创建函数，创建2021年的所有子表

SELECT creat_test_table(2021);

执行完成后出现13张表与两个函数

执行插入语句进行测试 1630857600000为2021-09-06 00:00:00

INSERT INTO test_table(data_id, logtime, data_value) VALUES (1, 1630857600000, 1);

正常情况下该记录会被插入到test_table_2021_09表中

验证test_table_2021_09表

SELECT * FROM test_table_2021_09;

说明表分区的插入已经没有问题了

由于我们设置了触发器那么在查询的时候where子句中包含logtime条件时，应该被优化器所优化

下面开始验证查询语句，编写where子句中包含logtime条件的查询语句，查看其查询计划

EXPLAIN SELECT * FROM test_table where logtime = 1630857600000;

得知该查询已经被优化，他会匹配去从哪个子表中检索数据，而不是扫描所有子表

对比一下扫描所有子表的查询计划,该查询语句没有包含logtime条件所以就不能被优化器优化

EXPLAIN SELECT * FROM test_table;

pg数据库表分区属于物理分表，逻辑上不分表，使用中只需要对主表操作即可管理所有的数据

注意事项：

1. 条件语句中包含logtime时，不要写成表达式例如 logtime/1000>1630857600000,这种可能不会被优化器所优化，查询时就会扫描所有子表，当表的数量增多时就会严重影响查询效率。
2. 创建子表的check中也不要写复杂的表达式，最好是最简单的 （字段名 条件 值） 这种格式，这其中包含了计算，或者其他表达式也会让优化器优化失效，从而在查询时扫描所有子表，影响查询效率。
3. 主表中不用添加索引，因为主表中不存放数据。
4. 主表中的check都会被子表所继承。
5. 可以通过在触发器中加入检查插入子表的表名是否存在的逻辑，表名不存在即可调用创建函数创建子表。或者编写存储过程，在固定时间统一生成子表。