PostgreSQL Identity Columns: Modern Auto-Increment

Tip: IDENTITY Column

CREATE TABLE posts (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT
);

SQL standard alternative to SERIAL.

Gotcha: ALWAYS vs BY DEFAULT

• ALWAYS — PostgreSQL always generates the value
• BY DEFAULT — You can override with explicit insert

Tip: Override Identity

INSERT INTO posts (id, title) OVERRIDING SYSTEM VALUE VALUES (100, 'Test');

Required when inserting into an ALWAYS identity column.

Gotcha: Reset Sequence

ALTER TABLE posts ALTER COLUMN id RESTART WITH 1000;

After bulk inserts, reset the sequence to avoid conflicts.

Tip: IDENTITY in Laravel Migrations

$table->id(); // Uses BIGSERIAL, not IDENTITY

Laravel uses SERIAL by default. Use raw SQL for IDENTITY.

Gotcha: SERIAL is Deprecated

PostgreSQL recommends IDENTITY over SERIAL for new projects. SERIAL is a compatibility shim.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Identity columns (PostgreSQL 10+) are now preferred over SERIAL for auto-incrementing primary keys. Unlike SERIAL, identity columns fully conform to the SQL standard and support GENERATED BY DEFAULT vs GENERATED ALWAYS modes. I've migrated all my projects from SERIAL to IDENTITY. The migration is straightforward: ALTER TABLE t ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY. The benefit: you can override identity values during data migration (with GENERATED BY DEFAULT).

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)