当前位置：首页 > article >正文

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

article 2024/9/20 13:52:56

PostgreSQL分区表的灵活应用

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

1、简介

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

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

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

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

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

文章目录

PostgreSQL分区表的灵活应用
- 1、简介
- 2、列表分区
- 3、范围分区
- 4、分区管理
- 5、常用的分表方式，范围分区（包括分区表嵌套，添加、删除分区）
- - 5.1、创建主表
  - 5.2、创建分区表
  - 5.3、创建生成数据的函数
  - 5.4、插入数据到表格
  - 5.5、断开分区
  - 5.6、再创建与原来那个分区表一样的表结构,添加两个分区
  - 5.7、把原先那个总表的2021分区表数据导入现在分区表
  - 5.8、把这个分区表加入到到之前的分区表分区中
  - 5.9、查看当前的分区表信息

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 TABLE sales_orders_2023_q1_q2
    PARTITION OF sales_orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-06-30'); -- 2023年第一季度和第二季度的订单分区

CREATE TABLE sales_orders_2023_q3_q4
    PARTITION OF sales_orders
    FOR VALUES FROM ('2023-07-01') TO ('2023-12-31'); -- 2023年第三季度和第四季度的订单分区

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

5.3、创建生成数据的函数

CREATE OR REPLACE FUNCTION insert_random_data(
    start_date DATE,   -- 起始日期
    end_date DATE,     -- 结束日期
    num_rows INT        -- 生成的记录数量
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO sales_orders (
        customer_id,   -- 客户ID
        order_date,    -- 订单日期
        amount,        -- 订单金额
        status         -- 订单状态
    )
    SELECT
        (random() * 1000)::int AS customer_id,           -- 随机生成客户ID
        generate_series(start_date, end_date, '1 day'::interval)::date AS order_date, -- 从起始日期到结束日期生成日期系列
        (random() * 500 + 50)::numeric(10, 2) AS amount, -- 随机生成订单金额，范围在50到550之间
        CASE WHEN random() > 0.5 THEN 'completed' ELSE 'pending' END AS status -- 随机生成订单状态
    FROM generate_series(start_date, end_date, '1 day'::interval) -- 生成日期系列
    LIMIT num_rows;   -- 限制插入的记录数
END;
$$;

5.4、插入数据到表格

SELECT insert_random_data('2021-01-01', '2021-12-30', 50000);
SELECT insert_random_data('2022-01-01', '2022-12-30', 50000);

test=# select count(*) from sales_orders;
 count  
--------
 100000
(1 row)

test=#

5.5、断开分区

alter table sales_orders detach partition sales_orders_2021;

test=# select count(*) from sales_orders;
 count 
-------
 50000
(1 row)

5.6、再创建与原来那个分区表一样的表结构,添加两个分区

这种情况一般应用于，如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去

-- 创建2021年订单分区表
CREATE TABLE sales_orders_2021_p (
    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_2021_p_pkey PRIMARY KEY (order_id, order_date)  -- 主键约束
)
PARTITION BY RANGE (order_date);       -- 根据订单日期进行范围分区


--新建两个分区
create table sales_orders_2021_p_1 partition of sales_orders_2021_p for VALUES FROM ('2021-01-01') TO ('2021-5-31');
create table sales_orders_2021_p_2 partition of sales_orders_2021_p for VALUES FROM ('2021-5-31') TO ('2021-12-31');

5.7、把原先那个总表的2021分区表数据导入现在分区表

insert into sales_orders_2021_p select * from sales_orders_2021;

5.8、把这个分区表加入到到之前的分区表分区中

test=# select count(*) from sales_orders;
 count 
-------
 50000
(1 row)

alter table sales_orders ATTACH PARTITION sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

test=# select count(*) from sales_orders;
 count  
--------
 100000
(1 row)

5.9、查看当前的分区表信息

--以下就是带有二级分区的分区表
test=# \d+ sales_orders;
                                                              Partitioned table "public.sales_orders"
   Column    |         Type          | Collation | Nullable |                    Default                     | Storage  | Compression | Stats target | Description 
-------------+-----------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
 order_id    | integer               |           | not null | nextval('sales_orders_order_id_seq'::regclass) | plain    |             |              | 
 customer_id | integer               |           | not null |                                                | plain    |             |              | 
 order_date  | date                  |           | not null |                                                | plain    |             |              | 
 amount      | numeric(10,2)         |           | not null |                                                | main     |             |              | 
 status      | character varying(20) |           |          |                                                | extended |             |              | 
Partition key: RANGE (order_date)
Indexes:
    "sales_orders_pkey" PRIMARY KEY, btree (order_id, order_date)
Partitions: sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'), PARTITIONED,
            sales_orders_2022 FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'),
            sales_orders_2023_q1_q2 FOR VALUES FROM ('2023-01-01') TO ('2023-06-30'),
            sales_orders_2023_q3_q4 FOR VALUES FROM ('2023-07-01') TO ('2023-12-31'),
            sales_orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2024-12-31')
test=# \d+;
                                                       List of relations
 Schema |               Name               |       Type        | Owner | Persistence | Access method |    Size    | Description 
--------+----------------------------------+-------------------+-------+-------------+---------------+------------+-------------
 public | sales_orders                     | partitioned table | fbase | permanent   |               | 0 bytes    | 
 public | sales_orders_2021                | table             | fbase | permanent   | heap          | 3048 kB    | 
 public | sales_orders_2021_p              | partitioned table | fbase | permanent   |               | 0 bytes    | 
 public | sales_orders_2021_p_1            | table             | fbase | permanent   | heap          | 1248 kB    | 
 public | sales_orders_2021_p_2            | table             | fbase | permanent   | heap          | 1768 kB    | 
 public | sales_orders_2021_p_order_id_seq | sequence          | fbase | permanent   |               | 8192 bytes | 
 public | sales_orders_2022                | table             | fbase | permanent   | heap          | 2976 kB    | 
 public | sales_orders_2023_q1_q2          | table             | fbase | permanent   | heap          | 16 kB      | 
 public | sales_orders_2023_q3_q4          | table             | fbase | permanent   | heap          | 16 kB      | 
 public | sales_orders_2024                | table             | fbase | permanent   | heap          | 16 kB      | 
 public | sales_orders_order_id_seq        | sequence          | fbase | permanent   |               | 8192 bytes | 
(11 rows)