Mastering PostgreSQL 12 - Third Edition

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