Re: REINDEX CONCURRENTLY 2.0

From: Andreas Karlsson <andreas(at)proxel(dot)se>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: REINDEX CONCURRENTLY 2.0
Date: 2017-02-28 17:21:39
Message-ID: a8e56251-bd3c-521e-7a2c-35d66b5f7f29@proxel.se
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

Here is a third take on this feature, heavily based on Michael Paquier's
2.0 patch. This time the patch does not attempt to preserve the index
oids, but instead creates new indexes and moves all dependencies from
the old indexes to the new before dropping the old ones. The only
downside I can see to this approach is that we no logner will able to
reindex catalog tables concurrently, but in return it should be easier
to confirm that this approach can be made work.

This patch relies on that we can change the indisvalid flag of indexes
transactionally, and as far as I can tell this is the case now that we
have MVCC for the catalog updates.

The code does some extra intermediate commits when building the indexes
to avoid long running transactions.

How REINDEX CONCURRENTLY operates:

For each table:

1. Create new indexes without populating them, and lock the tables and
indexes for the session.

2. After waiting for all running transactions populate each index in a
separate transaction and set them to ready.

3. After waiting again for all running transactions validate each index
in a separate transaction (but not setting them to valid just yet).

4. Swap all dependencies over from each old index to the new index and
rename the old and the new indexes (from the <name> to <name>_ccold and
<name>_new to <name>), and set isprimary and isexclusion flags. Here we
also mark the new indexes as valid and the old indexes as invalid.

5. After waiting for all running transactions we change each index from
invalid to dead.

6. After waiting for all running transactions we drop each index.

7. Drop all session locks.

Andreas

Attachment Content-Type Size
reindex-concurrenctly-v1.patch text/x-diff 89.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-02-28 17:46:13 Re: Performance degradation in TPC-H Q18
Previous Message Tomas Vondra 2017-02-28 17:15:47 update comments about CatalogUpdateIndexes