Skip to Content
PostgreSQL 10 Administration Cookbook - Fourth Edition
book

PostgreSQL 10 Administration Cookbook - Fourth Edition

by Simon Riggs, Gianni Ciolli
May 2018
Intermediate to advanced content levelIntermediate to advanced
576 pages
30h 25m
English
Packt Publishing
Content preview from PostgreSQL 10 Administration Cookbook - Fourth Edition

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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

PostgreSQL 13 Cookbook

PostgreSQL 13 Cookbook

Vallarapu Naga Avinash Kumar
PostgreSQL 9 Administration Cookbook - Second Edition

PostgreSQL 9 Administration Cookbook - Second Edition

Simon Riggs, GIANNI CIOLLI, Hannu Krosing, Gabriele Bartolini

Publisher Resources

ISBN: 9781788474924Supplemental Content