######## 0. 실습 환경

$ docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity
4f4ff147becaad7b9b222ce8f3be8edfa1fcf0f445d7b4458d52ce90c61ade66
[exit=0]

$ postgres --version
$ initdb -D $PGDATA  > /home/postgres/initdb.log 2>&1 && echo "initdb ok"
postgres (PostgreSQL) 18.6
initdb ok
[exit=0]

$ pg_ctl -D $PGDATA -l /home/postgres/server.log start
$ psql -X -q -c "CREATE EXTENSION pageinspect" -c "CREATE EXTENSION pg_visibility" -c "CREATE EXTENSION pg_freespacemap"
$ psql -X -q -c "CREATE TABLE t (id int PRIMARY KEY, v int, pad text) WITH (autovacuum_enabled = off)"
$ psql -X -q -c "INSERT INTO t SELECT g, 0, repeat('x', 100) FROM generate_series(1, 20000) g"
$ sleep 2
$ psql -X -q -c "VACUUM (ANALYZE) t"
waiting for server to start.... done
server started
[exit=0]


######## 1. UPDATE를 하면 dead tuple이 쌓인다

$ psql -X <<'SQL'
$ SELECT pg_size_pretty(pg_relation_size('t')) AS size, pg_relation_size('t') / 8192 AS pages;
$ UPDATE t SET v = v + 1;
$ SELECT pg_size_pretty(pg_relation_size('t')) AS size, pg_relation_size('t') / 8192 AS pages;
$ SQL
$ sleep 2
$ psql -X -c "SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 't'"
  size   | pages 
---------+-------
 2760 kB |   345
(1 row)

UPDATE 20000
  size   | pages 
---------+-------
 5520 kB |   690
(1 row)

 n_live_tup | n_dead_tup 
------------+------------
      20000 |      20000
(1 row)

[exit=0]


######## 2. VACUUM이 dead tuple을 정리한다

$ psql -X -c "VACUUM (VERBOSE, PROCESS_TOAST false) t" 2>&1
$ sleep 2
$ psql -X <<'SQL'
$ SELECT pg_size_pretty(pg_relation_size('t')) AS size, pg_relation_size('t') / 8192 AS pages;
$ SELECT n_live_tup, n_dead_tup, vacuum_count FROM pg_stat_user_tables WHERE relname = 't';
$ SELECT count(*) AS pages, pg_size_pretty(sum(avail)) AS free_space FROM pg_freespace('t');
$ SQL
INFO:  vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 1
pages: 0 removed, 690 remain, 690 scanned (100.00% of total), 0 eagerly scanned
tuples: 20000 removed, 20000 remain, 0 are dead but not yet removable
removable cutoff: 759, which was 0 XIDs old when operation ended
new relfrozenxid: 758, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 690 pages set all-visible, 344 pages set all-frozen (0 were all-visible)
index scan needed: 345 pages from table (50.00% of total) had 20000 dead item identifiers removed
index "t_pkey": pages: 112 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 1898 hits, 0 reads, 0 dirtied
WAL usage: 1492 records, 0 full page images, 257943 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
  size   | pages 
---------+-------
 5520 kB |   690
(1 row)

 n_live_tup | n_dead_tup | vacuum_count 
------------+------------+--------------
      20000 |          0 |            2
(1 row)

 pages | free_space 
-------+------------
   690 | 2761 kB
(1 row)

[exit=0]


######## 3. 비운 공간은 다시 쓴다

$ psql -X <<'SQL'
$ INSERT INTO t SELECT g, 0, repeat('x', 100) FROM generate_series(20001, 30000) g;
$ SELECT pg_size_pretty(pg_relation_size('t')) AS size, pg_relation_size('t') / 8192 AS pages;
$ SQL
INSERT 0 10000
  size   | pages 
---------+-------
 5520 kB |   690
(1 row)

[exit=0]


######## 4. Visibility Map과 index-only scan

$ psql -X <<'SQL'
$ SELECT n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 't';
$ UPDATE t SET v = v + 1 WHERE id <= 5000;
$ SELECT pg_sleep(2);
$ SELECT n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 't';
$ SELECT * FROM pg_visibility_map_summary('t');
$ SET enable_seqscan = off;
$ SET enable_bitmapscan = off;
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(id) FROM t WHERE id <= 5000;
$ VACUUM t;
$ SELECT * FROM pg_visibility_map_summary('t');
$ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(id) FROM t WHERE id <= 5000;
$ SQL
 n_tup_hot_upd 
---------------
             0
(1 row)

UPDATE 5000
 pg_sleep 
----------
 
(1 row)

 n_tup_hot_upd 
---------------
            10
(1 row)

 all_visible | all_frozen 
-------------+------------
         342 |         84
(1 row)

SET
SET
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Index Only Scan using t_pkey on t (actual rows=5000.00 loops=1)
         Index Cond: (id <= 5000)
         Heap Fetches: 9990
         Index Searches: 1
(5 rows)

VACUUM
 all_visible | all_frozen 
-------------+------------
         690 |        170
(1 row)

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Index Only Scan using t_pkey on t (actual rows=5000.00 loops=1)
         Index Cond: (id <= 5000)
         Heap Fetches: 0
         Index Searches: 1
(5 rows)

[exit=0]


######## 5. HOT 업데이트와 pruning

$ psql -X <<'SQL'
$ CREATE TABLE hot (id int PRIMARY KEY, v int) WITH (autovacuum_enabled = off);
$ INSERT INTO hot VALUES (1, 0);
$ UPDATE hot SET v = 1 WHERE id = 1;
$ UPDATE hot SET v = 2 WHERE id = 1;
$ UPDATE hot SET v = 3 WHERE id = 1;
$ SELECT lp, lp_flags, lp_off, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('hot', 0));
$ SQL
$ sleep 2
$ psql -X <<'SQL'
$ SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'hot';
$ VACUUM hot;
$ SELECT lp, lp_flags, lp_off, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('hot', 0));
$ SQL
CREATE TABLE
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 1
 lp | lp_flags | lp_off | t_xmin | t_xmax | t_ctid 
----+----------+--------+--------+--------+--------
  1 |        1 |   8160 |    762 |    763 | (0,2)
  2 |        1 |   8128 |    763 |    764 | (0,3)
  3 |        1 |   8096 |    764 |    765 | (0,4)
  4 |        1 |   8064 |    765 |      0 | (0,4)
(4 rows)

 n_tup_upd | n_tup_hot_upd 
-----------+---------------
         3 |             3
(1 row)

VACUUM
 lp | lp_flags | lp_off | t_xmin | t_xmax | t_ctid 
----+----------+--------+--------+--------+--------
  1 |        2 |      4 |        |        | 
  2 |        0 |      0 |        |        | 
  3 |        0 |      0 |        |        | 
  4 |        1 |   8160 |    765 |      0 | (0,4)
(4 rows)

[exit=0]


######## 6. 긴 트랜잭션이 있으면 지우지 못한다

# 세션 A 시작: psql -X 

[세션 A] $ BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

[세션 A] $ SELECT count(*) FROM t;
 count 
-------
 30000
(1 row)


[세션 A] $ SELECT pg_current_snapshot();
 pg_current_snapshot 
---------------------
 766:766:
(1 row)


$ psql -X -c "DELETE FROM t WHERE id > 25000"
$ psql -X -c "VACUUM (VERBOSE, PROCESS_TOAST false) t" 2>&1 | grep -E 'tuples:|removable cutoff'
$ psql -X -c "SELECT pid, state, backend_xmin, now() - xact_start > interval '0' AS in_xact FROM pg_stat_activity WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()"
DELETE 5000
tuples: 0 removed, 26842 remain, 5000 are dead but not yet removable
removable cutoff: 766, which was 1 XIDs old when operation ended
 pid |        state        | backend_xmin | in_xact 
-----+---------------------+--------------+---------
 119 | idle in transaction |          766 | t
(1 row)

[exit=0]

[세션 A] $ COMMIT;
COMMIT

$ psql -X -c "VACUUM (VERBOSE, PROCESS_TOAST false) t" 2>&1 | grep -E 'tuples:|removable cutoff'
tuples: 5000 removed, 19106 remain, 0 are dead but not yet removable
removable cutoff: 767, which was 0 XIDs old when operation ended
[exit=0]


######## 6-1. 테이블 끝의 빈 페이지는 잘라 낸다

$ psql -X -q -c "CREATE TABLE tail (id int, pad text) WITH (autovacuum_enabled = off)"
$ psql -X -q -c "INSERT INTO tail SELECT g, repeat('x', 100) FROM generate_series(1, 20000) g"
$ psql -X -c "SELECT pg_relation_size('tail') / 8192 AS pages"
$ psql -X -q -c "DELETE FROM tail WHERE id > 10000"
$ psql -X -c "VACUUM (VERBOSE, PROCESS_TOAST false) tail" 2>&1 | grep -E 'pages:|tuples:'
$ psql -X -c "SELECT pg_relation_size('tail') / 8192 AS pages"
 pages 
-------
   345
(1 row)

pages: 172 removed, 173 remain, 345 scanned (100.00% of total), 0 eagerly scanned
tuples: 10000 removed, 10000 remain, 0 are dead but not yet removable
 pages 
-------
   173
(1 row)

[exit=0]


######## 7. VACUUM FULL은 테이블을 새로 쓴다

$ psql -X <<'SQL'
$ DELETE FROM t WHERE id % 2 = 0;
$ VACUUM t;
$ SELECT pg_relation_filenode('t') AS filenode, pg_size_pretty(pg_relation_size('t')) AS size;
$ VACUUM FULL t;
$ SELECT pg_relation_filenode('t') AS filenode, pg_size_pretty(pg_relation_size('t')) AS size;
$ SQL
DELETE 12500
VACUUM
 filenode |  size   
----------+---------
    16442 | 5520 kB
(1 row)

VACUUM
 filenode |  size   
----------+---------
    16461 | 1728 kB
(1 row)

[exit=0]


######## 8. autovacuum은 언제 도는가

$ psql -X -q <<'SQL'
$ ALTER SYSTEM SET autovacuum_naptime = '1s';
$ ALTER SYSTEM SET log_autovacuum_min_duration = 0;
$ SELECT pg_reload_conf();
$ CREATE TABLE av (id int PRIMARY KEY, v int);
$ INSERT INTO av SELECT g, 0 FROM generate_series(1, 10000) g;
$ SQL
$ sleep 5
$ psql -X <<'SQL'
$ SELECT relname, reltuples FROM pg_class WHERE relname = 'av';
$ SELECT current_setting('autovacuum_vacuum_threshold')::int
$      + current_setting('autovacuum_vacuum_scale_factor')::float * reltuples AS vacuum_threshold
$ FROM pg_class WHERE relname = 'av';
$ SELECT n_dead_tup, autovacuum_count, last_autovacuum IS NOT NULL AS vacuumed FROM pg_stat_user_tables WHERE relname = 'av';
$ UPDATE av SET v = 1 WHERE id <= 1500;
$ SQL
$ sleep 4
$ psql -X -c "SELECT n_dead_tup, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'av'"
$ psql -X -q -c "UPDATE av SET v = 2 WHERE id <= 1500"
$ sleep 4
$ psql -X -c "SELECT n_dead_tup, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'av'"
$ grep -A8 'automatic vacuum of table "postgres.public.av"' /home/postgres/server.log | grep -E 'automatic vacuum|tuples:|index scan'
 pg_reload_conf 
----------------
 t
(1 row)

 relname | reltuples 
---------+-----------
 av      |     10000
(1 row)

 vacuum_threshold 
------------------
             2050
(1 row)

 n_dead_tup | autovacuum_count | vacuumed 
------------+------------------+----------
          0 |                1 | t
(1 row)

UPDATE 1500
 n_dead_tup | autovacuum_count 
------------+------------------
       1500 |                1
(1 row)

 n_dead_tup | autovacuum_count 
------------+------------------
          0 |                2
(1 row)

2026-09-24 03:40:42.396 UTC [274] LOG:  automatic vacuum of table "postgres.public.av": index scans: 0
	tuples: 0 removed, 10000 remain, 0 are dead but not yet removable
	index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
2026-09-24 03:40:51.454 UTC [300] LOG:  automatic vacuum of table "postgres.public.av": index scans: 1
	tuples: 1500 removed, 8893 remain, 0 are dead but not yet removable
	index scan needed: 14 pages from table (24.14% of total) had 3000 dead item identifiers removed
[exit=0]

done