Answer

SEQUENCE (e.g., in PostgreSQL and Oracle) is a special database object that stores the current value and allows obtaining unique numbers for insertion (usually keys). IDENTITY is a property of a column that automatically assigns the next sequential value upon insertion (table autoincrement in SQL Server, MySQL, PostgreSQL).

Key differences:

SEQUENCE is an object outside the table, accessible from different places, and you can get the next value without insertion (nextval / currval). It can be used for different tables, and there can be multiple SEQUENCE for one table.
IDENTITY is part of the column definition, automatically increments only upon inserting a row.

Example:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id will be assigned automatically

Issues and nuances:

If you manually insert ids, there may be "gaps" or overlaps.
When deleting rows, the values are not reused, which may lead to gaps in the key range.
In some DBMS (e.g., MySQL), during a transaction rollback, autoincrement may "jump ahead," leaving the unchanged value.
SEQUENCE can be used in multiple tables, but this complicates the maintenance of uniqueness.

Trick question

What happens if two requests simultaneously request nextval() from a single SEQUENCE? Is it possible to get non-unique values?

Answer: No, SEQUENCE guarantees uniqueness. Each request receives a unique value, even with concurrent access. However, the order may not correspond to the order of insertion due to concurrency.

Example:

-- The first thread gets nextval=100, the second shortly afterward gets nextval=101.
-- Even if the first one rolls back later, the value 100 is already in use.

Story #1

After migrating the database from MySQL to PostgreSQL, the differences between SEQUENCE and IDENTITY were overlooked: attempts were made to manually insert ids, leading to a "duplicate key" error during automatic generation. This was fixed by completely transferring key management to SEQUENCE.

Story #2

After partially deleting rows from a table with autoincrement, there was a need to "densely" fill the id range. Due to uncontrolled recreation of new rows, ambiguities arose — keys became inconsistent with the business logic, and a separate SEQUENCE had to be implemented for control.

Story #3

Due to a lack of control over SEQUENCE in a large OLTP system, the order number exceeded 2,147,483,647 and caused a critical failure in the integration API, as it wasn't prepared for bigint values. The request was re-implemented with check limit values for SEQUENCE.

How do sequence generation and processing (SEQUENCE/IDENTITY) work in SQL for automatically creating unique keys, and how are they fundamentally different? What potential issues may arise?

Answer

Trick question