技术博客：PostgreSQL在武汉政务项目中的分区表优化实践

项目背景

某武汉政务数据平台，需要存储和处理全市800万+市民的办事记录。核心表 service_records 的数据特征：

• 日均新增记录：5-8 万条
• 历史数据总量：3 亿+条（5年累积）
• 查询模式：按时间范围 + 按行政区划 + 按事项类型
• 写入模式：批量导入（每日凌晨）+ 实时插入
• 数据保留：在线 2 年，归档 3 年，共 5 年

最初使用单表存储，随着数据量增长出现严重的性能问题：单次查询耗时30秒+，表膨胀导致磁盘占用2TB+。

分区方案设计

分区键选择

经过分析查询模式，决定采用复合分区：

• 一级分区：按 RANGE（创建时间），每月一个分区
• 二级分区：按 LIST（行政区划编码），武汉 13 个区 + 其他

这样设计的理由：

1. 绝大多数查询都带有时间范围条件（如"查询2024年1月江岸区的办件"）
2. 行政区划是固定的 13 个值，LIST 分区天然合适
3. 复合分区使分区裁剪（Partition Pruning）效率极高

DDL 实现

-- 创建分区表
CREATE TABLE service_records (
    id BIGSERIAL PRIMARY KEY,
    citizen_id VARCHAR(32) NOT NULL,          -- 市民身份证号（脱敏）
    district_code CHAR(6) NOT NULL,           -- 行政区划编码
    service_type VARCHAR(50) NOT NULL,        -- 事项类型
    status SMALLINT NOT NULL DEFAULT 0,       -- 办理状态
    create_time TIMESTAMP NOT NULL DEFAULT NOW(),
    update_time TIMESTAMP NOT NULL DEFAULT NOW(),
    detail JSONB                            -- 办件详情（灵活字段）
) PARTITION BY RANGE (create_time)
  SUBPARTITION BY LIST (district_code);

-- 创建子分区（以2024年1月为例）
Create TABLE service_records_202401
PARTITION OF service_records
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 为2024年1月创建各区的子分区
ALTER TABLE service_records_202401
SUBPARTITION BY LIST (district_code);

-- 江岸区 (420102)
CREATE TABLE service_records_202401_420102
PARTITION OF service_records_202401
FOR VALUES IN ('420102');

-- 江汉区 (420103)
CREATE TABLE service_records_202401_420103
PARTITION OF service_records_202401
FOR VALUES IN ('420103');

-- ... 其他区类似 ...

-- 默认分区（用于未知区划或其他）
CREATE TABLE service_records_202401_default
PARTITION OF service_records_202401
FOR VALUES IN (DEFAULT);

自动化分区创建

-- 使用 pg_partman 插件自动管理分区
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 配置自动分区创建策略
SELECT partman.create_parent(
    p_parent_table => 'public.service_records',
    p_control => 'create_time',
    p_type => 'native',
    p_interval => '1 month',
    p_premake => 3,          -- 提前创建3个月的分区
    p_retention => '26 months'  -- 保留26个月后自动删除
);

索引优化

-- 主键索引（自动在每个分区上创建）
-- 已有: id

-- 常用查询条件的组合索引
CREATE INDEX idx_sr_district_time ON service_records(district_code, create_time);
CREATE INDEX idx_sr_citizen_time ON service_records(citizen_id, create_time);
CREATE INDEX idx_sr_type_status ON service_records(service_type, status);

-- JSONB 字段的 GIN 索引（用于详情字段搜索）
CREATE INDEX idx_sr_detail_gin ON service_records USING GIN(detail);

-- 部分索引（只对近期活跃数据建立）
CREATE INDEX idx_sr_active_status ON service_records(status)
WHERE create_time > NOW() - interval '3 months';

查询优化效果

优化前的查询

-- 查询：2024年上半年江岸区所有办件
EXPLAIN ANALYZE
SELECT count(*) FROM service_records
WHERE district_code = '420102'
AND create_time >= '2024-01-01'
AND create_time < '2024-07-01';

-- 优化前：
-- Seq Scan on service_records (cost=0.00..850000.00 rows=...)
-- 实际执行时间: 32543.215 ms (约33秒)

优化后的查询

-- 同样的查询
EXPLAIN ANALYZE
SELECT count(*) FROM service_records
WHERE district_code = '420102'
AND create_time >= '2024-01-01'
AND create_time < '2024-07-01';

-- 优化后：
-- Append (cost=0.00..125.00 rows=...)
--   -> Index Only Scan using ... on service_records_202401_420102
--   -> Index Only Scan using ... on service_records_202402_420102
--   -> Index Only Scan using ... on service_records_202403_420102
--   -> Index Only Scan using ... on service_records_202404_420102
--   -> Index Only Scan using ... on service_records_202405_420102
--   -> Index Only Scan using ... on service_records_202406_420102
-- 实际执行时间: 156.328 ms (约0.16秒)

-- 性能提升: 33秒 → 0.16秒 = 提升 208倍!

维护自动化

定期清理旧分区

-- 归档超过2年的数据到冷存储
CREATE OR REPLACE FUNCTION archive_old_partitions()
RETURNS void AS $$
DECLARE
    partition_name TEXT;
    cutoff_date DATE := CURRENT_DATE - INTERVAL '2 years';
BEGIN
    FOR partition_name IN (
        SELECT tablename FROM pg_tables
        WHERE schemaname = 'public'
        AND tablename LIKE 'service_records_%'
        AND tablename NOT LIKE '%default%'
    ) LOOP
        -- 检查分区日期是否早于截止日期
        -- 如果是，则 detach 并导出到归档库
        -- （此处简化处理）
    END LOOP;
END;
$$ LANGUAGE plpgsql;

VACUUM 策略

-- 由于分区表每个分区相对较小，VACUUM 压力大大减轻
-- 配置 autovacuum 更积极的参数
ALTER TABLE service_records SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 默认0.2
    autovacuum_analyze_scale_factor = 0.02,  -- 默认0.1
    autovacuum_vacuum_cost_limit = 1000      -- 默认200
);

运行数据对比

经验总结

1. 分区键选择至关重要：必须基于实际的查询模式，而不是拍脑袋
2. pg_partman 是神器：手动管理分区容易出错，自动化工具必不可少
3. 分区粒度要适中：太细（如每天）分区太多增加管理开销，太粗（如每年）分区裁剪效果差
4. 别忘了索引：分区表上的索引同样重要，而且可以在每个分区上独立创建
5. 监控分区大小：某些分区可能异常增长（如特定时间段的活动激增）
6. 归档策略要前置：不要等到磁盘满了才想起来清理