跳到主要内容

PostgreSQL 分区表

分区（Partitioning）是将大表分割成多个物理上独立但逻辑上统一的小表的技术。PostgreSQL 从 10 版本开始原生支持声明式分区，这是处理大型表的重要优化手段。

分区概述​

什么是分区？​

分区将一个大表按照特定规则拆分成多个较小的物理表（分区），但对用户而言仍然是一个逻辑上的整体表。当查询条件包含分区键时，PostgreSQL 可以只扫描相关的分区，而不是整个表，这就是分区裁剪（Partition Pruning）。

分区与继承的区别​

PostgreSQL 历史上曾使用表继承实现分区。声明式分区（PostgreSQL 10+）提供了更简洁的语法和更好的性能：

特性声明式分区表继承
语法简洁度简洁，使用 PARTITION BY复杂，需要手动管理
分区裁剪自动优化需要设置约束
性能更好一般
灵活性较低更高
推荐程度推荐仅特殊场景

声明式分区是大势所趋，新项目应该优先使用。

分区的优势​

查询性能提升：分区裁剪可以大幅减少扫描的数据量。比如一个按日期分区的订单表，查询某个月的数据时，只扫描对应的分区，而不是几年的全部数据。

维护效率提高：可以单独对某个分区进行维护操作，如重建索引、VACUUM，不影响其他分区的使用。删除旧数据时，直接删除分区比执行 DELETE 快得多，而且不会产生死元组。

数据加载优化：批量加载大量数据时，可以创建一个新的分区，加载数据后挂载到分区表，比直接 INSERT 到分区表更高效。

存储管理灵活：可以将不同的分区放在不同的表空间，从而存储在不同的物理设备上。热点数据可以放在 SSD，冷数据可以放在 HDD。

何时使用分区？​

分区并非银弹，需要根据实际场景判断：

适合分区的场景：

  • 单表数据量超过几百 GB 或几亿行
  • 有明确的分区键，且查询经常使用该键过滤
  • 需要定期批量删除或归档历史数据
  • 数据加载频繁，需要高效的批量导入方式

不适合分区的场景：

  • 表数据量较小（小于几十 GB）
  • 查询很少按分区键过滤
  • 分区键选择困难，数据分布不均匀
  • 需要频繁跨分区更新

分区类型​

PostgreSQL 支持三种分区类型：范围分区（RANGE）、列表分区（LIST）和哈希分区（HASH）。

范围分区（RANGE）​

范围分区按连续值的范围划分数据，最常用于日期、时间戳等字段。分区边界是左闭右开区间，即 FROM 值包含在内，TO 值不包含在内。

-- 创建分区表
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INTEGER,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2),
    status VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- 创建分区：每个季度一个分区
-- 注意：边界是左闭右开区间 [FROM, TO)
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE orders_2024_q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- 创建默认分区（捕获不属于任何分区的数据）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

关键点说明：

PARTITION BY RANGE (order_date) 指定按 order_date 列进行范围分区。分区键必须有 NOT NULL 约束。

分区边界 FROM ('2024-01-01') TO ('2024-04-01') 表示该分区包含 order_date >= '2024-01-01' AND order_date < '2024-04-01' 的所有行。

DEFAULT 分区用于捕获不属于任何已定义分区的行。如果没有默认分区，插入无法匹配任何分区的行会报错

数字范围分区：

-- 按ID范围分区
CREATE TABLE logs (
    id BIGSERIAL,
    user_id BIGINT,
    action VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (id);

CREATE TABLE logs_1 PARTITION OF logs
    FOR VALUES FROM (1) TO (1000001);

CREATE TABLE logs_2 PARTITION OF logs
    FOR VALUES FROM (1000001) TO (2000001);

列表分区（LIST）​

列表分区按离散值划分数据，适合地区、状态、类型等有限取值的字段。

-- 创建列表分区表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    region VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY LIST (region);

-- 创建分区：每个分区包含一组地区
CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE customers_south PARTITION OF customers
    FOR VALUES IN ('广东', '福建', '海南');

CREATE TABLE customers_east PARTITION OF customers
    FOR VALUES IN ('上海', '江苏', '浙江');

CREATE TABLE customers_west PARTITION OF customers
    FOR VALUES IN ('四川', '重庆', '云南');

-- 默认分区处理其他地区
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

NULL 值处理：

列表分区可以显式处理 NULL 值：

-- 创建包含 NULL 的分区
CREATE TABLE customers_unknown PARTITION OF customers
    FOR VALUES IN (NULL);

-- 或者让默认分区捕获 NULL

哈希分区（HASH）​

哈希分区按分区键的哈希值均匀分布数据，适合需要将数据均匀分散的场景。哈希分区没有明确的业务含义，主要用于分散写入压力。

-- 创建哈希分区表
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT,
    event_type VARCHAR(50),
    event_data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

-- 创建分区：MODULUS 指定分区总数，REMAINDER 指定余数
-- 通常创建 2 的幂次方个分区（如 4、8、16、32）
CREATE TABLE events_0 PARTITION OF events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE events_1 PARTITION OF events
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE events_2 PARTITION OF events
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE events_3 PARTITION OF events
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

哈希分区的工作原理：

哈希分区通过对分区键计算哈希值，然后对分区数取模来决定数据存放位置。计算公式为：hash(partition_key) % MODULUS = REMAINDER。

哈希分区的特点是数据分布均匀，但不支持范围查询优化。如果经常需要按范围查询用户 ID，应该选择范围分区而不是哈希分区。

增加哈希分区：

增加哈希分区需要重新分配数据，比较复杂：

-- 假设原来有 4 个分区，要增加到 8 个
-- 需要手动创建新分区并迁移数据

-- 1. 创建新分区
CREATE TABLE events_new PARTITION OF events
    FOR VALUES WITH (MODULUS 8, REMAINDER 4);

-- 2. 这时需要重新计算所有数据的哈希值
-- PostgreSQL 不会自动迁移数据，需要手动处理
-- 建议在设计初期就规划好分区数量

选择合适的分区类型​

分区类型适用场景优点缺点
RANGE时间序列、连续数值支持范围查询优化需要预定义边界
LIST地区、状态等离散值业务含义明确分区数量可能不均
HASH均匀分散写入数据分布均匀不支持范围查询优化

复合分区​

PostgreSQL 10+ 支持分区嵌套（子分区）：

-- 先按日期分区，再按地区子分区
CREATE TABLE sales (
    id BIGSERIAL,
    sale_date DATE NOT NULL,
    region VARCHAR(50),
    amount NUMERIC(12, 2)
) PARTITION BY RANGE (sale_date);

-- 创建父分区
CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (region);

-- 创建子分区
CREATE TABLE sales_2024_north PARTITION OF sales_2024
    FOR VALUES IN ('北京', '天津');

CREATE TABLE sales_2024_south PARTITION OF sales_2024
    FOR VALUES IN ('广东', '福建');

CREATE TABLE sales_2024_other PARTITION OF sales_2024 DEFAULT;

分区索引与约束​

分区索引​

PostgreSQL 的分区索引是"本地索引"（Local Index），每个分区有独立的索引。当在分区表上创建索引时，PostgreSQL 会自动在所有现有分区上创建相同的索引，并在新分区上自动创建。

创建分区索引：

-- 在分区表上创建索引（自动在所有分区创建）
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

-- 查看分区上的索引
\d orders_2024_q1
-- 会显示 idx_orders_customer 和 idx_orders_date 已在该分区创建

索引自动传播：

-- 创建新分区时，索引会自动创建
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- 新分区自动拥有 idx_orders_customer 和 idx_orders_date

仅在特定分区创建索引：

-- 可以只在某个分区上创建额外索引
CREATE INDEX idx_orders_2024_q1_status ON orders_2024_q1(status);
-- 这个索引只在 orders_2024_q1 上存在

主键与唯一约束​

分区表上的主键和唯一约束必须包含分区键。这是因为在分区表中，要保证唯一性，必须知道数据在哪个分区。

-- 错误：主键不包含分区键
ALTER TABLE orders ADD PRIMARY KEY (id);
-- ERROR:  unique constraint on partitioned table must include all partitioning columns

-- 正确：主键包含分区键
ALTER TABLE orders ADD PRIMARY KEY (id, order_date);

-- 正确：唯一约束包含分区键
ALTER TABLE orders ADD CONSTRAINT uk_orders_customer_date 
    UNIQUE (customer_id, order_date);

设计考虑：

如果业务上需要仅基于 id 的主键，可以考虑：

  1. 使用 UUID 并在应用层生成
  2. 使用复合主键，包含分区键
  3. 重新设计分区策略

外键约束​

分区表引用普通表（PostgreSQL 11+）：

-- 分区表可以引用普通表
ALTER TABLE orders ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 这会自动在所有分区上创建外键约束

普通表引用分区表（PostgreSQL 12+）：

-- PostgreSQL 12+ 允许普通表引用分区表
CREATE TABLE order_items (
    id SERIAL,
    order_id BIGINT,
    order_date DATE NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (id, order_date)
);

-- 引用分区表
ALTER TABLE order_items ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id, order_date) 
    REFERENCES orders(id, order_date);

外键约束的限制：

  • 外键引用必须包含分区表的主键列（即分区键必须包含在外键中）
  • 分区表之间的相互引用支持有限

CHECK 约束​

CHECK 约束可以添加到分区表上，自动应用到所有分区：

-- 添加 CHECK 约束
ALTER TABLE orders ADD CONSTRAINT chk_amount_positive 
    CHECK (total_amount >= 0);

-- 约束会自动应用到所有分区
-- 新插入的数据必须满足约束

分区管理​

PostgreSQL 提供了丰富的分区管理命令，包括添加、删除、分离、挂载分区等操作。

添加分区​

创建新分区：

-- 添加新的范围分区
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- 添加新的列表分区
CREATE TABLE customers_central PARTITION OF customers
    FOR VALUES IN ('河南', '湖北');

-- 添加新的哈希分区（需要调整 MODULUS）
-- 见哈希分区部分的说明

从现有表创建分区：

如果已有数据表，可以将其转换为分区：

-- 1. 创建结构与分区表相同的表
CREATE TABLE orders_staging (LIKE orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- 2. 加载数据到临时表
\COPY orders_staging FROM 'orders_2025_q1.csv' CSV

-- 3. 将表作为分区挂载
ALTER TABLE orders ATTACH PARTITION orders_staging
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

删除分区​

直接删除分区：

-- 删除分区（数据也会删除）
DROP TABLE orders_2022_q1;

直接删除分区比执行 DELETE FROM orders WHERE order_date < '2022-04-01' 快得多，因为：

  • 不需要扫描表查找符合条件的行
  • 不会产生死元组，不需要 VACUUM
  • 不会触发相关触发器

分离分区：

分离分区将分区从分区表中移除，但保留数据和表结构：

-- 从分区表分离分区（保留数据）
ALTER TABLE orders DETACH PARTITION orders_2022_q1;

-- 分离后，orders_2022_q1 成为独立的普通表
-- 可以单独查询、修改或删除

-- PostgreSQL 14+ 支持并发分离
-- 并发分离不会阻塞其他操作
ALTER TABLE orders DETACH PARTITION orders_2022_q1 CONCURRENTLY;

并发分离适用于生产环境，不会阻塞对分区表的读写操作。

挂载分区​

将独立表挂载为分区：

-- 创建独立表
CREATE TABLE orders_archive (
    id BIGINT,
    customer_id INTEGER,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2),
    status VARCHAR(20)
);

-- 加载历史数据
\COPY orders_archive FROM 'archive.csv' CSV

-- 挂载为分区
ALTER TABLE orders ATTACH PARTITION orders_archive
    FOR VALUES FROM ('2020-01-01') TO ('2024-01-01');

-- 挂载时会验证数据是否符合分区边界
-- 如果数据不符合，会报错

挂载分区的注意事项：

挂载分区时，PostgreSQL 会扫描表验证所有行是否符合分区边界。对于大表，这可能耗时较长。可以通过预先添加约束来加速：

-- 在挂载前添加 CHECK 约束
ALTER TABLE orders_archive ADD CONSTRAINT chk_date_range
    CHECK (order_date >= '2020-01-01' AND order_date < '2024-01-01');

-- 挂载时，由于已有约束，PostgreSQL 可以跳过数据验证
ALTER TABLE orders ATTACH PARTITION orders_archive
    FOR VALUES FROM ('2020-01-01') TO ('2024-01-01');

合并与拆分分区​

PostgreSQL 不提供直接合并或拆分分区的命令，需要手动操作：

合并分区：

-- 假设要合并 orders_2024_q1 和 orders_2024_q2

-- 1. 分离分区
ALTER TABLE orders DETACH PARTITION orders_2024_q1;
ALTER TABLE orders DETACH PARTITION orders_2024_q2;

-- 2. 创建新的合并分区
CREATE TABLE orders_2024_h1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-07-01');

-- 3. 将数据移动到新分区
INSERT INTO orders SELECT * FROM orders_2024_q1;
INSERT INTO orders SELECT * FROM orders_2024_q2;

-- 4. 删除旧表
DROP TABLE orders_2024_q1;
DROP TABLE orders_2024_q2;

拆分分区：

-- 假设要拆分 orders_2024_h1 为两个季度分区

-- 1. 分离分区
ALTER TABLE orders DETACH PARTITION orders_2024_h1;

-- 2. 创建新分区
CREATE TABLE orders_2024_q1_new PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2_new PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 3. 分配数据
INSERT INTO orders SELECT * FROM orders_2024_h1
    WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01';

INSERT INTO orders SELECT * FROM orders_2024_h1
    WHERE order_date >= '2024-04-01' AND order_date < '2024-07-01';

-- 4. 删除旧表
DROP TABLE orders_2024_h1;

重命名分区​

-- 重命名分区
ALTER TABLE orders_2024_q1 RENAME TO orders_2024_first_quarter;

-- 注意：重命名后，分区仍然是分区表的一部分

分区裁剪​

分区裁剪是 PostgreSQL 最重要的优化之一。当查询条件包含分区键时，查询规划器会自动排除不需要扫描的分区，只查询可能包含目标数据的分区。

分区裁剪示例：

-- 启用分区裁剪（默认启用）
SET enable_partition_pruning = on;

-- 查询特定日期的订单
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date = '2024-02-15';

执行计划会显示：

Append  (cost=0.00..123.45 rows=1 width=100)
  ->  Seq Scan on orders_2024_q1  (cost=0.00..123.45 rows=1 width=100)
        Filter: (order_date = '2024-02-15'::date)

-- 注意：只扫描了 orders_2024_q1，其他分区被排除

范围查询的分区裁剪：

-- 查询一个季度的数据
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01';
-- 只扫描 orders_2024_q1

-- 查询跨季度的数据
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01';
-- 扫描 orders_2024_q1 和 orders_2024_q2

运行时分区裁剪：

PostgreSQL 11+ 支持运行时分区裁剪，即使分区键的值在规划时未知：

-- 参数化的查询（如 Prepared Statement）
PREPARE get_orders(date) AS SELECT * FROM orders WHERE order_date = $1;
EXECUTE get_orders('2024-02-15');
-- 运行时才确定分区，仍可裁剪

-- 子查询中的分区裁剪
SELECT * FROM orders 
WHERE order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = 1);
-- 子查询结果未知，但执行时仍可裁剪

分区裁剪的限制：

分区裁剪在某些情况下可能不生效：

-- 1. 对分区键使用函数
SELECT * FROM orders WHERE DATE_TRUNC('month', order_date) = '2024-02-01';
-- 不生效，函数阻止了分区裁剪

-- 改写为：
SELECT * FROM orders 
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01';
-- 生效

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_date = '2024-02-15';
-- 生效（PostgreSQL 会自动转换）

SELECT * FROM orders WHERE order_date::text = '2024-02-15';
-- 不生效，类型转换阻止了分区裁剪

-- 3. 使用 OR 条件
SELECT * FROM orders 
WHERE order_date = '2024-02-15' OR customer_id = 1;
-- 部分生效，需要扫描所有分区

查看分区裁剪效果：

-- 使用 EXPLAIN 查看扫描了哪些分区
EXPLAIN (ANALYZE, COSTS OFF) 
SELECT * FROM orders WHERE order_date = '2024-02-15';

-- 输出中查看：
-- Partitions selected: 1 (out of 4)
-- 表示从 4 个分区中选择了 1 个

查看分区信息​

-- 查看分区表结构
\d+ orders

-- 查询分区信息
SELECT 
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- 使用系统视图
SELECT * FROM pg_partitions WHERE tablename = 'orders';

分区与约束​

分区键约束​

分区键必须有 NOT NULL 约束：

-- 正确：分区键有 NOT NULL
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,  -- 必须有 NOT NULL
    customer_id INTEGER
) PARTITION BY RANGE (order_date);

-- 错误：分区键可以为 NULL
CREATE TABLE bad_orders (
    id BIGSERIAL,
    order_date DATE,  -- 缺少 NOT NULL
    customer_id INTEGER
) PARTITION BY RANGE (order_date);
-- ERROR: cannot create range partition with NULL partition key

添加约束​

-- 添加 CHECK 约束（自动应用到所有分区）
ALTER TABLE orders ADD CONSTRAINT chk_amount_positive 
    CHECK (total_amount >= 0);

-- 添加外键（分区表可以有外键引用其他表）
ALTER TABLE orders ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 注意：分区表不能被其他表引用

分区表数据操作​

插入数据​

-- 插入数据自动路由到正确分区
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (1, '2024-02-15', 100.00, 'completed');

-- 插入到不存在的分区会报错（除非有默认分区）
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (1, '2025-06-15', 100.00, 'completed');
-- ERROR: no partition of relation "orders" found for row

批量加载​

-- 方法1：临时挂载分区
-- 创建新表（结构与分区表相同）
CREATE TABLE orders_new (LIKE orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- 批量加载数据
\copy orders_new FROM 'data.csv' CSV

-- 将新表作为分区挂载
ALTER TABLE orders ATTACH PARTITION orders_new
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- 方法2：直接插入分区
INSERT INTO orders_2024_q1 SELECT * FROM staging_table;

更新和删除​

-- 更新分区键会导致行移动
UPDATE orders 
SET order_date = '2024-05-01' 
WHERE id = 1;
-- 行会从 q1 分区移动到 q2 分区

-- 删除旧数据（直接删除分区）
DROP TABLE orders_2020_q1;
-- 比DELETE快得多，且不会产生死元组

分区维护自动化​

PostgreSQL 没有内置的分区自动创建和删除功能，需要通过函数和定时任务实现。

自动创建分区​

使用 PL/pgSQL 函数自动创建未来的分区：

-- 创建自动分区函数
CREATE OR REPLACE FUNCTION create_monthly_partition(
    base_table TEXT,
    months_ahead INTEGER DEFAULT 1
) RETURNS VOID AS $$
DECLARE
    partition_date DATE;
    partition_name TEXT;
    start_date TEXT;
    end_date TEXT;
BEGIN
    -- 循环创建指定月份的分区
    FOR i IN 0..months_ahead-1 LOOP
        -- 计算分区日期
        partition_date := DATE_TRUNC('month', CURRENT_DATE + (i || ' months')::INTERVAL);
        partition_name := base_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
        start_date := partition_date::TEXT;
        end_date := (partition_date + INTERVAL '1 month')::TEXT;
        
        -- 检查分区是否已存在
        IF NOT EXISTS (
            SELECT 1 FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE c.relname = partition_name
            AND n.nspname = CURRENT_SCHEMA()
        ) THEN
            -- 创建分区
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                partition_name, base_table, start_date, end_date
            );
            RAISE NOTICE 'Created partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用：创建未来 3 个月的分区
SELECT create_monthly_partition('orders', 3);

-- 可以配合 pg_cron 扩展每月自动执行
-- SELECT cron.schedule('create_orders_partitions', '0 0 1 * *',
--     $$SELECT create_monthly_partition('orders', 3)$$);

自动删除旧分区​

-- 创建删除旧分区的函数
CREATE OR REPLACE FUNCTION drop_old_partitions(
    base_table TEXT,
    keep_months INTEGER DEFAULT 12
) RETURNS VOID AS $$
DECLARE
    partition_rec RECORD;
    partition_name TEXT;
    cutoff_date DATE;
    partition_start DATE;
BEGIN
    -- 计算截止日期
    cutoff_date := DATE_TRUNC('month', CURRENT_DATE - (keep_months || ' months')::INTERVAL);
    
    -- 查找所有分区
    FOR partition_rec IN 
        SELECT 
            child.relname AS partition_name,
            pg_get_expr(child.relpartbound, child.oid) AS partition_bound
        FROM pg_inherits
        JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
        JOIN pg_class child ON pg_inherits.inhrelid = child.oid
        JOIN pg_namespace n ON parent.relnamespace = n.oid
        WHERE parent.relname = base_table
        AND n.nspname = CURRENT_SCHEMA()
        AND child.relname != base_table  -- 排除默认分区
    LOOP
        -- 解析分区边界获取开始日期
        -- 这里简化处理，假设是范围分区且格式为 FOR VALUES FROM ('YYYY-MM-DD') TO ('YYYY-MM-DD')
        partition_start := 
            SUBSTRING(partition_rec.partition_bound FROM '\(''([0-9-]+)''\)')::DATE;
        
        -- 如果分区在截止日期之前，删除
        IF partition_start < cutoff_date THEN
            EXECUTE format('DROP TABLE IF EXISTS %I', partition_rec.partition_name);
            RAISE NOTICE 'Dropped partition: %', partition_rec.partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用：保留最近 12 个月的分区
SELECT drop_old_partitions('orders', 12);

分区归档脚本​

将旧分区归档而不是删除：

-- 创建归档函数
CREATE OR REPLACE FUNCTION archive_old_partition(
    base_table TEXT,
    partition_name TEXT,
    archive_schema TEXT DEFAULT 'archive'
) RETURNS VOID AS $$
DECLARE
    archive_table TEXT;
BEGIN
    -- 创建归档 Schema（如果不存在）
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', archive_schema);
    
    -- 分离分区
    EXECUTE format('ALTER TABLE %I DETACH PARTITION %I', base_table, partition_name);
    
    -- 移动到归档 Schema
    archive_table := archive_schema || '.' || partition_name;
    EXECUTE format('ALTER TABLE %I SET SCHEMA %I', partition_name, archive_schema);
    
    -- 可选：压缩归档表
    -- EXECUTE format('VACUUM FULL %I', archive_table);
    
    RAISE NOTICE 'Archived partition: %', archive_table;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT archive_old_partition('orders', 'orders_2022_01', 'archive');

分区健康检查​

-- 创建健康检查视图
CREATE OR REPLACE VIEW v_partition_health AS
SELECT 
    p.relname AS partitioned_table,
    c.relname AS partition_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
    pg_stat_get_live_tuples(c.oid) AS live_tuples,
    pg_stat_get_dead_tuples(c.oid) AS dead_tuples,
    CASE 
        WHEN pg_stat_get_dead_tuples(c.oid)::FLOAT / 
             NULLIF(pg_stat_get_live_tuples(c.oid), 0) > 0.2 
        THEN 'NEEDS VACUUM'
        ELSE 'OK'
    END AS status
FROM pg_class p
JOIN pg_inherits i ON p.oid = i.inhparent
JOIN pg_class c ON i.inhrelid = c.oid
WHERE p.relkind = 'p'  -- 分区表
ORDER BY p.relname, c.relname;

-- 查看健康状态
SELECT * FROM v_partition_health;

使用 pg_cron 自动化​

如果安装了 pg_cron 扩展，可以设置定时任务：

-- 安装 pg_cron（需要超级用户）
CREATE EXTENSION pg_cron;

-- 每月 1 号凌晨创建新分区
SELECT cron.schedule(
    'create_orders_partitions',
    '0 0 1 * *',  -- 每月 1 号 00:00
    $$SELECT create_monthly_partition('orders', 2)$$
);

-- 每月 1 号清理旧分区
SELECT cron.schedule(
    'drop_old_partitions',
    '0 1 1 * *',  -- 每月 1 号 01:00
    $$SELECT drop_old_partitions('orders', 12)$$
);

-- 查看定时任务
SELECT * FROM cron.job;

-- 取消定时任务
SELECT cron.unschedule('create_orders_partitions');

分区最佳实践​

分区键选择原则​

选择合适的分区键是分区设计最关键的决策：

原则一：选择查询条件常用的列

-- 如果查询经常按日期过滤
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- 应该按日期分区
CREATE TABLE orders (...) PARTITION BY RANGE (order_date);

-- 如果查询经常按地区过滤
SELECT * FROM customers WHERE region = '广东';
-- 应该按地区分区
CREATE TABLE customers (...) PARTITION BY LIST (region);

原则二：数据分布均匀

-- 好的设计：数据分布均匀
-- 按月分区，每月数据量相近
CREATE TABLE logs (...) PARTITION BY RANGE (created_at);

-- 不好的设计：数据分布不均
-- 按状态分区，90% 是 'completed'，10% 是其他
CREATE TABLE orders (...) PARTITION BY LIST (status);
-- 某个分区过大，失去了分区的意义

原则三：避免频繁更新分区键

-- 不好的设计：分区键经常更新
CREATE TABLE orders (...) PARTITION BY RANGE (status);
-- 订单状态经常变化，导致频繁的分区移动

-- 好的设计：分区键相对稳定
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
-- 创建时间不会变化

原则四：考虑数据增长趋势

-- 对于快速增长的表，选择能自然扩展的分区键
-- 如时间字段，新数据自然落入新分区

-- 对于稳定大小的表，哈希分区可能更合适
CREATE TABLE users (...) PARTITION BY HASH (id);

分区粒度控制​

粒度过细的问题：

  • 分区数量过多，增加规划器开销
  • 每个分区都有独立的元数据和索引
  • 管理复杂度增加

粒度过粗的问题：

  • 分区裁剪效果不明显
  • 单个分区仍然过大
  • 维护操作时间过长

建议：

  • 每个分区大小建议在 1GB - 100GB 之间
  • 总分区数量建议不超过几百个
  • 根据数据增长速度调整：快速增长用细粒度，缓慢增长用粗粒度
-- 高频写入场景：按日或周分区
CREATE TABLE logs (...) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024_01_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-01-02');

-- 中频场景：按月分区
CREATE TABLE orders (...) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 低频查询场景：按季度或年分区
CREATE TABLE archive (...) PARTITION BY RANGE (archived_at);
CREATE TABLE archive_2024 PARTITION OF archive
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

分区与索引策略​

-- 分区键通常不需要额外索引，分区裁剪已经优化

-- 应该索引的列：
-- 1. 高频查询的非分区键列
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 2. 排序和分组列
CREATE INDEX idx_orders_amount ON orders(total_amount);

-- 3. 外键列
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 复合索引设计
-- 如果经常按 customer_id 过滤并按 order_date 排序
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

存储策略​

-- 热数据和冷数据分离
-- 热数据放在 SSD
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    TABLESPACE hot_data;

-- 冷数据放在 HDD
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    TABLESPACE cold_data;

-- 可以压缩冷数据分区
ALTER TABLE orders_2023 SET (
    toast_tuple_target = 128,
    autovacuum_enabled = false
);

分区监控​

-- 查看分区大小
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'orders_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 查看分区数据分布
SELECT 
    tableoid::regclass AS partition,
    COUNT(*) AS row_count
FROM orders
GROUP BY tableoid
ORDER BY partition;

-- 查看最近分区
SELECT 
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname DESC
LIMIT 5;

分区表限制与解决方案​

PostgreSQL 的声明式分区有一些限制，了解这些限制有助于正确设计分区策略。

主键和唯一约束限制​

限制：主键和唯一约束必须包含所有分区键。

-- 错误示例
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,  -- 只有 id
    order_date DATE NOT NULL,
    customer_id INTEGER
) PARTITION BY RANGE (order_date);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

-- 正确示例：主键包含分区键
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    customer_id INTEGER,
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

解决方案：

  • 使用复合主键，包含分区键
  • 如果业务要求单一列主键，考虑重新设计分区策略

更新分区键​

限制：更新分区键会导致行移动到不同分区。

-- 更新分区键
UPDATE orders SET order_date = '2024-05-01' WHERE id = 1;
-- 如果原数据在 q1 分区，更新后会移动到 q2 分区

-- 这会带来额外开销：
-- 1. 删除旧行
-- 2. 在新分区插入行
-- 3. 更新所有相关索引

解决方案：

  • 设计分区键时，选择不常更新的列
  • 如果必须更新，考虑批量操作以提高效率

BEFORE ROW 触发器限制​

限制：分区表上不能创建 BEFORE ROW 触发器。

-- 错误示例
CREATE TRIGGER trg_orders_before
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION check_order();
-- ERROR: "orders" is a partitioned table

-- 解决方案：在各分区上创建触发器
CREATE TRIGGER trg_orders_q1_before
BEFORE INSERT ON orders_2024_q1
FOR EACH ROW EXECUTE FUNCTION check_order();

ON CONFLICT 限制​

限制：ON CONFLICT 必须包含分区键。

-- 错误示例
INSERT INTO orders (id, order_date, customer_id)
VALUES (1, '2024-02-15', 100)
ON CONFLICT (id) DO NOTHING;
-- ERROR: ON CONFLICT clause is not supported with partitioned tables

-- 正确示例：冲突目标包含分区键
INSERT INTO orders (id, order_date, customer_id)
VALUES (1, '2024-02-15', 100)
ON CONFLICT (id, order_date) DO NOTHING;

默认分区的限制​

限制：有默认分区后，添加新分区需要扫描默认分区验证数据。

-- 有默认分区时添加新分区
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
-- 会扫描 orders_default 确认没有属于新分区的数据

-- 解决方案：
-- 1. 添加新分区时移除默认分区
-- 2. 或者对默认分区添加约束限制数据范围

外键引用限制​

限制（PostgreSQL 11）：分区表不能被外键引用。

-- PostgreSQL 11 会报错
CREATE TABLE order_items (
    id SERIAL,
    order_id BIGINT REFERENCES orders(id)  -- orders 是分区表
);
-- ERROR: cannot reference partitioned table "orders"

-- PostgreSQL 12+ 支持，但外键必须包含分区键
CREATE TABLE order_items (
    id SERIAL,
    order_id BIGINT,
    order_date DATE,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

表空间限制​

限制：分区表本身不能指定表空间，但各个分区可以。

-- 错误：分区表指定表空间
CREATE TABLE orders (...) PARTITION BY RANGE (order_date)
TABLESPACE fast_ssd;
-- ERROR: cannot specify tablespace for partitioned table

-- 正确：在分区级别指定表空间
CREATE TABLE orders_hot PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01')
    TABLESPACE fast_ssd;

CREATE TABLE orders_cold PARTITION OF orders
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
    TABLESPACE slow_hdd;

小结​

本章我们学习了 PostgreSQL 分区表的核心知识和实践技能：

基础概念：

  • 分区将大表拆分为多个物理小表，逻辑上统一
  • 声明式分区是 PostgreSQL 10+ 推荐的方式
  • 分区裁剪自动优化查询，只扫描相关分区

分区类型：

  • 范围分区（RANGE）：适合时间序列、连续数值
  • 列表分区（LIST）：适合地区、状态等离散值
  • 哈希分区（HASH）：适合均匀分散数据

分区管理：

  • 创建、删除、分离、挂载分区
  • 分区索引自动传播到所有分区
  • 主键和唯一约束必须包含分区键

限制与解决方案：

  • 主键必须包含分区键 → 使用复合主键
  • BEFORE ROW 触发器不支持 → 在各分区创建
  • 外键引用限制 → PostgreSQL 12+ 已支持

最佳实践：

  • 选择查询条件常用的列作为分区键
  • 控制分区粒度，每个分区 1GB-100GB
  • 热数据和冷数据分离存储
  • 自动化分区创建和清理

分区表是处理大规模数据的重要工具。合理设计分区策略，可以显著提升查询性能和管理效率。在实际项目中，建议先在小规模数据上测试分区效果，再应用到生产环境。

练习​

  1. 创建分区表：创建一个按日期范围分区的日志表，包含过去 12 个月和未来 3 个月的分区

  2. 测试分区裁剪：使用 EXPLAIN ANALYZE 比较分区裁剪前后的查询计划差异

  3. 实现自动分区：编写函数实现每月自动创建新分区、删除 12 个月前的旧分区

  4. 分区迁移：将一个现有的非分区表迁移为分区表，保持数据完整

  5. 性能对比：对比相同数据量下，分区表和非分区表的查询性能差异

参考资源​