# Safer PostgreSQL migrations for MENA SaaS

A release that adds a field can look harmless until an older worker tries to write a row. For a SaaS team serving Saudi and UAE customers, a useful migration plan should explain what happens while both application versions are running, which data remains unfinished, and who can stop the rollout.

Consider a hypothetical product replacing a free-text `delivery_method` column with a `delivery_method_id` referencing a lookup table. The examples below assume separately operated Saudi and UAE deployments. Those labels describe release boundaries, not hosting locations or regulatory requirements.

The proposed sequence is expand, backfill, validate, switch, then contract. PostgreSQL behavior here follows the [PostgreSQL 18 documentation](https://www.postgresql.org/docs/18/sql-altertable.html); check your installed major version and migration framework before implementing it. The rollout gates and test cases are a suggested engineering playbook, not benchmark results or a promise of uninterrupted service.

*Cover: original SultanByte artwork showing five schema-migration stages, from expansion to destructive cleanup.*

## Expand without demanding an immediate upgrade

Start by adding the new representation while preserving the old one. For this example, keep the new reference nullable during transition and leave existing reads on the text field. Define the mapping explicitly: known values, unknown values, empty strings, and values whose meaning has changed. Do not quietly assign every unfamiliar value to a convenient default.

Write a compatibility matrix before deployment. Include the oldest supported web release, current web release, background workers, importers, and administrative scripts. For each, specify which representation it reads and writes. An old worker that only updates text needs a deliberate synchronization path before new reads can trust the reference.

For this proposed design, keep text authoritative initially. Choose a reviewed database synchronization mechanism or upgrade every writer to compatible dual writes before proceeding. Where both values live in one database, make their coordinated update atomic. Specify conflict behavior rather than allowing whichever writer runs last to redefine the mapping.

A fallback read does not solve stale data. If the new reference is populated but wrong, falling back only when it is null will miss the error. Include disagreement detection in the design.

## Give DDL a waiting budget

[PostgreSQL distinguishes lock and statement timeouts](https://www.postgresql.org/docs/18/runtime-config-client.html): `lock_timeout` limits each lock-acquisition wait; `statement_timeout` limits statement duration, including waiting. With both enabled, a lock timeout equal to or longer than the statement timeout offers little benefit.

Set deliberate budgets on the migration connection rather than changing global defaults. A short lock-wait allowance can let a schema change abandon an awkward moment, while validation may need a longer execution allowance. Pick values from your workload and service objectives, not a copied recipe.

After a timeout, record the blocker and actual database state before retrying. Put bounded attempts and a delay between retries in the runbook. A deployment controller should report a paused migration clearly, rather than treating repeated attempts as progress.

## Treat concurrent indexes as a separate operation

The [PostgreSQL 18 index documentation](https://www.postgresql.org/docs/18/sql-createindex.html) says concurrent builds permit ordinary writes, but require extra work, two scans, and transaction waits. They cannot run inside a transaction block. Check whether your framework automatically wraps migrations, and isolate this operation accordingly.

Failure can leave an invalid index that queries ignore but writes still maintain. A unique concurrent build can enforce uniqueness before completion; after a second-scan failure, its invalid index can continue enforcing it. Inspect validity and definition before retrying. An existing name, including a successful `IF NOT EXISTS` check, is not proof of the intended index.

PostgreSQL documents dropping and retrying or concurrent reindexing as recovery options. Choose a reviewed recovery operation for the observed state. Only one concurrent build can run per table, and a partitioned parent needs a separate plan because concurrent creation there is unsupported.

Our proposed gate is simple: do not switch a query path until its required indexes have passed inspection and workload-specific query testing.

## Backfill work that can survive interruption

[GitLab's batched migration guidance](https://docs.gitlab.com/development/database/batched_background_migrations/) requires small, idempotent jobs and warns that reaching the next release does not prove a migration has finished. Its framework also keeps migration logic independent of changing application models. These are useful constraints even if your service does not use Rails.

For the delivery-method example, use a stable cursor and bounded batches. Store committed progress durably. Make replay safe if the database commit succeeds but the worker dies before acknowledging the job. Keep the mapping version with the job so a later application release cannot silently change its meaning.

Protect against a more subtle race: a batch reads an old text value, a live request changes it, and the batch then writes a reference derived from stale text. Design the update to verify the source value or version it observed, or use a reviewed locking strategy. Requeue conflicts for reconciliation. Merely processing each identifier once is insufficient.

[GitLab's batching recommendations](https://docs.gitlab.com/development/database/batching_best_practices/) warn that heavy modifications can increase replication lag and primary load. Set pause thresholds for the indicators your deployment actually has, including request latency, database load and replica lag where applicable. Reduce batch size or concurrency when those thresholds are crossed.

[Microsoft's background-job guidance](https://learn.microsoft.com/en-us/azure/architecture/best-practices/background-jobs) likewise calls for idempotent activity functions so retries do not duplicate side effects. Keep this backfill focused on data conversion; do not let replay send customer notifications or repeat external actions.

## Validate the rule, then the business meaning

For enforced foreign keys and `CHECK` constraints, PostgreSQL's [staged validation mechanism](https://www.postgresql.org/docs/18/sql-altertable.html) separates adding the rule from checking historical rows. `NOT VALID` skips that initial scan; subsequent inserts and updates must still obey the rule. `VALIDATE CONSTRAINT` later checks existing data.

Adding a foreign key takes `SHARE ROW EXCLUSIVE` locks on both tables. Adding a check generally needs `ACCESS EXCLUSIVE`. Validation takes `SHARE UPDATE EXCLUSIVE` on the altered table, plus `ROW SHARE` on the referenced table for a foreign key. Validation allows ordinary updates, but it is not lock-free.

Do not apply this syntax indiscriminately to unique or primary-key constraints. PostgreSQL 18 also supports not-null constraints in this mechanism; older-version recipes need separate checking.

A foreign key establishes that a referenced delivery method exists, not that the mapping chose the right one. A check has another trap: [a true or null result satisfies it](https://www.postgresql.org/docs/18/ddl-constraints.html). A condition intended to reject unknown values may therefore still admit nulls.

For this rollout, require both database validation and a semantic reconciliation report. Count missing references, unmapped source values and disagreements under the agreed mapping. Define how to account for live changes during reconciliation. Completion should mean the required invariant holds, not merely that a worker reached the last identifier.

![Five migration stages with separate Saudi and UAE approval gates, and a stop before destructive cleanup.](https://cdn.hashnode.com/uploads/covers/60ecf4a0fc37a15ec15655e8/75e36708-7ea6-491c-8444-8b6b9e8e7c57.png)
*Original infographic: SultanByte. Proposed rollout framework informed by PostgreSQL 18, GitLab, Microsoft and AWS documentation. The diagram shows engineering gates, not measured results.*

## Switch each deployment on its own evidence

Keep schema readiness separate from the feature flag that changes reads. In the hypothetical Saudi deployment, approve the switch only after its own backfill, reconciliation, constraint validation and application checks pass. If the UAE deployment still has unmapped values, leave its reads on the compatible path.

Use the same acceptance criteria where appropriate, but collect separate evidence and approvals. If both deployments actually share a database, document that coupling: independent flags do not create independent DDL boundaries.

Create one rollout evidence record per database and migration. The following fields are a proposed starting point:

- Migration identifier, change owner, reviewer, database identity and PostgreSQL version.
- Schema revision, mapping version, application and worker versions still permitted.
- Current phase, flag state, start time and latest observation time.
- Lock and statement budgets, retry limit, batch bounds and committed cursor.
- Remaining work, unresolved failures, unmapped values and reconciliation mismatches.
- Constraint-validation results, index definitions and validity, with evidence locations.
- Baseline and observed service metrics, pause thresholds and named stop authority.
- Approved fallback release, data-compatibility limits and cleanup approval.

For a CTO, this record identifies who owns an unfinished migration and what blocks retirement. For the engineer on call, it should answer whether to pause batches, revert reads or escalate, without reconstructing the deployment from chat messages.

## Rehearse the failures before contraction

Use a representative test environment to exercise these proposed cases and retain actual results:

1. Hold a conflicting transaction open. Confirm the migration reaches its lock budget and the controller pauses rather than retrying indefinitely.
2. Introduce a duplicate into a unique-index rehearsal. Inspect the failed build's state and verify the recovery procedure before rerunning.
3. Seed an orphan reference and a check violation. Confirm staged validation fails, repair the fixtures, then validate again. Test null behavior separately.
4. Kill a backfill worker after commit but before acknowledgement. Replay the batch and verify the same final values without repeated side effects.
5. Let an old writer update a row while its batch runs. Verify the new representation matches the final authoritative value, including after conflict retries.
6. Switch reads, accept writes, then restore the supported application release. Verify it can interpret those writes. Keep one deployment paused while the other proceeds.

Only then consider contraction. Stop old reads and writers, account for delayed jobs and scripts, and observe the replacement path before scheduling removal as a separate change.

[AWS recommends documenting and testing both rollback and permitted fix-forward plans](https://docs.aws.amazon.com/wellarchitected/latest/framework/ops_mit_deploy_risks_plan_for_unsucessful_changes.html). Apply that distinction carefully here: reverting application code is different from reversing a lossy data transformation. Once information has been discarded, a reverse migration cannot reconstruct it by assertion.

Before dropping the old representation, require an explicit decision about retained information, compatibility and recovery. Keep the destructive step blocked until that decision is supported by evidence from every affected deployment.