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?
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.
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);
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.
