PostgreSQL大表分区策略与跨分区查询性能优化实战

大表分区的触发条件与策略选型

当单表数据量超过5000万行，或表文件大小超过32GB时，PostgreSQL的VACUUM、索引扫描和查询规划开销会显著上升。此时应考虑分区。PostgreSQL从10版本开始原生声明式分区，12版本后性能已趋于成熟。

分区策略的选择依据：

# 分区策略决策树
# 数据特征                    推荐策略           示例
# 按时间增长（日志、订单）     RANGE分区（按月/日）  订单表按月分区
# 按离散值分布（地区、类型）   LIST分区            按省份分区
# 数据量均匀的哈希分布        HASH分区            用户表按ID哈希
# 时间+地区二维查询           多级分区             按月x地区

RANGE分区：订单表的按月分区实践

-- 创建分区父表
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    amount DECIMAL(12, 2),
    status SMALLINT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);

-- 创建月分区（提前创建未来3个月的分区）
CREATE TABLE orders_2026_07 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 默认分区（捕获超出范围的日期，防止写入报错）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 分区上的索引
CREATE INDEX idx_orders_2026_07_user ON orders_2026_07 (user_id);
CREATE INDEX idx_orders_2026_07_status ON orders_2026_07 (status);

分区自动维护脚本（cron任务）：

#!/bin/bash
# auto_create_partitions.sh - 自动创建未来3个月的分区
# 每月1号凌晨执行

PGHOST="localhost"
PGDB="production"
PGUSER="postgres"

# 计算3个月后的分区
for i in 1 2 3; do
    START_DATE=$(date -v+${i}m -v1d +"%Y-%m-01")
    END_DATE=$(date -v+$((i+1))m -v1d +"%Y-%m-01")
    PART_NAME="orders_${START_DATE//-/_}"

    psql -h $PGHOST -U $PGUSER -d $PGDB -c "
    CREATE TABLE IF NOT EXISTS ${PART_NAME} PARTITION OF orders
        FOR VALUES FROM ('${START_DATE}') TO ('${END_DATE}');
    CREATE INDEX IF NOT EXISTS idx_${PART_NAME}_user ON ${PART_NAME} (user_id);
    CREATE INDEX IF NOT EXISTS idx_${PART_NAME}_status ON ${PART_NAME} (status);
    " 2>&1 | logger -t pg-partition
done

跨分区查询的执行计划与优化

分区表查询的关键是分区裁剪（Partition Pruning）——查询规划器只扫描包含目标数据的分区。如果分区键不出现在WHERE条件中，所有分区都会被扫描。

-- 分区裁剪生效的查询（只扫描目标分区）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-15'
  AND user_id = 12345;

-- 执行计划应显示：
-- Append
--   -> Seq Scan on orders_2026_07
--        Filter: (created_at >= '2026-07-01' AND ...)

-- 分区裁剪不生效的查询（扫描所有分区！）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345;  -- 缺少created_at条件

-- 修复：强制加入分区键范围
SELECT * FROM orders
WHERE created_at >= '2026-01-01'  -- 限制分区范围
  AND user_id = 12345;

跨分区JOIN的性能陷阱：

-- 低效：跨分区JOIN
SELECT o.*, p.name
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2026-07-01';

-- 优化方案1：确保被JOIN的表也按相同键分区
CREATE TABLE order_items (
    id BIGSERIAL,
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 优化方案2：子查询先缩小范围
SELECT o.*, p.name
FROM (
    SELECT * FROM orders
    WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'
) o
JOIN products p ON o.product_id = p.id;

冷热数据分离：分区迁移与归档

超过6个月的订单数据查询频率极低，可以迁移到冷存储。

-- 方案1：DETACH分区并导出
ALTER TABLE orders DETACH PARTITION orders_2025_01;

-- 导出为压缩格式
-- pg_dump -t orders_2025_01 -d production | gzip > orders_2025_01.sql.gz

-- 方案2：使用外部表（postgres_fdw）访问归档库
CREATE EXTENSION postgres_fdw;
CREATE SERVER archive_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.internal', dbname 'archive', port '5432');

CREATE FOREIGN TABLE orders_archive (
    id BIGINT,
    user_id BIGINT,
    order_no VARCHAR(32),
    amount DECIMAL(12,2),
    status SMALLINT,
    created_at TIMESTAMPTZ
) SERVER archive_db OPTIONS (table_name 'orders_2025_01');

-- 查询时视图合并
CREATE VIEW orders_all AS
SELECT * FROM orders
UNION ALL
SELECT * FROM orders_archive;

分区表维护的常见问题与排查

问题1：写入时分区不存在报错

-- 错误：no partition of relation "orders" found for row
-- 原因：数据的时间超出已创建分区范围
-- 解决：创建默认分区 + 监控告警

-- 监控默认分区数据量
SELECT count(*) FROM orders_default;  -- >0 触发告警

-- 批量将默认分区数据重分布
INSERT INTO orders SELECT * FROM orders_default;
TRUNCATE orders_default;

问题2：分区过多导致规划时间过长

-- 查看当前分区数
SELECT count(*) FROM pg_inherits
WHERE inhparent = 'orders'::regclass;

-- 建议：活跃分区数控制在100以内
-- 冷分区DETACH或DROP
ALTER TABLE orders DETACH PARTITION orders_2024_01;
DROP TABLE orders_2024_01;  -- 确认已归档后

问题3：分区上的UNIQUE约束必须包含分区键

-- 错误：分区键不在唯一约束中
-- CREATE UNIQUE INDEX idx_orders_no ON orders (order_no);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

-- 正确：包含分区键
CREATE UNIQUE INDEX idx_orders_no ON orders (order_no, created_at);

-- 如果业务要求order_no全局唯一，需要用应用层去重或序列号生成器

分区表是处理大规模时序数据的标准方案。核心要点：提前规划分区粒度、自动创建未来分区、定期归档冷分区、所有查询必须包含分区键条件。