Quality and Security

Database Migrations: Changing Schema and Data Under Control

How to change a database schema and its stored data as a controlled transition that stays compatible with running code, respects locks and load, verifies…

Chapter 52Quality and Security

Changing the structure of a table rarely touches only the table. It reaches the data already stored, the application code, background jobs and, often, several versions of the program running at once. A database migration is a managed transition between such states. A migration is correct not merely because a Data Definition Language (DDL) command finished: old and new code must both work correctly against the intermediate schema, existing rows must end up with well-defined content, and the way back must match what has actually been changed.

In the second edition of Designing Data-Intensive Applications (DDIA), Martin Kleppmann and Chris Riccomini treat the evolution of formats and schemas together with changes to code: in a large system, different versions of the code may read and write the same data at the same time. Software Architecture: The Hard Parts treats the splitting of operational data as a trade-off between independent change on one side and coupling, transactions and shared infrastructure on the other. For a migration, this means compatibility and load have to be planned together, and the transition pattern has to be chosen to fit the concrete guarantees of your store and of its consumers. (DDIA, 2nd ed., "Encoding and Evolution"; Software Architecture: The Hard Parts, "Pulling Apart Operational Data")

After this unit you will be able to:

  • tell apart a structural migration, a data migration and the record of which migrations have been applied;
  • check whether a schema is compatible with both the old and the new version of the code;
  • assess table rewrites, locks, scans, indexes and batched backfills;
  • plan the expansion, migration and contraction of a schema, with checks between the stages;
  • decide whether structure, data or behaviour can still be rolled back once the source of truth has been switched;
  • name the signals that tell you to continue, slow down or stop a migration.

The examples below are synthetic and written for teaching. The SQL (Structured Query Language) is there to explain ideas; it has not been executed. PostgreSQL command behaviour is tied to the PostgreSQL 18 documentation; other versions and other database management systems may behave differently.

Three different things that all get called "migration"

A structural migration changes database objects: tables, columns, types, indexes, constraints, views or triggers. In SQL terms this is mostly DDL, for example ALTER TABLE.

A data migration changes or adds values in rows that are already stored. It is usually Data Manipulation Language (DML), for example a batched UPDATE. Filling a new column, normalising values and converting an amount are all data changes, even when they run as part of deploying a new schema.

The migration version history records which migration steps the tool believes have been applied. Rails, for instance, stores the number of each executed migration in a schema_migrations table. That record helps the tool work out the next step, but on its own it proves neither that nobody has changed the structure in the database catalogue by hand nor that every old row holds the correct value. This distinction follows from the fact that the version log and the actual objects and rows are separate observations. (Rails 7.2, Active Record Migrations)

So check three things separately: is the expected version registered; does the actual schema contain the required objects; and does the data satisfy the new invariants. We will call the gap between the intended state and the actual one schema drift or data drift. It can arise from a manual change, an incomplete restore, a failure halfway through a migration, or a migration that is missing in one environment.

Changing a single column also changes the contract for everyone who consumes it: the web application, a queue, a report, another service, or an old worker that is still running. The list of consumers and their versions is a precondition of the plan, not a clean-up item for after the deployment.

Compatibility between code and schema over time

In a simple local environment you can stop the program, change the schema, fill in the data and start the new version. In a distributed deployment, old web processes, queues, schedulers and new instances may all be running in parallel, and any of them can touch the same table before, during and after the migration.

For every stage, ask:

  1. Can the old code read the new schema?
  2. Can the new code read old rows that have not been filled in yet?
  3. Where does each version write, and which value is the source of truth?
  4. What happens if a worker or the migration process repeats an operation after a failure?
  5. What condition must be verified before the old field or old format is removed?

A common way to organise compatibility has three parts:

  1. Expand. Add the new structure in a way that keeps the old code working: for example a column that allows NULL, a destination table, or a new index.
  2. Migrate. Deploy code that can read both states; first make sure new writes are handled, then move the old data across in small, repeatable batches. Verify the result.
  3. Contract. Once old versions, queues, reports and integrations no longer read or write the old format, switch the source of truth, and later remove the unneeded field or compatibility path.

This sequence is sometimes called Expand–Migrate–Contract. It is a planning pattern, not a database command and not a guarantee of uninterrupted service. Checks are needed between the stages, and some changes require a coordinated switch-over or a short maintenance window.

While both fields are in use, name the source of truth explicitly. Reading with a fallback to the old field can help you read rows that have not been migrated yet. Dual writes can keep versions compatible, but two separate statements without a shared transaction can complete partially. If the writes cannot be atomic, you need a different delivery mechanism plus retries and reconciliation — for example an event log with a guarantee of reprocessing. After an agreed checkpoint, reads and writes move to the new source; old writes must be stopped before the old column is dropped.

In DDIA's terms, this is the evolution of code and data format that must coexist for a while. In the terms of The Hard Parts, it is also a decision about data boundaries and dependencies: an external consumer of the old field, or a shared transaction, changes the cost and the ordering of the transition. Neither idea by itself tells you the window or the tool you need; for that you have to know your own consumers and your database's guarantees.

A DDL operation can be short and its lock still noticeable

Changing a schema affects concurrent access. In PostgreSQL 18, most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock by default, although the documentation lists a weaker level for some subcommands. ACCESS EXCLUSIVE conflicts with every lock mode, including the one an ordinary SELECT needs. So a metadata-only change that itself completes quickly may still have to wait behind an earlier long-running transaction, and while it waits or holds the lock, other queries can stall. Check the specific subcommand, the open transactions and the version. (PostgreSQL 18, ALTER TABLE; Explicit Locking)

It matters to separate "does not rewrite the table" from "creates no risk for the workload":

  • Adding a column with a fixed, non-volatile default value can, in PostgreSQL 18, store that value in metadata without updating every existing row. A volatile default may require a rewrite.
  • Changing a column's type usually rewrites the table and its indexes; certain compatible conversions are exceptions. Rewriting a large table can take a long time and may need substantially more temporary disk space.
  • A normal index build blocks writes to the table. CREATE INDEX CONCURRENTLY allows normal writes, but it makes two passes, waits for transactions or snapshots, consumes resources and cannot run inside a transaction block. A failure can leave behind an invalid index that has to be checked and then dropped or rebuilt.
  • Validating a new constraint may scan every existing row. In PostgreSQL, NOT VALID lets you add some constraints without checking existing rows first, but new writes must already obey the rule; a separate VALIDATE CONSTRAINT then checks the old rows with a different lock level.

These properties are specific to PostgreSQL 18 and to the exact kind of operation. Combining subcommands in one ALTER TABLE can raise the lock level required; another database or another version may behave differently. A plan should not promise "zero downtime" just because a command is called concurrent or fast. (PostgreSQL 18, ALTER TABLE; CREATE INDEX; PostgreSQL 18, release notes)

Before a risky DDL operation, decide how long it may wait for a lock and under what conditions the attempt is abandoned, check for open transactions, prepare a retry after a timeout, and schedule the window around system activity. Interrupting a migration does not always restore the starting state: a concurrent index build, for example, can leave behind an object that needs separate clean-up.

Backfill large tables in batches and verify the invariant

A backfill moves or computes a new value for rows that already exist. A single large UPDATE can hold a transaction open for a long time, generate a large volume of change log, delay replicas, increase lock contention and strain the connection pool.

For a large data set you normally plan bounded batches:

  • choose a stable key, such as the primary id, and advance a checkpoint by key rather than with OFFSET;
  • limit the number of rows or the duration of each transaction, commit, and repeat;
  • make the operation idempotent: re-running a batch that already succeeded must not damage a correct value;
  • add a condition that protects newer writes, for example "fill only while the new field is still empty";
  • if the source field can change during the move, keep both values in sync on write or make a second pass to detect mismatches;
  • record the checkpoint only after a batch has been committed successfully; after a failure, resume from the last committed boundary;
  • slow the batches down if query latency, lock waits, connection usage, write-ahead log volume or replica lag rise.

A checkpoint confirms where the process stopped, not that the result is correct. After the move, check the number of incomplete rows, conversion errors and duplicates, the difference between source and converted values, and the business rule the schema is now supposed to enforce. Where the database supports it, turn an important invariant into a constraint. A report saying "100% of the selected rows were updated" does not yet prove the conversion was correct.

In PostgreSQL, a CHECK constraint expresses a condition on a row. The condition email_key IS NOT NULL is different from email_key <> '': an empty string and NULL can mean different things to the business. Check both cases against the contract. A NULL value passes an ordinary CHECK when the expression evaluates to unknown, so checking only email_key <> '' is no substitute for NOT NULL. (PostgreSQL 18, Constraints)

Three worked scenarios

A. Safely renaming contact_email

The change: the column contact_email is to become email_address. A plain RENAME COLUMN would immediately pull the old name out from under the running code, reports and queues. If any code still refers to it, the deployment breaks.

Transition sequence:

  1. Find every reader and writer: the application, queue workers, SQL reports, exports, triggers, views and integrations. Check whether the column is part of an index or a constraint.
  2. Add an email_address column that allows NULL. Old instances keep using contact_email.
  3. Deploy a compatible version of the code. Until the new field is filled, reads may fall back to the old value. If the compatible version writes to both columns, do so in one transaction and decide which field is primary in each phase.
  4. Backfill email_address from the old field in batches. This first pass is not the final reconciliation: while old writers are still running, they can change contact_email after a row has been processed.
  5. Stop the old processes, delayed messages and reports that write only to contact_email. While the old field is still the source of truth, reconcile every row again, correct any mismatches in email_address, and confirm none remain. Before switching over, restore the required type, NOT NULL, indexes and other constraints so that they preserve the original contract.
  6. Switch reads and writes to email_address, keeping the old field for an agreed period of compatibility and code recovery.
  7. Only after the checkpoint at which every consumer has moved to the new column and all checks have passed, drop contact_email in a separate migration.

Verification: no supported process reads or modifies only the old field; values in the new column follow the agreed policy; after the switch there are no old writers. If the requirement is purely a name change and every consumer can be updated in lockstep, a direct rename may be appropriate — but that rests on a different set of preconditions.

B. Filling a mandatory field on a large table

The change: the customers table is to gain normalized_code. In this example every row of the table, whatever the customer's status, must have a normalised internal code. In this synthetic scenario, normalisation strips leading and trailing U+0020 spaces and converts a code drawn from the ASCII (American Standard Code for Information Interchange) set to upper case. The acceptable source format after trimming is 1 to 32 characters: the first character a Latin letter or digit, the rest Latin letters, digits or hyphens. Checking character ranges and converting case can depend on the collation; the examples below set collation C explicitly so the rule matches that character set. (PostgreSQL 18, Pattern Matching; Collation Support) Before running anything, decide how to fix old rows where legacy_code is missing or does not match the format.

Transition sequence:

  1. Add normalized_code allowing NULL. Make sure the old version can still insert a row without knowing about the new field.
  2. Deploy code that validates the format of legacy_code and, for every new and updated customer, writes a consistent legacy_code and normalized_code. If it updates an old row where normalized_code is still NULL, it must fill it in the same operation. For an invalid code, the writer must require a correction or reject the change.
  3. Wait for the deployment to finish and the queues to drain: every active writer must produce a valid legacy_code and write the matching normalized_code at the same time. Old readers may remain, but every application and every direct SQL writer must be upgraded or stopped. Otherwise, once the constraint is added, even an ordinary update of an old row with normalized_code IS NULL will violate it if the writer does not fill the new field.
ALTER TABLE customers
  ADD CONSTRAINT customers_normalized_code_present
  CHECK (normalized_code IS NOT NULL
         AND (normalized_code COLLATE "C") ~ '^[A-Z0-9][A-Z0-9-]{0,31}$') NOT VALID;
  1. Backfill old rows by id range, in small transactions. The placeholders :last_committed_id and :batch_end_id below stand for the batch boundaries; their exact form depends on the tool running the statement:
UPDATE customers
SET normalized_code = upper(btrim(legacy_code) COLLATE "C")
WHERE id > :last_committed_id
  AND id <= :batch_end_id
  AND normalized_code IS NULL
  AND (btrim(legacy_code) COLLATE "C") ~ '^[A-Za-z0-9][A-Za-z0-9-]{0,31}$';
  1. After each committed batch, save the checkpoint. If the process is interrupted, reprocess the unfinished range. Keep the IS NULL condition only if it matches your overwrite rule; if the source code changes concurrently, keep both values in sync or reconcile and make another pass.
  2. Find the rows that do not match the defined format and fix them according to the agreed product rule. Because the final constraint applies to every row in the table, do not leave exceptions without a value; if a row needs to be moved or removed, agree that before the migration. Do not invent a code just to pass the check. Reprocess the corrected rows by their id, even if the original checkpoint has already moved past them.
  3. Reconcile counts and values, then validate the constraint and set the final NOT NULL guarantee:
ALTER TABLE customers
  VALIDATE CONSTRAINT customers_normalized_code_present;

ALTER TABLE customers
  ALTER COLUMN normalized_code SET NOT NULL;

The syntax and locking in this example are PostgreSQL's. NOT VALID skips the initial check of existing rows, but it does not allow new or modified rows to break the added rule. That is why the compatible writer must fill normalized_code even when it updates an old row that has not been migrated yet; otherwise the constraint will reject that update. The batch statement deliberately skips rows with a missing or invalid source code until they are fixed; the fixed rows then have to be processed separately. Validation checks all the old rows, and setting NOT NULL completes the transition. Check the scanning and locking of each operation in the documentation for your version. The words NOT VALID do not mean "the constraint is not in force".

Verification before contracting: every normalized_code matches the constraint's format; checksums or sampled pairs of source and normalised values agree; new writes pass the constraint; old code versions have already been retired. Check the documentation for the version you use: PostgreSQL 18, for example, separately added NOT VALID support for NOT NULL constraints, but the procedure and compatibility on older versions are not the same. (PostgreSQL 18, release notes)

C. Losing precision when converting money

The change: an existing amount numeric(18,4) must become an amount with two digits after the decimal point. This loses information for values with four fractional digits: once 12.3456 has been rounded to two places, the original number cannot be recovered. Besides precision, you also need to check the permitted magnitude, the currency, the point at which rounding happens and the rule for negative values.

Transition sequence:

  1. Agree the domain rounding rule for each currency and check the range of the new type. Not every currency has two minor decimal places; "an amount of money" does not define the format by itself.
  2. First add a field or destination table of the appropriate type. Record the old exact value, the currency and the row key in a conversion log with restricted access, or keep the old column. Define the retention period and protection for this sensitive data set.
  3. For each batch, compute the new value with the agreed function, record how many rows were rounded or overflowed, and compare totals per currency. In PostgreSQL, a numeric(p, s) value is coerced to the declared scale by rounding; numeric values exactly halfway between two candidates are rounded away from zero, whereas real and double precision usually round such ties to the nearest even value. This exception cannot be carried over to an arbitrary type or database. (PostgreSQL 18, Numeric Types)
  4. Do not switch the source of truth until the computation, overflows, rounding, totals and sampled records have been checked by the owner of the rule.
  5. Once new transactions have been written at the reduced precision, going back to the old version of the code will not restore the discarded fractional digits. Recovery requires the saved original values and a defined procedure for reconciling new operations; the backup or log must cover the moment you need.

Conclusion: this is a potentially irreversible data change. You can design it so that a source for recovery exists, but a down migration is not the mathematical inverse of rounding.

Rolling back code does not always roll back data

Rolling back structure, code and values are three different actions. Dropping a freshly added empty column may be reversible; filling it with derived values may be reversible only if the rule and the source were kept; rounding, merging duplicates or deleting rows can lose information. Transactional behaviour of DDL also differs between databases and operations: check whether a command can run inside a transaction and what remains after an error.

Before you start, agree on two paths:

  • Roll back the code: if the new version fails against the expanded schema, are the old fields and the read format the previous version needs still in place? Are new writes compatible with the old reader?
  • Roll forward: if the data has already been converted, is it safer to fix the migration and run it again, temporarily switch off the new behaviour, or compensate for the faulty conversion from the log?

Define in advance the point after which the old code can no longer be brought back. If, after the transition, the new version writes only to the new field, the old code may technically run again but read a stale value. If a field has been dropped, recreating it does not bring back the erased data. Restoring a backup to an earlier point in time can undo every write made since, so the recovery plan must also record how to preserve or replay the useful changes made in the meantime.

The Rails documentation states plainly that some migration operations destroy data and cannot be reversed automatically. A generic down function does not make an irreversible recalculation reversible. (Rails, Active Record Migrations: Reversible Migrations)

Real cases: parallel migrators and resource pressure

In 2023, the author of golang-migrate issue #960 described a failure with PostgreSQL 12 and golang-migrate 4.15.2: several instances call m.Up() in parallel for a migration containing CREATE INDEX CONCURRENTLY. According to the report, the author's test passed with a concurrency of 1, but with higher concurrency it hit a deadlock, a pg_advisory_lock locking error, a "dirty" migration version flag and an invalid index. This is the author's own reproduction for one specific combination, not proof that every concurrent index build always produces this error. At the time of research the issue was still open and contained no confirmed fix in that project; treat it as a signal to check how launches are serialised and how a failed attempt is cleaned up. (golang-migrate, issue #960; PostgreSQL 18, CREATE INDEX)

Another first-hand report on locking was published by GitLab's infrastructure team. An incident record dated 11 March 2025 reports saturation of PgBouncer connections to the patroni-ci database, long transactions in a pipeline background worker — up to 600 seconds — and the postponement of a post-deployment migration to a later run. In a related corrective-action issue, the team explained that long lock waits under peak load can hold up the queue and connections; it chose to fail fast and retry. In merge request (MR) !185888, merged on 11 April 2025, the longer lock-wait timeouts were cut to 2 seconds and the long pauses to 15 seconds, and the number of attempts was increased, keeping the total retry window at roughly 40 minutes. These are GitLab's settings, not universal values. The incident describes both the worker's long transactions and the migration; it does not claim that a single SQL statement was the sole cause. (GitLab, incident #19464; corrective action #26379; MR !185888)

A separate lesson about load capacity appears in GitHub's report for May 2026. During recovery work after another incident, GitHub ran a large database migration that caused replication lag. A protective mechanism interpreted the increased lag as a signal to slow down writes; as a result, internal events that trigger pull request processing and Copilot agent sessions were delayed for a while. The report says the system recovered once the lag returned to normal and describes plans to make background processing more resilient. It does not name the exact SQL or lock, so it is an example of impact on dependent systems, not evidence of a particular query bug. In a migration plan, watch replication and queues, and assess the impact during recovery from other incidents too. (GitHub Availability Report: May 2026)

These examples show different mechanisms: running migrators in parallel can collide with coordination, and prolonged data work can put noticeable load on replication and dependent services. They do not define one universal strategy. Before launching, find answers to both questions: who guarantees that the required migration runs exactly once, and which signals will make the system slow down or stop the batch work?

Observation plan and completion criteria

Before the migration, write down the expected preconditions, checkpoints, owner, dependent releases and recovery steps. During the change, watch:

  • lock waits, timeouts and long transactions;
  • query duration, CPU (central processing unit) usage, disk input/output and space for temporary rewrites;
  • the number of rows processed, errors and backfill checkpoints;
  • replica and queue lag, the number of open connections and free pool capacity;
  • application errors, the share of invalid values and constraint violations.

Stopping must be a real action: stop issuing new batches, finish the current transaction safely, save the position, determine which partial objects remain, and decide whether to retry from the same version. The thresholds are set by the system owner based on the system's normal level and the degradation it can tolerate; this unit does not prescribe a universal number of seconds or percentage of load.

A migration can be considered complete when the actual structure matches the expected one, the relevant data has passed independent verification, the invariants are enforced or confirmed, old writers no longer change the previous format, and monitoring shows no delayed consequences. The migration version record is only one part of that evidence.

Summary

A migration spans the schema, the data, the record of what has been applied, and versions of code that coexist for a while. Expand the structure before switching consumers, move old values in controlled, repeatable batches, verify the invariants, and contract compatibility only after old readers and writers have been retired. Assess locks, rewrites, replication, load and loss of information for the specific command and database version. Choose between rolling back and rolling forward according to whether the earlier content of the data can still be recovered.

Sources and further reading

Materials checked on 10 October 2026. Before running anything in practice, compare the documentation and database behaviour against your version, driver, ORM (object-relational mapping — the layer that maps program classes onto relational tables) and actual configuration.