VACUUM과 Autovacuum

docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity

4f4ff147becaad7b9b222ce8f3be8edfa1fcf0f445d7b4458d52ce90c61ade66
[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]

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]

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]

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]

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]

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]

-- 세션 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]

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]

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]

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]

ALTER TABLE big_orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000);