跳到主要内容

PostgreSQL 分区表

分区（Partitioning）是将大表分割成多个物理上独立但逻辑上统一的小表的技术。对于大型数据库，分区可以显著提高查询性能和管理效率。

分区概述​

什么是分区？​

分区将一个大表按照特定规则拆分成多个较小的物理表（分区），但对用户而言仍然是一个逻辑上的整体表。

分区的优势​

优势说明
查询性能分区裁剪减少扫描数据量
维护效率可以单独维护、删除分区
索引优化每个分区独立索引，重建更快
数据加载批量加载可以挂载新分区
存储管理不同分区可放在不同存储

何时使用分区？​

  1. 表数据量大：超过几百 GB 或几亿行
  2. 有明显的分区键：如日期、地区、类型等
  3. 查询有分区条件：经常按分区键过滤
  4. 需要定期归档：如按月删除旧数据

分区类型​

范围分区（RANGE）​

按值的范围分区，最常用于日期字段：

-- 创建分区表
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);

-- 创建分区
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;

列表分区（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;

哈希分区（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);

-- 创建分区（通常创建 2 的幂次方个分区）
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);

多列分区​

可以基于多个列进行分区：

-- 按年和月组合分区
CREATE TABLE sales (
    id BIGSERIAL,
    sale_date DATE,
    region VARCHAR(50),
    amount NUMERIC(12, 2)
) PARTITION BY RANGE (sale_date, region);

-- 这种方式较少使用，通常建议使用复合分区

复合分区​

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;

分区索引​

全局索引 vs 本地索引​

PostgreSQL 的分区索引是"本地索引"——每个分区有独立的索引：

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

-- 创建主键（必须包含分区键）
ALTER TABLE orders ADD PRIMARY KEY (id, order_date);

-- 创建唯一约束（必须包含分区键）
ALTER TABLE orders ADD CONSTRAINT uk_orders_customer_date 
    UNIQUE (customer_id, order_date);

仅在特定分区创建索引​

-- 在特定分区创建索引
CREATE INDEX idx_orders_2024_q1_status ON orders_2024_q1(status);

分区管理​

添加分区​

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

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

删除分区​

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

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

-- 并发分离（PostgreSQL 14+）
ALTER TABLE orders DETACH PARTITION orders_2022_q1 CONCURRENTLY;

合并分区​

-- 分离旧分区
ALTER TABLE orders DETACH PARTITION orders_2024_q1;

-- 重命名
ALTER TABLE orders_2024_q1 RENAME TO orders_archive_q1;

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

-- 将数据移动到新分区
INSERT INTO orders SELECT * FROM orders_archive_q1;

-- 删除旧表
DROP TABLE orders_archive_q1;

分区裁剪​

分区裁剪是自动优化，查询时只扫描相关分区：

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

-- 查看是否使用分区裁剪
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date = '2024-02-15';

-- 输出中会显示：Partition Iterator
-- 和：Partitions selected: 1 (out of 4)

查看分区信息​

-- 查看分区表结构
\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快得多，且不会产生死元组

分区维护任务​

自动创建分区​

使用函数和触发器自动创建分区：

-- 创建自动分区函数
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS VOID AS $$
DECLARE
    partition_date DATE;
    partition_name TEXT;
    start_date TEXT;
    end_date TEXT;
BEGIN
    -- 计算下个月的分区
    partition_date := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
    partition_name := 'orders_' || 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 WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 手动执行
SELECT create_monthly_partition();

-- 或使用 pg_cron 扩展定期执行
-- SELECT cron.schedule('create_partition', '0 0 1 * *', 
--     $$SELECT create_monthly_partition()$$);

自动删除旧分区​

-- 创建删除旧分区的函数
CREATE OR REPLACE FUNCTION drop_old_partitions(
    table_name TEXT,
    keep_months INTEGER DEFAULT 12
)
RETURNS VOID AS $$
DECLARE
    partition_rec RECORD;
    cutoff_date 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
        WHERE parent.relname = table_name
    LOOP
        -- 简化版：实际需要解析 partition_bound
        RAISE NOTICE 'Checking partition: %', partition_rec.partition_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

分区最佳实践​

分区键选择​

-- 好的分区键选择：
-- 1. 经常用于 WHERE 条件
-- 2. 数据分布均匀
-- 3. 有明确的业务含义

-- 示例1：按时间分区（适合日志、订单等）
PARTITION BY RANGE (created_at);

-- 示例2：按地区分区（适合多地区业务）
PARTITION BY LIST (region);

-- 示例3：按用户ID哈希（适合高并发写入）
PARTITION BY HASH (user_id);

分区粒度​

-- 分区数量建议：
-- 太少：分区优势不明显
-- 太多：管理开销大

-- 建议：
-- - 范围分区：每个分区 1GB - 100GB
-- - 分区总数：不超过几百个

-- 对于时间序列数据：
-- - 高频写入：按日或周分区
-- - 中频写入：按月分区
-- - 低频查询：按季度或年分区

分区与索引​

-- 索引建议：
-- 1. 每个分区独立索引
-- 2. 分区键通常不需要额外索引
-- 3. 高频查询列创建索引

-- 创建索引
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

-- 分区键 order_date 不需要索引，分区裁剪已经优化

分区表限制​

-- 1. 主键和唯一约束必须包含分区键
-- 错误：
ALTER TABLE orders ADD PRIMARY KEY (id);
-- ERROR: primary key constraints are not supported on partitioned tables

-- 正确：
ALTER TABLE orders ADD PRIMARY KEY (id, order_date);

-- 2. 不支持 AFTER ROW 触发器在分区表上
-- 需要在各个分区上单独创建触发器

-- 3. 外键约束限制
-- 分区表可以引用普通表
-- 普通表不能引用分区表（PostgreSQL 11 及之前）

-- 4. ON CONFLICT 限制
-- PostgreSQL 11+ 支持，但需要分区键在冲突目标中

小结​

本章我们学习了 PostgreSQL 分区表：

  1. 分区概念：将大表拆分成多个物理小表
  2. 分区类型：范围分区、列表分区、哈希分区
  3. 复合分区：分区嵌套
  4. 分区索引：每个分区独立索引
  5. 分区管理：添加、删除、合并分区
  6. 分区裁剪：自动优化查询
  7. 最佳实践：分区键选择、粒度控制

练习​

  1. 创建一个按月的范围分区表
  2. 设计一个按地区的列表分区表
  3. 实现分区自动创建和删除脚本
  4. 测试分区裁剪效果

参考资源​