How can an index be created for a large table column without locking the entire table?

To create an index in a large table without locking the entire table in PostgreSQL, you can use the CONCURRENTLY option:

CREATE INDEX CONCURRENTLY index_name ON table_name(column_name);

This mode allows creating an index without exclusive locking the table, meaning other read and write operations can continue. However, creating an index this way takes more time and resources.

Important: CONCURRENTLY cannot be used inside a transaction; the command must be executed separately.

Other DBMS may have similar mechanisms, for example, in MySQL with InnoDB, you can use ALGORITHM=INPLACE to create indexes without full table lock.