How do you add a new column to a table with 300 MILLION rows without taking your system offline?
Imagine this scenario: Your database is being read and written to 24/7. You can’t afford any downtime, and taking the database offline isn't an option.
The most common mistake? Trying to add the column and update all 300 million rows in one giant transaction. Doing this will lock massive numbers of rows, forcing live user traffic to wait and causing severe latency or outages.
Here is the step-by-step zero-downtime strategy:
Stage 1: Add the column as NULLABLE Add the new column as nullable with no default value.
In most modern databases, this is purely a metadata change. The database simply updates its schema definition without rewriting existing rows, completing in milliseconds.
Get Md. Zahid Fesabelilla’s stories in your inbox
Join Medium for free to get updates from this writer.
Stage 2: Fix new writes first Before updating historical data, update your application logic. Ensure all new incoming data includes the new column values. Once deployed, every new row written to the database will be complete, preventing you from constantly chasing a growing backlog of incomplete entries.
Stage 3: Backfill historical data in small batches Run a background process to populate the new column for existing rows in small, controlled batches (e.g., a few thousand rows at a time). Pause briefly between batches to check the database load, pausing execution if CPU or lock contention spikes.
Instead of attempting one high-risk migration, break complex database operations into small, safe, and incremental changes.
Reff: https://lnkd.in/gc4MFCdP
#DatabaseMigration #DatabasePerformance #SystemDesign #SoftwareArchitecture #BackendDevelopment #ScalableSystems #ZeroDowntimeDeployment #PostgreSQL #DevOps #EngineeringBestPractices
