Most teams treat a schema change as one event: write a migration file, run it in the deploy pipeline, and move on. In a multi-tenant SaaS product, that single event is really two separate problems. The first is a locking problem: the DDL statement must not stall every query behind it. The second is a correctness problem: millions of existing rows, owned by thousands of tenants, must be converted without losing a write, overwriting newer data, or silently skipping a tenant.
This article is written for the tech lead who owns that change. It covers the expand, backfill, and contract lifecycle, the PostgreSQL locking behavior that matters most, and a proposed design for a tenant-scoped, resumable backfill ledger. It also explains what to do when a batch times out and nobody knows whether it committed. The design is a pattern we propose for your evaluation. It is not an industry standard or a packaged product, and you should adapt it to your engine, ORM, and tenancy model.
- Primary keyword: zero-downtime schema migration for multi-tenant SaaS
- Related concepts: expand and contract, parallel change, online backfill, dual writes, lock_timeout, CREATE INDEX CONCURRENTLY, fencing tokens, reconciliation
Key Takeaways (Bottom Line Upfront)
- Split every risky change into reversible phases. Expand, dual-write, backfill, verify, switch reads, then contract. Only the final contract step is hard to undo.
- Protect the lock queue first. A fast DDL statement can still cause an outage if it waits behind a long transaction. Use a short
lock_timeoutand retry.- Make the backfill a ledgered job, not a script. Bind a tenant-scoped operation key to an immutable hash of the approved change, and reject reuse of the key with a different payload.
- Claim work atomically and write conditionally. Use a lease with a fencing token for batches. Make the data write conditional on the target side, for example
WHERE new_column IS NULL.- A timeout means unknown, not failed. Reconcile against the ledger and the data before retrying any batch.
- Gate the contract step with all-required controls. Do not use a weighted score. One failed control means no-go.
Why Online Schema Changes Break Multi-Tenant SaaS (and Why a Runbook Alone Fails)
Key takeaway: Multi-tenancy adds data skew, mixed application versions, and shared tables. A migration that passes on uniform staging data can still fail on production data.
Three conditions make schema changes harder in multi-tenant systems than in single-tenant ones.
- Skewed data. One large tenant can own a large share of a table, while thousands of small tenants own almost nothing. A global batching strategy that treats all rows equally will behave very differently for each group.
- Mixed-version fleets. During any rolling deploy, old and new application versions run side by side. Background workers, import jobs, and admin tools often deploy on a different cadence from the web tier.
- Shared blast radius. A table-level lock or a replica-lag spike affects every tenant at once. This holds even if your isolation model is otherwise strong. See our guide to multi-tenant database isolation patterns and scaling trade-offs for how the isolation model changes migration options.
How most teams approach it, and why that fails
The common approach is a single migration file that alters the table and runs one large UPDATE. It is simple, and it works on small tables. At scale, each step hides a failure mode.
| Common practice | What goes wrong | Control that addresses it |
|---|---|---|
One ALTER TABLE in the deploy pipeline | The statement waits for a lock, and every later query queues behind it | Short lock_timeout plus bounded retry |
One giant UPDATE for the backfill | Long transaction, bloat, replica lag, no resume point | Small batches tracked in a durable ledger |
| Script identified by file name | An edited script re-run under the same name applies different logic to the same job | Operation key bound to an immutable payload hash |
| Success means exit code 0 | Rows missed by a forgotten writer stay NULL until the old column is dropped | Reconciliation per tenant before any contract step |
| Retry on any error | A timed-out batch may already have committed | Reconcile state first, then retry |
The pattern underneath all of these is the same. The team has no authoritative record of what has been done, so every retry is a guess.
The Expand, Backfill, Contract Lifecycle: A Mental Map for Online Changes
Key takeaway: Each phase should be independently deployable and, until the final step, reversible. The old shape stays authoritative until verification passes.
The underlying idea is often called parallel change or expand and contract. Martin Fowler's write-up of the parallel change pattern describes the three phases: expand, migrate, and contract. Stripe's engineering team has described a closely related four-step sequence for data model migrations in its post on online migrations at scale: dual write, change reads, change writes, then remove the old data. We split the middle phase further because the backfill and the verification each need their own gate.
| Phase | What happens | Reversible? | Exit gate |
|---|---|---|---|
| 1. Expand | Add new column, table, or index. Old code ignores it. | Yes | DDL applied without lock stalls; no invalid indexes |
| 2. Dual-write | All writers populate both old and new shapes | Yes | Every writer inventoried and deployed, including jobs and imports |
| 3. Backfill | Existing rows are converted in tracked batches | Yes (old data untouched) | No pending or claimed batches; quarantine resolved |
| 4. Verify | Reconcile counts and values per tenant; shadow-compare reads | Yes | Zero unexplained differences over an agreed observation window |
| 5. Switch reads | Application reads from the new shape, behind a flag | Yes, via flag | Error and latency stable; rollback tested |
| 6. Contract | Drop the old column or table; add final constraints | Hard | All go/no-go controls in the last section pass |
Rule of thumb: do not let a single deploy cross two phases. If expand and contract ship in the same release, you no longer have an expand-contract migration. You have a risky one-shot change with extra steps.
If the change must also emit events or notify downstream consumers, record the event in the same transaction as the data change. Our article on the outbox pattern covers that approach.
PostgreSQL Locking Realities: Which DDL Is Safe and Which Needs a Pattern
Key takeaway: The risk is rarely the duration of the DDL itself. It is the time the statement spends waiting for a lock while blocking everything queued behind it.
The examples below are PostgreSQL-specific, and behavior differs by engine and version. We checked them against the current PostgreSQL documentation, which at the time of writing is version 18. Confirm each behavior against the version you actually run, and consult your engine's documentation if you use MySQL, SQL Server, or another database.
According to the PostgreSQL ALTER TABLE documentation, an ACCESS EXCLUSIVE lock is acquired unless a subform is explicitly noted otherwise. It also states that adding a column with a non-volatile default stores the value in table metadata, so no rewrite is needed. A volatile default, a stored generated column, an identity column, or a domain type with constraints will rewrite the table and its indexes. The explicit locking chapter explains how lock modes conflict.
| Operation | Behavior to plan for | Safer pattern |
|---|---|---|
| Add nullable column, no default | Metadata-only, but needs a brief ACCESS EXCLUSIVE lock | Run with lock_timeout and retry |
| Add column with constant default | No rewrite in current PostgreSQL versions | Same as above |
| Add column with volatile default | Rewrites table and indexes | Add without default, set default for new rows, backfill in batches |
Plain CREATE INDEX | Blocks writes during the build | CREATE INDEX CONCURRENTLY, then verify validity |
| Add CHECK or foreign key constraint | Validates existing rows while holding a strong lock | Add NOT VALID, then VALIDATE CONSTRAINT separately |
| Change column type | Often rewrites the table | New column, dual-write, backfill, switch, drop |
| Rename or drop column | Fast, but breaks any old code still reading it | Expand and contract across releases |
Protect the lock queue
A DDL statement that cannot get its lock waits. Queries that arrive afterward wait behind it. Setting a short lock_timeout makes the migration fail fast instead of freezing traffic. A lock timeout is a busy moment, not a broken migration, so it is safe to retry the same idempotent statement. The code below is illustrative.
-- Illustrative: expand step with bounded lock wait SET lock_timeout = '2s'; ALTER TABLE invoices ADD COLUMN IF NOT EXISTS currency_code text; -- On 'canceling statement due to lock timeout': pause, check for -- long-running transactions, then retry with a bounded attempt count.Concurrent index builds need a state check, not a blind retry
The CREATE INDEX documentation explains that a failed concurrent build can leave an invalid index behind. That index is ignored for queries but still adds update overhead. The recommended recovery is to drop it and try again, or rebuild it with REINDEX INDEX CONCURRENTLY. This is the first appearance of a theme we return to later: after a failure, check authoritative state before you repeat the action.
-- Illustrative: find invalid indexes before retrying SELECT c.relname AS index_name, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE NOT i.indisvalid;Add constraints in two steps
Adding a constraint as NOT VALID enforces it on new writes without scanning old rows. A later VALIDATE CONSTRAINT checks the existing rows. Per the PostgreSQL documentation, validation takes a weaker lock than a full scan under ACCESS EXCLUSIVE. Be careful with ordering: a NOT VALID check on a new column rejects writes from any old code that still leaves the column NULL. Do this only after dual-write is fully deployed.
A Proposed Design: The Tenant-Scoped Backfill Ledger
Key takeaway: Treat the backfill as a consequential mutation. It needs a durable identity, an approved payload, atomic work claims, conditional writes, and a ledger that records what actually committed.
The design below is a proposed pattern that we suggest tech leads evaluate. It is not a published standard, and it is not a claim about any specific product. It combines familiar ideas: idempotency keys, leases with fencing tokens, and transactional bookkeeping. Our articles on idempotency in distributed systems and worker leases and fencing tokens cover the building blocks in more depth.
Design invariants
- Durable, tenant-scoped identity. The operation is identified by
(tenant_id, operation_key). Two tenants can use the same key without colliding. - Immutable approved payload. The operation stores a hash of the approved change: transform version, target columns, and selection rules. The row is not updatable after approval.
- Changed-payload reuse is rejected. If someone re-submits an existing key with a different hash, the system refuses. A legitimately different change gets a new key.
- Atomic claim. A worker takes a batch with a single statement that sets a lease and increments a fencing token. Two workers cannot hold the same batch.
- Target-side conditional write. The data write only touches rows that still need conversion, such as
WHERE currency_code IS NULL. Reading a row, comparing in the application, and then writing is not compare-and-swap, so the condition lives in the statement itself. - Ledger and data commit together. The batch is marked done in the same transaction as the data write. If either fails, both roll back.
Schema (illustrative)
CREATE TABLE backfill_operation ( tenant_id uuid NOT NULL, operation_key text NOT NULL, payload_hash bytea NOT NULL, -- hash of approved transform spec; never updated state text NOT NULL CHECK (state IN ('approved','running','paused','verified','failed')), PRIMARY KEY (tenant_id, operation_key) ); CREATE TABLE backfill_batch ( tenant_id uuid NOT NULL, operation_key text NOT NULL, batch_no bigint NOT NULL, lo_id bigint NOT NULL, hi_id bigint NOT NULL, state text NOT NULL CHECK (state IN ('pending','claimed','done','quarantined')), fencing_token bigint NOT NULL DEFAULT 0, lease_expires_at timestamptz, rows_written int, rows_quarantined int, PRIMARY KEY (tenant_id, operation_key, batch_no) );Register the operation, then compare the hash
-- Illustrative: insert-if-absent is atomic; the hash comparison is a guard, not a write INSERT INTO backfill_operation (tenant_id, operation_key, payload_hash, state) VALUES ($1, $2, $3, 'approved') ON CONFLICT (tenant_id, operation_key) DO NOTHING; SELECT payload_hash FROM backfill_operation WHERE tenant_id = $1 AND operation_key = $2; -- Application: if stored hash != $3, reject. A changed spec requires a new operation_key.Claim a batch atomically
PostgreSQL's FOR UPDATE SKIP LOCKED lets concurrent workers pick different rows without waiting on each other. A lease on the batch means a crashed worker's work can be reclaimed once the lease expires.
-- Illustrative: claim one pending or lease-expired batch UPDATE backfill_batch b SET state = 'claimed', fencing_token = b.fencing_token + 1, lease_expires_at = now() + interval '60 seconds' WHERE (b.tenant_id, b.operation_key, b.batch_no) = ( SELECT tenant_id, operation_key, batch_no FROM backfill_batch WHERE tenant_id = $1 AND operation_key = $2 AND (state = 'pending' OR (state = 'claimed' AND lease_expires_at < now())) ORDER BY batch_no LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING batch_no, lo_id, hi_id, fencing_token;Write conditionally and acknowledge inside the same transaction
BEGIN; -- Target-side condition: only rows that still need conversion UPDATE invoices i SET currency_code = s.default_currency FROM tenant_settings s WHERE s.tenant_id = i.tenant_id AND i.tenant_id = $1 AND i.id BETWEEN $lo AND $hi AND i.currency_code IS NULL; -- Verified acknowledgment: fencing token must still match UPDATE backfill_batch SET state = 'done', rows_written = $n WHERE tenant_id = $1 AND operation_key = $2 AND batch_no = $3 AND state = 'claimed' AND fencing_token = $4; -- If this affected 0 rows, ROLLBACK: a newer claim exists and this worker is stale. COMMIT;A stale worker that wakes up after its lease expired cannot complete the batch, because the fencing token no longer matches and its data write rolls back with it. Rows whose tenant has no usable settings are left untouched by the join. They are not guessed at, and the next section covers how they are handled.
Skew handling: create batches by row range per tenant, and cap batch size by row count rather than by tenant. A large tenant simply gets many batches, and small tenants finish quickly. Throttle on replica lag and p95 write latency, not on elapsed time.
Ambiguous Timeouts, Uncertain Input, and Privacy in Backfills
Key takeaway: An unknown result means completion could not be confirmed. It is neither success nor verified failure. Reconcile authoritative state first, then decide.
If a worker loses its connection while sending COMMIT, it does not know whether the batch committed. Retrying blindly can repeat a non-idempotent transform or overwrite values that dual-writes have since updated. The same reconcile-before-retry principle applies to any consequential mutation, and we cover it in depth in ambiguous timeouts in consequential API calls.
| Observed outcome | What it means | Action |
|---|---|---|
Commit acknowledged; ledger row is done | Verified completion | Move to the next batch |
Error before commit; ledger row still claimed with same token | Verified not committed (after reading the ledger in a new session) | Retry within the lease, or let the lease expire and re-claim |
| Timeout or connection loss during commit | Unknown | Do not retry yet. Read the ledger, then verify the data. Only then decide. |
Ledger shows done but verification query finds NULL rows | Inconsistency | Stop, open an incident, and investigate. Do not auto-repair. |
| Rows cannot be transformed (missing or invalid source data) | Terminal failure for those rows | Quarantine with a reason code and an owner |
Reconciliation in pseudocode
# Illustrative pseudocode, not an official API contract def reconcile_after_unknown(tenant_id, op_key, batch_no, my_token): row = read_ledger(tenant_id, op_key, batch_no) # new session, primary if row.state == 'done': return 'CONFIRMED_DONE' if row.state == 'claimed' and row.fencing_token == my_token: if lease_active(row): return 'NOT_COMMITTED_RETRY_ALLOWED' return 'RECLAIM_THROUGH_NORMAL_CLAIM' # token will increment if row.fencing_token > my_token: return 'SUPERSEDED_STOP' # someone else owns this batch now return 'UNKNOWN_ESCALATE'Because the data write is conditional on IS NULL and deterministic, a re-run by a legitimate new claim is safe even when the earlier attempt partially succeeded. Use deterministic transforms for exactly that reason. A transform that depends on random() or the current time can produce different results on retry.
Uncertain input: quarantine, do not guess
Some rows will not convert cleanly: a tenant with no default currency, a malformed legacy value, or a record owned by a deleted account. Do not silently skip them, and do not invent a value. Mark the batch's affected rows as quarantined with a reason code, count them in the ledger, and require a named owner to decide the outcome. A migration with unresolved quarantined rows is not eligible for the contract step.
Privacy ownership
- Identifiers can be sensitive. A tenant ID can identify a customer. Keep it out of broadly shared logs, dashboards, and high-cardinality metric labels unless your policy allows it.
- Keep values out of the ledger. Store IDs, ranges, counts, hashes, and reason codes. Do not store the data being migrated.
- Copies expand deletion scope. A new column or table is another place where personal data lives. Make sure erasure workflows cover both shapes until the old one is removed. See our design for verified data deletion in distributed systems.
- Legal obligations are specific to you. Data residency, retention, and contractual terms depend on your jurisdiction and customer agreements. This article is engineering guidance, not legal advice. Ask your privacy owner or counsel to review any migration that touches regulated data.
Why This Fails in the Real World: Common Failure Patterns
Key takeaway: Capable teams fail because their process measures activity (the script finished) instead of state (every row in every tenant is correct). The scenarios below are composite patterns, not reports about a specific customer.
Failure pattern 1: the forgotten writer
Scenario (illustrative composite). The team ships dual-write in the API and web tier, runs the backfill, sees zero errors, and drops the old column. Weeks later, support reports blank values for a handful of tenants. A nightly import job, deployed from a separate repository, still wrote only the old column. Every row it created after the backfill window had no new value.
Why intelligent teams still fail: there is no inventory of writers, and ownership of background jobs sits with a different team. The backfill reported success because it only processed rows that existed when it ran. Control: a writer inventory as a gate item, and per-tenant reconciliation after the last old-version instance is drained.
Failure pattern 2: the lock queue outage
Scenario (illustrative composite). A migration adds a column during business hours. The statement is metadata-only and should take milliseconds. However, a long reporting transaction holds a conflicting lock on the table. The ALTER TABLE waits, and every new query queues behind it. The application looks down even though the migration is technically trivial.
Why it happens: staging has no long transactions, and the pipeline has no timeout on lock acquisition. Control: lock_timeout, bounded retries, and a pre-flight check for long-running transactions.
Failure pattern 3: the blind retry overwrite
Scenario (illustrative composite). A backfill batch times out. The orchestrator retries it with an unconditional update that sets every row in the range. Meanwhile, users and dual-writes have already updated some rows with newer values. The retry overwrites them with older derived values.
Why it happens: the timeout was treated as failure, and the write was not conditional. Control: reconcile the ledger first, and keep the IS NULL condition inside the statement.
Failure pattern 4: the edited script under the same name
Scenario (illustrative composite). An engineer fixes a bug in a backfill script and re-runs it under the same job name. The ledger from the first run says some tenants are complete, so they are skipped. Those tenants contain output from the buggy version, and nobody knows.
Why it happens: job identity is a name, not a name plus an approved payload. Control: bind the operation key to an immutable payload hash, and reject changed-payload reuse.
Cross-cutting causes
- Success is defined by exit codes, not by reconciliation.
- Staging data is uniform, so skew and edge-case tenants never appear.
- Backfill, deploy, and contract steps are owned by different people with no shared gate.
- Rollback is assumed, never rehearsed.
The Contract Gate: Mandatory Go/No-Go Controls, Trade-Offs, and a Lower-Risk Path
Key takeaway: The contract step is the one hard-to-reverse action. Treat the controls below as all-required checks. A single failure means no-go, and passing them does not certify the system as safe in general.
Go/no-go controls before dropping the old shape
| # | Control (all required) | Evidence to attach |
|---|---|---|
| 1 | Operation key and payload hash recorded; no changed-payload reuse | Ledger row and approval record |
| 2 | All writers inventoried and confirmed on dual-write, including jobs, imports, and admin tools | Writer inventory with owners and deployed versions |
| 3 | No old-version instances still running | Fleet version report |
| 4 | No pending or claimed batches in the ledger | Ledger query output |
| 5 | Quarantine is empty, or every item has a recorded owner decision | Quarantine report |
| 6 | Per-tenant reconciliation of counts and values matches | Reconciliation results, including the largest tenants |
| 7 | Shadow-read mismatches are zero across an observation window that covers your business cycles | Mismatch metrics for the window |
| 8 | Read switch tested, including rollback by flag | Rehearsal record |
| 9 | Restore plan for the contract step exists and is understood by the on-call owner | Backup and restore note |
| 10 | Privacy owner has reviewed copied data, logs, and deletion coverage | Sign-off record |
We deliberately did not turn this into a weighted score. A score of 9 out of 10 can hide the one failed control that causes the incident.
Verification queries (illustrative)
-- Remaining unconverted rows per tenant (run against a replica or in tenant-range slices; -- a partial index built with CONCURRENTLY can keep this cheap) SELECT tenant_id, count() AS remaining FROM invoices WHERE currency_code IS NULL GROUP BY tenant_id; -- Ledger completeness SELECT state, count() FROM backfill_batch WHERE operation_key = 'invoice-currency-v1' GROUP BY state;Adding the final NOT NULL constraint
One commonly used sequence adds a CHECK (col IS NOT NULL) constraint as NOT VALID, validates it separately, and then sets the column to NOT NULL. Recent PostgreSQL versions can use a validated check constraint to skip the full-table scan for SET NOT NULL. Confirm that behavior in the documentation for your version before relying on it.
ALTER TABLE invoices ADD CONSTRAINT invoices_currency_nn CHECK (currency_code IS NOT NULL) NOT VALID; ALTER TABLE invoices VALIDATE CONSTRAINT invoices_currency_nn; ALTER TABLE invoices ALTER COLUMN currency_code SET NOT NULL; ALTER TABLE invoices DROP CONSTRAINT invoices_currency_nn;Trade-offs you accept
| Cost | Why it is worth paying | When it is not |
|---|---|---|
| Temporary dual-write code and extra write load | Keeps old shape authoritative and rollback possible | Small, low-traffic tables where a short maintenance window is acceptable |
| Ledger tables and worker logic | Resume, audit, reconcile | One-time changes on tables small enough for one safe statement |
| Longer calendar time (days or weeks, not minutes) | Shadow reads need a real observation window | Changes with no consumer risk, such as adding an unused index |
| Temporary extra storage | Two shapes coexist until contract | Storage-constrained systems; plan capacity first |
What a smarter, lower-risk approach looks like
- Classify the change: metadata-only, rewrite, or data conversion. Only the last two need the full ledger.
- Run the DDL with bounded lock waits, and verify that no invalid objects remain.
- Pilot the backfill on one small tenant, then on your largest tenant, before the long tail.
- Throttle on replica lag and write latency, with an operator pause switch.
- Reconcile before every phase transition, not only at the end.
- Keep the old shape for at least one full business cycle after the read switch.
If your team lacks the capacity to build this safely alongside roadmap work, Developers.dev DevOps services can supply engineers who work inside your repositories and review process.
Planning a risky migration on a live multi-tenant database?
Get a second set of engineering eyes on your phase plan, lock strategy, and contract gate before production traffic is involved.
Talk to Developers.dev engineers about migration planning and delivery.
Contact Us2027 Update and Evergreen Framing
Database engines continue to improve online DDL, and some operations that once required workarounds are now cheaper. That makes version-specific facts the part of this article most likely to age. The durable principles do not change: keep the lock queue short, separate schema change from data conversion, claim work atomically, write conditionally, treat timeouts as unknown, and gate irreversible steps with all-required controls. Re-verify any engine behavior against the documentation for the exact version you run, and treat the code here as illustrative rather than an official API contract.
Conclusion: Five Decisions to Make Before the Next Schema Change
A zero-downtime schema migration for multi-tenant SaaS is less about clever SQL and more about keeping an authoritative record of what has happened. Use these five actions to guide your next change.
- Classify the change first. Decide whether it is metadata-only, a table rewrite, or a data conversion, and size the process to match.
- Set a lock budget. Require
lock_timeoutand bounded retries on every DDL step, and check for long-running transactions beforehand. - Give the backfill a durable identity. Use a tenant-scoped operation key bound to an immutable payload hash, with atomic batch claims and conditional writes.
- Reconcile before you retry. Treat every ambiguous timeout as unknown, and read the ledger and the data before acting.
- Gate the contract step with every control. Do not drop the old shape while any required control is failing, unverified, or unowned.
About the authors: This article reflects the engineering experience of Developers.dev, an offshore software development and staff augmentation company founded in 2007, with 1000+ in-house IT professionals and 3000+ delivered projects. Company-level credentials include CMMI Level 5, ISO 27001, and SOC 2, which describe the company's own processes and do not certify any reader's system or imply that your migration is compliant. Reviewed by the Developers.dev Expert Team.
Frequently Asked Questions
What is the expand and contract pattern for database migrations?
It is a way to change a schema in phases so old and new application versions can run at the same time. You first add the new structure (expand), move data and traffic to it (migrate), and finally remove the old structure (contract). It is also known as parallel change. Only the last step is hard to reverse.
Does adding a column lock a PostgreSQL table?
It takes a strong lock briefly, and the PostgreSQL documentation says an ACCESS EXCLUSIVE lock is the default for ALTER TABLE unless noted otherwise. Adding a nullable column or one with a non-volatile default does not rewrite the table. The real danger is waiting for the lock behind a long transaction, which is why a short lock_timeout and retries matter.
Why shouldn't I run one big UPDATE to backfill a new column?
A single large update creates a long transaction, can cause bloat and replica lag, and gives you no resume point if it fails. Small tracked batches let you throttle, pause, resume, and reconcile. They also let you handle a very large tenant without blocking everyone else.
What should I do if a backfill batch times out?
Treat the result as unknown. Read the ledger entry in a new session, then check the affected rows if needed. If the batch is confirmed done, move on. If it is confirmed not committed, retry or re-claim it. If the state cannot be established, escalate instead of repeating the write.
How do I stop two workers from processing the same batch?
Claim batches with a single atomic statement that sets a lease and increments a fencing token, for example using FOR UPDATE SKIP LOCKED in PostgreSQL. Then require the token to match when the worker marks the batch done, so a stale worker's transaction rolls back.
Is a read-then-compare-then-write check enough to avoid overwriting newer data?
No. Another transaction can change the row between your read and your write. Put the condition in the write statement itself, such as WHERE new_column IS NULL, or use a version column that the update must match. If your system cannot do that, document the serialization or conflict limits clearly.
Can I use this approach with databases other than PostgreSQL?
The phases and the ledger pattern are engine-independent. The locking details, online DDL options, and constraint behavior are not. Check your engine's documentation for the exact version you run before copying any statement.
When is a maintenance window still the right choice?
For small tables, low-traffic systems, or tenants who accept scheduled downtime, a short window can be cheaper and less risky than building dual-write and a ledger. The decision should rest on table size, traffic, contractual availability commitments, and your team's capacity to operate a multi-phase change.
Need migration-ready engineers, not just more headcount?
Schema changes, backfills, and contract gates fail at the seams between teams. Developers.dev places in-house, on-roll engineers who plan, build, and verify these changes with you.
Share your migration scope and get a practical delivery plan from our engineering team.
Contact UsPlatform Engineering Services
This guide is designed for engineering leaders who want to plan a practical implementation. Use the related Developers.dev path to compare delivery options, implementation fit, risk, and practical next steps.
Reviewed by the Experts team
This guide is reviewed for clarity, technical and operational relevance, service alignment, and a useful next step. Verified by our SEO team for clear search presentation.
Reviewed by the Experts team. Verified by our SEO team. Validate legal, security, data, budget, and operational requirements with the relevant stakeholders before rollout.
