vacuum & long transaction

auto vacuum이나 수동으로 vacuum 명령을 수행하여도 실제 vacuum이 수행되지 않는 경우가 발생할 수 있다.

DML이 빈번히 발생하고 있는 테이블이라서 auto vacuum 이나 vacuum이 수행 되어서 dead tuple을 정리해 줘야 하는데 그렇지 못한 경우가 발생

 

원인 :

    DML이 빈번히 발생하는 테이블에 대한 조회 쿼리가 오래 수행되고 있는 중에 vacuum이나 auto vacuum이 수행 되면 해당 명령은 정상적으로 수행 되었다고 나옴

    dead_tuple도 삭제 되었다고 나오지만, 실제 블럭을 확인해 보면 정리가 되진 않음 --> 테이블 사이즈는 계속 증가

 

시나리오 :

    세션 1에서 update 수행  --> dead tuple 발생

    세션 2에서 해당 테이블을 select하는 long query 수행

    세션 1에서 vacuum 수행  --> long 쿼리와 상관없는 테이블은 정상적으로 vacuum이 수행 됨

 

세션 1	세션 2
drop table if exists t1 ; drop table if exists t2 ; create table t1 ( id int , name varchar(10)) ; create table t2 ( id int , name varchar(10)) ; insert into t1 values ( 1 , 'name1') ; insert into t2 values ( 1 , 'name1') ; update t1 set name = 'changed' where id = 1 ; update t2 set name = 'changed' where id = 1 ; select c.relname, c.reltuples , n_tup_ins , n_tup_upd , n_live_tup , n_dead_tup , last_vacuum from    pg_class c ,         pg_stat_all_tables s where   c.relname = s.relname and     c.relname in ('t1','t2') ; +---------+-----------+-----------+-----------+------------+------------+-------------+ | relname | reltuples | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup | last_vacuum | +---------+-----------+-----------+-----------+------------+------------+-------------+ | t1      |         0 |         0 |         0 |          0 |          0 | NULL        | | t2      |         0 |         0 |         0 |          0 |          0 | NULL        | +---------+-----------+-----------+-----------+------------+------------+-------------+ t1 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   1270 |   1272 |     0 | (0,2)  | |     2 |   1272 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+ t2 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   1271 |   1273 |     0 | (0,2)  | |     2 |   1273 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+
	select * , pg_sleep(120) from t2 ;
vacuum t1 ; vacuum t2 ; select c.relname, c.reltuples , n_tup_ins , n_tup_upd , n_live_tup , n_dead_tup , last_vacuum from    pg_class c ,         pg_stat_all_tables s where   c.relname = s.relname and     c.relname in ('t1','t2') ; +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ | relname | reltuples | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup |          last_vacuum          | +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ | t1      |         1 |         1 |         1 |          1 |          0 | 2022-07-01 11:56:24.216476+09 | | t2      |         0 |         1 |         1 |          0 |          0 | 2022-07-01 11:56:25.023881+09 | +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ t1 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   NULL |   NULL |  NULL | NULL   | |     2 |   1272 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+ t2 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   1271 |   1273 |     0 | (0,2)  | |     2 |   1273 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+
세션2의 쿼리가 끝난 후 조회 t2 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   1271 |   1273 |     0 | (0,2)  | |     2 |   1273 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+ vacuum t2 ; +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ | relname | reltuples | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup |          last_vacuum          | +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ | t1      |         1 |         1 |         1 |          1 |          0 | 2022-07-01 11:56:24.216476+09 | | t2      |         1 |         1 |         1 |          1 |          0 | 2022-07-01 12:00:27.061505+09 | +---------+-----------+-----------+-----------+------------+------------+-------------------------------+ t2 +-------+--------+--------+-------+--------+ | tuple | t_xmin | t_xmax | t_cid | t_ctid | +-------+--------+--------+-------+--------+ |     1 |   NULL |   NULL |  NULL | NULL   | |     2 |   1273 |      0 |     0 | (0,2)  | +-------+--------+--------+-------+--------+