Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

On 29th of March 2019, Peter Eisentraut committed patch:

REINDEX CONCURRENTLY 
 
This adds the CONCURRENTLY option to the REINDEX command.  A REINDEX
CONCURRENTLY on a specific index creates a new index (like CREATE
INDEX CONCURRENTLY), then renames the old index away and the new index
in place and adjusts the dependencies, and then drops the old
index (like DROP INDEX CONCURRENTLY).  The REINDEX command also has
the capability to run its other variants (TABLE, DATABASE) with the
CONCURRENTLY option (but not SYSTEM).
 
The reindexdb command gets the --concurrently option.
 
Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
 
Discussion: https://www.postgresql.org/message-id/flat/-956b-4478-45ed-%402ndquadrant.com#

This is amazing.

Well, reindex can be used to fix broken indexes, or remove bloat from one.

But it required exclusive lock on the index for the duration of rebuild.

This was possible to work around by doing:

=$ CREATE INDEX concurrently new_index ON ...;
=$ DROP INDEX concurrently old_index;
=$ ALTER INDEX new_index RENAME TO old_index;

Which also needed exclusive lock, but only for the rename process.

What's more – it wasn't that easy to do it for indexes that are basis of constraint, especially if it was primary key index on a table that has foreign keys pointing to it.

Now, luckily we can:

=$ CREATE TABLE s (id serial PRIMARY KEY, payload text);
CREATE TABLE
 
=$ CREATE TABLE d (id serial PRIMARY KEY, s_id int4 NOT NULL REFERENCES s (id), payload text);
CREATE TABLE
 
=$ reindex INDEX CONCURRENTLY s_pkey;
REINDEX

This is absolutely great, thanks guys.

2 thoughts on “Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY”

  2. @hessam – you do realize that I wrote about a new thing in PostgreSQL 12, and you commented with docs about PostgreSQL 9.4?

Comments are closed.