Index creation taking forever on postgres

Question

I was trying to create an index to an integer column using a btree index, but it was taking forever (more than 2 hours!). The table has 17.514.879 lines. I didn't expect it to take that long.

After almost 2.5 hours, the connection to the database just died. When I reconnected to it, the index was there, but I don't know how good is this index.

How can I be sure the index was not messed up by the connection lost?

Answer 1

How to Check Whether the Index is Fine

Connect to the database via psql and run \d table_name (where table_name is the name of your table). For example:

grn=# \d users
            Table "public.users"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 name   | character varying(255) |
Indexes:
    "users_name_idx" btree (name)

You'll see indexes listed below the table schema. If the index is corrupt it'll be marked as so.

How to Create Indexes Without Locking the Whole Table

You can create an index in a way that doesn't lock the whole table but is even slower. To do so you need to add CONCURRENTLY to CREATE INDEX. For example:

CREATE INDEX CONCURRENTLY users_name_idx ON users(name);

How to Fix a Corrupt Index

If the index is corrupt you can either drop it and recreate CONCURRENTLY or use REINDEX INDEX index_name. For example:

REINDEX INDEX users_name_idx

will recreate the users_name_idx.