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”
that is false . please modify the article
… DROP INDEX momentarily takes an exclusive lock on the parent table, blocking both writes and reads…
@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.