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

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.

What serial actually creates

The PostgreSQL manual states that serial types "are not true types, but merely a notational convenience." An id serial becomes three distinct components:

1. A sequence - e.g., users_id_seq AS integer
2. A column - id integer NOT NULL DEFAULT nextval('users_id_seq')
3. An ownership link - the sequence is dropped with the column via OWNED BY

Identity columns arrived in PostgreSQL 10 and were described in the release notes as "similar to SERIAL columns, but are SQL standard compliant." The framework you use determines which one you end up with:

Framework	What it creates for an auto-increment key on PostgreSQL
Django 4.1 and later	Identity column, GENERATED BY DEFAULT
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

The command \d users reveals which one a table has. A serial key shows nextval('users_id_seq'::regclass) as its default, while an identity key shows generated always as identity or generated by default as identity.

Serial vs identity: which should you use?

For every new table, choose an identity column. The differences between serial and identity only manifest during unusual operations, which explains why serial persists in older schemas. The following scenarios demonstrate where serials misbehave:

• Duplicate key violations: An INSERT supplying id = 1 succeeds, but the next generated id is also 1 and fails with "duplicate key value violates unique constraint "users_pkey"".
• Permission issues: An application role with INSERT permission on the table receives permission denied for the sequence because the sequence is locked by the column.
• Shared counters across copies: Creating a COPY (LIKE users INCLUDING ALL) inherits the same underlying sequence, causing both tables to draw from one counter.
• Integer overflow: After widening a column from integer to bigint, the original sequence remains AS integer and caps at 2,147,483,647. Subsequent inserts beyond that cap fail even though the column type is now bigint.
• Foreign key bugs: When a foreign key column is defined as serial, it behaves like a regular column and allows arbitrary IDs, bypassing the intended referential integrity.

Five ways serial misbehaves (PostgreSQL 18.3)

Each scenario was reproduced on PostgreSQL 18.3 within a throwaway container.

1. Generated Always As Identity

Step	Result
Insert id = 1	Accepted; the next generated id is also 1 and fails with "Refused: cannot insert a non-DEFAULT value into column "id", with the hint Use OVERRIDING SYSTEM VALUE to override"
App role with INSERT-only permission	Permission denied for sequence users_id_seq

When converting to identity, the workaround is to use GENERATED BY DEFAULT AS IDENTITY instead of GENERATED ALWAYS AS IDENTITY, and to employ OVERRIDING SYSTEM VALUE in any loader that must assign explicit IDs.

2. The widening row is the costly operation

After a table outgrows integer, a typical schema migration alters the column type to bigint. However, the associated sequence remains AS integer and caps at 2,147,483,647. The next insert after 2,147,483,647 fails with "ERROR:nextval: reached maximum value of sequence "users_id_seq" (2147483647)". The fix requires an additional statement: ALTER SEQUENCE users_id_seq AS bigint. This step is often overlooked because \d users displays the sequence as bigint and appears complete.

3. Integer-to-Bigint rewrite is expensive

Changing an integer key to bigint rewrites the entire table. In a test with 1,000,000 rows, the operation took approximately 761 ms under an ACCESS EXCLUSIVE lock. Every foreign key column pointing at the key must undergo the same change before IDs exceed 2,147,483,647. Additionally, if a view reads the key, PostgreSQL will refuse the ALTER TABLE outright until the view is dropped and recreated.

4. Foreign key columns inherit the wrong type

Copying a parent's column type into a foreign key creates a subtle bug. Consider:

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

Although user_id is defined as serial, the foreign key actually stores plain integers (bigint) rather than the serial type. An INSERT INTO orders (note) VALUES ('forgot user_id') succeeds and returns user_id = 1, passing the foreign key validation because user 1 exists. Later inserts that omit the column attach subsequent orders to user 2, then 3, until reaching an ID with no corresponding parent record-at which point the foreign key constraint fails.

In general, a foreign key column should take the plain integer type under the parent's key (integer for serial, bigint for bigserial), and should be NOT NULL when the relationship is mandatory. Never store the serial itself in a foreign key column.

5. Conversion without table rewrite

Converting an existing serial column to identity is possible in a short transaction that touches only the catalog, not the rows. The process involves swapping the default for an identity property while preserving the old counter:

BEGIN;
-- Remove the default from the serial column
ALTER TABLE invoices ALTER COLUMN id DROP DEFAULT;

-- Rename the sequence to reflect its history
ALTER SEQUENCE invoices_id_seq RENAME TO invoices_id_seq_old;

-- Add the identity property to the column
ALTER TABLE invoices ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

-- Sync the new sequence with the old one, keeping the old name
SELECT setval('invoices_id_seq', last_value, is_called) FROM invoices_id_seq_old;

-- Drop the old sequence
DROP SEQUENCE invoices_id_seq_old;
COMMIT;

On a 1,000,000-row table where the top ten rows had been deleted, this transaction completed in roughly 3 ms. The table's data file remained unchanged, and the next insert received id = 1000001. The critical detail is copying last_value rather than max(id): using max(id) would redistribute the deleted IDs (e.g., 999,991-1,000,000) back to new rows, breaking any external references to those deleted records.

How Schemity handles serial and identity keys

Schemity is a desktop ERD tool that reads your live database, showing the impact of every schema change before execution, and keeps the diagram as a file in Git. When connecting to PostgreSQL, each column's default is displayed on the canvas: serial columns show nextval(...) as their default, while identity columns display nothing. This visual distinction makes legacy keys immediately recognizable and clarifies common bugs-such as a nextval on a foreign key column that should have been an integer.

During design, Schemity encourages creating tables with a single integer primary key and no default. The planned SQL reads "id" INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or BIGINT with a bigint key). When drawing relationships, the tool ensures the foreign key column retains its plain type (integer for serial or bigint for bigserial) and is marked NOT NULL when the relationship is mandatory, avoiding the pitfalls described above.

Recommendations

• New tables: Always use bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or BIGINT GENERATED BY DEFAULT AS IDENTITY).
• Loaders that write IDs: Either use GENERATED BY DEFAULT AS IDENTITY or explicitly include OVERRIDING SYSTEM VALUE in the insertion logic.
• Existing serial keys: Convert them in a short transaction after verifying there are no shared sequences and no scripts inserting explicit IDs. Follow the five-step conversion outlined above.
• Foreign key columns: Keep the plain integer type under the parent key, never serialize the parent's type in a foreign key definition.
• Widening strategies: If a serial key must become bigint, widen the sequence alongside the column, or convert to identity first. The latter approach avoids touching the data files entirely.