=> CREATE FUNCTION p(param text, c pg_class) RETURNS float
AS $$
SELECT coalesce(
-- если параметр хранения задан, то берем его
(SELECT option_value
FROM pg_options_to_table(c.reloptions)
WHERE option_name = CASE -- для toast-таблиц имя отличается
WHEN c.relkind = 't' THEN 'toast.' ELSE ''
END || param
),
-- иначе берем значение конфигурационного параметра
current_setting(param)
)::float;
$$ LANGUAGE sql;
=> CREATE VIEW need_vacuum AS
WITH c AS (
SELECT c.oid,
greatest(c.reltuples, 0) AS reltuples,
CASE
WHEN c.relpages > 0 THEN
1 - c.relallfrozen::float / c.relpages
ELSE 1
END AS pct_unfrozen,
p('autovacuum_vacuum_threshold', c) AS threshold,
p('autovacuum_vacuum_scale_factor', c) AS scale_factor,
p('autovacuum_vacuum_max_threshold', c) AS max_threshold,
p('autovacuum_vacuum_insert_threshold', c) AS ins_threshold,
p('autovacuum_vacuum_insert_scale_factor', c) AS ins_scale_factor
FROM pg_class c
WHERE c.relkind IN ('r','m','t')
),
r AS (
SELECT st.schemaname || '.' || st.relname AS tablename,
st.n_dead_tup AS dead_tup,
least(
c.threshold + c.scale_factor * c.reltuples,
c.max_threshold
) AS max_dead_tup,
st.n_ins_since_vacuum AS ins_tup,
c.ins_threshold + c.ins_scale_factor * c.reltuples * c.pct_unfrozen
AS max_ins_tup,
st.last_autovacuum
FROM pg_stat_all_tables st
JOIN c ON c.oid = st.relid
)
SELECT *,
CASE
WHEN dead_tup > max_dead_tup OR ins_tup > max_ins_tup
THEN 'Пора!'
END AS is_it_time
FROM r;
=> CREATE VIEW need_analyze AS
WITH c AS (
SELECT c.oid,
greatest(c.reltuples, 0) AS reltuples,
p('autovacuum_analyze_threshold', c) AS threshold,
p('autovacuum_analyze_scale_factor', c) AS scale_factor
FROM pg_class c
WHERE c.relkind IN ('r','m')
),
r AS (
SELECT st.schemaname || '.' || st.relname AS tablename,
st.last_autoanalyze,
st.n_mod_since_analyze AS mod_tup,
c.threshold + c.scale_factor * c.reltuples AS max_mod_tup
FROM pg_stat_all_tables st
JOIN c ON c.oid = st.relid
)
SELECT *,
CASE
WHEN mod_tup > max_mod_tup THEN 'Пора!'
END AS is_it_time
FROM r;
列 max_mod_tup 显示触发自动分析的阈值。
为了不长时间等待自动清理，实验中让该进程每秒运行一次：
=> ALTER SYSTEM SET autovacuum_naptime = '1s';
=> SELECT pg_reload_conf();
清空表 vac，再向其中插入 1000 行。提醒一下，表级别的自动清理仍处于关闭状态。
=> TRUNCATE TABLE vac;
=> INSERT INTO vac(id,s)
SELECT id, 'A' FROM generate_series(1,1000) id;
=> CREATE FUNCTION heap_page(
relname text, pageno_from integer, pageno_to integer
)
RETURNS TABLE(
ctid tid, state text,
xmin text, xmin_age integer, xmax text
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
WHEN (t_infomask & 256) > 0 THEN ' c'
WHEN (t_infomask & 512) > 0 THEN ' a'
ELSE ''
END AS xmin,
age(t_xmin) AS xmin_age,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' c'
WHEN (t_infomask & 2048) > 0 THEN ' a'
ELSE ''
END AS xmax
FROM generate_series(pageno_from, pageno_to) p(pageno),
heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE sql;
向表中插入一些行，并立即执行清理，以便创建可见性映射：
=> CREATE EXTENSION IF NOT EXISTS pg_visibility;
=> INSERT INTO tfreeze(id, s)
SELECT id, 'FOO'||id FROM generate_series(1,100) id;
INSERT 0 100
=> VACUUM tfreeze;
=> SELECT *
FROM generate_series(0,1) g(blkno),
pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | f
1 | t | f
(2 rows)
创建这些行的事务的 xmin_age 为 1，因为它是系统中最近执行的事务：
=> SELECT * FROM heap_page('tfreeze',0,1);
ctid | state | xmin | xmin_age | xmax
-------+--------+-------+----------+------
(0,1) | normal | 877 c | 1 | 0 a
(0,2) | normal | 877 c | 1 | 0 a
(1,1) | normal | 877 c | 1 | 0 a
(1,2) | normal | 877 c | 1 | 0 a
(4 rows)
=> ALTER SYSTEM SET vacuum_freeze_min_age = 1;
=> SELECT pg_reload_conf();
现在更新第 0 页上的一行。由于 fillfactor 值很小，新的行版本会进入同一个页面：
=> UPDATE tfreeze SET s = 'BAR' WHERE id = 1;
事务年龄增加了 1。现在表页面如下：
=> SELECT * FROM heap_page('tfreeze',0,1);
ctid | state | xmin | xmin_age | xmax
-------+--------+-------+----------+------
(0,1) | normal | 877 c | 2 | 878
(0,2) | normal | 877 c | 2 | 0 a
(0,3) | normal | 878 | 1 | 0 a
(1,1) | normal | 877 c | 2 | 0 a
(1,2) | normal | 877 c | 2 | 0 a
(5 rows)
=> SELECT *
FROM generate_series(0,1) g(blkno),
pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
blkno | all_visible | all_frozen
-------+-------------+------------
0 | f | f
1 | t | f
(2 rows)
=> VACUUM tfreeze;
VACUUM
=> SELECT * FROM heap_page('tfreeze',0,1);
ctid | state | xmin | xmin_age | xmax
-------+---------------+-------+----------+------
(0,1) | redirect to 3 | | |
(0,2) | normal | 877 f | 2 | 0 a
(0,3) | normal | 878 f | 1 | 0 a
(1,1) | normal | 877 c | 2 | 0 a
(1,2) | normal | 877 c | 2 | 0 a
(5 rows)
=> SELECT * FROM generate_series(0,1) g(blkno),
pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | t
1 | t | f
(2 rows)
=> ALTER SYSTEM SET vacuum_freeze_table_age = 2;
=> SELECT pg_reload_conf();
执行清理：
=> VACUUM VERBOSE tfreeze;
INFO: aggressively vacuuming "internals.public.tfreeze"
INFO: finished vacuuming "internals.public.tfreeze": index scans: 0
pages: 0 removed, 50 remain, 50 scanned (100.00% of total), 0 eagerly
scanned
tuples: 0 removed, 100 remain, 0 are dead but not yet removable
removable cutoff: 879, which was 0 XIDs old when operation ended
new relfrozenxid: 879, which is 2 XIDs ahead of previous value
frozen: 48 pages from table (96.00% of total) had 96 tuples frozen
visibility map: 0 pages set all-visible, 48 pages set all-frozen (48
were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 116 hits, 0 reads, 0 dirtied
WAL usage: 97 records, 0 full page images, 6615 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
现在，激进清理会检查整张表，因此可以推进冻结事务编号：页面中已经保证不存在更老的未冻结事务。
=> SELECT relfrozenxid, age(relfrozenxid)
FROM pg_class WHERE relname = 'tfreeze';
relfrozenxid | age
--------------+-----
879 | 0
(1 row)
当然，第 1 页上的所有行版本现在都已冻结：
=> SELECT * FROM heap_page('tfreeze',0,1);
ctid | state | xmin | xmin_age | xmax
-------+---------------+-------+----------+------
(0,1) | redirect to 3 | | |
(0,2) | normal | 877 f | 2 | 0 a
(0,3) | normal | 878 f | 1 | 0 a
(1,1) | normal | 877 f | 2 | 0 a
(1,2) | normal | 877 f | 2 | 0 a
(5 rows)
此外，第 1 页也已经在冻结映射中标记：
=> SELECT * FROM generate_series(0,1) g(blkno),
pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | t
1 | t | t
(2 rows)
=> SELECT * FROM heap_page('tfreeze',0,8191);
ctid | state | xmin | xmin_age | xmax
----------+--------+-------+----------+------
(0,1) | normal | 880 c | 2 | 0 a
(0,2) | normal | 880 c | 2 | 0 a
(1,1) | normal | 880 c | 2 | 0 a
(1,2) | normal | 880 c | 2 | 0 a
...
(8191,1) | normal | 880 c | 2 | 0 a
(8191,2) | normal | 880 c | 2 | 0 a
(16384 rows)
=> SELECT * FROM generate_series(0,8191) g(blkno),
pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
blkno | all_visible | all_frozen
-------+-------------+------------
0 | t | f
1 | t | f
...
8191 | t | f
(8192 rows)
最后一切就绪，用 verbose 参数启动清理：
=> VACUUM VERBOSE tfreeze;
INFO: vacuuming "internals.public.tfreeze"
INFO: disabling eager scanning after freezing 1638 eagerly scanned
blocks of relation "internals.public.tfreeze"
INFO: finished vacuuming "internals.public.tfreeze": index scans: 0
pages: 0 removed, 8192 remain, 1640 scanned (20.02% of total), 1639
eagerly scanned
tuples: 0 removed, 16384 remain, 0 are dead but not yet removable
removable cutoff: 882, which was 0 XIDs old when operation ended
frozen: 1640 pages from table (20.02% of total) had 3280 tuples frozen
visibility map: 0 pages set all-visible, 1640 pages set all-frozen
(1640 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 3299 hits, 0 reads, 0 dirtied
WAL usage: 3281 records, 0 full page images, 215167 bytes, 0 buffers
full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
=> SELECT * FROM pg_visibility_map('tfreeze',0);
all_visible | all_frozen
-------------+------------
t | t
(1 row)
=> SELECT flags & 4 > 0 AS all_visible
FROM page_header(get_raw_page('tfreeze',0));
all_visible
-------------
t
(1 row)
