PostgreSQL 13.4大表Update语句执行过慢优化求助

基础信息 #

• PostgreSQL版本：13.4
• 服务器配置：32GB内存、8核CPU
• 目标表usage_data：1亿+条记录，表结构：

Column                 |        Datatype 
------------------------------------------------
id                     | bigint (Primary Key)
customer_id            | bigint 
created_date           | timestamp with time zone
user_id                | bigint (Foreign Key)
log_type               | character varying(255)
duration               | numeric
encrypted_ip_address   | text
is_active              | boolean
is_delete              | boolean

• 表索引与约束：

Index List
--------------------
-> idx_18837_primary PRIMARY KEY, btree (id),
-> customer_id_idx btree (customer_id)
-> idx_usage_data_created_date brin (created_date)
-> idx_usage_data_log_type btree (lower(log_type::text))
-> idx_usage_data_is_active_is_delete_created_date_encrypted_ip_address_duration_log_type btree 
   (is_active, is_delete, created_date,encrypted_ip_address, duration,log_type)
-> idx_usage_data_isactive_isdelete btree (is_active, is_delete)
-> idx_usage_data_encrypted_ip_address btree (lower(encrypted_ip_address))
-> idx_user_id btree (user_id)
-> idx_dashboard_rpt btree (customer_id, is_active, is_delete, created_date, encrypted_ip_address)

Unique constraints
-----------------------
-> usage_data_user_id_created_date_log_type_login_duration_key UNIQUE CONSTRAINT, btree 
   (user_id, created_date, log_type, login_duration)
-> ukdx5uuutm8d5rx9yje3r9wn7ok UNIQUE CONSTRAINT, btree (user_id, created_date, log_type, login_duration)

Foreign-key constraints
-----------------------
    "fk29o8rfxe366qmvg7fhmaafmv9" FOREIGN KEY (user_id) REFERENCES user(id)

问题 #

执行以下更新语句耗时近1.5分钟（执行计划显示实际耗时约31秒），涉及107406条记录，目标是将执行时间压缩至1分钟以内：

UPDATE usage_data SET is_active=false, is_delete=true WHERE user_id = 201; 

执行计划：

QUERY PLAN
----------------------------------------
Update on public.usage_data (cost=0.56..258643.39 rows=82093 width=541) (actual time=29869.115..29869.115 rows=0 loops=1)
Buffers: shared hit=6555539 read=275043 dirtied=226100 written=28004
-> Index Scan using idx_user_id on public.usage_data (cost=0.56..258643.39 rows=82093 width=541) (actual
time=7.389..581.313 rows=107486 loops=1)
Output: id,customer_id, created_date, user_id , log_type, duration, encrypted_ip_address, false, is_delete
Index Cond: (usage_data.user_id = 201)
Buffers: shared hit=14926 read=75213 dirtied=470 written=9259
Planning Time: 0.150 ms
Trigger RI_ConstraintTrigger_c_24524390 for constraint fk29o8rfxe366qmvg7fhmaafmv9: time=1036.717 calls=107406
JIT:
Functions: 4
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.925 ms, Inlining 0.000 ms, Optimization 0.516 ms, Emission 6.607 ms, Total 8.048 ms
Execution Time: 30913.532 ms
(13 rows)

已尝试操作 #

修改postgresql.conf将shared_buffers从500MB调整为4GB，无明显性能提升。

优化建议 #

1. 拆分批量更新 #

单次更新10万条记录会生成大量WAL日志，且长时间持有锁。拆分小批量更新，每次处理1000-5000条，降低IO峰值与锁竞争：

WHILE EXISTS (SELECT 1 FROM usage_data WHERE user_id = 201 AND is_active = true) LOOP
    WITH batch AS (
        SELECT id FROM usage_data WHERE user_id = 201 AND is_active = true LIMIT 1000
    )
    UPDATE usage_data 
    SET is_active=false, is_delete=true 
    WHERE id IN (SELECT id FROM batch);
    COMMIT;
END LOOP;

循环执行直到所有目标记录更新完成，每次提交后释放锁，减少资源占用。

2. 临时禁用外键触发器 #

执行计划显示外键触发器RI_ConstraintTrigger_c_24524390耗时约1秒，若确认user_id=201在关联表user中存在，可临时禁用触发器加速更新：

ALTER TABLE usage_data DISABLE TRIGGER RI_ConstraintTrigger_c_24524390;
UPDATE usage_data SET is_active=false, is_delete=true WHERE user_id = 201;
ALTER TABLE usage_data ENABLE TRIGGER RI_ConstraintTrigger_c_24524390;

注意：操作期间需禁止对usage_data和user表的写入操作，避免数据完整性问题。

3. 调整WAL相关参数 #

针对32GB内存的服务器，优化WAL写入参数，减少IO压力：
修改postgresql.conf：

wal_buffers = 64MB          # 增大WAL缓冲区，减少磁盘写入次数
checkpoint_completion_target = 0.9  # 平滑checkpoint过程，降低IO峰值
max_wal_size = 16GB         # 增大WAL日志上限，避免频繁触发checkpoint
min_wal_size = 4GB

修改后重启PostgreSQL生效。

4. 临时删除冗余索引 #

更新is_active和is_delete会触发多个包含这两个字段的索引更新，若这些索引非业务刚需，可临时删除后更新，再重建：

-- 删除索引
DROP INDEX idx_usage_data_is_active_is_delete_created_date_encrypted_ip_address_duration_log_type;
DROP INDEX idx_usage_data_isactive_isdelete;
DROP INDEX idx_dashboard_rpt;

-- 执行更新
UPDATE usage_data SET is_active=false, is_delete=true WHERE user_id = 201;

-- 重建索引（可选CONCURRENTLY避免锁表）
CREATE INDEX idx_usage_data_is_active_is_delete_created_date_encrypted_ip_address_duration_log_type ON usage_data (is_active, is_delete, created_date,encrypted_ip_address, duration,log_type);
CREATE INDEX idx_usage_data_isactive_isdelete ON usage_data (is_active, is_delete);
CREATE INDEX idx_dashboard_rpt ON usage_data (customer_id, is_active, is_delete, created_date, encrypted_ip_address);

若业务不允许长时间无索引，可使用CREATE INDEX ... CONCURRENTLY，但重建时间会更长。

5. 启用并行更新 #

PostgreSQL 13支持并行查询，可尝试强制开启并行优化更新：

SET max_parallel_workers_per_gather = 4;
UPDATE usage_data SET is_active=false, is_delete=true WHERE user_id = 201;

效果取决于数据分布，建议测试验证。

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