insert on conflict - 合并写 (消除不必要更新)

本文探讨了在PostgreSQL中优化合并写入操作的方法,特别是在批量操作中避免不必要的更新,以减少写入放大现象。通过调整INSERTINTOONCONFLICT语法,仅在数据确实发生变化时进行更新,从而提高数据库性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

作者
digoal

日期
2018-04-03

标签
PostgreSQL , 合并写 , insert on conflict , 不必要更新

背景
合并更新的应用非常广泛,存在则更新,不存在则写入。

但是在某些场景中,存在并不一定要更新,原因是新的内容可能和老的内容完全一致。这种更新操作是完全没有必要的。

因为PG是多版本的,更新会产生新的TUPLE版本,如果这种没必要的更新很多,只会给数据库带来额外的负担同时影响性能。特别体现在批量操作中。

例如电商场景中,运营人员会将需要操作的一批店铺导入到操作表,在操作表中再进行数据的操作。导入动作就是一个合并写的动作,每一批导入的店铺可能在操作表中已存在并且记录完全没有变化,如果不注意INSERT INTO ON CONFLICT语法的使用,会导致写入放大。

例子
1、新建测试表

create table tbl(
c1 int,
c2 int,
c3 int,
c4 int,
c5 timestamp,
unique (c1,c2)
);
2、使用普通的insert into on conflict合并写入,存在写入放大

insert into tbl
select id,id,1,random(),now() from generate_series(1,1000000) t(id)
on conflict(c1,c2)
do update
set
c3=excluded.c3,c4=excluded.c4,c5=excluded.c5;
每一次操作都会更新所有记录

INSERT 0 1000000

INSERT 0 1000000

INSERT 0 1000000
3、优化方法,加入更新条件,避免未变化的记录被更新

例如当c3,c4没有变化时,不更新。

where
tbl.c3 is distinct from excluded.c3 or
tbl.c4 is distinct from excluded.c4;
SQL如下

insert into tbl
select id,id,1,random(),now() from generate_series(1,1000000) t(id)
on conflict(c1,c2)
do update
set
c3=excluded.c3,c4=excluded.c4,c5=excluded.c5
where
tbl.c3 is distinct from excluded.c3 or
tbl.c4 is distinct from excluded.c4;
此时每次更新的就是那些真正发生了变化的记录

INSERT 0 500172

INSERT 0 500383

INSERT 0 500664
参考
https://www.postgresql.org/docs/10/static/sql-insert.html

[ WITH [ RECURSIVE ] with_query [, …] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, …] ) ]
[ OVERRIDING { SYSTEM | USER} VALUE ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, …] ) [, …] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ]

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
ON CONSTRAINT constraint_name  

and conflict_action is one of:

DO NOTHING  
DO UPDATE SET { column_name = { expression | DEFAULT } |  
                ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |  
                ( column_name [, ...] ) = ( sub-SELECT )  
              } [, ...]  
          [ WHERE condition ]  

is distinct from是一种不等于的用法,其中包括NULL值的处理(认为null is not distinct from null is TRUE)。两个NULL值返回FALSE,一个NULL值返回TRUE。

null is distinct from null 返回false

null is distinct from nonnull 返回true

nonnull is distinct from nonnull 根据实际的VALUE来判断是否相等,相等返回false,不相等返回true。

当要判断NULL时,这个比=操作符好用,使用=等操作符时,通常底层函数是strict的,所以当操作数包含NULL值时,操作符的结果也返回NULL而不是我们想要的true或false。

转载:https://github.com/digoal/blog/blob/master/201804/20180403_01.md

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值