PostgreSQL分区表原理、案例的灵活应用

PostgreSQL分区表的灵活应用

通常情况下，扫描一个大表会很慢,需要扫描整张表格，如果能够把大表分拆成小表，查询数据的时猴，只扫描数据所属的小表，就能大大降低扫描时间，提高查询速度。

1、简介

PostgreSQL10之前的版本不支持内置分区表，若要实现分区功能，需通过继承的方式实现。

PostgreSQL 10.x 之前的版本提供了一种“手动”方式使用分区表的方式，需要使用继承 + 触发器的来实现分区表，步骤较为繁琐，需要定义附表、子表、子表的约束、创建子表索引，创建分区删除、修改，触发器等。

PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。内置分区简化了操作，将部分操作内置，最终简单三步就能够创建分区表。但是只支持范围分区（RANGE）和列表分区（LIST），11.x 版本添加了对 HASH 分区。

如果要充分使用分区表的查询优势，必须使用分区时的字段作为过滤条件。
除了在查询上的优势，分区表的使用也可提高删除数据的性能，因为删除一个分区要比删除分区上的所有数据要快的多。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

概念：分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。

2、列表分区

--创建主表
CREATE TABLE info_list (
    id bigint NOT NULL,
    protocol varchar(16),
    ip varchar(50),
    create_time timestamp
) partition by list(create_time);

--创建分区表
create table info_list20200801 partition of info_list for values in ('2020-08-01');
create table info_list20200802 partition of info_list for values in ('2020-08-02');
create table info_list20200803 partition of info_list for values in ('2020-08-03');
--分区表建索引
CREATE INDEX idx_info_list20200801 ON info_list20200801 (create_time);
CREATE INDEX idx_info_list20200802 ON info_list20200802 (create_time);
CREATE INDEX idx_info_list20200803 ON info_list20200803 (create_time);

3、范围分区

注意:如分表的范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b。

--1、创建主表(根据create_time进行范围分区)
CREATE TABLE info_range (
    id bigint NOT NULL,
    protocol varchar(16),
    ip varchar(50),
    create_time timestamp
) partition by range(create_time);
--2、创建分表（根据下面表范围，如果插入2020-08-04,则会报错;如范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b;）
create table info_range20200801 partition of info_range for values from ('2020-08-01') to ('2020-08-02');
create table info_range20200802 partition of info_range for values from ('2020-08-02') to ('2020-08-03');
create table info_range20200803 partition of info_range for values from ('2020-08-03') to ('2020-08-04');

--3、创建索引
CREATE INDEX idx_info_range20200801 ON info_range20200801 (create_time);
CREATE INDEX idx_info_range20200802 ON info_range20200802 (create_time);
CREATE INDEX idx_info_range20200803 ON info_range20200803 (create_time);

4、分区管理

--断开分区
alter table info_range detach partition info_range20200803;
--连接分区
alter table info_range attach partition info_range20200803 for values from ('2020-08-03') to ('2020-08-04');
--删除分区
drop table info_range20200803;

PG11以上

哈希分区
通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。

5、常用的分表方式，范围分区（包括分区表嵌套，添加、删除分区）

如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去（具体做法请参考5.5~5.8）

5.1、创建主表

CREATE TABLE sales_orders (
    order_id SERIAL,                       -- 订单ID，自动递增
    customer_id INT NOT NULL,              -- 客户ID，不允许为空
    order_date DATE NOT NULL,              -- 订单日期，不允许为空
    amount NUMERIC(10, 2) NOT NULL,        -- 订单金额，精确到小数点后两位，不允许为空
    status VARCHAR(20),                    -- 订单状态，例如 'completed' 或 'pending'
    CONSTRAINT sales_orders_pkey PRIMARY KEY (order_id, order_date) -- 设定主键约束，唯一标识每一条订单
)
PARTITION BY RANGE (order_date);          -- 根据订单日期进行范围分区

5.2、创建分区表

CREATE TABLE sales_orders_2021
    PARTITION OF sales_orders
    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'); -- 2021年的订单分区

CREATE TABLE sales_orders_2022
    PARTITION OF sales_orders
    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'); -- 2022年的订单分区

CREATE