On 4th of April 2019, Robert Haas committed patch:
Allow VACUUM to be run with index cleanup disabled. This commit adds a new reloption, vacuum_index_cleanup, which controls whether index cleanup is performed for a particular relation by default. It also adds a new option to the VACUUM command, INDEX_CLEANUP, which can be used to override the reloption. If neither the reloption nor the VACUUM option is used, the default is true, as before. Masahiko Sawada, reviewed and tested by Nathan Bossart, Alvaro Herrera, Kyotaro Horiguchi, Darafei Praliaskouski, and me. The wording of the documentation is mostly due to me. Discussion: http://postgr.es/m/CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com
This is basically for making certain maintenance tasks faster.
Let's assume you have a table, with LOTS of indexes. And you plan on reindexing them, but first, you'd like to vacuum the table.
Normal vacuum will also vacuum indexes. To see it, first I need test table:
=$ CREATE TABLE test ( id serial PRIMARY KEY, x text NOT NULL, y int4, UNIQUE (x) ); CREATE TABLE =$ CREATE INDEX z ON test (y); CREATE INDEX
and now I'll run vacuum. Due to volume of output, I'll run it with some grepping:
=$ psql -Xc "vacuum (verbose true) test" 2>&1 | grep INFO INFO: vacuuming "public.test" INFO: index "test_pkey" now contains 0 row versions in 1 pages INFO: index "test_x_key" now contains 0 row versions in 1 pages INFO: index "z" now contains 0 row versions in 1 pages INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages INFO: vacuuming "pg_toast.pg_toast_18582" INFO: index "pg_toast_18582_index" now contains 0 row versions in 1 pages INFO: "pg_toast_18582": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
If you are sure that scanning indexes is not needed you can:
=$ psql -Xc "vacuum (index_cleanup false, verbose true) test" 2>&1 | grep INFO INFO: vacuuming "public.test" INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages INFO: vacuuming "pg_toast.pg_toast_18582" INFO: "pg_toast_18582": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
Additionally, if you have a table that you know doesn't need index vacuuming ever (unless specified) – you can change default:
=$ ALTER TABLE test SET ( vacuum_index_cleanup = FALSE ); ALTER TABLE
and then:
=$ psql -Xc "vacuum verbose test" 2>&1 | grep INFO INFO: vacuuming "public.test" INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages INFO: vacuuming "pg_toast.pg_toast_18582" INFO: "pg_toast_18582": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
of course, if you'd need, you can force vacuuming the indexes:
=$ psql -Xc "VACUUM ( index_cleanup true, verbose true ) test" 2>&1 | grep INFO INFO: vacuuming "public.test" INFO: index "test_pkey" now contains 0 row versions in 1 pages INFO: index "test_x_key" now contains 0 row versions in 1 pages INFO: index "z" now contains 0 row versions in 1 pages INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages INFO: vacuuming "pg_toast.pg_toast_18582" INFO: index "pg_toast_18582_index" now contains 0 row versions in 1 pages INFO: "pg_toast_18582": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
Pretty cool. In some production systems, I definitely have some tables that I could use it with. Thanks to all involved.