|
일반적인 데이터베이스 용어로 보면 tuple / record / row 는 모두 동일하게 행을 가리킨다고 알고 있습니다.
oracle 과 mysql 은 변경 데이터 또는 삭제 데이터를 undo segment 에 저장하는 것과 달리 PostgreSQL 은 변경 또는 삭제 데이터를 테이블이 사용 중인 page 에
그대로 쌓아가고 dead tuple 이라고 마킹만 하는걸로 알고 있습니다.(MVCC 구현의 차이) 그렇기 떄문에 tuple 의 개념이 타 rdbms 와 조금 다르게 이해하고 있구요.(live tuple 과 dead tuple)
그래서 아래와 같이 테스트 해봤는데 PostgreSQL 버전별로 VACUUM FULL verbose 결과가 아래와 같이 상이하게 나타났습니다.
PostgreSQL v9.5 에서는 VACUUM FULL verbose 수행하면 INFO: "tuple_test": found 1200 removable, 1100 nonremovable row versions in 13 pages 이라고 떠서 dead tuple 이 1200개 였고,
VACUUM FULL 수행후에 dead tuple 이 0 으로 변했음을 확인할 수 있는데
PostgreSQL v12 에서는 동일하게 VACUUM FULL verbose 수행했을 때, INFO: "tuple_test": found 0 removable, 1100 nonremovable row versions in 13 pages 라고 확인되서 문의드립니다.
-- 테이블 생성 및 1000건 insert
postgres=# CREATE TABLE tuple_test (
id NUMERIC NOT NULL,
rand_num NUMERIC NOT NULL,
birth DATE,
CONSTRAINT id_pk PRIMARY KEY (id)
);
CREATE TABLE
postgres=# INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1, 1000);
INSERT 0 1000
postgres=# SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
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_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname = 'tuple_test' ORDER BY n_live_tup DESC;
relname | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
tuple_test | 1000 | 1000 | 0 | 100.00 | 0.00 | 120 kB | 48 kB
(1 row)
-- 다시 100 건 insert
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1001, 1100);
INSERT 0 100
postgres=# COMMIT;
COMMIT
postgres=# SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
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_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
relname | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 120 kB | 48 kB
(1 row)
-- 100 건 insert 하고 rollback
postgres=# BEGIN ;
BEGIN
postgres=# INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1101, 1200);
INSERT 0 100
postgres=# ROLLBACK ;
ROLLBACK
postgres=# SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
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_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname = 'tuple_test' ORDER BY n_live_tup DESC;
relname | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
tuple_test | 1200 | 1100 | 100 | 91.00 | 8.00 | 136 kB | 56 kB
(1 row)
-- update 하고 tuple 정보 확인
postgres=# UPDATE tuple_test SET rand_num = rand_num + 1;
UPDATE 1100
postgres=# SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
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_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname = 'tuple_test' ORDER BY n_live_tup DESC;
relname | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
tuple_test | 2300 | 1100 | 1200 | 47.00 | 52.00 | 232 kB | 104 kB
(1 row)
-- vacuum full 수행하고, 다시 tuple 정보 확인
postgres=# vacuum FULL verbose tuple_test ;
INFO: vacuuming "public.tuple_test"
INFO: "tuple_test": found 1200 removable, 1100 nonremovable row versions in 13 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
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_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname = 'tuple_test' ORDER BY n_live_tup DESC;
relname | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 104 kB | 48 kB
(1 row)
|
