PostgreSQL 14分离分区时级联清除关联外键记录的方案咨询

问题背景 #

现有两张业务表：按expires_on范围分区的order表，以及关联该表的customer表。需求为：分离order表分区时自动清除customer表中对应关联记录，同时实现按expires_on结合order_id的全表分区逻辑。

原表结构 #

order分区表

CREATE TABLE IF NOT EXISTS "order"
(
    id bigint NOT NULL,
    site character varying(4) NOT NULL,
    "order" character varying(8) NOT NULL,
    expires_on timestamp with time zone NOT NULL,
    CONSTRAINT pick_pkey PRIMARY KEY (id, expires_on)
) PARTITION BY RANGE (expires_on);

注意：order是PostgreSQL保留关键字，建表及查询时需用双引号包裹。

customer关联表

CREATE TABLE IF NOT EXISTS customer
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    order_id bigint,
    updated_on timestamp with time zone NOT NULL,
    expires_on timestamp with time zone NOT NULL,
    CONSTRAINT customer_pk PRIMARY KEY (id),
    CONSTRAINT order_fk FOREIGN KEY (expires_on, order_id)
        REFERENCES "order" (expires_on, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

核心问题分析 #

1. 当前外键ON DELETE NO ACTION配置无法自动触发关联customer记录的删除，需调整外键行为或通过脚本/触发器实现联动清除。
2. order表已按expires_on分区，但customer表未分区，批量清理关联数据效率较低；需将customer表也按expires_on分区，实现分区级别的高效清理。

解决方案 #

方案一：修改外键为级联删除（优先推荐） #

直接调整外键的ON DELETE行为为CASCADE，当order表记录被删除时，关联的customer记录会自动删除。分离分区时，删除分区表的操作会触发级联删除逻辑：

-- 先验证原有外键（若之前为NOT VALID状态）
ALTER TABLE customer VALIDATE CONSTRAINT order_fk;

-- 删除原有外键并重建为级联删除
ALTER TABLE customer DROP CONSTRAINT order_fk;
ALTER TABLE customer ADD CONSTRAINT order_fk FOREIGN KEY (expires_on, order_id)
    REFERENCES "order" (expires_on, id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;

操作流程：分离order分区后，直接删除该分区表，PostgreSQL会自动清理customer表中所有关联的记录。

方案二：分区清理脚本+批量删除（业务不允许级联时使用） #

若业务限制不能用级联删除，可在分离order分区前，按expires_on范围批量清理customer的关联数据：

-- 清理指定过期范围内的customer关联记录
DELETE FROM customer
WHERE expires_on < '2024-01-01'
AND EXISTS (
    SELECT 1 FROM "order" o
    WHERE o.id = customer.order_id
    AND o.expires_on = customer.expires_on
    AND o.expires_on < '2024-01-01'
);

-- 分离指定order分区（假设分区名为order_exp_2023_q4）
ALTER TABLE "order" DETACH PARTITION order_exp_2023_q4;

-- 可选：删除已分离的分区表
DROP TABLE order_exp_2023_q4;

可将上述逻辑封装为定时任务（如借助pg_cron扩展），定期执行过期数据清理。

方案三：将customer表按expires_on分区（理想状态） #

为实现高效的分区级数据管理，将customer表也改为按expires_on范围分区，与order表的分区逻辑对齐：

-- 重建customer为分区表
CREATE TABLE IF NOT EXISTS customer
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    order_id bigint,
    updated_on timestamp with time zone NOT NULL,
    expires_on timestamp with time zone NOT NULL,
    CONSTRAINT customer_pk PRIMARY KEY (id, expires_on)
) PARTITION BY RANGE (expires_on);

-- 创建示例分区
CREATE TABLE customer_exp_2024_q1 PARTITION OF customer
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- 重建级联删除外键
ALTER TABLE customer ADD CONSTRAINT order_fk FOREIGN KEY (expires_on, order_id)
    REFERENCES "order" (expires_on, id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;

此方案下，清理过期数据时只需同时分离并删除order和customer的对应分区，实现最高效的批量数据清理，完全匹配“按expires_on基于order_id对所有表分区”的需求。

注意事项 #

1. 分区表的主键必须包含分区键（expires_on），否则无法创建分区。
2. 外键关联字段需与主表的主键/唯一约束字段完全匹配，此处customer的(expires_on, order_id)与order的(expires_on, id)匹配逻辑合理。
3. 使用pg_cron定时任务需先安装扩展：CREATE EXTENSION pg_cron;

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