Regular Maintenance - PostgreSQL 9 Administration - page 228

Now, let's look at the internal identifier of the tables, the oid , and the current file number,
or relfilenodes as shown next:
SELECT oid, relname, relfilenode
FROM pg_class
WHERE oid in (SELECT indexrelid
FROM pg_index
WHERE indrelid = 'test'::regclass);
oid | relname | relfilenode
-------+-------------------+-------------
16639 | test_pkey | 16639
16641 | test_category_idx | 16641
(2 rows)
How to do it...
PostgreSQL supports a command known as CREATE INDEX CONCURRENTLY , that 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 had. Et voila, fresh index, 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;
and if we check our internal identifiers again, we get the following:
SELECT oid, relname, relfilenode
FROM pg_class
WHERE oid in (SELECT indexrelid
FROM pg_index
WHERE indrelid = 'test'::regclass);
oid | relname | relfilenode
-------+-------------------+-------------
16639 | test_pkey | 16639
16642 | test_category_idx | 16642
(2 rows)
So, we can see that test_category_idx is now a completely new index.