pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Geoghegan <pg@bowt.ie>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Don't overlook indexes during parallel VACUUM.
Date: Tue, 02 Nov 2021 19:07:40 +0000
Message-ID: <E1mhz8G-0001MD-9S@gemulon.postgresql.org> (raw)

Don't overlook indexes during parallel VACUUM.

Commit b4af70cb, which simplified state managed by VACUUM, performed
refactoring of parallel VACUUM in passing.  Confusion about the exact
details of the tasks that the leader process is responsible for led to
code that made it possible for parallel VACUUM to miss a subset of the
table's indexes entirely.  Specifically, indexes that fell under the
min_parallel_index_scan_size size cutoff were missed.  These indexes are
supposed to be vacuumed by the leader (alongside any parallel unsafe
indexes), but weren't vacuumed at all.  Affected indexes could easily
end up with duplicate heap TIDs, once heap TIDs were recycled for new
heap tuples.  This had generic symptoms that might be seen with almost
any index corruption involving structural inconsistencies between an
index and its table.

To fix, make sure that the parallel VACUUM leader process performs any
required index vacuuming for indexes that happen to be below the size
cutoff.  Also document the design of parallel VACUUM with these
below-size-cutoff indexes.

It's unclear how many users might be affected by this bug.  There had to
be at least three indexes on the table to hit the bug: a smaller index,
plus at least two additional indexes that themselves exceed the size
cutoff.  Cases with just one additional index would not run into
trouble, since the parallel VACUUM cost model requires two
larger-than-cutoff indexes on the table to apply any parallel
processing.  Note also that autovacuum was not affected, since it never
uses parallel processing.

Test case based on tests from a larger patch to test parallel VACUUM by
Masahiko Sawada.

Many thanks to Kamigishi Rei for her invaluable help with tracking this
problem down.

Author: Peter Geoghegan <pg@bowt.ie>
Author: Masahiko Sawada <sawada.mshk@gmail.com>
Reported-By: Kamigishi Rei <iijima.yun@koumakan.jp>
Reported-By: Andrew Gierth <andrew@tao11.riddles.org.uk>
Diagnosed-By: Andres Freund <andres@anarazel.de>
Bug: #17245
Discussion: https://postgr.es/m/17245-ddf06aaf85735f36@postgresql.org
Discussion: https://postgr.es/m/20211030023740.qbnsl2xaoh2grq3d@alap3.anarazel.de
Backpatch: 14-, where the refactoring commit appears.

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/61a86ed55ba169044b9a692542bad1b05341147b

Modified Files
--------------
src/backend/access/heap/vacuumlazy.c          | 60 ++++++++++++++++-----------
src/include/commands/vacuum.h                 |  2 +-
src/test/regress/expected/vacuum_parallel.out | 49 ++++++++++++++++++++++
src/test/regress/parallel_schedule            |  1 +
src/test/regress/sql/vacuum_parallel.sql      | 46 ++++++++++++++++++++
5 files changed, 132 insertions(+), 26 deletions(-)



view thread (2+ messages)  latest in thread

Message-ID: <E1mhz8G-0001MD-9S@gemulon.postgresql.org>
Permalink:  ../E1mhz8G-0001MD-9S@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1mhz8G-0001MD-9S@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: pg@bowt.ie
  Subject: Re: pgsql: Don't overlook indexes during parallel VACUUM.
  In-Reply-To: <E1mhz8G-0001MD-9S@gemulon.postgresql.org>
  References: <E1mhz8G-0001MD-9S@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox