What happens after ALTER SEQUENCE RESTART on a live table?
Sequence restarted: duplicate key collisions ahead
Note: this works and blocks nothing, but a performance regression is likely.
What happens
RESTART rewinds a sequence regardless of what values are already in use. If any existing row holds an id at or above the restart point, inserts start failing with duplicate-key errors at some unpredictable later moment.
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 SEQUENCE orders_id_seq RESTART WITH 1;The safe pattern
Derive the sequence position from the data instead of restarting at an absolute value: setval to the current max leaves no room for collisions. If the goal was reclaiming ids, don't, gaps are normal and harmless.
-- Position the sequence relative to real data:
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));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 SEQUENCE orders_id_seq RESTART WITH 1;Stays silent (1)
ALTER SEQUENCE orders_id_seq CACHE 50;How to check locally
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