PostgreSQL分区表Range与List分区策略及分区裁剪查询优化

PostgreSQL分区表架构与适用场景

PostgreSQL原生分区表通过声明式分区（Declarative Partitioning）将大表拆分为多个物理子表，每个子表独立存储和索引。分区表适用于单表数据量超过千万级且查询存在明显数据访问局部性的场景，如按时间范围查询的日志表、按地区查询的订单表。

PostgreSQL支持三种分区策略：Range分区按连续值范围划分（如日期、ID区间）、List分区按离散值集合划分（如地区编码、状态枚举）、Hash分区按哈希值均匀分布（用于负载均衡）。选择策略需根据查询条件的类型决定，查询条件与分区键匹配才能触发分区裁剪。

Range分区按时间范围拆分实践

以日志表为例，按月分区是最常见的Range分区模式。需要预先创建分区或使用pg_partman扩展实现自动分区管理。

-- 创建主表（分区父表）
CREATE TABLE app_logs (
    id          BIGSERIAL,
    log_time    TIMESTAMP NOT NULL,
    level       VARCHAR(10) NOT NULL,
    service     VARCHAR(50) NOT NULL,
    message     TEXT,
    request_id  VARCHAR(64),
    PRIMARY KEY (id, log_time)  -- 分区键必须包含在主键中
) PARTITION BY RANGE (log_time);

-- 按月创建分区
CREATE TABLE app_logs_202601 PARTITION OF app_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE app_logs_202602 PARTITION OF app_logs
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE app_logs_202603 PARTITION OF app_logs
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 创建默认分区存储不匹配任何分区的数据
CREATE TABLE app_logs_default PARTITION OF app_logs DEFAULT;

-- 为每个分区创建本地索引
CREATE INDEX idx_logs_202601_service ON app_logs_202601 (service, log_time);
CREATE INDEX idx_logs_202602_service ON app_logs_202602 (service, log_time);
CREATE INDEX idx_logs_202603_service ON app_logs_202603 (service, log_time);

子分区组合使用

Range分区可以嵌套List或Hash子分区，实现多维度数据划分。例如按月分区后，每个月再按服务名List子分区：

-- 子分区：2026年1月按服务名List分区
CREATE TABLE app_logs_202601 PARTITION OF app_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY LIST (service);

CREATE TABLE app_logs_202601_api PARTITION OF app_logs_202601
    FOR VALUES IN ('api-gateway');

CREATE TABLE app_logs_202601_auth PARTITION OF app_logs_202601
    FOR VALUES IN ('auth-service', 'oauth-service');

CREATE TABLE app_logs_202601_other PARTITION OF app_logs_202601
    DEFAULT;

List分区按离散值拆分实践

List分区适用于按地区、租户或状态等离散维度查询的场景。多租户SaaS系统中按租户ID分区可以实现租户级数据隔离。

-- 多租户订单表按地区List分区
CREATE TABLE tenant_orders (
    order_id     BIGSERIAL,
    tenant_id    VARCHAR(20) NOT NULL,
    region       VARCHAR(10) NOT NULL,
    amount       DECIMAL(12,2),
    status       VARCHAR(20),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

-- 华东、华北、华南、海外分区
CREATE TABLE tenant_orders_cn_east PARTITION OF tenant_orders
    FOR VALUES IN ('CN-EAST', 'CN-SHANGHAI', 'CN-NANJING');

CREATE TABLE tenant_orders_cn_north PARTITION OF tenant_orders
    FOR VALUES IN ('CN-NORTH', 'CN-BEIJING', 'CN-TIANJIN');

CREATE TABLE tenant_orders_cn_south PARTITION OF tenant_orders
    FOR VALUES IN ('CN-SOUTH', 'CN-GUANGZHOU', 'CN-SHENZHEN');

CREATE TABLE tenant_orders_overseas PARTITION OF tenant_orders
    DEFAULT;

分区裁剪机制与查询计划验证

分区裁剪（Partition Pruning）是分区表性能优化的核心机制。查询执行时，规划器根据WHERE条件中的分区键过滤，只扫描匹配的分区，跳过不相关的分区子表。分区裁剪发生在计划阶段（静态裁剪）和执行阶段（动态裁剪）。

静态裁剪：常量条件

当WHERE条件包含分区键的常量值时，规划器在计划阶段即可确定需要扫描的分区：

EXPLAIN SELECT * FROM app_logs
WHERE log_time >= '2026-02-01' AND log_time < '2026-03-01' AND level = 'ERROR';

-- 预期查询计划（静态裁剪）：
-- Append
--   -> Seq Scan on app_logs_202602  <- 仅扫描2月分区
--        Filter: ((log_time >= '2026-02-01') AND (log_time < '2026-03-01') AND (level = 'ERROR'))
-- 注意：app_logs_202601和app_logs_202603被裁剪，不出现在计划中

动态裁剪：参数化查询

当WHERE条件包含参数占位符（如PREPARE语句或ORM生成的参数化查询）时，规划器无法在计划阶段确定具体分区，但执行器在运行时根据参数值动态裁剪：

-- 参数化查询（动态裁剪）
PREPARE get_logs(TIMESTAMP, TIMESTAMP) AS
    SELECT * FROM app_logs WHERE log_time >= $1 AND log_time < $2;

EXPLAIN EXECUTE get_logs('2026-01-15', '2026-01-20');

-- 预期查询计划（动态裁剪）：
-- Append
--   Subplans Removed: 2  <- 动态裁剪移除了2个分区
--   -> Seq Scan on app_logs_202601
--        Filter: ((log_time >= $1) AND (log_time < $2))

裁剪失效的常见场景

当WHERE条件不包含分区键，或对分区键使用函数导致无法匹配分区边界时，分区裁剪失效，全表扫描所有分区：

-- 裁剪失效：对分区键使用函数
EXPLAIN SELECT * FROM app_logs
WHERE EXTRACT(MONTH FROM log_time) = 2;

-- 预期：扫描所有分区（裁剪失效），性能差
-- Append
--   -> Seq Scan on app_logs_202601
--   -> Seq Scan on app_logs_202602
--   -> Seq Scan on app_logs_202603
--   -> Seq Scan on app_logs_default

-- 正确写法：使用范围条件替代函数
EXPLAIN SELECT * FROM app_logs
WHERE log_time >= '2026-02-01' AND log_time < '2026-03-01';
-- 预期：仅扫描app_logs_202602（裁剪生效）

分区维护操作：分离、附加与数据迁移

分区维护包括将旧分区离线归档、将新分区上线、以及在分区之间迁移数据。PostgreSQL提供ALTER TABLE … DETACH/ATTACH PARTITION操作。

-- 分离分区（数据保留，但不再属于父表）
ALTER TABLE app_logs DETACH PARTITION app_logs_202601;

-- 分离后可独立操作：归档、删除、导出
-- 例如导出归档
COPY app_logs_202601 TO '/backup/app_logs_202601.csv' WITH CSV HEADER;

-- 重新附加分区
ALTER TABLE app_logs ATTACH PARTITION app_logs_202601
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- 在线迁移：将普通表转为分区
-- 1. 创建新分区表
-- 2. 将旧表数据导入新分区
-- 3. 重命名切换
BEGIN;
CREATE TABLE app_logs_new (LIKE app_logs INCLUDING ALL) PARTITION BY RANGE (log_time);
-- 创建分区...
INSERT INTO app_logs_new SELECT * FROM app_logs;
-- 验证数据一致性后切换
ALTER TABLE app_logs RENAME TO app_logs_old;
ALTER TABLE app_logs_new RENAME TO app_logs;
COMMIT;

pg_partman扩展实现自动分区管理

手动维护分区表在分区数量增长后管理负担加大。pg_partman扩展提供自动创建未来分区、归档过期分区的功能。

-- 安装pg_partman
CREATE EXTENSION pg_partman;

-- 创建按月自动分区配置
SELECT partman.create_parent(
    p_parent_table := 'public.app_logs',
    p_control := 'log_time',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 4  -- 预创建未来4个月的分区
);

-- 配置自动归档：超过12个月的分区DETACH并移动到归档schema
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = true,
    retention_schema = 'archive'
WHERE parent_table = 'public.app_logs';

-- 调度pg_partman维护函数（每天执行一次）
-- 通过pg_cron扩展或外部调度器执行：
SELECT partman.run_maintenance_proc();

分区表索引策略与全局唯一约束

PostgreSQL分区表的索引需要在父表或每个子表上分别创建。在父表上创建索引会自动传播到所有子表（包括未来新建的分区），这是推荐的方式。

-- 在父表创建索引，自动传播到所有分区
CREATE INDEX idx_app_logs_request_id ON app_logs (request_id);
-- 此索引会自动创建在所有现有分区上，新分区创建时也会自动添加

-- 全局唯一约束的限制
-- PostgreSQL不支持跨分区的全局唯一索引，除非唯一约束包含分区键
-- 以下会失败：
-- ALTER TABLE app_logs ADD CONSTRAINT uk_request_id UNIQUE (request_id);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

-- 正确做法：唯一约束必须包含分区键
ALTER TABLE app_logs ADD CONSTRAINT uk_id_logtime UNIQUE (id, log_time);

分区表查询性能监控与调优

使用pg_stat_statements和EXPLAIN ANALYZE监控分区表查询性能，识别裁剪失效和索引未命中的问题查询：

-- 查找扫描分区数过多的慢查询
SELECT query, calls, total_exec_time, rows,
    regexp_match(query, 'FROM app_logs', 'i') AS hits_partition_table
FROM pg_stat_statements
WHERE query ILIKE '%app_logs%'
  AND total_exec_time > 1000  -- 超过1秒的查询
ORDER BY total_exec_time DESC
LIMIT 20;

-- 验证分区裁剪效果
EXPLAIN ANALYZE SELECT count(*) FROM app_logs
WHERE log_time >= '2026-02-01' AND log_time < '2026-03-01' AND service = 'api-gateway';

-- 关注指标：
-- "Subplans Removed" = 动态裁剪移除的分区数
-- "Actual Rows" 与预估行的偏差 = 统计信息是否过期
-- "Partitioned Scan" = 确认扫描了哪些分区

-- 更新分区统计信息
ANALYZE app_logs;
-- 或针对特定分区
ANALYZE app_logs_202602;