PostgreSQL 300列表触发器批量插入过慢问题咨询

你遇到的批量插入性能问题核心在于行级触发器的设计缺陷：

1. FOR EACH ROW 触发器会在每插入一行时执行一次，批量插入1000条就会触发1000次函数调用
2. 触发器函数内的三次UPDATE都是全表扫描并关联，每次都会遍历整个patients表，重复计算所有行的quarter/month/daily值，随着数据量增长，性能消耗呈指数级上升
3. 实际业务表有300列，全表更新的IO开销会被进一步放大

方案1：改用生成列（最优选择） #

quarter/month/daily都是基于registration_date的派生值，完全不需要触发器维护，PostgreSQL的生成列可以自动计算并存储这些值，插入/更新时自动同步，零额外性能开销：

-- 先删除原有冗余字段
ALTER TABLE patients
    DROP COLUMN daily,
    DROP COLUMN month,
    DROP COLUMN quarter;

-- 添加生成列，自动计算派生值
ALTER TABLE patients
    ADD COLUMN daily varchar(8) GENERATED ALWAYS AS (
        extract(year from registration_date)::text || to_char(registration_date, 'MMDD')
    ) STORED,
    ADD COLUMN month varchar(6) GENERATED ALWAYS AS (
        extract(year from registration_date)::text || to_char(registration_date, 'MM')
    ) STORED,
    ADD COLUMN quarter varchar(6) GENERATED ALWAYS AS (
        extract(year from registration_date)::text || 'Q' || extract(quarter from registration_date)::text
    ) STORED;

-- 删除原有触发器（不再需要）
DROP TRIGGER IF EXISTS trigger_update_data_after_insert_patients ON patients;
DROP FUNCTION IF EXISTS update_data_after_insert_data_into_patients();

优势 #

• 插入数据时无需手动指定daily/month/quarter，数据库自动计算
• 批量插入性能和普通插入完全一致，无额外开销
• 避免触发器带来的锁冲突和重复计算

方案2：修改为语句级触发器（保留触发器场景） #

如果必须保留触发器逻辑（比如有额外业务规则），将行级触发器改为语句级触发器，仅在批量插入完成后执行一次，且只更新本次插入的行：

-- 替换原有触发器函数
CREATE OR REPLACE FUNCTION update_data_after_insert_data_into_patients() 
RETURNS trigger AS
$$BEGIN
    -- 仅更新本次INSERT操作插入的行，通过主键关联避免全表扫描
    UPDATE patients t1
    SET 
        quarter = extract(year from t1.registration_date)::text || 'Q' || extract(quarter from t1.registration_date)::text,
        month = extract(year from t1.registration_date)::text || to_char(t1.registration_date, 'MM'),
        daily = extract(year from t1.registration_date)::text || to_char(t1.registration_date, 'MMDD')
    FROM NEW t2
    WHERE t1.id = t2.id;

    RETURN NULL; -- 语句级触发器返回NULL即可
END;
$$ LANGUAGE plpgsql;

-- 删除原有行级触发器，创建语句级触发器
DROP TRIGGER IF EXISTS trigger_update_data_after_insert_patients ON patients;
CREATE TRIGGER trigger_update_data_after_insert_patients
    AFTER INSERT ON patients
    FOR EACH STATEMENT 
EXECUTE PROCEDURE update_data_after_insert_data_into_patients();

优势 #

• 批量插入仅触发一次函数调用，而非每行一次
• 仅更新本次插入的行，避免全表扫描
• 合并三次UPDATE为一次，减少IO操作

方案3：临时禁用触发器（批量导入场景） #

如果是一次性的批量导入任务，可以临时禁用触发器，插入完成后一次性更新所有派生值：

-- 禁用触发器
ALTER TABLE patients DISABLE TRIGGER trigger_update_data_after_insert_patients;

-- 执行批量插入（推荐使用多行VALUES或COPY命令提升插入速度）
INSERT INTO public.patients
("name", registration_date, age, address, country, city, phone_number, education, occupation, marital_status, "E-mail")
VALUES
('Adam', '2022-08-17 19:01:10-08', 24, '', '', '', 1245578, '', '', '', ''),
('Bob', '2023-03-20 10:30:00+08', 31, '', '', '', 9876543, '', '', '', '');

-- 一次性更新所有需要计算的行（可通过时间范围过滤本次插入的数据）
UPDATE patients
SET 
    quarter = extract(year from registration_date)::text || 'Q' || extract(quarter from registration_date)::text,
    month = extract(year from registration_date)::text || to_char(registration_date, 'MM'),
    daily = extract(year from registration_date)::text || to_char(registration_date, 'MMDD')
WHERE quarter IS NULL; -- 或使用 registration_date BETWEEN '起始时间' AND '结束时间'

-- 重新启用触发器
ALTER TABLE patients ENABLE TRIGGER trigger_update_data_after_insert_patients;

• 批量插入时优先使用COPY命令，比多行INSERT性能更高
• 确保registration_date或主键id有索引（方案2中依赖主键关联，主键默认已有索引）
• 实际业务表有300列，避免全表更新，仅修改必要字段

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