如何在Postgres中最优添加带默认值新列？10M行生产表锁优化

核心思路 #

先通过元数据操作创建可空列（避免全表扫描锁表），再用**键集分页（Keyset Pagination）**替代OFFSET进行批量更新，最后按需设置列非空。这种方式能最小化锁冲突，避免大事务和OFFSET带来的性能问题。

分步实现 #

1. 创建可空新列（轻量无锁）

Postgres 11及以上版本中，添加带默认值的可空列是元数据级操作，不会扫描全表，几乎无锁，执行速度极快：

ALTER TABLE your_table ADD COLUMN new_column data_type DEFAULT your_default_value;

注：若你的Postgres版本低于11，需先创建无默认值的可空列，后续再补加默认值：

ALTER TABLE your_table ADD COLUMN new_column data_type;

2. 键集分页批量更新（替代OFFSET）

由于UUID主键无单调性，但Postgres支持对UUID类型稳定排序，我们可以基于上一批次的最后一个UUID作为下一批的起始条件，避免OFFSET大值导致的全表扫描性能损耗。同时使用FOR UPDATE SKIP LOCKED跳过已被业务操作锁定的行，减少冲突。

单次批量更新SQL示例

第一次执行：

WITH batch AS (
    SELECT id
    FROM your_table
    WHERE new_column IS NULL
    ORDER BY id
    LIMIT 10000
    FOR UPDATE SKIP LOCKED
)
UPDATE your_table t
SET new_column = your_default_value
FROM batch b
WHERE t.id = b.id;

后续循环执行（替换'last_uuid_from_previous_batch'为上一批的最后一个UUID值）：

WITH batch AS (
    SELECT id
    FROM your_table
    WHERE new_column IS NULL
      AND id > 'last_uuid_from_previous_batch'
    ORDER BY id
    LIMIT 10000
    FOR UPDATE SKIP LOCKED
)
UPDATE your_table t
SET new_column = your_default_value
FROM batch b
WHERE t.id = b.id;

外部程序循环实现（Python示例）

用脚本自动循环执行批量更新，直到所有行处理完成：

import psycopg2

# 数据库连接配置
DB_CONFIG = {
    "dbname": "your_prod_db",
    "user": "your_user",
    "password": "your_pwd",
    "host": "your_db_host"
}

BATCH_SIZE = 10000
DEFAULT_VALUE = "your_default_value"  # 替换为实际默认值
COLUMN_NAME = "new_column"
TABLE_NAME = "your_table"

def main():
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    last_uuid = None
    total_updated = 0

    while True:
        # 获取当前批次的UUID
        if last_uuid is None:
            fetch_query = f"""
                SELECT id
                FROM {TABLE_NAME}
                WHERE {COLUMN_NAME} IS NULL
                ORDER BY id
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            """
            cur.execute(fetch_query, (BATCH_SIZE,))
        else:
            fetch_query = f"""
                SELECT id
                FROM {TABLE_NAME}
                WHERE {COLUMN_NAME} IS NULL
                  AND id > %s
                ORDER BY id
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            """
            cur.execute(fetch_query, (last_uuid, BATCH_SIZE))
        
        batch_ids = [row[0] for row in cur.fetchall()]
        if not batch_ids:
            break
        
        # 执行批量更新
        update_query = f"""
            UPDATE {TABLE_NAME}
            SET {COLUMN_NAME} = %s
            WHERE id = ANY(%s)
        """
        cur.execute(update_query, (DEFAULT_VALUE, batch_ids))
        conn.commit()

        batch_count = len(batch_ids)
        total_updated += batch_count
        last_uuid = batch_ids[-1]
        print(f"已更新 {batch_count} 行，累计更新 {total_updated} 行，最后处理UUID: {last_uuid}")

    # 若需要将列设置为非空，执行以下语句
    print("所有行更新完成，开始设置列非空...")
    alter_query = f"""
        ALTER TABLE {TABLE_NAME} ALTER COLUMN {COLUMN_NAME} SET NOT NULL
    """
    cur.execute(alter_query)
    conn.commit()

    cur.close()
    conn.close()
    print(f"操作完成，累计更新 {total_updated} 行")

if __name__ == "__main__":
    main()

关键注意事项 #

• 批次大小调整：根据行数据大小、数据库负载调整BATCH_SIZE（5k-20k均可），避免单批次执行时间过长。
• 事务控制：每批次单独提交，避免长事务导致的锁占用和WAL日志膨胀。
• 执行时机：尽量在业务低峰期执行，减少对生产流量的影响。
• 锁冲突规避：FOR UPDATE SKIP LOCKED确保批量更新不会阻塞正常业务操作，也不会被业务操作阻塞。
• 版本兼容：Postgres 9.5及以上支持SKIP LOCKED，若版本更低，可去掉该子句，但需承担锁等待风险。

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