PostgreSQL 不重建事实表新增列及带会话计数的增量数据写入实现

第一步：在线新增目标字段 #

PostgreSQL 支持在线加列，全程不会重写整个表结构，无长锁影响业务运行：

• 直接执行加列语句，先设置字段为可空，避免触发全表重写的性能开销：

ALTER TABLE fact_sessions ADD COLUMN session_count integer;

• 提前创建匹配计算逻辑的辅助索引（用CONCURRENTLY模式，不会锁表写入），大幅提升后续计算和查询速度：

CREATE INDEX CONCURRENTLY idx_fact_sessions_client_date ON fact_sessions(client_id, session_date);

第二步：存量历史数据回填 #

复用已调试通的窗口函数计算逻辑，对百万级存量数据做批量更新（可按client_id分片执行，拆成短事务避免大事务锁表）：

• 核心回填逻辑，加入session_id作为同日期会话的排序兜底，避免同日期数据排序不稳定导致编号错乱：

WITH historical_count_calc AS (
    SELECT
        session_id,
        ROW_NUMBER() OVER (
            PARTITION BY client_id
            ORDER BY session_date, session_id
        ) AS calc_session_count
    FROM fact_sessions
)
UPDATE fact_sessions fs
SET session_count = hcc.calc_session_count
FROM historical_count_calc hcc
WHERE fs.session_id = hcc.session_id;

• 回填完成、数据校验无误后，再补充非空约束，此时因为全表字段已经有值，约束添加是秒级操作：

ALTER TABLE fact_sessions ALTER COLUMN session_count SET NOT NULL;

第三步：固化增量数据自动计算流程 #

把已调试通的新数据计算逻辑直接嵌入日常增量加载流程，每次同步新会话时自动完成编号计算，直接追加写入既有事实表：

• 固定增量加载逻辑，每次同步时替换临时的新会话源表即可：

WITH existing_client_max AS (
    -- 获取每个客户当前已有的最大会话编号
    SELECT
        client_id,
        MAX(session_count) AS current_max_seq
    FROM fact_sessions
    GROUP BY client_id
),
incremental_calc AS (
    SELECT
        ns.session_id,
        ns.client_id,
        ns.advisor_id,
        ns.session_date,
        -- 新数据按客户、日期排序后，叠加历史最大编号
        ROW_NUMBER() OVER (
            PARTITION BY ns.client_id
            ORDER BY ns.session_date, ns.session_id
        ) + COALESCE(ecm.current_max_seq, 0) AS calc_session_count
    FROM new_sessions ns -- 替换为日常同步的增量临时表
    LEFT JOIN existing_client_max ecm
        ON ns.client_id = ecm.client_id
)
-- 计算完成后直接插入既有事实表，无需重建表结构
INSERT INTO fact_sessions (session_id, client_id, advisor_id, session_date, session_count)
SELECT session_id, client_id, advisor_id, session_date, calc_session_count
FROM incremental_calc;

关键注意点 #

• 所有排序环节必须加入session_id作为同日期数据的排序兜底，避免同一客户同日多会话时排序逻辑不稳定，导致编号计算错误
• 百万级存量数据回填时，可按client_id范围拆分为多批次执行，避免单事务过大导致的锁表、WAL日志暴涨问题

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