PostgreSQL 优化之分表

概述

数据库分表，就是把一张表分成多张表，物理上虽然分开了，逻辑上彼此仍有联系。

优势

定义一个规则（rule） 或者触发器（trigger），把对父表的写入重定向到对应的分表

1.创建父表

CREATE TABLE tyhcjzpt.gg_jzpswj_1
(
    bsm character varying(50) COLLATE pg_catalog."default" NOT NULL,
    tb_bsm character varying(50) COLLATE pg_catalog."default",
    file_name character varying(100) COLLATE pg_catalog."default",
    file_path character varying(500) COLLATE pg_catalog."default",
    scsj timestamp(6) without time zone,
    scry character varying(50) COLLATE pg_catalog."default",
    psfs character varying(50) COLLATE pg_catalog."default",
    wjlx character varying(50) COLLATE pg_catalog."default",
    file_size numeric(18,6),
    status character varying(20) COLLATE pg_catalog."default",
    wjly character varying(255) COLLATE pg_catalog."default",
    ztlb character varying(255) COLLATE pg_catalog."default",
    wjfl character varying(255) COLLATE pg_catalog."default"
)

2. 创建子表

CREATE TABLE tyhcjzpt.gg_jzpswj_2020() inherits (gg_jzpswj_1);
CREATE TABLE tyhcjzpt.gg_jzpswj_2021() inherits (gg_jzpswj_1);
---scsj必填
ALTER TABLE gg_jzpswj_2020
ADD CONSTRAINT gg_jzpswj_2020_scsj_key
CHECK (scsj < '2021-01-01'::date );
--scsj必填
ALTER TABLE gg_jzpswj_2021
ADD CONSTRAINT gg_jzpswj_2021_scsj_key
CHECK (scsj >= '2021-01-01'::date );
----为子表创建索引
CREATE INDEX "idx_gg_jzpswj_2020_tb_bsm"
    ON tyhcjzpt.gg_jzpswj_2020 USING btree
    (tb_bsm COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2020_psfs"
    ON tyhcjzpt.gg_jzpswj_2020 USING btree
    (psfs COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2020_scsj"
    ON tyhcjzpt.gg_jzpswj_2020 USING btree
    (scsj) ;
CREATE INDEX "idx_gg_jzpswj_2020_wjlx"
    ON tyhcjzpt.gg_jzpswj_2020 USING btree
    (wjlx COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2020_ztlb"
    ON tyhcjzpt.gg_jzpswj_2020 USING btree
    (ztlb COLLATE pg_catalog."default") ;

CREATE INDEX "idx_gg_jzpswj_2021_tb_bsm"
    ON tyhcjzpt.gg_jzpswj_2021 USING btree
    (tb_bsm COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2021_psfs"
    ON tyhcjzpt.gg_jzpswj_2021 USING btree
    (psfs COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2021_scsj"
    ON tyhcjzpt.gg_jzpswj_2021 USING btree
    (scsj) ;
CREATE INDEX "idx_gg_jzpswj_2021_wjlx"
    ON tyhcjzpt.gg_jzpswj_2021 USING btree
    (wjlx COLLATE pg_catalog."default") ;
CREATE INDEX "idx_gg_jzpswj_2021_ztlb"
    ON tyhcjzpt.gg_jzpswj_2021 USING btree
    (ztlb COLLATE pg_catalog."default") ;

3.创建触发器

CREATE OR REPLACE FUNCTION gg_jzpswj_partition_function()
  RETURNS TRIGGER AS $$
BEGIN
  IF NEW.scsj < DATE '2021-01-01' 
  THEN
    INSERT INTO gg_jzpswj_2020 VALUES (NEW.*);
  ELSIF NEW.scsj >= DATE '2021-01-01' 
    THEN
      INSERT INTO gg_jzpswj_2021 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_gg_jzpswj_partition_trigger
BEFORE INSERT ON gg_jzpswj_1
FOR EACH ROW EXECUTE PROCEDURE gg_jzpswj_partition_function();