Define the customer action at risk
A database migration can be syntactically valid and still interrupt a checkout. The practical question is what happens when a customer submits an order while the migration acquires locks, rewrites data, or when one application instance still runs the previous code. Begin with a narrow change statement: what column, constraint, or index is changing; which reads and writes touch it; and which customer action must remain available. For an online shop, that might be creating an order, updating a payment status, or showing an existing order to support staff. Write down the current and proposed shapes and the application versions that may overlap during deployment. This makes the review about a real transaction rather than a generic promise of zero downtime.
Use the documentation for the database version actually deployed. PostgreSQL describes different locking and rewrite behaviour for different ALTER TABLE operations. Adding a column without a constraint can avoid a table rewrite, while changing an existing column type normally rewrites the table and indexes unless documented exceptions apply. A fast operation in one form does not mean all ALTER TABLE statements are fast. Check the table size, write rate, dependent indexes, and transaction duration in the environment that will run the change. A development copy with ten rows cannot reveal the effect of a production table with long-running transactions.
Expand the shape before switching code
Suppose a shop wants to split one free-text delivery note into two new fields, “delivery instruction” and “staff-only note.” Removing or renaming the old column in the same release as new code creates a gap: old application instances can still try to read it, while new instances expect the new fields. A safer staged plan is to add the new nullable columns first, deploy code that can read the old data and write or derive the new values, backfill existing rows in controlled batches, then switch reads after checking representative records. Only after all writers and readers use the new shape should the team plan removal of the old column. This is an application compatibility pattern, not a statement that every step is automatically safe for every table.
The team must specify what happens when values disagree during the overlap. If an old checkout worker writes only the original note while a new worker writes both fields, which source wins? One possible rule is to keep the original as the source of truth until all workers have upgraded, and derive the new fields through an explicit, idempotent backfill. Another is to make a compatibility layer handle both versions. The right choice depends on the data meaning and write path. Test repeated runs and edits made during the backfill; a one-time copy that overwrites newer customer instructions can be worse than leaving the field unfilled. Count unmigrated rows and inspect exceptions, not just the job exit code.
Plan indexes and constraints separately
An index added to speed a new order lookup is a separate operational event. PostgreSQL documents that an ordinary CREATE INDEX blocks writes to the table while the build runs. CREATE INDEX CONCURRENTLY allows normal writes to continue, but uses more work, can take longer, cannot run inside a transaction block, and can leave an invalid index after failure. Those caveats matter if a migration framework wraps every script in a transaction or automatically retries on error. The team should check its framework behaviour, schedule the build with resource headroom, observe progress, and verify the resulting index is valid before treating the migration as complete.
Constraints need their own plan. PostgreSQL notes that validating existing rows for a new check or foreign-key constraint may require a substantial scan and affect concurrent work. Its NOT VALID and later VALIDATE CONSTRAINT path can reduce the immediate impact for supported constraints: new or updated rows are checked after the constraint is added, while existing rows are checked later. That does not remove the need to clean inconsistent old records and decide what a validation failure means. Use the exact syntax and lock behaviour documented for the deployed version and constraint type. A proposed command copied from a blog post for a different version is not an acceptance test.
Rehearse the operational path
Before touching production, rehearse on a realistic copy with representative row counts, indexes, and data anomalies, while preserving the confidentiality of customer information. Measure migration duration, lock waits, and the effect on order creation and support lookups. A rehearsal cannot perfectly reproduce production traffic, so define a limited change window and thresholds for pausing. Prepare a backup and a tested recovery method, but do not call a backup a rollback plan: restoring an entire database can discard legitimate orders placed after the copy. For additive changes, an application rollback that still understands the old schema may be the safer immediate response; destructive changes require a more specific recovery design.
During release, watch the signals that relate to the customer action: order submissions that time out, database lock waits, queue backlog, failed writes, and error reports. Record the start and end of each schema and application step so an operator can tell which state the system is in. Avoid running a heavy backfill at the same time as an index build unless capacity testing supports it. If a threshold is crossed, stop introducing further changes, preserve evidence, and use the prepared compatible application version where possible. The instruction “roll back the migration” is too vague when rows have been changed or customer orders have arrived in the meantime.
Prove the new shape before cleanup
After release, compare a sample of orders created before, during, and after the switch. Check that each can be displayed and updated, that new fields have the intended values, and that no writer still depends on the old column. Review unresolved backfill exceptions and inspect the database for any invalid index if a concurrent build failed. Then schedule cleanup as a separate, reviewable change with its own rollback consideration. Dropping an old column may be quick in PostgreSQL, but the removal is still incompatible with code that reads it, and it does not necessarily reclaim disk space immediately. Clean up only when the business and technical evidence support that decision.
The owner should ask for a short release record: the exact operation against the actual database version, evidence from a realistic rehearsal, the compatibility rule for overlapping app versions, observed customer transaction checks, and the plan for anomalies. A vendor or internal engineer can propose the commands, but the business should understand what would make the release pause and how orders remain protected. The aim is a controlled change with observable states, not a blanket guarantee that every schema edit can happen without interruption. That distinction makes the next change easier to plan and gives support staff an intelligible answer if customers report trouble.
Make the go or pause decision explicit
The release owner needs a decision point before each irreversible or expensive step. For the illustrative delivery-note change, an acceptable first gate might be that the new columns exist, old checkout code still works, and a test order can be created and read. A later gate might require the backfill exception count to reach an agreed state and a sample of real orders to preserve customer instructions. The team should set its own thresholds using actual traffic and service expectations; a generic number copied from another shop would give false confidence. At each gate, write who can approve continuation, what evidence they see, and how to pause without leaving staff guessing. This is especially useful when work crosses a shift change or a vendor handover.
A failed step should produce a specific diagnosis. If the migration waits for a lock, find the blocking transaction and decide whether to wait or reschedule under the normal operations procedure. If an index build fails, inspect its validity and the cause before a retry; PostgreSQL warns that an invalid concurrent index may remain and consume write overhead. If validation finds old bad rows, separate data cleanup from forcing the constraint through. If new code produces wrong values, stop the rollout and preserve orders created during the interval for reconciliation. These branches are more useful than a single “restore backup” instruction, because they protect legitimate customer activity while the team decides the next safe change.
Release checks
- Is the exact database operation checked against the deployed PostgreSQL version?
- Can old and new application versions process orders during the overlap?
- Are lock waits, customer transactions, backfill exceptions and index validity observed?
