PostgreSQL：如何在已有数据表上无锁创建分区？当前操作引发死锁

针对已有数据的表，直接转换为分区表会产生长时间锁表甚至死锁，推荐使用**分区交换（EXCHANGE PARTITION）**的方式实现低锁迁移，核心思路是通过元数据交换替代数据拷贝，最大程度减少锁表时间。

核心步骤 #

1. 复制原表结构创建分区父表 #

确保分区父表与原表结构完全一致（包括约束、索引、触发器、默认值等）：

CREATE TABLE big_table_partitioned (
    LIKE big_table INCLUDING ALL
) PARTITION BY RANGE (created_at); -- 按需选择分区策略：RANGE/LIST/HASH

INCLUDING ALL会完整复制原表的所有属性，避免后续交换时结构不匹配。

2. 创建所需分区 #

根据原表数据分布和业务需求，创建覆盖历史数据和未来数据的分区：

-- 历史数据分区（示例按年份划分）
CREATE TABLE big_table_2020 PARTITION OF big_table_partitioned
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE big_table_2021 PARTITION OF big_table_partitioned
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE big_table_2022 PARTITION OF big_table_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE big_table_2023 PARTITION OF big_table_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 未来数据分区（接收新写入数据）
CREATE TABLE big_table_future PARTITION OF big_table_partitioned
FOR VALUES FROM ('2024-01-01') TO ('MAXVALUE');

3. 转发新写入数据到分区表 #

在交换原表之前，创建触发器将原表的新写入操作转发到分区表，避免数据丢失：

-- 创建转发触发器函数
CREATE OR REPLACE FUNCTION forward_to_partitioned()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO big_table_partitioned VALUES (NEW.*);
    RETURN NULL; -- 不再写入原表
END;
$$ LANGUAGE plpgsql;

-- 给原表绑定触发器
CREATE TRIGGER trigger_forward_to_partitioned
BEFORE INSERT ON big_table
FOR EACH ROW EXECUTE FUNCTION forward_to_partitioned();

4. 交换原表与临时分区 #

如果原表所有数据能被一个临时分区覆盖，先创建临时分区，再通过元数据交换将原表数据转移到分区表：

-- 创建覆盖所有原数据的临时分区
CREATE TABLE big_table_temp PARTITION OF big_table_partitioned
FOR VALUES FROM ('2020-01-01') TO ('2024-01-01');

-- 执行分区交换（元数据操作，锁表时间极短）
ALTER TABLE big_table_partitioned EXCHANGE PARTITION big_table_temp WITH TABLE big_table;

这一步是原子操作，仅交换表的元数据，不会拷贝数据，因此锁表时间可以忽略不计。

5. 拆分临时分区（可选） #

如果需要将临时分区拆分为更细粒度的子分区，可在交换完成后执行拆分：

-- 拆分临时分区为2020-2023的子分区
ALTER TABLE big_table_partitioned SPLIT PARTITION big_table_temp AT ('2021-01-01') INTO (PARTITION big_table_2020, PARTITION big_table_temp_remaining);

ALTER TABLE big_table_partitioned SPLIT PARTITION big_table_temp_remaining AT ('2022-01-01') INTO (PARTITION big_table_2021, PARTITION big_table_temp_remaining);

ALTER TABLE big_table_partitioned SPLIT PARTITION big_table_temp_remaining AT ('2023-01-01') INTO (PARTITION big_table_2022, PARTITION big_table_2023);

-- 删除剩余的临时分区
DROP TABLE big_table_temp_remaining;

拆分操作仅锁定被拆分的分区，不影响其他分区的正常读写。

6. 切换表名（业务无感知） #

交换完成后，原表已为空，可删除原表并将分区表重命名为原表名，保证业务代码无需修改：

DROP TABLE big_table;
ALTER TABLE big_table_partitioned RENAME TO big_table;

7. 更新统计信息 #

最后更新分区表的统计信息，确保查询优化器能正确生成执行计划：

ANALYZE big_table;

注意事项 #

• 结构一致性：分区与原表必须完全一致（包括索引、约束、触发器、列顺序等），否则交换会失败。
• 外键处理：如果原表有外键，需先删除外键，交换完成后再重建分区表的外键。
• 业务低峰期操作：虽然交换锁表时间极短，但建议在业务低峰期执行，避免极端情况下的短暂阻塞。
• PostgreSQL版本：该方法适用于PostgreSQL 11及以上版本（支持声明式分区和EXCHANGE PARTITION）。

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