The schema is live, but the data feels wrong. You check the table again. The fix is simple: add a new column. The challenge is doing it without breaking production, without locking writes, and without leaving a trail of partial migrations.
Adding a new column sounds trivial until scale turns seconds into hours. In relational databases like PostgreSQL, MySQL, and MariaDB, a naïve ALTER TABLE can lock the table and block queries. At terabyte scale, that downtime is unacceptable. The key is choosing the right approach for your database engine, workload, and uptime requirements.
For PostgreSQL, adding a nullable column with no default is fast and metadata-only. Avoid setting a default that isn’t NULL in the same statement; that forces a table rewrite. If you must set a default, add the column first, then run an UPDATE in batches, or use ALTER TABLE ... SET DEFAULT after the fact. For MySQL, the process varies by storage engine and version. With InnoDB, some schema changes are online, but many still require a copy of the table. MySQL’s ALGORITHM=INPLACE and LOCK=NONE modifiers can minimize impact, but test them on production-like data before rolling out.