Why Safe PostgreSQL Migrations Require Short Transactions

When a migration runs in one long transaction, PostgreSQL keeps its locks until the transaction ends. The migration also leaves more unfinished changes to roll back, and a failure means more work to repeat. A transaction is a group of database changes that PostgreSQL commits or rolls back as one unit. A lock limits which other database operations can use the same table or rows at the same time. For large data changes, do a bounded portion in each transaction, commit it, and then start the next transaction.

Short transactions keep locks in place for less time and limit how much work a failure can undo, which also reduces the cost of rollback. Earlier batches stay committed if a later batch fails, so the migration must record progress and make retries safe.

How long transactions hold locks

PostgreSQL normally holds a transaction’s locks until the transaction ends. If a migration changes a table and then spends several minutes updating rows before committing, locks acquired earlier can remain in place for that time. PostgreSQL’s locking documentation describes how lock modes determine which other operations must wait.

The effect depends on the command. Many forms of ALTER TABLE acquire an ACCESS EXCLUSIVE lock, which conflicts with every table-level lock, including the lock used by ordinary reads. Other forms take less restrictive locks; only ACCESS EXCLUSIVE blocks a plain SELECT. Check the lock behavior of the specific command in PostgreSQL’s ALTER TABLE documentation rather than assuming every schema change blocks all queries.

A large data update creates a separate concern. It can contend with application writes to the same rows and consume database resources even when it does not block ordinary reads. GitLab’s batching guidance recommends dividing large data changes into smaller units to reduce timeout risk and limit the load from update or delete jobs.

Separate schema changes from large backfills

A backfill fills in or updates data in existing rows. For a large backfill, add or prepare the new schema separately from the work that updates those rows. Then deploy code that can handle both the old and new data while the backfill runs. After the migration verifies that existing rows have the new value, a later change can switch reads or remove the old representation. The sequence depends on how the application reads and writes data; GitLab describes this staged approach in its downtime-avoidance guidance.

Use a key that does not change and has an index, such as a primary key, to divide the backfill into batches. An index helps PostgreSQL find the rows for each batch. Each transaction should handle a bounded set of rows and commit before the next set begins. Record progress so the job can resume, and make each batch safe to rerun by updating only rows that still need a change. Earlier batches remain committed after a later failure, so a retry must account for completed work.

Choose batch sizes based on what you observe in the database. Smaller batches reduce the amount of work held in one transaction, but they add transaction overhead and can increase total runtime. Monitor query duration and lock waits. Also watch database load, replication lag, and vacuum activity as the backfill runs. Reduce the pace or pause the job when those signals show pressure. Keep external API calls outside database transactions. Run long computations and waits outside them too. GitLab’s transaction guidelines explain why unrelated work should not extend a transaction.

What shorter transactions change during recovery

If a batch times out, hits an error, or stops during a deployment or worker restart, PostgreSQL rolls back that batch’s uncommitted changes. Previously committed batches stay complete. PostgreSQL describes this boundary in its transaction documentation. With retry-safe updates and recorded progress, an operator can focus on the failed portion instead of repeating one large migration from the beginning.

Smaller batches help operators identify problematic rows and diagnose failing queries. GitLab’s guidance for batched background migrations describes rerunning a particular failed batch with additional logging. The exact retry and progress-tracking tools depend on the framework, but the recovery principle applies broadly: make the work small enough to identify and repeat safely.

Batching changes what PostgreSQL can commit or roll back as one unit. A single transaction lets PostgreSQL commit or roll back all its work together. With multiple batches, earlier commits remain even if later work fails. Use that approach when the application can tolerate partially migrated data during the backfill, and verify completion before deploying code or constraints that depend on every row being updated.

Some PostgreSQL commands need their own procedure

A short transaction limits how long it holds locks, but the command still has to acquire every lock it requires. A schema change that needs ACCESS EXCLUSIVE still needs that lock. A data batch still locks rows while it runs.

Some operations also cannot run inside a transaction block. For example, CREATE INDEX CONCURRENTLY lets writes continue during index construction, but PostgreSQL documents that it performs two table scans, waits for relevant transactions, and cannot run inside a transaction block. A failed concurrent build can leave an invalid index that needs cleanup before a retry. Follow the command-specific instructions in PostgreSQL’s CREATE INDEX documentation instead of treating every migration as ordinary batched work.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com