Situation & goal: We must rename a column and split its value into two new columns across millions of rows without downtime and while clients continue to read/write.Strategy overview (safe, incremental):1. Compatibility layer- Add new columns (col_a, col_b) nullable.- Keep old column (col_old) until cutover.- Implement adapter in application/service: - Writes: dual-write — on every write, write both col_old and computed split into col_a/col_b. - Reads: prefer new columns if non-null; fallback to parsing col_old.Pseudocode:python
def read_row(row):
if row.col_a is not None or row.col_b is not None:
return (row.col_a, row.col_b)
return parse_old(row.col_old)
def write_row(val):
col_a, col_b = split(val)
db.update(..., col_old=val, col_a=col_a, col_b=col_b)
sql
UPDATE table
SET col_a = split_part(col_old,':',1), col_b = split_part(col_old,':',2)
WHERE col_a IS NULL
AND id BETWEEN $start AND $end;
