Should several ALTER TABLE statements on one table be combined?

Repeated ALTER TABLE statements on the same table

Note: this works and blocks nothing, but a performance regression is likely.

What happens

Each ALTER TABLE statement acquires its own ACCESS EXCLUSIVE lock, queueing behind traffic every single time. One ALTER TABLE with comma-separated actions acquires the lock once and does all the work under it.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

ALTER TABLE orders ADD COLUMN a text;
ALTER TABLE orders ADD COLUMN b text;

The safe pattern

Combine the actions into one ALTER TABLE statement with comma-separated subcommands: one lock acquisition, one queue traversal, all the work done under it.

SET lock_timeout = '5s';
ALTER TABLE orders
  ADD COLUMN a text,
  ADD COLUMN b text;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

repeated alters

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN a text;
ALTER TABLE orders ADD COLUMN b text;

Stays silent (1)

combined alter

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN a text, ADD COLUMN b text;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV034 BV036 →