2026-09-29 · 8 min read
- sql
- postgresql
- schema
Identity columns are PostgreSQL's standards-aligned way to generate keys. The column owns a sequence. BY DEFAULT allows explicit values and still advances when the column is omitted. ALWAYS generates the value unless the insert uses an override clause.
Treat the sequence position as operational state. Schema migrations, restores, and bulk loads can leave it behind the maximum key or far ahead of it.
Choose ALWAYS or BY DEFAULT
Use GENERATED ALWAYS when the database should be the only source of new keys. Use BY DEFAULT when imports must preserve existing keys and ordinary inserts should still generate them. Document the override clause that loaders are allowed to use.
- Match the column type to the expected key space.
- Name sequences through the column, not ad hoc calls.
- Keep one identity column as the primary key unless designed otherwise.
- Plan a reviewed conversion if the table still uses serial.
Grant table rights, not sequence trivia
Applications should insert into the table and let PostgreSQL use the owned sequence. Direct nextval calls couple clients to sequence names and privileges that identity was meant to hide.
Review roles after a migration from serial. A role that used to have USAGE on a public sequence can fail, or keep a privilege it no longer needs, depending on how the column was converted.
Restart only from a measured maximum
Gaps after rollback are expected. Do not compact them. After a restore or a load that inserted explicit keys, compare the sequence with the maximum existing key under a lock that blocks concurrent inserts, then restart to a value past that key.
Record who ran the restart, the old value, and the new value. A second worker that resets from a stale replica can reintroduce duplicate keys.
Plan failover and logical copy
Sequences are database objects with their own durability and replication behavior. Confirm, for your PostgreSQL version, whether identity sequence state arrives with the failover method you actually use.
Bulk copy tools that insert explicit keys must be paired with a sequence alignment step. Skip that step and the first user-created row after cutover collides.
Rehearse inserts and recovery
Use Flashman's SQL formatter for ownership queries, the timestamp converter for load windows, the JSON formatter for a redacted row, diff for migration text, and the Markdown editor for the restart runbook.
Test default insert, explicit insert, ALWAYS without override, a role without direct sequence access, restore past the maximum key, failover, and two concurrent sessions during restart.