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 the 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 1 TB of data or so will take some time, and therefore blocking a table for too long can become an issue.

The solution to this problem is the 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's how it works:

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

Note that PostgreSQL doesn't guarantee success if you are using the CREATE INDEX CONCURRENTLY command. An index can end up being marked as invalid if the operations running on your system somehow conflict with the index creation. If you want to figure out if your indexes are invalid, use \d on the relation.