=> SELECT pid, backend_start, backend_type FROM pg_stat_activity WHERE backend_type = 'autovacuum launcher';
pid | backend_start | backend_type -------+-------------------------------+--------------------- 11118 | 2018-03-02 14:25:48.585017+03 | autovacuum launcher (1 row)
=> ALTER SYSTEM SET autovacuum = off;
ALTER SYSTEM
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
=> SELECT pid, backend_start, backend_type FROM pg_stat_activity WHERE backend_type = 'autovacuum launcher';
pid | backend_start | backend_type -----+---------------+-------------- (0 rows)
=> CREATE DATABASE admin_maintenance;
CREATE DATABASE
=> \c admin_maintenance
You are now connected to database "admin_maintenance" as user "postgres".
=> CREATE TABLE t(n numeric);
CREATE TABLE
=> CREATE INDEX t_n on t(n);
CREATE INDEX
=> INSERT INTO t SELECT random() FROM generate_series(1,100000);
INSERT 0 100000
=> \set SIZE 'SELECT pg_size_pretty(pg_table_size(''t'')) table_size, pg_size_pretty(pg_indexes_size(''t'')) index_size;'
=> :SIZE
table_size | index_size ------------+------------ 3960 kB | 3440 kB (1 row)
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> :SIZE
table_size | index_size ------------+------------ 5936 kB | 5168 kB (1 row)
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> :SIZE
table_size | index_size ------------+------------ 7912 kB | 6864 kB (1 row)
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> :SIZE
table_size | index_size ------------+------------ 9880 kB | 8576 kB (1 row)
Размер таблицы и индекса постоянно растет.
=> VACUUM FULL t;
VACUUM
=> :SIZE
table_size | index_size ------------+------------ 3944 kB | 2664 kB (1 row)
Размер таблицы практически вернулся к начальному, индекс стал компактнее (построить индекс по большому объему данных эффективнее, чем добавлять эти данные к индексу построчно).
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> VACUUM t;
VACUUM
=> :SIZE
table_size | index_size ------------+------------ 5944 kB | 3992 kB (1 row)
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> VACUUM t;
VACUUM
=> :SIZE
table_size | index_size ------------+------------ 5952 kB | 3992 kB (1 row)
=> UPDATE t SET n=n WHERE n < 0.5;
UPDATE 50191
=> VACUUM t;
VACUUM
=> :SIZE
table_size | index_size ------------+------------ 5952 kB | 3992 kB (1 row)
Размер увеличился один раз и затем стабилизировался.
=> ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)