When creating an index for a large table, does it affect the ability to read data from it during its construction?

When creating an index for a large table, most modern DBMSs typically allow data to be read from the table without being fully blocked. However, behavior can depend on the specific DBMS and the type of index.

For example, in PostgreSQL, creating an index using the CREATE INDEX CONCURRENTLY command allows the index to be created without blocking read and write operations, although the process takes more time.

If the index is created without the concurrent creation option, some DBMSs may lock for writing or even reading, which temporarily restricts access to the table.

Thus:

• In most cases, data reading is possible during index construction.
• Special index creation modes are used to minimize locks.
• It is important to consider the specifics of the particular DBMS and the type of index.

Example for PostgreSQL:

-- Creating an index without blocking read and write
CREATE INDEX CONCURRENTLY idx_name ON table_name(column_name);

This allows continuing work with the table during index construction.