PostgreSQL identity columns generate values from an associated sequence when an insert requests the generated value. They simplify key allocation, but they do not guarantee uniqueness by themselves, provide gapless numbering, or authorize access to the row identified by a generated number.
The dependable design separates generation from constraints, retrieval, import policy, and business meaning. That makes ordinary inserts simple while keeping unusual maintenance and recovery operations deliberate.
Choose the key’s meaning first
A generated key is usually an internal identifier, not a statement about business order or a secret access capability. Decide what the key identifies and how it is exposed before selecting its generation mechanism.
Do not use a sequential ID as the only authorization check for an API record. The application must still verify that the current caller can access that object.
Also distinguish internal identity from customer-facing numbering with legal or business requirements. A mechanism that tolerates gaps is not automatically suitable for a numbering scheme that demands explicit accounting for every issued number.
Define identity and uniqueness separately
An identity column is automatically non-null, but uniqueness still requires a primary key or unique constraint. Sequences can be reset, and some insertion paths allow explicit values.
CREATE TABLE report_job (
job_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
requested_by text NOT NULL
);The example combines automatic generation with an explicit key constraint. It does not establish that the requesting identity is authorized or that the job request is unique in the business sense.
Review any additional uniqueness requirements separately, such as one active request per customer and operation key.
Choose ALWAYS or BY DEFAULT deliberately
GENERATED ALWAYS provides protection against accidentally supplying a value during ordinary inserts. Explicit insertion uses the documented override mechanism when appropriate.
GENERATED BY DEFAULT allows a supplied value to take precedence. That can support controlled imports, but it also increases the importance of validating which clients are allowed to provide identities.
Choose the policy based on the write paths, not personal preference. Document maintenance exceptions so an operator does not weaken the schema merely because an import tool unexpectedly sends a key column.
Request and retrieve the generated value clearly
Omitting the identity value or using DEFAULT requests sequence-based generation in the supported insert context. Explicit null should not be treated as an informal synonym for that request.
Use the insert’s returned value when the application needs the key it just created. A RETURNING clause ties the result to the actual inserted row and avoids guessing from a later global maximum.
Do not query the largest ID to identify your insert. Another session can insert between operations, and the largest value may not correspond to your transaction or business action.
Expect gaps without calling them corruption
Sequence values obtained during an operation are not generally reclaimed when its transaction later aborts. Conflicts and other normal execution paths can also advance a sequence without producing a new committed row.
A gap therefore does not automatically prove that data was deleted or that a transaction disappeared. Investigate through application records and database evidence rather than treating the numeric pattern as a complete audit trail.
If gapless business numbering is genuinely required, design it as a separate invariant with the appropriate concurrency and accounting rules. Do not promise that an ordinary identity column supplies it.
Do not infer commit order from IDs
Concurrent transactions can obtain sequence values and commit in a different order. A higher ID is not guaranteed to represent a later successful business event.
Store the timestamps and ordering evidence the application actually needs. If pagination or processing depends on a stable event order, define that contract explicitly rather than deriving it casually from an internal key.
Also consider imported rows and restored data. Their identities may follow a preservation policy rather than the chronology of the current database’s writes.
Reconcile explicit imports and sequence state
An import that supplies existing IDs needs both collision checks and a plan for future generated values. Successfully loading rows does not automatically establish that the associated sequence is positioned appropriately.
Use a controlled maintenance process that accounts for concurrent writers, the actual sequence configuration, and the imported key range. Do not casually run a maximum-plus-one repair while the application continues allocating values.
Preserve a backup and test the procedure on representative data. After the import, verify a normal generated insert and the uniqueness constraints before reopening the full write workload.
Treat sequence resets as consequential changes
Changing sequence state can affect other transactions and future identities. The documented setval behavior is not undone merely because the caller later rolls back its transaction.
A transaction wrapper therefore does not make an improvised reset harmless. Review the exact semantics, permissions, and concurrency plan before executing maintenance that changes the generator.
Avoid resetting simply to remove visible gaps. Cosmetic renumbering can collide with existing rows, break references, or confuse external systems that already recorded the old identities.
Review type capacity and schema relationships
Choose a supported identity type with sufficient capacity for expected growth. Monitor long-lived systems so exhaustion is identified before inserts begin failing.
Table inheritance and partitioning have documented identity behavior that should be reviewed for the actual design. Do not assume copying a table definition or adding a child table always creates the generator relationship you intended.
Test generated inserts through the table the application really targets. A direct insert into a child or another schema path can behave differently from the parent-level workflow used in a simple demonstration.
Include generation in restore tests
A restore needs to preserve both data and the relevant generator behavior. After a test restore, verify constraints, references, and a normal insert that requests a generated key.
Test an explicit import, a rolled-back insert, a conflicting insert, and concurrent writers. Confirm the application handles expected failures without returning an unrelated key or retrying a consequential action blindly.
The final contract should be straightforward: identity allocates candidate values, constraints protect uniqueness, and the application defines meaning and access. Keeping those duties separate makes maintenance safer and errors easier to explain.
Frequently asked questions
Does identity automatically mean primary key?
No. Add the appropriate primary key or unique constraint separately.
Are gaps evidence of deleted records?
Not necessarily. Aborted or conflicting operations can consume sequence values without creating a committed row.
Can I roll back every sequence-state change?
Do not assume that. Review sequence function semantics before treating maintenance as transactionally reversible.
See the identity column documentation and sequence function reference for generation, override, and rollback behavior.
For a complementary workflow, read PostgreSQL UPSERT: Conflicts, Keys and Update Meaning.
