The query returned fast, but the table was wrong. The missing piece was a new column.
Adding a new column should be simple. In SQL, the syntax is direct:
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
The complexity starts when the dataset is large, the table is hot, and the service cannot stop. Adding columns in production requires thought about locks, migration paths, default values, and backward compatibility.
A new column can cause a full table rewrite, locking reads and writes until the operation completes. Modern databases like PostgreSQL optimize some cases, but types with defaults can still be slow. Plan the change for minimal impact—add nullable columns first, then backfill in small batches.
Data Integrity
If the new column cannot be NULL, create it as nullable, populate it via background jobs, verify the data, and then add the NOT NULL constraint. This avoids downtime and rollback disasters.
Versioning and Deployment
Application code must handle schemas that are mid-migration. Deploy code that does not break if the column is absent. After the column exists and data is in place, flip feature flags or merge code relying on it.
Migrations should be part of CI/CD pipelines. Use migration scripts that can run incrementally and be rolled back if necessary. Monitor metrics for slow queries, lock times, and replication lag during the migration.
A new column is easy to write into code but hard to introduce into a living, high-traffic database. When planned right, it can be online, safe, and fast.
See schema changes like this run live in minutes at hoop.dev.