Как обезопасить id SERIAL для корректной работы без промежутков значений?

Question

[Boris007]

Создал таблицу:

CREATE TABLE users(
   id SERIAL PRIMARY KEY,
   name VARCHAR(255)
)

При первых INSERT запросах было всё нормально, но потом после id == 9, следующий был id == 37, потом 81,82,83,84,85
Из-за чего это происходит и как этого избежать?
Как обезопасить записи в БД по их корректности?

Comments

[Алексей Ярков]

Из-за чего это происходит

Рискну предположить, что записи между 9 и 37 были удалены, а счётчик автоинкремента просто продолжил работу.

Answer 1

[kalapanga]

Прежде всего: https://www.postgresql.org/docs/current/datatype-n...
А здесь примеры есть: https://habr.com/ru/companies/tensor/articles/507688/
Если кратко, то это нормальное поведение.

Comments

[Boris007]

Спасибо!

Как понять, что разумно не использовать SERIAL?
Ведь нам везде нужны уникальные ID наших пользователей, товаров, записей, сообщений в чате
Мне сложнее представить таблицу, где не нужен уникальный id

разумно
Не использовать serial и ON CONFLICT на таблицах с ожидаемо существенным количеством конфликтующих вставок.

[kalapanga]

Как раз уникальность serial вполне себе обеспечивает. Пользуйтесь на здоровье!
Но если у Вас работа с базой организована так, что строк вставляется по многу и при этом часто могут происходить отмены операции вставки (не важно по каким причинам), нужно учитывать, что номера могут расходоваться быстрее чем ожидается (выделенные но неиспользованные номера обратно не возвращаются). Тут и нужно выбирать serial подходящей размерности, либо отказываться от него.

[Boris007]

kalapanga, спасибо!
В каких таблицах serial не используют?
Мне просто трудно представить таблицу, где мы не привязываемся к уникальному id записи

[kalapanga]

Boris007, уникальный id действительно практически всегда есть. Речь о том, что это не обязательно должен быть тип serial. Например это может быть GUID.

Answer 2

[Алексей Черемисин]

Автоинкрементные ключи вообще лучше не использовать - это вселенское зло!
Используйте или uuid, или свой какой-то счетчик, или композинтый ключ или хеш, да все что угодно, только не автоинкремент.
Почему автоинкремент плохо - сотни статей в интернетах.
Вот на почитать;
- https://azimutt.app/blog/stop-using-auto-increment...
- https://www.clever-cloud.com/blog/engineering/2015...

Comments

[Akina]

Автоинкрементные ключи вообще лучше не использовать - это вселенское зло!
...
Почему автоинкремент плохо - сотни статей в интернетах.

Не надо повторять глупые фразы за безответственными людьми.
В интернетовских статьях (в т.ч. и в статьях по ссылкам) все аргументы - или (1) вообще не по делу, или (2) откровенно за уши притянуты, или (3) пытаются перенести на автоинкремент вину за изначально неправильную архитектуру системы.
Во всяком случае системно поддерживаемый автоинкремент куда как лучше какого-то своего самопального счётчика. А уж UUID - это и вовсе не альтернатива, ибо проигрывает автоинкременту по всем параметрам, кроме предсказуемости (а в этом случае - см. пункт 3).

[Алексей Черемисин]

Akina, Увы, у меня больше 30 лет разработки. Так что уж за кем и повторяют, то это и за мной в том числе.
И большинство проблем с SQL, особенно у новичков, как раз из-за автоинкремента, вот как сейчас.
Больше скажу, правильно спроектированная база в автоинкременте не нуждается.
Еще более скажу - uuid совсем не зло, особенно при миграции баз данных, и особенно при шардировании, расщеплении, слиянии, кешировании.
У меня очень просто пример - есть пару сотен (тысяч и т.д.) устройств, каждая со своей локальной базой. Если в этих базах мы будем использовать автоинкремент и попытаемся их когда нибудь слить, а это рано или поздно произойдет (и происходит регулярно у всех, но вразное время), то получим очень большую головную боль на несколько месяцев разработки.

[Akina]

большинство проблем с SQL, особенно у новичков, как раз из-за автоинкремента, вот как сейчас.

Это - проблема не автоинкремента. Это безграмотность новичка, который пытается на одно поле навьючить две разные функции одновременно. Причём функции несовместны - и, ясен пень, ничего не получается.

правильно спроектированная база в автоинкременте не нуждается.

100%.
До тех пор, пока речь не пойдёт о ресурсах и производительности - однозначно так.

Еще более скажу - uuid совсем не зло

А вот с точки зрения как раз потребления ресурсов и производительности - зло, да ещё какое... не, не само по себе, а по сравнению с автоинкрементом, конечно.

У меня очень просто пример

Бессмысленно. Всегда найдутся условия, в которых самый идиотский подход окажется оптимальным.

[Boris007]

Почему это плохо?
В MySQL с этим проблем никогда не было, почему Postgres слабоват на этот момент?

[Дмитрий]

Boris007, а с чего вы решили что у MySQL проблем не было?
https://www.percona.com/blog/illustrating-primary-...

Answer 3

[Akina]

Примите постулат:

Никто не должен видеть значений автоинкрементного синтетического ключа, кроме самого сервера.

AI PK существует только и исключительно для правильной работы встроенной подсистемы СУБД, осуществляющей контроль целостности и непротиворечивости данных. Попытка возложить на такое поле ещё какую-то функцию, тем более функцию, в результате выполнения которой значения этого поля станут видны пользователю (ещё хуже - если эти значения станет необходимо видеть пользователю), немедленно порождает проблемы.
Причём порождаемые проблемы никак не связаны с основной функцией поля - вот какое пользователя собачье дело, последовательны значения или с разрывами? разрывы влияют на уникальность? нет... или они нарушают нормальную работу ссылочной целостности? нет... или они...? нет... А вся претензия в одном - типа "некрасиво". Аргумент для дураков - потому как значение в таблице БД, а некрасивость в выводе на экран, осуществляемом клиентским приложением. Но если даже отсутствие логики не препятствие, так вон тут рядом советовали - заводишь отдельное поле, в него "свой какой-то счётчик" программный, и поддерживай свою непрерывность хоть до посинения! тем более что сейчас найти (актуальную версию любой) СУБД, не поддерживающую CTE, ROW_NUMBER(), тем более ORDER BY - без шансов.

Comments

[Boris007]

То есть перескоки с числами это нормальное поведение в Postgres при указание SIREAL и не стоит на это обращать внимание?
В MySQL такого не было просто

[Akina]

Boris007, это нормальное поведение в ЛЮБОЙ СУБД.

Генератор автоинкремента гарантирует, что при полностью автономной работе (т.е. без вмешательства в его работу извне) очередное сгенерированное значение превышает любое ранее сгенерированное значение. К слову, именно следствием этой гарантии и является гарантия, что никакое значение не будет сгенерировано повторно.

Всё. Более автоинкремент НИЧЕГО не обещает и не гарантирует. В том числе и непрерывности в списке генерируемых значений.

В MySQL такого не было просто

Ну либо ты не видел, либо не обратил внимания. Попробуй INSERT .. ODKU.

[Boris007]

Akina, спасибо!
Для БД имеет значение, какой длинны будут после нумерация id, к примеру миллиард, ей все равно на длину числа или id == 1 по памяти и нагрузке тоже самое, что и id будет равен 1000000000?

[Akina]

Boris007, СУБД пофиг на значение. Всё, что её интересует - это размер данных. Скажем - есть 64 бита, и они и хранятся, и обрабатываются одинаково вне зависимости от того, сколько и какие именно биты единичные и какие нулевые.