Настройка автоочистки

Настраиваем автоочистку на 10 % строк. 

=> ALTER SYSTEM SET autovacuum_vacuum_threshold = 0;

ALTER SYSTEM

=> ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;

ALTER SYSTEM

Устанавливаем частоту запуска процессов в 1 раз в секунду: 

=> ALTER SYSTEM SET autovacuum_naptime = '1s';

ALTER SYSTEM

=> SELECT pg_reload_conf();

 pg_reload_conf 
----------------
 t
(1 row)

Таблица 

=> CREATE DATABASE mvcc_autovacuum;

CREATE DATABASE

=> \c mvcc_autovacuum

You are now connected to database "mvcc_autovacuum" as user "postgres".

=> CREATE TABLE t(n integer);

CREATE TABLE

=> INSERT INTO t(n) SELECT 1 FROM generate_series(1,100000);

INSERT 0 100000

Изменение данных

Начальный размер таблицы: 

=> SELECT pg_size_pretty(pg_table_size('t'));

 pg_size_pretty 
----------------
 3168 kB
(1 row)

Выполняем обновление таблицы с задержками в несколько секунд между командами. 

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5584

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5421

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5422

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5509

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5522

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5528

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5396

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5513

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5501

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5397

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5431

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5472

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5477

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5453

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5353

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5497

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5470

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5475

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5557

=> UPDATE t SET n = n + 1 WHERE random() < 0.055;

UPDATE 5548

Оценка разрастания

Статистика по таблице (autovacuum_count - сколько раз выполнялась автоочистка): 

=> SELECT * FROM pg_stat_all_tables WHERE relname='t' \gx

-[ RECORD 1 ]-------+------------------------------
relid               | 25124
schemaname          | public
relname             | t
seq_scan            | 20
seq_tup_read        | 2000000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 100000
n_tup_upd           | 109526
n_tup_del           | 0
n_tup_hot_upd       | 67610
n_live_tup          | 100000
n_dead_tup          | 5548
n_mod_since_analyze | 5548
last_vacuum         | 
last_autovacuum     | 2019-08-12 17:19:50.669151+03
last_analyze        | 
last_autoanalyze    | 2019-08-12 17:19:50.767039+03
vacuum_count        | 0
autovacuum_count    | 5
analyze_count       | 0
autoanalyze_count   | 9

Вот как увеличилась таблица: 

=> SELECT pg_size_pretty(pg_table_size('t'));

 pg_size_pretty 
----------------
 4192 kB
(1 row)

Суммарно изменилось порядка 110000 строк, что составляет 110% от размера таблицы. Можно было бы предположить, что автоочистка сработает 10-11 раз.

Однако автоочистка выполнялась примерно в два раза реже, поскольку существенная часть обновлений была оптимизирована внутристраничной очисткой (в нашем случае, HOT-очисткой: поле n_tup_hot_upd).

Таблица увеличилась не в два раза, а меньше - примерно на треть. В идеале достаточно было бы 5-6 %, но: