An original worked migration guide: introduce a required task status gradually, backfill existing records, and verify compatibility before removing the old field.
Help me plan a schema change for a fictional task app. The tasks table currently has a non-null boolean column called done. I want to introduce status with the values todo, in_progress, and done, while old application instances may still be running during deployment.
Start with the compatibility questions and a staged plan. Define how existing boolean values map to the new field, explain why a direct rename is insufficient, and do not provide a destructive migration that assumes every client upgrades at once.
The old boolean can represent only two states: false maps to todo and true maps to done. The new in_progress state has no exact boolean equivalent. A direct rename would preserve neither the data type nor the meaning expected by old code, and a single deployment instant cannot be assumed when requests or workers may still use the previous application version.
First identify every reader and writer, including background jobs, imports, admin tools, and long-lived workers. Decide how updates made during the transition will keep the two representations consistent. This guide chooses a temporary compatibility release that continues to expose only the old two-state behavior, followed by enabling in_progress once incompatible writers have been retired.
The staged plan is to add a nullable status column, deploy code that understands both fields, backfill existing rows, enforce the final constraints, and then enable the richer workflow. Remove done only after the rollback window and a usage check. PostgreSQL describes adding columns and constraints in its table-modification documentation; the deployment sequence here is an application-specific compatibility plan.
Work through the backfill stage. Assume the application team can identify and drain the old instances and workers, but the table is large enough that one unbounded update is undesirable. Explain how the compatibility release should read and write records before and during the backfill.
Describe a bounded backfill and the checks that tell us whether it completed correctly. Include null values, unexpected statuses, and an update that happens while backfilling. Be precise about where the consistency guarantee comes from.
During the initial rolling deployment, old code may still update only done, so do not treat a partially populated status column as authoritative. Keep reads based on done until all old writers have been drained. The compatibility release writes done and its two-state status mapping together in the same transaction; only after every active writer follows that rule should the mapping backfill begin.
Process stable primary-key ranges in bounded batches and update only rows whose status is null. A conceptual assignment is status = CASE WHEN done THEN 'done' ELSE 'todo' END. Commit each batch, record its last processed range, and measure latency and lock waits while it runs. Restarting the job should revisit safely rather than skipping rows because it assumes an earlier batch completed after a lost connection.
Once old writers are retired, compatible writers atomically maintain both fields, and the conditional backfill fills only missing status values. Check that null status count is zero, every value belongs to the allowed set, and each two-state status agrees with done before switching reads. A late old worker would violate these assumptions; deployment inventory and writer retirement are part of the correctness argument, not optional housekeeping.
Explain when to enforce non-null status and when to enable in_progress. I want a clear rollback plan for failures before and after that richer state is used. Include what should be checked in a staging rehearsal with production-like row counts.
Write a short migration runbook outline with stop conditions. Distinguish application rollback from restoring lost data, and explain why retaining done temporarily does not automatically make every rollback safe.
After the backfill and compatibility checks pass, enforce the allowed-value and non-null requirements using a migration approach reviewed for the actual database version, table size, and workload. Then deploy the status-based read path. Enable in_progress only when the running code and relevant workers support it. Continue maintaining done as a coarse compatibility projection if needed, with status as the authoritative field.
Before in_progress is enabled, a rollback to the tested compatibility release can preserve the two-state behavior. After richer states exist, older code could collapse in_progress into false and overwrite information during a later edit. Freeze the new-state feature and prefer a forward fix or a carefully reviewed compatibility rollback; retaining the boolean alone is not a guarantee that the old application can safely run.
The runbook should record the schema expansion, writer inventory, batch progress, consistency queries, constraint rollout, feature enablement, and later field removal. Rehearse interrupted batches, concurrent updates, and the rollback target on production-like data. Stop if latency exceeds the agreed limit, old writers remain, or consistency checks fail. Data restoration is a separate procedure with its own recovery point and potential loss of newer writes, so it is not interchangeable with reverting application code.
Comments 0
Share how you would use this prompt or suggest a useful improvement.