PostgreSQL migration rollback: find the first incompatible write

The old worker can read the row and still do the wrong thing

A team adds scheduled dispatch to a PostgreSQL-backed job service. The existing worker sends every pending job immediately. The new release adds a dispatch_mode value of scheduled and a run_at timestamp. A customer can now ask for a job to run tomorrow.

Suppose the deployment fails after that request is stored. Restoring the old worker may succeed technically: the table still exists, the old columns are present, and its query still returns rows. It can also send tomorrow's job today. The first scheduled write has changed what a valid rollback must preserve.

This is an illustrative design exercise, with no production measurements. The decision is whether an older release can honor every accepted job, including work created during the release being reversed. A migration review needs that answer before new behavior is enabled.

Prepare every consumer before accepting scheduled jobs

Separate support for the new representation from permission to create it. AWS's rollback guidance describes preparing readers before activating new writers. Its deployment guidance also calls for testing coexistence and an actual rollback. Apply those principles to the job's meaning as well as its fields.

Call the original immediate-only release A. Release B understands immediate and scheduled jobs, including the rule that a scheduled job must not dispatch before run_at. B continues creating immediate jobs while scheduling is disabled. Expand the schema so both A and B can use it during this transition, and verify their real queries and writes against it.

Upgrade every consumer that can interpret or execute a job. Include workers started by autoscaling, delayed tasks, administrative tools, and recovery automation. Keep scheduled-job creation disabled until A can no longer reappear through an old image or rollback action. A healthy web deployment does not establish that the worker fleet has changed.

Release C enables scheduled-job creation. B becomes the retained rollback target because it can continue processing those jobs correctly. Turning creation off must preserve execution of already accepted scheduled jobs. A flag that disables both paths would strand work the service has promised to complete.

Constrain the stored state without breaking the old writer

For this design, dispatch_mode must be present and belong to the supported set. A scheduled job must have run_at. An immediate job can leave run_at empty, so adding a global NOT NULL requirement to that timestamp would break the intended coexistence. Preserve immediate behavior for older inserts that do not supply the new fields.

PostgreSQL documents that a CHECK expression passes when its result is true or null. A comparison involving a missing timestamp can therefore be weaker than it looks. Express the presence requirement explicitly, and check mode validity separately. A row constraint can protect this representation; the worker still owns the rule about when dispatch is allowed.

PostgreSQL 18 permits adding a CHECK constraint as NOT VALID and validating existing rows later. New inserts and updates must already obey it. Use this separation only with a plan for existing rows and old writers. ALTER TABLE lock requirements still apply, so skipping the initial scan does not establish uninterrupted service. The documentation defines behavior; a representative workload must establish the deployment's operating limits.

Test rollback with the data that makes it necessary

Build the rehearsal around observable dispatch decisions. Start with pending immediate jobs, then run A and B together with scheduled-job creation disabled. Verify that both accept the old write shape and preserve immediate processing. This checks the transition the rollout will actually make.

Once every consumer runs B, enable C and accept a job with a future run_at. Disable further scheduled-job creation, return the fleet to B, and advance a controlled clock across that timestamp. The job must remain pending before its due time and become eligible at or after it. Capture the selected release, stored row, and dispatch observation for each step.

Exercise a worker restart and the deployment mechanism that creates replacement workers. Test creation through each supported entry point, including operational tools. An obsolete A worker must not return after activation. Missing run_at on a scheduled job and an unsupported mode must be rejected at the owning boundaries.

Keep this proof narrow. It establishes scheduling compatibility only for the exercised paths and conditions. Duplicate dispatch, clock synchronization in production, cancellation, and effects in a remote delivery service need their own contracts and evidence.

Choose recovery that preserves accepted work

After activation, a rollback to B can stop new scheduled-job creation while preserving the jobs already stored. Returning to A requires a separate way to satisfy or resolve those outstanding commitments. Changing scheduled to immediate would erase the customer's requested timing. Leaving a scheduled row in a table that A will scan remains unsafe.

PostgreSQL point-in-time recovery can reconstruct database state to a chosen point using a base backup and archived write-ahead log. Restoring a point before activation also excludes later database changes from that recovered state. Account for accepted work after that point and for effects that already happened elsewhere before choosing this route.

Retain the compatible schema and release B while that recovery option is needed. Remove compatibility support only after checking the remaining stored jobs, delayed inputs, and deployment paths that could still depend on it. Decide how restored backups will be upgraded before they can serve traffic again.

Put the rollback boundary in the release decision

The migration plan should identify the first newly accepted state that A cannot honor, the compatible release retained for recovery, and the control that prevents obsolete workers from returning. Attach the rehearsal evidence and the conditions that stop activation. A successful schema statement supplies only part of that evidence.

For an assessment, bound the investigation to one path from job acceptance to dispatch. The useful deliverable is a decision on that path: whether activation can proceed, what remains unproven, and which recovery procedure preserves accepted work. Carry the same questions into any later replacement of the database or dispatch service.