PostgreSQL 分区指南 body { font-family: Arial, sans-serif; line-height: 1.6; margin: 20px; } h1, h2, h3 { color: #333; } code { background-color: #f4f4f4; padding: 2px 4px; border-radius: 4px; } pre { background-color: #f4f4f4; padding: 10px; border-radius: 4px; overflow-x: auto; }

PostgreSQL 分区指南

本文档展示了如何创建一个分区表，并将旧表中的数据迁移到新的分区表中。通过分区，可以提高查询性能和数据管理的灵活性。

创建旧表

CREATE TABLE old_sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id)
);

解释

• old_sales 表: 用于存储销售数据，包含以下字段：
  • id: 自动递增的唯一标识符。
  • sale_date: 销售日期，不能为空。
  • amount: 销售金额。

生成随机数据

DO $$
DECLARE
    i INT;
    random_date DATE;
    random_amount NUMERIC;
BEGIN
    FOR i IN 1..100000 LOOP
        random_date := DATE '2022-01-01' + (RANDOM() * 730)::INT;
        random_amount := ROUND((RANDOM() * 999 + 1)::NUMERIC, 2);
        INSERT INTO old_sales (sale_date, amount) VALUES (random_date, random_amount);
    END LOOP;
END $$;

解释

• 生成随机数据: 使用 PL/pgSQL 块生成 10 万条随机数据。
  • random_date: 生成的随机日期在 2022-01-01 到 2023-12-31 之间。
  • random_amount: 生成的随机金额在 1 到 1000 之间，保留两位小数。
  • 循环插入生成的随机数据到 old_sales 表中。

创建分区主表

CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

解释

• sales 表: 创建一个分区表 sales，按 sale_date 的范围进行分区。
  • 该表结构与 old_sales 类似，但增加了分区功能。

创建分区

2022 年分区

CREATE TABLE sales_2022_01 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');

CREATE TABLE sales_2022_02 PARTITION OF sales
    FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');

CREATE TABLE sales_2022_03 PARTITION OF sales
    FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');

CREATE TABLE sales_2022_04 PARTITION OF sales
    FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');

CREATE TABLE sales_2022_05 PARTITION OF sales
    FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');

CREATE TABLE sales_2022_06 PARTITION OF sales
    FOR VALUES FROM ('2022-06-01') TO ('2022-07-01');

CREATE TABLE sales_2022_07 PARTITION OF sales
    FOR VALUES FROM ('2022-07-01') TO ('2022-08-01');

CREATE TABLE sales_2022_08 PARTITION OF sales
    FOR VALUES FROM ('2022-08-01') TO ('2022-09-01');

CREATE TABLE sales_2022_09 PARTITION OF sales
    FOR VALUES FROM ('2022-09-01') TO ('2022-10-01');

CREATE TABLE sales_2022_10 PARTITION OF sales
    FOR VALUES FROM ('2022-10-01') TO ('2022-11-01');

CREATE TABLE sales_2022_11 PARTITION OF sales
    FOR VALUES FROM ('2022-11-01') TO ('2022-12-01');

CREATE TABLE sales_2022_12 PARTITION OF sales
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

解释

• 2022 年分区: 为 sales 表创建 2022 年每个月的分区。
  • 每个分区表存储一个月的数据，按 sale_date 范围划分。

2023 年及 2024 年 1 月分区

CREATE TABLE sales_2023_01 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE sales_2023_02 PARTITION OF sales
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

CREATE TABLE sales_2023_03 PARTITION OF sales
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

CREATE TABLE sales_2023_04 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

CREATE TABLE sales_2023_05 PARTITION OF sales
    FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');

CREATE TABLE sales_2023_06 PARTITION OF sales
    FOR VALUES FROM ('2023-06-01') TO ('2023-07-01');

CREATE TABLE sales_2023_07 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-08-01');

CREATE TABLE sales_2023_08 PARTITION OF sales
    FOR VALUES FROM ('2023-08-01') TO ('2023-09-01');

CREATE TABLE sales_2023_09 PARTITION OF sales
    FOR VALUES FROM ('2023-09-01') TO ('2023-10-01');

CREATE TABLE sales_2023_10 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');

CREATE TABLE sales_2023_11 PARTITION OF sales
    FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');

CREATE TABLE sales_2023_12 PARTITION OF sales
    FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

CREATE TABLE sales_2024_01 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

解释

• 2023 年及 2024 年 1 月分区: 为 sales 表创建 2023 年每个月及 2024 年 1 月的分区。
  • 同样按 sale_date 范围划分，每个分区表存储一个月的数据。

数据迁移

INSERT INTO sales (id, sale_date, amount)
SELECT id, sale_date, amount
FROM old_sales;

解释

• 数据迁移: 将 old_sales 表中的数据插入到新的分区表 sales 中。
  • 将旧表中的所有数据迁移到相应的分区中，以便利用分区表的性能优势。