Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.

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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.