Concurrent indexes
In this recipe, we will be discussing how to create an online index that will not block the base table for the incoming transactions.
Getting ready
In general, while creating/dropping an index, it will block the table to avoid further write operations into the index. Until the index operation is complete, the whole table will be locked for write operations, which will be an outage to the application. To avoid this table lock problem, PostgreSQL provides an option called concurrent, which will avoid this blocking behavior. It will also keep this index status as invalid until its creation is successfully completed. Building an index concurrently takes more time, as the operation needs to allow the incoming write operations.
How to do it...
Let's create a regular index on the bmsql_item table, and see how many full table scans it performed to build this index:
benchmarksql=# CREATE INDEX data_idx ON bmsql_item(i_data);
CREATE INDEX
benchmarksql=# SELECT seq_scan,seq_tup_read...
