PostgreSQL 11 Administration Cookbook

How to do it…

PostgreSQL supports a command known as CREATE INDEX CONCURRENTLY, which builds an index without taking a full table lock. PostgreSQL also supports the ability to have two indexes, with different names, that have exactly the same definition. So, the trick is to build another index identical to the one you wish to rebuild, drop the old index, and then rename the new index to the same name as the old index. Et voilà, fresh index, and no locking! Let's see that in slow motion:

CREATE INDEX CONCURRENTLY new_index ON test (category);BEGIN;DROP INDEX test_category_idx;ALTER INDEX new_index RENAME TO test_category_idx;COMMIT;

When we check our internal identifiers again, we get the following:

SELECT oid, relname, relfilenodeFROM pg_class ...