概述
數(shù)據(jù)庫分表，就是把一張表分成多張表，物理上雖然分開了，邏輯上彼此仍有聯(lián)系。
優(yōu)勢

1. 查詢速度大幅提升
2. 刪除數(shù)據(jù)速度更快
3. 可以將使用率低的數(shù)據(jù)通過表空間技術(shù)轉(zhuǎn)移到低成本的存儲介質(zhì)上

分表有兩種方式：水平分表，即按列分開；垂直分表，即按行分開

垂直分表創(chuàng)建過程

1. 創(chuàng)建父表，父表中不需要創(chuàng)建索引、主鍵等
2. 創(chuàng)建子表，子表必須繼承父表，不要新加字段 // 給每個子表創(chuàng)建索引
3. 定義一個規(guī)則（rule） 或者觸發(fā)器（trigger），把對父表的寫入重定向到對應(yīng)的分表

1.創(chuàng)建父表

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. 創(chuàng)建子表

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 );
----為子表創(chuàng)建索引
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.創(chuàng)建觸發(fā)器

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();