What is a database sequence object, and how do IDENTITY columns and auto-increment/serial columns relate to it?
answer
- sequence = standalone number generator
- identity/serial = sequence bolted to a column
- ALWAYS blocks user values; BY DEFAULT allows them
- currval is session-scoped
- explicit inserts ⇒ reset the generator
basics
~20 sA sequence is a standalone database object that hands out unique increasing numbers on request, independent of any table. An IDENTITY or serial column is a column wired to such a generator so inserts get a value automatically. Same machinery, different packaging.
solid answer
~50 sA **sequence** is a schema object whose only job is to produce numbers. You ask it for the next value and it returns one, guaranteeing no two callers get the same number. It is not tied to a table or a column, so several tables can share one, and you can fetch a value before you insert anything. An **IDENTITY column** (the SQL-standard form) declares that a column's value is supplied by such a generator, with `GENERATED ALWAYS` refusing user-supplied values and `GENERATED BY DEFAULT` allowing them. Legacy `serial`/`AUTO_INCREMENT` spellings are the same idea with less control: implicitly create or use a counter attached to the column. Useful knobs: the start value, the increment, `CACHE` (how many values a session preallocates), and `CYCLE` (wrap around at the maximum instead of erroring). The important shared property is that allocation happens outside your transaction's isolation — that is what makes values unique but also gappy.
code
sql · 10 linesCREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1 CACHE 20 NO CYCLE;
INSERT INTO orders (id, customer_id)
VALUES (NEXT VALUE FOR order_id_seq, 42);
-- equivalent packaging
CREATE TABLE orders2 (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL
);go deeper
Say a sequence is an object that hands out unique increasing numbers, and identity/serial columns use one automatically on insert.
Add the knobs (start, increment, CACHE, CYCLE), the ALWAYS vs BY DEFAULT distinction, and the drift problem after explicit-id loads.
Discuss ownership and permissions, pre-allocation patterns for object graphs, and why id order does not imply commit order.
Frame identifier generation as a design choice — centralised sequence, per-table identity, or externally generated ids — against write distribution, migration and multi-region needs.
## The generator A **sequence** is a persistent counter kept in the database catalog. Its interface is essentially one operation: give me the next value. The engine guarantees that concurrent callers never receive the same number. Because the sequence is an object in its own right, it has a life independent of tables: you can create one, use it from several tables, read a value into your application before inserting, or use it to number things that are not rows at all (batch ids, correlation ids). Typical parameters: - `START WITH` / `INCREMENT BY` — where it begins and the step (negative steps are legal). - `MINVALUE` / `MAXVALUE` — the range, bounded in practice by the column type it feeds. - `CACHE n` — how many values a session or instance preallocates in memory before touching shared state, trading gap-freedom for speed. - `CYCLE` / `NO CYCLE` — wrap around when the range is exhausted, or raise an error. `NO CYCLE` is the safe default for surrogate keys, because wrapping produces duplicate keys. The classic call pair is `nextval` (advance and return) and `currval` (return the value this *session* last obtained, without advancing). `currval` is session-scoped by design: it is not "the latest value globally", which would be useless and racy. ## Identity columns Writing `nextval` by hand on every insert is tedious, so the standard offers **identity columns**: a column declared `GENERATED ALWAYS AS IDENTITY` or `GENERATED BY DEFAULT AS IDENTITY`, backed by an implicit sequence the database owns. - `GENERATED ALWAYS` — the engine supplies the value and rejects a user-supplied one unless you explicitly override. This is the stronger choice for surrogate keys: it stops application code from inserting a colliding id. - `GENERATED BY DEFAULT` — the engine supplies a value only when you omit the column. Convenient for data loads and replication that must preserve existing ids, but it lets the sequence and the table's actual maximum drift apart, which surfaces later as duplicate-key errors. Older, vendor-specific spellings do the same thing: a `serial` pseudo-type that creates a sequence and a column default, or an `AUTO_INCREMENT` attribute where the counter is maintained with the table rather than as a separate object. They differ in whether you can query the generator directly, share it across tables, or grant permissions on it; conceptually they are all "a counter attached to a column". ## Why the distinction matters in practice Three practical consequences follow from "identity is a sequence with packaging": 1. **Ownership and lifecycle.** An implicit identity sequence is owned by the column and dropped with it; a standalone sequence outlives tables and must be managed (and permissioned) separately. 2. **Pre-allocation.** With a standalone sequence you can obtain the key first and use it to build an object graph in memory before any insert. Identity columns hand you the value only after the insert, which changes how you write parent/child inserts. 3. **Drift.** If ids are inserted explicitly — a restore, a migration, a `GENERATED BY DEFAULT` bulk load — the generator does not learn about them. The next generated value can collide with an existing row. After any such load, reset the generator above the current maximum. ## What sequences do not give you They are not a row counter, not a gap-free numbering scheme, and not an ordering guarantee for commits: two transactions can take values 10 and 11 and commit in the opposite order, so a higher id does not prove a later commit. They also should not be used to generate business-visible document numbers that regulators require to be contiguous — that needs a different, transactional mechanism.
- When would you use a standalone sequence rather than an identity column?When you need the value before the insert — building a parent and its children in memory, or writing the id into a message before the row exists — or when several tables must draw from one number space, or when you want to grant usage on the generator independently of table privileges. Identity is the better default otherwise because it keeps the generator's lifecycle tied to the column.
- What is the practical difference between GENERATED ALWAYS and GENERATED BY DEFAULT?GENERATED ALWAYS rejects a user-supplied value unless you explicitly override it, so application bugs cannot insert a colliding key. GENERATED BY DEFAULT only fills in the value when the column is omitted, which is convenient for migrations and restores that must preserve ids. The risk with BY DEFAULT is drift: explicitly inserted ids do not advance the generator, so later generated values can collide.
saying these in an interview costs you the question
- Thinking currval returns the newest value across all sessions
- Believing identity columns are a different mechanism from sequences rather than packaging around one
- Assuming ids are contiguous or usable as a row count
- Restoring data with explicit ids and not resetting the generator
- Treating a higher id as proof of a later commit