What happens when creating an index on a large hot table using a regular CREATE INDEX? How to properly create indexes in production?

When creating an index on a large hot table, a regular CREATE INDEX operation causes a lock on the table for writing (depending on the DBMS and index type), which can lead to performance degradation and service downtime that uses this table. Index creation is a resource-intensive operation that can take a significant amount of time and load the disk and CPU.

The correct approach to creating indexes in production:

• Use index creation with the CONCURRENTLY option (for example, in PostgreSQL) — this allows creating an index without locking write operations.
• Schedule index creation during periods of low load.
• Create indexes on copies of tables or use replicas to minimize impact on the primary database.
• Monitor the index creation process and system load.

Example for PostgreSQL:

CREATE INDEX CONCURRENTLY idx_column ON table_name(column);

This will avoid locks and minimize impact on application performance.