PostgreSQL订单表分区实现及关联表分区建议咨询

一、手动拆分（一次性迁移场景） #

如果只是想把现有订单一次性拆分到两张表，按以下步骤操作：

1. 新建order_current和order_archive表，结构与原order表一致：

-- 创建当前订单表（存近12个月数据）
CREATE TABLE order_current (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total NUMERIC(10, 2) NOT NULL
);

-- 创建归档订单表（存超过12个月数据）
CREATE TABLE order_archive (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL,
    total NUMERIC(10, 2) NOT NULL
);

注：归档表的created_at可以去掉默认值，因为只会写入历史数据

2. 迁移历史数据到归档表：

INSERT INTO order_archive (id, created_at, total)
SELECT id, created_at, total
FROM "order"
WHERE created_at < NOW() - INTERVAL '12 months';

3. 迁移近期数据到当前表：

INSERT INTO order_current (id, created_at, total)
SELECT id, created_at, total
FROM "order"
WHERE created_at >= NOW() - INTERVAL '12 months';

4. 处理外键关联：原order_item的order_id关联原order表，若要保留关联，可创建包含两张表的视图order_all，让order_item关联视图（更推荐用下面的分区父表方案）：

CREATE VIEW order_all AS
SELECT * FROM order_current
UNION ALL
SELECT * FROM order_archive;

-- 修改外键关联视图（注意：视图无法直接作为外键引用，仅用于查询关联，生产环境优先用分区父表）
ALTER TABLE order_item DROP CONSTRAINT order_item_order_id_fkey;
ALTER TABLE order_item ADD CONSTRAINT order_item_order_id_fkey FOREIGN KEY (order_id) REFERENCES order_all(id);

5. 可选操作：备份原order表后删除，避免混淆。

二、自动分区（持续归档场景） #

如果需要自动将超过12个月的订单归档，推荐用PostgreSQL原生范围分区（PostgreSQL 11+支持），替代pg_partman的方案如下：

1. 创建分区父表 #

先将原order表替换为分区父表（若原表有数据，先备份迁移）：

-- 备份原表（可选）
CREATE TABLE order_backup AS SELECT * FROM "order";

-- 删除原表
DROP TABLE IF EXISTS "order";

-- 创建分区父表，按created_at范围分区
CREATE TABLE "order" (
    id SERIAL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (created_at);

2. 创建当前分区与归档分区 #

-- 当前分区：存储近12个月的订单
CREATE TABLE order_current PARTITION OF "order"
FOR VALUES FROM (NOW() - INTERVAL '12 months') TO (MAXVALUE);

-- 归档分区：存储超过12个月的历史订单（可按年/月拆分为多个子分区，方便管理）
CREATE TABLE order_archive PARTITION OF "order"
FOR VALUES FROM (MINVALUE) TO (NOW() - INTERVAL '12 months');

3. 自动维护分区（每月调整） #

用pg_cron定时任务每月更新分区范围，自动归档旧数据：

-- 安装pg_cron（未安装的话）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 每月1号凌晨执行分区维护
SELECT cron.schedule('monthly-order-archive', '0 0 1 * *', $$
    -- 创建新的当前分区（更新范围为近12个月）
    CREATE TABLE IF NOT EXISTS order_current_new PARTITION OF "order"
    FOR VALUES FROM (NOW() - INTERVAL '12 months') TO (MAXVALUE);
    
    -- 将旧当前分区中过期的数据迁移到归档分区
    INSERT INTO order_archive
    SELECT * FROM order_current
    WHERE created_at < NOW() - INTERVAL '12 months';
    
    -- 删除旧当前分区的过期数据
    DELETE FROM order_current
    WHERE created_at < NOW() - INTERVAL '12 months';
    
    -- 替换旧当前分区
    ALTER TABLE order_current RENAME TO order_current_old;
    ALTER TABLE order_current_new RENAME TO order_current;
    DROP TABLE order_current_old;
$$);

三、order_item表是否需要分区？ #

分两种情况判断：

• 需要分区的场景：如果order_item数据量极大（千万级以上），且日常查询大多只涉及近12个月的订单，建议按order_id关联的created_at（或冗余order_created_at字段）做范围分区，与order表的分区对齐，减少查询扫描的数据量。
• 无需分区的场景：如果数据量不大，或经常需要跨当前/归档订单查询所有订单项，直接保留单表即可，分区反而增加复杂度。

示例：给order_item按order_created_at分区（需先冗余字段）：

-- 修改order_item表，添加冗余的order_created_at字段
ALTER TABLE order_item ADD COLUMN order_created_at TIMESTAMP NOT NULL;

-- 填充已有数据的order_created_at值
UPDATE order_item oi
SET order_created_at = o.created_at
FROM "order" o
WHERE oi.order_id = o.id;

-- 创建分区父表
CREATE TABLE order_item_new (
    id SERIAL,
    order_id INTEGER NOT NULL REFERENCES "order"(id),
    product_id INTEGER NOT NULL REFERENCES product(id),
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    order_created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (order_created_at);

-- 创建当前订单项分区
CREATE TABLE order_item_current PARTITION OF order_item_new
FOR VALUES FROM (NOW() - INTERVAL '12 months') TO (MAXVALUE);

-- 创建归档订单项分区
CREATE TABLE order_item_archive PARTITION OF order_item_new
FOR VALUES FROM (MINVALUE) TO (NOW() - INTERVAL '12 months');

-- 迁移数据到新分区表
INSERT INTO order_item_new SELECT * FROM order_item;

-- 替换原表
DROP TABLE order_item;
ALTER TABLE order_item_new RENAME TO order_item;

四、pg_partman失败的排查方向 #

之前用pg_partman没成功，大概率是以下原因：

• 未正确设置分区父表的分区键（必须是created_at）；
• 未启用pg_partman的自动分区定时任务，或执行权限不足；
• 原表已有数据，但未执行初始化同步操作。

重新尝试pg_partman的步骤：

-- 安装pg_partman
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 创建分区父表（同原生分区方案）
CREATE TABLE "order" (
    id SERIAL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (created_at);

-- 初始化分区，按月分区，保留12个月数据，自动归档到order_archive
SELECT partman.create_parent(
    p_parent_table := 'public.order',
    p_control := 'created_at',
    p_type := 'range',
    p_interval := 'month',
    p_premake := 1,
    p_retention := '12 months',
    p_retention_schema := 'public',
    p_archive_table := 'order_archive'
);

-- 检查定时任务是否正常运行
SELECT * FROM cron.job;

内容的提问来源于stack exchange，提问作者Odin