一、简介
在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。
PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。
分区的具体好处是：
?? ?某些类型的查询性能可以得到极大提升。
?? ?更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
?? ?批量删除可以用简单的删除某个分区来实现。
?? ?可以将很少用的数据移动到便宜的、转速慢的存储介质上。
在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。
小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。
PG目前（9.6）仅支持范围分区和列表分区，尚未支持散列分区。
二、环境
系统环境：CentOS Linux release 7.9
? ? ? ? ? ? ? ? ? ?PostgreSQL版本：PostgreSQL 9.6
三、实现分区
1.创建主表
CREATE TABLE ?"tbl_partition" (
? "id" int8 DEFAULT nextval('data_analysis_id_seq'::regclass) NOT NULL,
? "creator_name" varchar(225) COLLATE "pg_catalog"."default",
? "created_account_id" int8,
? "created_time" timestamp(6),
? "updated_account_id" int8,
? "updated_time" timestamp(6),
? "deleted" bool,
? "sort" int8,
? "version" int8,
? "request_time" timestamp(6)
);
2.创建分区表
create table "tbl_partition _202106"(check (created_time ?>= ?date '2021-06-01' and request_time < ?date '2021-07-01') )
? ? ? ? inherits (tbl_partition);
create table "tbl_partition _202107"(check (created_time ?>= ?date '2021-07-01' and request_time < ?date '2021-08-01') )
? ? ? ? inherits (tbl_partition);
create table "tbl_partition _202108"(check (created_time ?>= ?date '2021-08-01' and request_time < ?date '2021-09-01') )
? ? ? ? inherits (tbl_partition);
3. 创建触发器函数
--动态插入分区表数据
create or replace function tbl_partition_insert_trigger()
returns trigger as $$
begin
? ? EXECUTE format ('insert into %I values ($1.* )','tbl_partition' || to_char(NEW. created_time, 'yyyymm')) USING NEW;
? return null;
end;
$$
language plpgsql;
4. 创建触发器
create trigger tbl_partition_detail_trigger
before insert on tbl_partition
for each row execute procedure tbl_partition_insert_trigger ();
5.执行ddl语句
查询、插入、更新直接操作tbl_partition主表就行
?
|