PostgreSQL serial vs identity: Which to Use, and How to Convert Old serial Columns

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Use GENERATED ALWAYS AS IDENTITY for new tables. serial is a shortcut for a separate sequence plus a default, so the column and its counter drift apart: an explicit id breaks the next insert, a widened key still stops at 2,147,483,647, and a copied table shares the counter. An existing serial key converts to identity in a few milliseconds, with no table rewrite.

For a new PostgreSQL table, use an identity column: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY. serial still works, but it is not a type. It is a shortcut that creates a separate sequence and a column default, and the two can drift apart in ways an identity column does not allow.

Many production schemas still have serial keys, because the frameworks that created them used it, and two of the big ones still do. Below are the five ways serial misbehaves, each run on PostgreSQL 18.3 in a throwaway container, and the conversion, which is cheaper than most people expect.

What serial actually creates

The PostgreSQL manual says the serial types "are not true types, but merely a notational convenience". id serial becomes three things: a sequence users_id_seq AS integer, a column id integer NOT NULL DEFAULT nextval('users_id_seq'), and an OWNED BY link so the sequence is dropped with the column. Identity columns arrived in PostgreSQL 10, described in the release notes as "similar to SERIAL columns, but are SQL standard compliant".

The framework you use decides which one you have:

Framework	What it creates for an auto-increment key on PostgreSQL
Django 4.1 and later	Identity column, GENERATED BY DEFAULT. The 4.1 release notes: AutoField, BigAutoField and SmallAutoField "are now created as identity columns rather than serial columns with sequences"
Rails (Active Record)	bigserial primary key, the PostgreSQL adapter's default primary key type in the current source
Prisma	SERIAL, SMALLSERIAL or BIGSERIAL for @default(autoincrement()), in the Postgres renderer of its migration engine

\d users tells you which one a table has. A serial key shows nextval('users_id_seq'::regclass) as its default. An identity key shows generated always as identity or generated by default as identity.

Serial vs identity in Postgres: which should you use?

Identity, for every new table. The differences only show up when someone does something slightly unusual, which is why serial lasts so long in old schemas. Each row below was reproduced on 18.3:

What happens	serial	GENERATED ALWAYS AS IDENTITY
An INSERT supplies id = 1	Accepted. The next generated id is also 1 and fails with duplicate key value violates unique constraint "users_pkey"	Refused: cannot insert a non-DEFAULT value into column "id", with the hint Use OVERRIDING SYSTEM VALUE to override
An app role has INSERT on the table only	permission denied for sequence users_id_seq	The insert works
CREATE TABLE copy (LIKE users INCLUDING ALL)	The copy's default calls users_id_seq, so both tables draw from one counter	The copy gets its own sequence
The key is widened to bigint	The sequence stays AS integer and fails at 2,147,483,647	The sequence becomes bigint with the column
Someone tries to remove the default	DROP DEFAULT works and the column stops numbering	Refused: Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead

GENERATED BY DEFAULT AS IDENTITY is the in-between form. It fixes the permissions, copy and widening rows, but accepts a supplied id the way serial does, and the same duplicate key value error came back in the same test. Use ALWAYS unless a loader must write ids, and when one must, INSERT ... OVERRIDING SYSTEM VALUE says so in the statement. pg_dump handles it already: its --inserts output writes INSERT INTO ... OVERRIDING SYSTEM VALUE VALUES (...), and its default COPY output loads explicit ids into an ALWAYS column without complaint.

The serial overflow that survives the bigint migration

The widening row is the one that costs an outage. A table outgrows integer, someone runs the usual schema migration, ALTER TABLE users ALTER COLUMN id TYPE bigint, and the column now holds values up to 9,223,372,036,854,775,807. The sequence does not. serial created it AS integer, the ALTER did not touch it, and on 18.3 the next insert after 2,147,483,647 failed with:

ERROR:  nextval: reached maximum value of sequence "users_id_seq" (2147483647)

The fix is one more statement, ALTER SEQUENCE users_id_seq AS bigint, and it is easy to miss because \d users shows bigint and looks done. An identity column needs nothing: after the same ALTER, pg_sequences showed its sequence as bigint with a maximum of 9,223,372,036,854,775,807, and the insert at 2,147,483,648 succeeded.

The column change itself is the expensive part either way. integer to bigint rewrites the table: 761 ms for 1,000,000 rows here, under an ACCESS EXCLUSIVE lock, and every foreign key column pointing at it needs the same change before ids pass 2,147,483,647. If a view reads the key, PostgreSQL refuses the ALTER outright until the view is dropped and created again.

A foreign key declared as serial fills itself in

The same shortcut causes a quieter bug in child tables. Copy the parent's column type into a foreign key and you get this:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id serial REFERENCES users (id),
    note text
);

user_id now has its own sequence and a nextval default. An INSERT INTO orders (note) VALUES ('forgot user_id') should fail on the NOT NULL that serial implies. Instead it succeeded and returned user_id = 1, and the foreign key passed because user 1 exists. Each later insert that forgets the column attaches the order to user 2, then 3, until it reaches an id with no user and the foreign key finally fails. A foreign key column takes the plain type under the parent's key, integer for serial and bigint for bigserial, with NOT NULL when the relationship is mandatory. Never the serial itself.

How do you convert a serial column to identity?

Without rewriting the table. The conversion swaps the default for an identity property and carries the old counter over, so it touches the catalogue, not the rows. Here on an invoices table created with id serial:

BEGIN;
ALTER TABLE invoices ALTER COLUMN id DROP DEFAULT;
ALTER SEQUENCE invoices_id_seq RENAME TO invoices_id_seq_old;
ALTER TABLE invoices ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval('invoices_id_seq', last_value, is_called) FROM invoices_id_seq_old;
DROP SEQUENCE invoices_id_seq_old;
COMMIT;

On a 1,000,000-row table whose top ten rows had been deleted, the transaction took about 3 ms, the table's data file was the same one before and after, and the next insert got id = 1000001. Copying last_value rather than max(id) is deliberate: max(id) would hand out the deleted ids 999,991 to 1,000,000 again, and anything that still refers to them, a log line, an export, another system, would now point at a new row. The setval names the new sequence directly, because pg_get_serial_sequence can still return the renamed old one, which stays owned by the column until it is dropped. The new sequence takes the old name, invoices_id_seq.

Four things to check first:

• The lock. The transaction holds ACCESS EXCLUSIVE on the table. It is short, but it waits behind every running query, and everything else waits behind it. Set lock_timeout so a long report cannot turn a 3 ms change into a queue.
• A shared sequence. If a LIKE ... INCLUDING ALL copy or another table also calls the sequence, DROP SEQUENCE fails with cannot drop sequence invoices_id_seq_old because other objects depend on it, its DETAIL names every default using it, and the transaction rolls back. That is how you find the copies.
• Grants on the sequence. They are dropped with the old sequence. Inserts no longer need them, but a role that calls currval('invoices_id_seq') gets permission denied for sequence invoices_id_seq until you grant it again. INSERT ... RETURNING id avoids currval altogether.
• Writers that supply ids. Seed scripts and fixtures that insert explicit ids fail against ALWAYS. Add OVERRIDING SYSTEM VALUE to them, or convert to BY DEFAULT instead.

Converting does not change the column's type, so an integer key converted to identity still overflows at 2,147,483,647. If the table is heading there, widen it separately, and plan for the rewrite.

How Schemity handles serial and identity keys

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you connect to PostgreSQL, each column's default is drawn on the canvas, so every serial column shows nextval beside it and an identity column shows nothing. That makes the legacy keys easy to see, and the bug above easier still: a nextval on a foreign key column is a serial that should have been an integer.

When you design the next one, a table drawn with a single integer primary key and no default is created as an identity column. The planned SQL reads "id" INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, or BIGINT with a bigint key. Drawing a relationship never copies the parent key's default into the new foreign key column, and a parent typed SERIAL or BIGSERIAL in a design gives the column INTEGER or BIGINT, so the diagram does not reproduce the self-filling foreign key.

Widening a serial key in the diagram from INTEGER to BIGINT plans the ALTER COLUMN ... TYPE BIGINT and, after it, ALTER SEQUENCE users_id_seq AS BIGINT, so the counter is widened in the same migration as the column. Impact analysis reports the change before anything runs: the table is rewritten, reads and writes are held back while it happens, and the tables whose foreign keys reference the key are listed, since their columns need widening too. Until they are, lint reports each one as "Foreign key type does not match the column it references".

Which to use, in one list

• New table: bigint GENERATED ALWAYS AS IDENTITY.
• A loader must write ids: GENERATED BY DEFAULT AS IDENTITY, or keep ALWAYS and use OVERRIDING SYSTEM VALUE in the loader.
• Existing serial key: convert it in one short transaction, after checking for shared sequences and scripts that insert ids.
• Widening a serial key to bigint: widen the sequence too, or convert to identity first.
• Foreign key column: the plain integer type under the parent key, NOT NULL when the relationship is mandatory, never serial.

Whether the key should be a number at all is the question in UUID vs bigint primary keys in Postgres. What a new NOT NULL column with a volatile default costs on a large table is in how to add a NOT NULL column to a large table.