vacuum / vacuum full 의 차이

 

- postgresql 은 update 시 delete + insert 방식으로 처리
- DML 수행 후 더이상 사용되지 않는 과거데이터공간(dead tuple)이 재활용되는 시점은 vacuum 작업 이후
- vacuum full을 수행했을 경우 신규 테이블 생성 후 기존 테이블 삭제 방식으로 진행되어 디스크 공간 회수 가능하지만 작업진행동안 table lock 발생하므로 주의(작업 여유 공간도 확인(기존테이블 + 신규테이블))

 

테스트 준비

테이블 생성 및 autovacuum 비활성

postgres=# create table sk.tab1 as SELECT * FROM pg_class ;
SELECT 346
postgres=# ALTER TABLE sk.tab1 SET (autovacuum_enabled = false);
ALTER TABLE
postgres=# SELECT relname, reloptions
postgres-# FROM pg_class
postgres-# WHERE reloptions IS NOT NULL
postgres-# ;
 relname  |         reloptions         
----------+----------------------------
 pg_stats | {security_barrier=true}
 tab1     | {autovacuum_enabled=false}

postgres=# SELECT count(*) FROM sk.tab1 ;
 count 
-------
   346
(1 row)

postgres=# SELECT oid, relname, relfilenode
postgres-# FROM pg_catalog.pg_class 
postgres-# WHERE relname = 'tab1'
postgres-# ;
  oid  | relname | relfilenode 
-------+---------+-------------
 16555 | tab1    |       16555
(1 row)

 
delete 작업 시 dead tupple 변화량 확인(commit 시점에 live tuple -> dead tuple 로 변함)

postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         346 |        346 |          0 |          100.00 |            0.00 | 80 kB               | 72 kB
(1 row)

postgres=# begin ;
BEGIN
postgres=# DELETE FROM sk.tab1 WHERE relnamespace = 11 ;
DELETE 237
postgres=# SELECT count(*) FROM sk.tab1 ;
 count 
-------
   109
(1 row)

postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         346 |        346 |          0 |          100.00 |            0.00 | 80 kB               | 72 kB
(1 row)

postgres=# commit ;
COMMIT
postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         346 |        109 |        237 |           31.00 |           68.00 | 80 kB               | 72 kB
(1 row)

 
추가 데이터 insert 시 tuple 변화 

postgres=# INSERT INTO sk.tab1 SELECT * FROM pg_class ;
INSERT 0 349
postgres=# SELECT count(*) FROM sk.tab1 ;
 count 
-------
   458
(1 row)

postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         695 |        458 |        237 |           65.00 |           34.00 | 176 kB              | 144 kB
(1 row)

 

반응형

vacuum

vacuum 실행(dead tuple은 없어졌지만 테이블 사이즈는 줄어들지 않음)
기존 dead tuple 공간은 재사용 할 것이다.

postgres=# SELECT oid, relname, relfilenode
postgres-# FROM pg_catalog.pg_class 
postgres-# WHERE relname = 'tab1'
postgres-# ;
  oid  | relname | relfilenode 
-------+---------+-------------
 16555 | tab1    |       16555
(1 row)

postgres=# 
postgres=# vacuum sk.tab1 ;
VACUUM
postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         458 |        458 |          0 |          100.00 |            0.00 | 184 kB              | 144 kB
(1 row)

postgres=# SELECT oid, relname, relfilenode
postgres-# FROM pg_catalog.pg_class 
postgres-# WHERE relname = 'tab1'
postgres-# ;
  oid  | relname | relfilenode 
-------+---------+-------------
 16555 | tab1    |       16555
(1 row)

 

vacuum full

vacuum full 실행(테이블 사이즈 줄어듬)
작업 시 table lock(Access Exclusive Lock) 발생 하므로 주의

postgres=# vacuum full sk.tab1 ;
VACUUM
postgres=# SELECT
postgres-#     n.nspname AS schema_name,
postgres-#     c.relname AS table_name,
postgres-#     pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
postgres-#     pg_stat_get_live_tuples(c.oid) AS live_tuple,
postgres-#     pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
postgres-#     round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
postgres-#     round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
postgres-#     pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
postgres-#     pg_size_pretty(pg_relation_size(c.oid)) as relation_size
postgres-# FROM pg_class AS c
postgres-# JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
postgres-# WHERE 1=1
postgres-# AND pg_stat_get_live_tuples(c.oid) > 0
postgres-# AND c.relname NOT LIKE 'pg_%'
postgres-# --AND n.nspname = 'sk'
postgres-# ORDER BY dead_tuple DESC
postgres-# ;
 schema_name | table_name | total_tuple | live_tuple | dead_tuple | live_tuple_rate | dead_tuple_rate | total_relation_size | relation_size 
-------------+------------+-------------+------------+------------+-----------------+-----------------+---------------------+---------------
 sk          | tab1       |         458 |        458 |          0 |          100.00 |            0.00 | 104 kB              | 96 kB
(1 row)

postgres=# SELECT oid, relname, relfilenode
postgres-# FROM pg_catalog.pg_class 
postgres-# WHERE relname = 'tab1'
postgres-# ;
  oid  | relname | relfilenode 
-------+---------+-------------
 16555 | tab1    |       16562
(1 row)

postgres=#

 
테스트 시 특이한 점
vacuum full 작업 시 사이즈가 줄어드는 것은 확인 되는데 dead tuple 정보가 그대로 남아있다. 여러번 해줘도 마찬가지 vacuum full 이 아닌 vacuum 작업 진행 시 dead tuple 정보가 사라짐 이부분은 잘 모르겠다 버그 일지도...

postgres=# 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,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_table_size,
    pg_size_pretty(pg_relation_size(c.oid)) as table_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE 1=1
AND pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
AND n.nspname = 'sk'
ORDER BY dead_tupple DESC
;
 schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | total_table_size | table_size 
-------------+------------+-------------+------------+-------------+-----------------+-----------------+------------------+------------
 sk          | t1         |     2387968 |    1417216 |      970752 |           59.00 |           40.00 | 473 MB           | 473 MB
(1 row)

postgres=# vacuum full sk.t1 ;
VACUUM
postgres=# 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,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_table_size,
    pg_size_pretty(pg_relation_size(c.oid)) as table_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE 1=1
AND pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
AND n.nspname = 'sk'
ORDER BY dead_tupple DESC
;
 schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | total_table_size | table_size 
-------------+------------+-------------+------------+-------------+-----------------+-----------------+------------------+------------
 sk          | t1         |     2387968 |    1417216 |      970752 |           59.00 |           40.00 | 281 MB           | 281 MB
(1 row)

postgres=# vacuum full sk.t1 ;
VACUUM
postgres=# 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,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_table_size,
    pg_size_pretty(pg_relation_size(c.oid)) as table_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE 1=1
AND pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
AND n.nspname = 'sk'
ORDER BY dead_tupple DESC
;
 schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | total_table_size | table_size 
-------------+------------+-------------+------------+-------------+-----------------+-----------------+------------------+------------
 sk          | t1         |     2387968 |    1417216 |      970752 |           59.00 |           40.00 | 281 MB           | 281 MB
(1 row)

postgres=# vacuum sk.t1 ;     
VACUUM
postgres=# 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,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_table_size,
    pg_size_pretty(pg_relation_size(c.oid)) as table_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE 1=1
AND pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
AND n.nspname = 'sk'
ORDER BY dead_tupple DESC
;
 schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | total_table_size | table_size 
-------------+------------+-------------+------------+-------------+-----------------+-----------------+------------------+------------
 sk          | t1         |     1417216 |    1417216 |           0 |          100.00 |            0.00 | 281 MB           | 281 MB
(1 row)

 
참조 :
https://corekms.tistory.com/entry/vacuum-vacuum-full-의-차이에-대한-분석