Mastering PostgreSQL 9.6

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