Adding data while indexing

Creating an index is easy. However, keep in mind that you cannot modify a table, while an index is being built. The CREATE INDEX command will lock up a table using a SHARE lock to ensure that no changes happen. While this is clearly no problem for small tables, it will cause issues on large ones on production systems. Indexing a terabyte of data or so will take some time and therefore blocking a table for too long can become an issue.

The solution to the problem is CREATE INDEX CONCURRENTLY command. Building the index will take a lot longer (usually at least twice as long) but you can use the table normally during index creation.

Here is how it works:

test=# CREATE INDEX CONCURRENTLY idx_name2 ON t_test (name);
CREATE INDEX

Note that PostgreSQL does not guarantee success if you are using the CREATE INDEX CONCURRENTLY command. An index can end up being marked as invalid if the operations going on on your system somehow conflict with index creation.