В чём разница между колонками SERIAL и GENERATED AS IDENTITY?

SERIAL vs GENERATED AS IDENTITY

SERIAL — это псевдотип, появившийся в ранних версиях PostgreSQL. При его использовании движок неявно создаёт отдельную последовательность (SEQUENCE) и устанавливает на колонку дефолт через DEFAULT nextval('...'). Колонка при этом имеет тип integer, а связь между ней и последовательностью существует лишь через именование — никакой настоящей зависимости нет.

GENERATED AS IDENTITY (SQL:2003, поддерживается с PostgreSQL 10) — это стандартный SQL-синтаксис. Последовательность создаётся автоматически и жёстко привязана к колонке через системный каталог (pg_depend). При удалении колонки последовательность удаляется вместе с ней. Поддерживаются два варианта:

• GENERATED ALWAYS AS IDENTITY — PostgreSQL всегда генерирует значение; вставить произвольное число можно только явно указав OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT AS IDENTITY — можно передать своё значение без дополнительных оговорок; поведение схоже с SERIAL.

-- Старый способ
CREATE TABLE orders_old (
    id SERIAL PRIMARY KEY,
    amount numeric
);

-- Современный способ (рекомендуется)
CREATE TABLE orders_new (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric
);

-- Посмотреть привязанную последовательность
SELECT pg_get_serial_sequence('orders_new', 'id');
-- Результат: pg_catalog.orders_new_id_seq

-- Принудительная вставка с OVERRIDING
INSERT INTO orders_new (id, amount)
OVERRIDING SYSTEM VALUE
VALUES (999, 100.50);

-- Изменить параметры последовательности через ALTER
ALTER TABLE orders_new
    ALTER COLUMN id
    SET GENERATED BY DEFAULT
    SET START WITH 1000
    RESTART WITH 1000;

Ключевые практические отличия:

• Управление последовательностью: у SERIAL последовательность живёт отдельно и может случайно «потеряться» при DROP COLUMN — последовательность при этом остаётся. У IDENTITY зависимость зарегистрирована в каталоге и каскадно удаляется.
• Права: для SERIAL приходится отдельно выдавать GRANT USAGE ON SEQUENCE; с IDENTITY это не нужно — привилегии на таблицу охватывают и последовательность.
• pg_dump: дамп SERIAL воспроизводит CREATE SEQUENCE + DEFAULT nextval, тогда как IDENTITY сериализуется чище через стандартный синтаксис.
• Переносимость: GENERATED AS IDENTITY является частью стандарта ISO SQL и понятен другим СУБД (Oracle, DB2, MariaDB 10.3+).

Подводные камни

• При копировании таблицы через CREATE TABLE ... AS SELECT или pg_dump --schema-only атрибут IDENTITY не переносится автоматически — нужно явно пересоздавать колонку.
• INSERT ... OVERRIDING SYSTEM VALUE не обновляет «водяной знак» последовательности; после ручной вставки большого ID вызовите ALTER SEQUENCE ... RESTART, иначе получите конфликт уникальности.
• SERIAL создаёт последовательность в той же схеме, что и таблица; при смене схемы (ALTER TABLE SET SCHEMA) последовательность остаётся в старой схеме — неочевидная бомба.
• У GENERATED ALWAYS нельзя вставить DEFAULT через ORM без явного OVERRIDING — некоторые ORM (ранние версии SQLAlchemy) не справлялись с этим сценарием.
• Последовательности не транзакционны: «дырки» в значениях неизбежны при откате транзакций; не используйте ID для нумерации документов.
• При репликации через логическое декодирование IDENTITY-колонки ведут себя корректно, но на подписчике нужно явно устанавливать REPLICA IDENTITY, иначе UPDATE/DELETE упадут.
• bigserial / BIGINT GENERATED AS IDENTITY предпочтительнее serial для таблиц с высокой нагрузкой — диапазон integer исчерпывается при ~2.1 млрд строк.

• Отвечать про serial и generated as identity как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет serial и generated as identity через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.