postgresql优化之分表

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

1. 查询速度大幅提升
2. 删除数据速度更快
3. 可以将使用率低的数据通过表空间技术转移到低成本的存储介质上

分表有两种方式：水平分表，即按列分开；垂直分表，即按行分开

垂直分表创建过程

1. 创建父表，父表中不需要创建索引、主键等
2. 创建子表，子表必须继承父表，不要新加字段 // 给每个子表创建索引
3. 定义一个规则（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();