When you add a new column, you alter the shape of data. It affects queries, indexes, and storage. In relational databases like PostgreSQL and MySQL, ALTER TABLE is simple to write but complex to execute. Size, default values, and constraints all impact performance. Adding a column with a default value to a massive table will lock it. That lock can stall every read and write until the operation completes. The larger the table, the longer the stall.
For high-traffic systems, the safest path is to add a nullable column first. This avoids immediate data rewrite. Then backfill in small batches. Tools like pt-online-schema-change or gh-ost can handle this online without downtime. After the backfill, set the default and add constraints. Each step is isolated, reducing operational risk.
Indexing a new column is powerful but dangerous. An unindexed column on a hot query slows retrieval. But indexing too early or without monitoring can spike CPU usage and blow up write latency. Track query plans before and after indexing. Use partial or expression indexes if the data justifies it.