postgreSQL Duplicate Key 영향도

1. 채번 증가

 

개발팀에서 UPSERT 를 위해 주로 사용하는 INSERT ON CONFLICT DO UPDATE 구문에서 UPDATE 이후 SEQ가 증가하는 상황에 대해 확인하였습니다.

시퀀스 객체는 INSERT가 발생하여 채번이 된 이후 INSERT 구문이 롤백이 되더라도 채번 자체를 취소하지 않습니다.

이는 MySQL의 Auto increment 에서도 동일하게 발생하는데 INSERT ON DUPLICATE 로 UPDATE를 진행한 이후 다음 INSERT는 마지막 시퀀스에서 UPDATE가 일어난 만큼 점프한 값으로 입력이 됩니다.

postgreSQL도 동일한 현상이 발생하였고 약간의 차이점은 postgreSQL의 경우 HEAP 테이블로 입력 순으로 정렬이 되는데 UPDATE가 DELETE, INSERT로 구현되어 있어 

중간에 있던 데이터가 가장 최근 마지막에 입력된 것 처럼 순서가 변하는 점이었습니다.

BIGSERIAL, GENERATED ALWAYS AS IDENTITY 2개의 값으로 테스트를 하였는데 사실 내부적으로 시퀀스 객체를 쓰는 공통점이 있다보니 동일한 결과가 발생한 것으로 보입니다.

 

create table MINJAE_TSET01 (seq BIGSERIAL primary key, col1 int, col2 varchar);
 
create table MINJAE_TSET02 (seq bigint GENERATED ALWAYS AS identity primary key, col1 int, col2 varchar);
 
create unique index ux_test01 on MINJAE_TSET01 (col1);
create unique index ux_test02 on MINJAE_TSET02 (col1);
 
insert into MINJAE_TSET01 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET01 (col1) values(2)  on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET01 (col1) values(3) on conflict (col1) do update set col2 = '충돌';
select * from MINJAE_TSET01;
insert into MINJAE_TSET01 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET01 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET01 (col1) values(4) on conflict (col1) do update set col2 = '충돌';
select * from MINJAE_TSET01;
 
insert into MINJAE_TSET02 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET02 (col1) values(2)  on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET02 (col1) values(3) on conflict (col1) do update set col2 = '충돌';
select * from MINJAE_TSET02;
insert into MINJAE_TSET02 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET02 (col1) values(1) on conflict (col1) do update set col2 = '충돌';
insert into MINJAE_TSET02 (col1) values(4) on conflict (col1) do update set col2 = '충돌';

 

 

BIGSERIAL 첫번째 조회	BIGSERIAL 두째 조회

 

GENERATED ALWAYS AS identity 첫번째 조회	GENERATED ALWAYS AS identity 두번째 조회

 

 

2. Dead Tuple 증가

Insert on Conflict 구문을 사용하여 upsert 처리를 하는 과정에서 발생할 수 있는 dead tuple과 이를 회피하는 방안에 대하여 확인.

dead tuple은 delete, update 외에도 insert에서 duplicate key 중복이 발생 경우에도 생성됩니다.

 

create table minjae_test (col1 int, col2 varchar);
CREATE TABLE
 
create unique index minjae_test_ux_col1 on minjae_test (col1);
CREATE INDEX
 
--10건 입력
 
insert into minjae_test (col1,col2) values (1,'y');
insert into minjae_test (col1,col2) values (2,'y');
insert into minjae_test (col1,col2) values (3,'y');
insert into minjae_test (col1,col2) values (4,'y');
insert into minjae_test (col1,col2) values (5,'y');
insert into minjae_test (col1,col2) values (6,'y');
insert into minjae_test (col1,col2) values (7,'y');
insert into minjae_test (col1,col2) values (8,'y');
insert into minjae_test (col1,col2) values (9,'y');
insert into minjae_test (col1,col2) values (10,'y');
 
 
 
--Dead Tuple 확인 쿼리
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
      pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    s.n_mod_since_analyze, 
    s.n_ins_since_vacuum ,
    s.last_autovacuum AS Autovacuum,
    s.last_autoanalyze AS Autoanalyze,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
join pg_stat_user_tables as S on C.relname = S.relname
WHERE  c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;
 
 schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          10 |         10 |           0 |                  10 |                 10 |            |             | 32 kB               | 8192 bytes
 
 
--일반적인 INSERT에서 Key 중복으로 인한 Dead Tuple 생성
 
insert into minjae_test (col1,col2) values (1,'N');
ERROR:  duplicate key value violates unique constraint "minjae_test_ux_col1"
DETAIL:  Key (col1)=(1) already exists.
 
 
-- dead tuple 확인
 schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          11 |         10 |           1 |                  10 |                 11 |            |             | 32 kB               | 8192 bytes
 
 
--회피 방법 (do nothing 처리)
 
minjae_test=> insert into minjae_test (col1,col2) values (1,'N') on conflict (col1) do nothing;
INSERT 0 0
 
 schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          11 |         10 |           1 |                  10 |                 11 |            |             | 32 kB               | 8192 bytes
 
 
 
--필요 데이터만 업데이트
do update를 처리할 때 중복 외에 추가적인 조건을 사용하여 업데이트 양을 줄일 수 있다면 Dead Tuple 발생 양을 줄일 수 있습니다.
 
-- 아래 쿼리는 2개의 데이터만 업데이트가 필요하지만 로직상 별도 확인이 없어 5개의 업데이트 발생하고 5개의 데드 튜플이 생성됩니다.
 
insert into minjae_test (col1,col2)
values (1,'y'),(2,'y'),(3,'y'),(4,'n'),(5,'n')
ON CONFLICT (col1)
do update set col2 = EXCLUDED.col2;
INSERT 0 5
 
 schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          16 |         10 |           6 |                  15 |                 11 |            |             | 32 kB               | 8192 bytes
 
--동일하지 않은 값만 업데이트 하도록 조건을 추가하면 아래의 경우 2개의 데이터만 업데이트되고 Dead Tuple 또한 2건만 생성됩니다.
 
insert into minjae_test (col1,col2)
values (1,'y'),(2,'y'),(3,'y'),(6,'n'),(7,'n')
ON CONFLICT (col1)
do update set col2 = EXCLUDED.col2
WHERE minjae_test.col2 != EXCLUDED.col2;
 
INSERT 0 2
 
 schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          18 |         10 |           8 |                  17 |                 11 |            |             | 32 kB               | 8192 bytes
 
 
-- DO NOTHING과 마찬가지로 해당 구문은 키 중복으로 인한 INSERT 롤백이 발생하여도 Dead Tuple이 늘어나지 않습니다.
minjae_test=> insert into minjae_test (col1,col2)
minjae_test-> values (11,'y'),(12,'y')
minjae_test-> ON CONFLICT (col1)
minjae_test-> do update set col2 = EXCLUDED.col2
minjae_test-> WHERE minjae_test.col2 != EXCLUDED.col2;
INSERT 0 2
minjae_test=> insert into minjae_test (col1,col2)
minjae_test-> values (11,'y'),(12,'y')
minjae_test-> ON CONFLICT (col1)
minjae_test-> do update set col2 = EXCLUDED.col2
minjae_test-> WHERE minjae_test.col2 != EXCLUDED.col2;
INSERT 0 0
 
  schema_name | table_name  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+-------------+-------------+------------+-------------+---------------------+--------------------+------------+-------------+---------------------+---------------
 public      | minjae_test |          18 |         10 |           8 |                  17 |                 11 |            |             | 32 kB               | 8192 bytes