I want to add an index to a table with a million records — what are the consequences and how to do it correctly?

Adding an index to a table with a million records can lead to significant database load: the operation may take time, lock the table, or slow down other queries. To minimize the impact:

• Use the ONLINE option when creating the index (if supported by the DBMS) to avoid locking the table.
• Perform the operation during periods of low load.
• Consider creating the index on a replica and then switching to it.
• Ensure that the index is truly needed to avoid wasting resources.

Example for PostgreSQL:

CREATE INDEX CONCURRENTLY idx_column ON table(column);

The CONCURRENTLY option allows creating an index without locking write operations, but it takes more time and consumes more resources.