
АВТООЧИСТКА
~~~~~~~~~~~

Создадим БД и таблицу с 1000 строками:

        => create database db6;
        CREATE DATABASE

        => \c db6
        You are now connected to database "db6" as user "postgres".

        => create table tvac(id serial, n numeric);
        CREATE TABLE

        => insert into tvac(n) select 1 from generate_series(1,1000);
        INSERT 0 1000

И сразу проанализируем ее.

        => analyze tvac;
        ANALYZE

.......................................................................

Выставим настройки автоочистки.
Небольшое время ожидания, чтобы сразу видеть результат:

        => alter system set autovacuum_naptime=1;
        ALTER SYSTEM

Один процент строк:

        => alter system set autovacuum_vacuum_scale_factor=0.01;
        ALTER SYSTEM

Нулевой порог:

        => alter system set autovacuum_vacuum_threshold=0;
        ALTER SYSTEM

.......................................................................

Выставим настройки автоанализа.
Два процента строк:

        => alter system set autovacuum_analyze_scale_factor=0.02;
        ALTER SYSTEM

Нулевой порог:

        => alter system set autovacuum_analyze_threshold=0;
        ALTER SYSTEM

И перезапустим сервер.

        => \q

pg_ctl restart -m fast -w -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

.......................................................................

Есть ли сейчас таблицы, ожидающие очистки?
Создадим представление, показывающее такую информацию.

        psql 

        => \c db6
        You are now connected to database "db6" as user "postgres".

        => create view vacuum_v as
        =>   with params as (
        =>     select (select setting::integer
        =>             from   pg_settings
        =>             where  name='autovacuum_vacuum_threshold') as vacuum_threshold,
        =>            (select setting::float
        =>             from   pg_settings
        =>             where  name='autovacuum_vacuum_scale_factor') as vacuum_scale_factor
        =>   )
        =>   select st.relname,
        =>          st.n_dead_tup dead_tup,
        =>          (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer max_dead_tup,
        =>          st.n_dead_tup > (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer need_vacuum,
        =>          st.last_autovacuum
        =>   from   pg_stat_all_tables st,
        =>          pg_class c,
        =>          params p
        =>   where  c.oid = st.relid
        =>   and    c.relname = 'tvac';
        CREATE VIEW

.......................................................................

Проверим.

        => select * from vacuum_v;
         relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum 
        ---------+----------+--------------+-------------+-----------------
         tvac    |        0 |           10 | f           | 
        (1 row)
        

Таблица не требует очистки (0 ненужных версий) и ни разу не очищалась.

.......................................................................

А что с анализом?
Можно создать аналогичное представление:

        => create view analyze_v as
        =>   with params as (
        =>     select (select setting::integer
        =>             from   pg_settings
        =>             where  name='autovacuum_analyze_threshold') as analyze_threshold,
        =>            (select setting::float
        =>             from   pg_settings
        =>             where  name='autovacuum_analyze_scale_factor') as analyze_scale_factor
        =>   )
        =>   select st.relname,
        =>          st.n_mod_since_analyze mod_tup,
        =>          (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer max_mod_tup,
        =>          st.n_mod_since_analyze > (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer need_analyze,
        =>          st.last_autoanalyze
        =>   from   pg_stat_all_tables st,
        =>          pg_class c,
        =>          params p
        =>   where  c.oid = st.relid
        =>   and    c.relname = 'tvac';
        CREATE VIEW

.......................................................................

Проверим.

        => select * from analyze_v;
         relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze        
        ---------+---------+-------------+--------------+-------------------------------
         tvac    |       0 |          20 | f            | 2017-12-14 18:58:03.446295+03
        (1 row)
        

Таблица не требует анализа; он выполнялся вручную сразу после создания.

.......................................................................

Отключим автоочистку на уровне таблицы и изменим 11 строк:

        => alter table tvac set (autovacuum_enabled = off);
        ALTER TABLE

        => update tvac set n = n + 1 where id <= 11;
        UPDATE 11

.......................................................................

        => \q

sleep 1 
        psql 

        => \c db6
        You are now connected to database "db6" as user "postgres".

        => select * from vacuum_v;
         relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum 
        ---------+----------+--------------+-------------+-----------------
         tvac    |       11 |           10 | t           | 
        (1 row)
        

        => select * from analyze_v;
         relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze        
        ---------+---------+-------------+--------------+-------------------------------
         tvac    |      11 |          20 | f            | 2017-12-14 18:58:03.446295+03
        (1 row)
        

Как видно, таблице требуется автоочистка.

.......................................................................

Включим автоочистку для таблицы и подождем немного...

        => alter table tvac set (autovacuum_enabled = on);
        ALTER TABLE

sleep 3 

.......................................................................

        => select * from vacuum_v;
         relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum 
        ---------+----------+--------------+-------------+-----------------
         tvac    |       11 |           10 | t           | 
        (1 row)
        

        => select * from analyze_v;
         relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze        
        ---------+---------+-------------+--------------+-------------------------------
         tvac    |      11 |          20 | f            | 2017-12-14 18:58:03.446295+03
        (1 row)
        

Автоочистка пришла и обработала таблицу. Число ненужных версий снова равно нулю.
При этом автоанализ не выполнялся.

.......................................................................

Изменим еще 11 строк:

        => alter table tvac set (autovacuum_enabled = off);
        ALTER TABLE

        => update tvac set n = n + 1 where id <= 11;
        UPDATE 11

.......................................................................

        => \q

sleep 1 
        psql 

        => \c db6
        You are now connected to database "db6" as user "postgres".

        => select * from vacuum_v;
         relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum 
        ---------+----------+--------------+-------------+-----------------
         tvac    |       22 |           10 | t           | 
        (1 row)
        

        => select * from analyze_v;
         relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze        
        ---------+---------+-------------+--------------+-------------------------------
         tvac    |      22 |          20 | t            | 2017-12-14 18:58:03.446295+03
        (1 row)
        

Теперь должна отработать и автоочистка, и автоанализ.

.......................................................................

Проверим это.

        => alter table tvac set (autovacuum_enabled = on);
        ALTER TABLE

sleep 3 

        => select * from vacuum_v;
         relname | dead_tup | max_dead_tup | need_vacuum |        last_autovacuum        
        ---------+----------+--------------+-------------+-------------------------------
         tvac    |        0 |           10 | f           | 2017-12-14 18:58:10.611687+03
        (1 row)
        

        => select * from analyze_v;
         relname | mod_tup | max_mod_tup | need_analyze |       last_autoanalyze        
        ---------+---------+-------------+--------------+-------------------------------
         tvac    |       0 |          20 | f            | 2017-12-14 18:58:10.656747+03
        (1 row)
        

Все правильно, отработали оба процесса.

.......................................................................

ЗАМОРОЗКА
~~~~~~~~~

Сбросим значения автоочистки:

        => alter system reset all;
        ALTER SYSTEM

Установим для демонстрации параметры заморозки.
Небольшой возраст транзакции:

        => alter system set vacuum_freeze_min_age=2;
        ALTER SYSTEM

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

        => alter system set vacuum_freeze_table_age=10;
        ALTER SYSTEM

        => \q

pg_ctl restart -m fast -w -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

.......................................................................

        psql 

Создадим таблицу и выполним ее очистку, чтобы заполнить карту видимости:

        => \c db6
        You are now connected to database "db6" as user "postgres".

        => create extension pageinspect;
        CREATE EXTENSION

        => create table tfreeze(id serial, n numeric) with (autovacuum_enabled = off);
        CREATE TABLE

        => insert into tfreeze(n) select 1 from generate_series(1,100000);
        INSERT 0 100000

        => vacuum tfreeze;
        VACUUM

.......................................................................

Создадим представление для того, чтобы наблюдать за битами-подсказками.
Нас интересует только xmin и биты, которые относятся к нему,
поскольку версии строк с ненулевым xmax будут очищены.

        => create view tfreeze_v as
        =>   select '(0,'||lp||')' 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 as xmin,
        =>          age(t_xmin) as xmin_age,
        =>          case when (t_infomask & 256) > 0 then 't' end as xmin_c,
        =>          case when (t_infomask & 512) > 0 then 't' end as xmin_a,
        =>          t_xmax as xmax,
        =>          t_ctid
        =>   from heap_page_items(get_raw_page('tfreeze',0))
        =>   where lp between 1 and 5
        =>   order by lp;
        CREATE VIEW

.......................................................................

Каков возраст транзакции, создавшей строки?

        => select * from tfreeze_v;
         ctid  | state  |  xmin  | xmin_age | xmin_c | xmin_a | xmax | t_ctid 
        -------+--------+--------+----------+--------+--------+------+--------
         (0,1) | normal | 554822 |        2 | t      |        |    0 | (0,1)
         (0,2) | normal | 554822 |        2 | t      |        |    0 | (0,2)
         (0,3) | normal | 554822 |        2 | t      |        |    0 | (0,3)
         (0,4) | normal | 554822 |        2 | t      |        |    0 | (0,4)
         (0,5) | normal | 554822 |        2 | t      |        |    0 | (0,5)
        (5 rows)
        

Возраст равен 2; осталась одна транзакция до заморозки.

.......................................................................

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => select * from tfreeze_v;
         ctid  | state  |  xmin  | xmin_age | xmin_c | xmin_a |  xmax  |  t_ctid  
        -------+--------+--------+----------+--------+--------+--------+----------
         (0,1) | normal | 554822 |        3 | t      |        |      0 | (0,1)
         (0,2) | normal | 554822 |        3 | t      |        | 554824 | (490,41)
         (0,3) | normal | 554822 |        3 | t      |        |      0 | (0,3)
         (0,4) | normal | 554822 |        3 | t      |        |      0 | (0,4)
         (0,5) | normal | 554822 |        3 | t      |        |      0 | (0,5)
        (5 rows)
        

Сейчас страница уже попадает под условия заморозки: она изменена
(поэтому очистка ее обработает), а возраст транзакции превышает значение,
установленное параметром vacuum_freeze_min_age.

.......................................................................

        => vacuum tfreeze;
        VACUUM

        => select * from tfreeze_v;
         ctid  | state  |  xmin  | xmin_age | xmin_c | xmin_a | xmax | t_ctid 
        -------+--------+--------+----------+--------+--------+------+--------
         (0,1) | normal | 554822 |        3 | t      | t      |    0 | (0,1)
         (0,2) | unused |        |          |        |        |      | 
         (0,3) | normal | 554822 |        3 | t      | t      |    0 | (0,3)
         (0,4) | normal | 554822 |        3 | t      | t      |    0 | (0,4)
         (0,5) | normal | 554822 |        3 | t      | t      |    0 | (0,5)
        (5 rows)
        

После очистки установлены оба бита, как будто транзакция и зафиксирована,
и откачена одновременно. Это признак замороженной версии строки.

.......................................................................

Какой сейчас возраст транзакций для таблицы tfreeze?

        => select relfrozenxid, age(relfrozenxid) from pg_class where relname='tfreeze';
         relfrozenxid | age 
        --------------+-----
               554821 |   4
        (1 row)
        

Потому что очистка выполнялась не для всей таблицы, а только для измененных страниц,
так что нет гарантии, что где-то на других страницах не осталось версий строк
с более старыми транзакциями.

.......................................................................

Выполним еще несколько транзакций, чтобы возраст транзакций таблицы достиг
значения параметра vacuum_freeze_table_age.

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

        => update tfreeze set n=n+1 where id=2;
        UPDATE 1

.......................................................................

        => select relfrozenxid, age(relfrozenxid) from pg_class where relname='tfreeze';
         relfrozenxid | age 
        --------------+-----
               554821 |  10
        (1 row)
        

        => vacuum tfreeze;
        VACUUM

        => select relfrozenxid, age(relfrozenxid) from pg_class where relname='tfreeze';
         relfrozenxid | age 
        --------------+-----
               554829 |   2
        (1 row)
        

.......................................................................

Теперь номер последней замороженной транзакции увеличился, поскольку была
просканирована вся таблица целиком, без учета карты видимости.
Также есть специальная версия команды очистки - vacuum freeze -
которая принудительно выполняет полное сканирование и заморозку.

.......................................................................

Номер последней замороженной транзакции есть и на уровне всей БД:

        => select datfrozenxid from pg_database where datname='postgres';
         datfrozenxid 
        --------------
                  656
        (1 row)
        

Он устанавливается в минимальное значение из frozenxid всех таблиц этой БД.

.......................................................................

Конец демонстрации.

        => alter system reset all;
        ALTER SYSTEM

        => \q

pg_ctl restart -m fast -w -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
