Создадим таблицу с данными и индекс. Параметр autovacuum_enabled указан, чтобы не срабатывала автоматическая очистка.
=> CREATE DATABASE mvcc_vacuum;
CREATE DATABASE
=> \c mvcc_vacuum
You are now connected to database "mvcc_vacuum" as user "postgres".
=> CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
CREATE TABLE
=> INSERT INTO t SELECT gen.id FROM generate_series(1,1000000) gen(id);
INSERT 0 1000000
=> CREATE INDEX t_id ON t(id);
CREATE INDEX
Уменьшаем размер памяти, выделяемой под массив идентификаторов:
=> ALTER SYSTEM SET maintenance_work_mem = '1MB';
ALTER SYSTEM
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
Обновляем все строки:
=> UPDATE t SET id = id + 1;
UPDATE 1000000
Запускаем очистку. Заодно через небольшое время в другом сеансе обратимся к pg_stat_progress_vacuum.
=> VACUUM VERBOSE t;
=> \c mvcc_vacuum
You are now connected to database "mvcc_vacuum" as user "postgres".
=> SELECT * FROM pg_stat_progress_vacuum \gx
-[ RECORD 1 ]------+------------------ pid | 23919 datid | 25112 datname | mvcc_vacuum relid | 25113 phase | vacuuming indexes heap_blks_total | 7844 heap_blks_scanned | 3425 heap_blks_vacuumed | 2739 index_vacuum_count | 4 max_dead_tuples | 174762 num_dead_tuples | 174675
INFO: vacuuming "public.t" INFO: scanned index "t_id" to remove 174675 row versions DETAIL: CPU: user: 0.11 s, system: 0.00 s, elapsed: 0.11 s INFO: "t": removed 174675 row versions in 685 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "t_id" to remove 174675 row versions DETAIL: CPU: user: 0.10 s, system: 0.00 s, elapsed: 0.10 s INFO: "t": removed 174675 row versions in 685 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "t_id" to remove 174675 row versions DETAIL: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s INFO: "t": removed 174675 row versions in 685 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "t_id" to remove 174675 row versions DETAIL: CPU: user: 0.08 s, system: 0.00 s, elapsed: 0.09 s INFO: "t": removed 174675 row versions in 685 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "t_id" to remove 174675 row versions DETAIL: CPU: user: 0.08 s, system: 0.00 s, elapsed: 0.15 s INFO: "t": removed 174675 row versions in 685 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "t_id" to remove 126625 row versions DETAIL: CPU: user: 0.07 s, system: 0.00 s, elapsed: 0.14 s INFO: "t": removed 126625 row versions in 497 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "t_id" now contains 1000000 row versions in 6592 pages DETAIL: 1000000 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "t": found 1000000 removable, 1000000 nonremovable row versions in 7844 out of 7844 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 55505 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.73 s, system: 0.00 s, elapsed: 0.98 s. VACUUM
Восстановим значение измененного параметра.
=> ALTER SYSTEM RESET maintenance_work_mem;
ALTER SYSTEM
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
Текущий размер файла данных:
=> SELECT pg_size_pretty(pg_table_size('t'));
pg_size_pretty ---------------- 61 MB (1 row)
Удалим 90 % случайных строк. Случайность важна, чтобы в каждой странице остались какие-нибудь неудаленные строки - в противном случае очистка имеет шанс уменьшить размер файла.
=> DELETE FROM t WHERE random() < 0.9;
DELETE 899695
Объем после очистки:
=> VACUUM t;
VACUUM
=> SELECT pg_size_pretty(pg_table_size('t'));
pg_size_pretty ---------------- 61 MB (1 row)
Объем не изменился.
Заново наполним таблицу.
=> TRUNCATE t;
TRUNCATE TABLE
=> INSERT INTO t SELECT gen.id FROM generate_series(1,1000000) gen(id);
INSERT 0 1000000
Текущий размер файла данных:
=> SELECT pg_size_pretty(pg_table_size('t'));
pg_size_pretty ---------------- 31 MB (1 row)
Обратите внимание, что в прошлый раз размер был примерно в два раза больше. Вторая половина была занята версиями строк, которые создала команда UPDATE.
Объем после удаления и полной очистки:
=> DELETE FROM t WHERE random() < 0.9;
DELETE 900249
=> VACUUM FULL t;
VACUUM
=> SELECT pg_size_pretty(pg_table_size('t'));
pg_size_pretty ---------------- 3136 kB (1 row)
Объем уменьшился на 90 %.