Столбцы идентификации, последовательности и пропуски в сгенерированных ID

Что это такое

id bigint GENERATED ALWAYS AS IDENTITY или вариант BY DEFAULT объявляет столбец идентификации. Документация описывает его как столбец с неявной последовательностью: при ALWAYS значение пользователя принимается только тогда, когда INSERT содержит OVERRIDING SYSTEM VALUE; при BY DEFAULT переданное значение имеет приоритет. nextval продвигает последовательность. Документация функций последовательностей указывает, что значение, полученное через nextval, не возвращается, если вызывающая транзакция впоследствии прерывается; INSERT с ON CONFLICT вычисляет строку, включая вызовы nextval, до обнаружения конфликта. Поэтому последовательности не могут обеспечивать нумерацию без пропусков. Страница CREATE SEQUENCE добавляет, что CACHE больше единицы заранее выделяет значения каждому сеансу: неиспользованные значения теряются при его завершении, а значения разных сеансов могут идти не по порядку.

Почему это важно

Разработчики и аудиторы замечают пропуски и подозревают потерю строк. Код, предполагающий, что следующее значение равно max(id) + 1 или что count(*) равен max(id), ошибочен. Требования к непрерывной нумерации нельзя удовлетворить за счёт первичного ключа.

Как применять

• Предпочитайте bigint GENERATED ALWAYS AS IDENTITY; предел столбца integer — 2^31 - 1 (2,147,483,647), а последующая смена типа переписывает таблицу.
• Считайте ID непрозрачными идентификаторами: не выводите смысл из пропусков или величины. Если важно не раскрывать число строк, не допускайте его раскрытия через ID, а снаружи используйте UUID или случайные токены.
• Когда нужна непрерывная нумерация, например для документов, присваивайте номер в момент окончательного оформления документа из строки-счётчика, обновляемой в той же транзакции (UPDATE counters SET last = last + 1 WHERE name = 'invoice' RETURNING last). Документация CREATE SEQUENCE описывает такой блокируемый счётчик как гораздо более дорогой, чем последовательность: такова цена гарантии.
• После массовой загрузки явных ID в столбец идентификации BY DEFAULT перенастройте последовательность (SELECT setval(pg_get_serial_sequence('t', 'id'), max(id)) FROM t), иначе следующее сгенерированное значение вызовет конфликт.
• Оставляйте CACHE равным значению по умолчанию 1, если измерения не показали, что последовательность стала узким местом.

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

Изменения через setval немедленно видны другим сеансам и не отменяются откатом. Столбцы Serial (serial, bigserial) — это последовательность и значение столбца по умолчанию, а не столбец идентификации, поэтому сам этот механизм никогда не отклоняет явно заданные значения. Документация CREATE SEQUENCE указывает, что по умолчанию действует NO CYCLE: при достижении максимума nextval возвращает ошибку, а не начинает отсчёт заново.