Часть 1 ~~~~~~~ psql Создаем БД и таблицу. => create database db6; CREATE DATABASE => \c db6 You are now connected to database "db6" as user "postgres". => create table t1(id serial, n numeric); CREATE TABLE => insert into t1(n) select 1 from generate_series(1,100000); INSERT 0 100000 => analyze t1; ANALYZE Для оценки разрастания удобно воспользоваться расширением pgstattuple. => create extension pgstattuple; CREATE EXTENSION Вот начальная информация. Поле free_percent показывает свободное пространство в страницах. Оно может быть не нулевым из-за неиспользованного места в страницах даже при fillfactor=100. => \x Expanded display is on. => select * from pgstattuple('t1'); -[ RECORD 1 ]------+-------- table_len | 4022272 tuple_count | 100000 tuple_len | 3300000 tuple_percent | 82.04 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 8524 free_percent | 0.21 Настроиваем автоочистку на 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=1; ALTER SYSTEM => select pg_reload_conf(); -[ RECORD 1 ]--+-- pg_reload_conf | t Выполняем обновление таблицы. Будем изменять строки, равномерно распределенные по таблице, для чего воcпользуемся столбцом id. => update t1 set n=n+1 where id % 20 = 0; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 1; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 2; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 3; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 4; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 5; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 6; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 7; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 8; UPDATE 5000 sleep 2 => update t1 set n=n+1 where id % 20 = 9; UPDATE 5000 sleep 2 Оцениваем разрастание. Вот статистика по таблице (autovacuum_count - сколько раз выпонялась автоочистка): => select * from pg_stat_user_tables where relname='t1'; -[ RECORD 1 ]-------+------------------------------ relid | 143746 schemaname | public relname | t1 seq_scan | 11 seq_tup_read | 1100000 idx_scan | idx_tup_fetch | n_tup_ins | 100000 n_tup_upd | 50000 n_tup_del | 0 n_tup_hot_upd | 41078 n_live_tup | 100000 n_dead_tup | 7451 n_mod_since_analyze | 10000 last_vacuum | last_autovacuum | 2017-12-14 19:03:07.346578+03 last_analyze | 2017-12-14 19:03:02.024435+03 last_autoanalyze | 2017-12-14 19:03:17.876784+03 vacuum_count | 0 autovacuum_count | 1 analyze_count | 1 autoanalyze_count | 3 Вот как увеличилась таблица: => select * from pgstattuple('t1'); -[ RECORD 1 ]------+-------- table_len | 4374528 tuple_count | 100000 tuple_len | 3300000 tuple_percent | 75.44 dead_tuple_count | 5000 dead_tuple_len | 165000 dead_tuple_percent | 3.77 free_space | 3892 free_percent | 0.09 Суммарно изменилось 50000 строк, что составляет 50% от размера таблицы. Однако автоочистка выполнялась меньше пяти раз, поскольку существенная часть обновлений была оптимизирована внутристраничной очисткой, в частности HOT-обновлениями (поле n_tup_hot_upd). За счет этого и таблица увеличилась в размерах не сильно. Часть 2 ~~~~~~~ Создадим другую таблицу. => create table t2(id serial, n numeric); CREATE TABLE => insert into t2(n) select 1 from generate_series(1,100000); INSERT 0 100000 => analyze t2; ANALYZE Откроем новый сеанс. | psql | => \c db6 | You are now connected to database "db6" as user "postgres". | => begin; | BEGIN | => set transaction isolation level repeatable read; | SET | => select count(*) from t2; | count | -------- | 100000 | (1 row) | Выполняем обновление таблицы. => update t2 set n=n+1 where id % 20 = 0; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 1; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 2; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 3; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 4; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 5; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 6; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 7; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 8; UPDATE 5000 sleep 2 => update t2 set n=n+1 where id % 20 = 9; UPDATE 5000 sleep 2 | => \q sleep 5 Оценим разрастание. Статистика по таблице: => select * from pg_stat_user_tables where relname='t2'; -[ RECORD 1 ]-------+------------------------------ relid | 143764 schemaname | public relname | t2 seq_scan | 11 seq_tup_read | 1100000 idx_scan | idx_tup_fetch | n_tup_ins | 100000 n_tup_upd | 50000 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 100000 n_dead_tup | 0 n_mod_since_analyze | 5000 last_vacuum | last_autovacuum | 2017-12-14 19:03:43.524178+03 last_analyze | 2017-12-14 19:03:22.765318+03 last_autoanalyze | 2017-12-14 19:03:39.343096+03 vacuum_count | 0 autovacuum_count | 8 analyze_count | 1 autoanalyze_count | 3 Вот как увеличилась таблица: => select * from pgstattuple('t2'); -[ RECORD 1 ]------+-------- table_len | 6029312 tuple_count | 100000 tuple_len | 3300000 tuple_percent | 54.73 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 1808704 free_percent | 30 За счет того, что версии строк были в активном снимке, очистка не могла их удалить. Поэтому реальное разрастание таблицы может оказаться больше ожидаемого, несмотря на установленные параметры. Большое количество срабатываний автоочистки вызвано той же причиной: рабочий процесс запускался каждую секунду, обнаруживал, что таблица требует очистки, но не мог ее выполнить и завершался. Восстановим настройки. => alter system reset all; ALTER SYSTEM => select pg_reload_conf(); -[ RECORD 1 ]--+-- pg_reload_conf | t => \q