Disable autovacuum 

α=> SELECT pid, backend_start, backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum launcher';

  pid  |         backend_start         |    backend_type     
-------+-------------------------------+---------------------
 20241 | 2019-02-27 15:38:51.965183+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)

The database, the table, and the index 

α=> CREATE DATABASE admin_maintenance;

CREATE DATABASE

α=> \c admin_maintenance

You are now connected to database "admin_maintenance" as user "student".

α=> CREATE TABLE t(n numeric);

CREATE TABLE

α=> CREATE INDEX t_n on t(n);

CREATE INDEX

Inserting rows 

α=> 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    | 3448 kB
(1 row)





Updating the rows, no vacuuming





α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> :SIZE





 table_size | index_size 
------------+------------
 5936 kB    | 5136 kB
(1 row)






α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> :SIZE





 table_size | index_size 
------------+------------
 7904 kB    | 6840 kB
(1 row)






α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> :SIZE





 table_size | index_size 
------------+------------
 9880 kB    | 8632 kB
(1 row)





Size of the table and the index is constantly growing.




Vacuum Full





α=> VACUUM FULL t;





VACUUM





α=> :SIZE





 table_size | index_size 
------------+------------
 3944 kB    | 2664 kB
(1 row)





Table size is now almost the same as in the beginning. Index is even smaller:
it is more efficient to build an index on a large volume of data than to
add the data row-by-row to an empty index.




Updating the rows with vacuuming





α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> VACUUM t;





VACUUM





α=> :SIZE





 table_size | index_size 
------------+------------
 5944 kB    | 3992 kB
(1 row)






α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> VACUUM t;





VACUUM





α=> :SIZE





 table_size | index_size 
------------+------------
 5944 kB    | 3992 kB
(1 row)






α=> UPDATE t SET n=n WHERE n < 0.5;





UPDATE 50116





α=> VACUUM t;





VACUUM





α=> :SIZE





 table_size | index_size 
------------+------------
 5944 kB    | 3992 kB
(1 row)





Size increased for the first time and than doesn't change. After vacuum row
versions fit into the cleaned-up pages.




Re-enable autovacuum





α=> ALTER SYSTEM SET autovacuum = on;





ALTER SYSTEM





α=> SELECT pg_reload_conf();





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