4 ms·
To clarify: REINDEX CONCURRENTLY is a new feature. Previously there were only DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY, which meant that while you
by jeltz 7y ago
To clarify: REINDEX CONCURRENTLY is a new feature. Previously there were only DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY, which meant that while you could first create a new index and then drop the old one renaming still required an exclusive lock (this is also fixed in 12), as did switching over all foreign keys to the new index. REINDEX CONCURRENTLY essentially does all this work for you in a single command while also making sure no reader or writer is locked out from the table.
- ioltas 7y agoAn advantage of REINDEX CONCURRENTLY is that it becomes much easier to handle dependencies of a rebuilt index with constraints, as what the feature roughly does it to add a swapping phase between the creation and the drop portions which switches the dependencies from the old index to the new fresh one. Table-level reindex also makes life easier. The feature is equivalent to external tools like pg_repack (formerly pg_reorg) in more performant, and has a couple of limitations to be aware of: https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REIN...