Should I use serial or an identity column in Postgres?
serial instead of an identity column
Note: this works and blocks nothing, but a performance regression is likely.
What happens
serial is a legacy macro: the sequence it creates is only loosely attached, permissions and ownership drift from the column, and anyone can still insert arbitrary values. GENERATED ... AS IDENTITY is the standard replacement with tighter semantics.
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
CREATE TABLE users (id bigserial PRIMARY KEY);The safe pattern
Use GENERATED ALWAYS AS IDENTITY (or BY DEFAULT if tooling must insert explicit ids): the sequence is owned by the column, permissions follow it, and accidental manual inserts are rejected instead of silently desynchronizing the sequence.
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);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)
CREATE TABLE tags (id bigserial PRIMARY KEY, label text);Stays silent (1)
CREATE TABLE tags (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, label text);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