2024-06-27 PostgreSQL 分区实现

PostgreSQL 分区实现

PostgreSQL 数据分区
背景： 随着业务系统的运行，有些业务表肯定会越来越大，必然会导致某些查询变慢。并且在等保业务中，也有对历史数据归档的要求，那么我们是否可以考虑从分区的角度实现呢。

PostgreSQL 自动创建分区
说明：创建基础表。
CREATE TABLE public.patient_registration (
  id bigserial,
  patient_card varchar(20),
  patient_name varchar(20),
  patient_phone varchar(20),
  patient_card_number varchar(20),
  department_code varchar(10),
  department_name varchar(20),
  reg_cate varchar(10),
  reg_cate_name varchar(10),
  created_by int8,
  created_time timestamp(6),
  updated_by int8,
  updated_time timestamp(6),
  delete_flag bool NOT NULL DEFAULT false,
  cversion int4 NOT NULL DEFAULT 0,
  PRIMARY KEY (id) );

ALTER TABLE public.patient_registration OWNER TO postgres;

COMMENT ON COLUMN public.patient_registration.patient_card IS '患者卡号';
COMMENT ON COLUMN public.patient_registration.patient_name IS '患者姓名';
COMMENT ON COLUMN public.patient_registration.patient_phone IS '患者手机号';
COMMENT ON COLUMN public.patient_registration.patient_card_number IS '患者身份证号';
COMMENT ON COLUMN public.patient_registration.department_code IS '科室编码';
COMMENT ON COLUMN public.patient_registration.department_name IS '科室名称';
COMMENT ON COLUMN public.patient_registration.reg_cate IS '挂号类别';
COMMENT ON COLUMN public.patient_registration.reg_cate_name IS '挂号类别名称';
COMMENT ON COLUMN public.patient_registration.created_by IS '创建人';
COMMENT ON COLUMN public.patient_registration.created_time IS '创建时间';
COMMENT ON COLUMN public.patient_registration.updated_by IS '更新人';
COMMENT ON COLUMN public.patient_registration.updated_time IS '更新时间';
COMMENT ON COLUMN public.patient_registration.delete_flag IS '删除标记';
COMMENT ON COLUMN public.patient_registration.cversion IS '版本信息';

说明：创建触发器函数实现自动创建分区表。
CREATE OR REPLACE FUNCTION public.patient_registration_partition()
  RETURNS pg_catalog.trigger AS $BODY$
    DECLARE time_column_name text;
    DECLARE curr_date varchar(8);
    DECLARE is_exists boolean;
    DECLARE start_time text;
    DECLARE end_time text;
    DECLARE string_sql text;
BEGIN
    -- 根据传入的参数决定时间字段名称
    time_column_name := TG_ARGV[0];
    -- 判断对应的分区表是否已经存在
    EXECUTE 'SELECT $1.'||time_column_name INTO string_sql USING NEW;
    curr_date := to_char( string_sql::timestamp, 'YYYYMMDD');
    SELECT COUNT(*) INTO is_exists FROM pg_class WHERE relname = (TG_RELNAME||'_'||curr_date);
    IF ( is_exists = FALSE ) THEN
        start_time := curr_date||' 00:00:00.000';
        end_time := to_char(start_time::timestamp + interval '1 day','YYYY-MM-DD HH24:MI:SS.MS');
        string_sql := 'CREATE TABLE public.'||TG_RELNAME||'_'||curr_date||' ( check('||time_column_name||'>='''||start_time||''' and '||time_column_name||'<'''||end_time||''')) INHERITS (public.'||TG_RELNAME||');';
        EXECUTE string_sql; 
      -- 创建索引  
        string_sql := 'CREATE INDEX '||TG_RELNAME||'_'||curr_date||'_INDEX_'||time_column_name||' ON public.' ||TG_RELNAME||'_'||curr_date||' ('||time_column_name||');' ;  
        EXECUTE string_sql;      
    END IF;  
    -- 插入数据到子分区!  
    string_sql := 'INSERT INTO public.'||TG_RELNAME||'_'||curr_date||' SELECT $1.*' ; 
    EXECUTE string_sql USING NEW;  
    RETURN NULL;   
END  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION public.patient_registration_partition() OWNER TO postgres;

说明：创建触发器，在数据插入之前调用触发器函数。
CREATE TRIGGER patient_registration_trigger BEFORE INSERT ON public.patient_registration
FOR EACH ROW
EXECUTE PROCEDURE public.patient_registration_partition('created_time');

说明：创建测试用例。
INSERT INTO public.patient_registration(patient_card,patient_name,patient_phone,patient_card_number,department_code,department_name,reg_cate,reg_cate_name,created_by,created_time,updated_by,updated_time) VALUES('C10001','张三','15212121212','123456789123456789','CR10002','传染科','JZ','急诊',10001,'2023-12-19 10:00:00',10002,'2023-12-19 10:50:50');

INSERT INTO public.patient_registration(patient_card,patient_name,patient_phone,patient_card_number,department_code,department_name,reg_cate,reg_cate_name,created_by,created_time,updated_by,updated_time) VALUES('C10002','李四','15213131313','987654321987654321','CR10003','信息科','MZ','门诊',10003,'2023-12-19 11:00:00',10004,'2023-12-19 12:50:50');

INSERT INTO public.patient_registration(patient_card,patient_name,patient_phone,patient_card_number,department_code,department_name,reg_cate,reg_cate_name,created_by,created_time,updated_by,updated_time) VALUES('C10003','王五','15214141414','456789123456789123','CR10004','呼吸科','ZY','住院',10005,'2023-12-20 11:00:00',10004,'2023-12-20 12:50:50');

SELECT * FROM public.patient_registration;
说明：从主表查询数据，可以查询到三条记录。查看物理实体表可以看到三个实体表。

PostgreSQL 手动创建分区
说明：分区除了上面的这种方式外还可以用如下的方式。
CREATE TABLE public.partition_table (
    id SERIAL,
    create_time TIMESTAMP,
    ...
) PARTITION BY RANGE (create_time);

CREATE TABLE public.partition_table_202201 PARTITION OF my_table
    FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');

CREATE TABLE public.partition_table_202202 PARTITION OF my_table
    FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
    
INSERT INTO public.partition_table (create_time, ...) VALUES ('2022-01-05', ...);
INSERT INTO public.partition_table (create_time, ...) VALUES ('2022-02-05', ...);

SELECT * FROM public.partition_table;

备注：如果插入数据的时候分区不存在则插入失败。
INSERT INTO public.partition_table (create_time, cno) VALUES ('2022-03-05', 1003);
ERROR:  no partition of relation "partition_table" found for row
DETAIL:  Partition key of the failing row contains (create_time) = (2022-03-05 00:00:00).

PostgreSQL 分区维护
随着分区的不断增大和时间的推移，超过三年的数据分区我们一般都需要进行归档处理了，也就是说这些分区只能进行离线查询和操作，在线业务已经不再访问这些数据，如果有特殊需求，我们也能很快的将其绑定到在线中，这也是 PostgreSQL 在分区实现上最大的一个优势。

PostgreSQL 分区解绑
ALTER TABLE patient_registration DETACH PARTITION patient_registration_20231219;

PostgreSQL 分区绑定
ALTER TABLE patient_registration ATTACH PARTITION patient_registration_20231219;

PostgreSQL 分区的知识点
PostgreSQL 的分区在日志表的实现上非常有帮助，他可以避免在进行大量的数据删除上带来的数据碎片和批量的数据删除带来的 VACUUM 开销。
分区表的 CHECK 约束和 NOT NULL 约束总是会被其所有的分区所继承。
分区表并不直接拥有任何数据。
在分区表的键列上创建一个索引，还有其他需要的索引。这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引。