Tuning Postgres Autovacuum ssd

24.1. Регламентная очистка
Автоматическая очистка
Давайте отключим автовакуум!?
видео
Настройка Autovacuum в PostgreSQL и Autovacuum Internals
Агрессивный Autovacuum на PostgreSQL
Основы автовакуумной настройки
pg-autovacuum-diagnostics
MVCC-7. Автоочистка

$ sudo su postgres
$ psql
postgres=# SELECT name,setting FROM pg_settings WHERE name ~ 'autova|vacuum';

Пример настройки для SSD:
Давайте отключим автовакуум!?

$ sudo nano /etc/postgresql/10/main/postgresql.conf

Добавить в конец: 

log_autovacuum_min_duration = 0
vacuum_cost_delay = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 5
vacuum_cost_page_dirty = 5
vacuum_cost_limit = 200
autovacuum_max_workers =4
autovacuum_naptime = 1s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 5ms
autovacuum_vacuum_cost_limit = 1000
#autovacuum_vacuum_cost_limit = -1
Сохранить.

$ sudo systemctl restart postgresql.service

$ sudo su postgres
$ psql
postgres=# SELECT name,setting FROM pg_settings WHERE name ~ 'autova|vacuum';

$ ps -eaf | egrep "/post|autovacuum"

Оценка работы autovacuum в конкретной базе, например demo

$ sudo su postgres
$ psql
postgres=# \c demo
Вы подключены к базе данных "demo" как пользователь "postgres".
demo=#
Выполнить:
 

WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','),\

 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','),\

 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','),\

  'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','),\

 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_vac_threshold,\

 current_setting('autovacuum_vacuum_threshold')::BIGINT)\

  + coalesce(RS.rel_av_vac_scale_factor,\

 current_setting('autovacuum_vacuum_scale_factor')::NUMERIC)\

  * C.reltuples, '9G999G999G999') AS av_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold,\

 current_setting('autovacuum_vacuum_threshold')::BIGINT)\

  + coalesce(RS.rel_av_vac_scale_factor, current_setting(\

'autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples)\

  < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    END AS expect_av
FROM
    pg_stat_user_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY C.reltuples DESC;