Should ALTER SYSTEM appear in a migration?

ALTER SYSTEM in a migration

Critical: this fails outright or takes production down.

What happens

ALTER SYSTEM edits the server's configuration for every database and every connection, persists in postgresql.auto.conf far beyond the migration, cannot run inside a transaction block, and usually needs a reload or restart to even take effect. Nothing about it is schema, it is cluster administration in migration clothing.

Why it is dangerous on a populated table

On an empty table this finishes before anyone notices. On a populated one the lock is held for the whole operation, every query on the table queues behind it, and the connection pool fills from the back.

Fires on

ALTER SYSTEM SET max_connections = 500;

The safe pattern

Change server configuration through your infrastructure's configuration management (or an operator runbook), where it is reviewed as what it is: a cluster-wide operational change with a reload/restart step. Migrations should only ever touch schema.

-- Cluster configuration is an operational change, not schema history:
--    ALTER SYSTEM SET max_connections = 500;   -- run by an operator
--    SELECT pg_reload_conf();                  -- and reloaded deliberately
-- or better: set it in your managed-Postgres / config-management layer

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)

alter system

ALTER SYSTEM SET max_connections = 500;

Stays silent (1)

set local

-- Session-scoped SET is fine — it dies with the migration's connection.
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region 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

← BV047 BV049 →