15.04.2024 09:42
alexmusyka 23 4100 Источник

В этом посте я расскажу о двух причинах, по которым я буду избегать автоинкрементных полей в PostgreSQL и MySQL в будущем. Я буду использовать UUID-поля, если только у меня не будет очень веской причины не делать этого.

MySQL <8.0

Если вы используете старую версию MySQL, то возможно повторное использование автоинкрементных идентификаторов. Давайте посмотрим на это в действии.

$ docker volume create mysql-data
$ docker run --platform linux/amd64 -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -v mysql-data:/var/lib/mysql mysql:5.7

В результате мы получим Docker-контейнер с MySQL 5.7, подключенный к тому, который будет хранить данные между запусками контейнера. Далее создадим простую схему, с которой мы сможем работать:

$ docker run -it --rm --network host --platform linux/amd64 mysql:5.7 mysql -h 127.0.0.1 -P 3306 -u root -p

mysql> CREATE DATABASE my_database;
Query OK, 1 row affected (0.01 sec)

mysql> USE my_database;
Database changed
mysql> CREATE TABLE my_table (
    -> ID INT AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.02 sec)

Теперь давайте вставим пару строк.

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

Пока все хорошо. Мы можем перезапустить сервер MySQL и снова выполнить тот же оператор SELECT и получить тот же результат.

Давайте удалим строку.

mysql> DELETE FROM my_table WHERE ID=3;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Давайте вставим новую строку, чтобы убедиться, что идентификатор 3 не будет использоваться повторно.

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

Идеально. Давайте удалим последнюю строку, перезапустим сервер, а затем вставим новую строку.

mysql> DELETE FROM my_table WHERE ID=4;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM my_table;
ERROR 2013 (HY000): Lost connection to MySQL server during query

$ docker run -it --rm --network host --platform linux/amd64 mysql:5.7 mysql -h 127.0.0.1 -P 3306 -u root -p

mysql> USE my_database;
Database changed

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> INSERT INTO my_table () VALUES ();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM my_table;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Опа. MySQL повторно использовал идентификатор 3. Это потому, что автоинкремент в InnoDB работает так: при перезагрузке сервера он выясняет, какой следующий идентификатор будет использоваться, выполняя этот запрос:

SELECT MAX(ID) FROM my_table;

Если вы удалили последние записи из таблицы непосредственно перед перезагрузкой, то "удалённые" идентификаторы будут повторно использованы при восстановлении сервера.

Теоретически это не должно вызывать проблем. Лучшие практики говорят, что вы не должны использовать идентификаторы из таблиц базы данных за пределами этой таблицы, если только это не поле внешнего ключа, и вы, конечно, не станете использовать эти идентификаторы вне системы, верно?

На практике такое случается и может привести к разрушительным и малозаметным ошибкам. MySQL 8.0 изменил это поведение, сохранив значение автоинкремента на диске таким образом, чтобы оно сохранялось при перезагрузках.

Проблемы с репликацией в PostgreSQL

Как и в MySQL 8.0, в Postgres значения с автоматическим инкрементом хранятся на диске. Он делает это в объекте схемы, называемом "последовательностью".

Когда вы создаете в Postgres поле с автоинкрементами, за кулисами создается последовательность для поддержки этого поля и длительного отслеживания того, каким должно быть следующее значение.

Давайте рассмотрим это на практике.

$ docker volume create postgres-14-data
$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-14-data:/var/lib/postgresql/data -p postgres:14

Запустив Postgres, давайте создадим нашу таблицу и вставим несколько строк:

$ docker run -it --rm --network host postgres:14 psql -h 127.0.0.1 -U postgres
postgres=# CREATE TABLE my_table (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT * FROM my_table;
 id
----
  1
  2
  3
(3 rows)

Пока все хорошо. Давайте посмотрим на таблицу:

postgres=# \d my_table
                            Table "public.my_table"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('my_table_id_seq'::regclass)
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)

Этот вывод говорит нам о том, что значением по умолчанию для нашего поля id является nextval из my_table_id_seq. Давайте посмотрим на my_table_id_seq:

postgres=# \d my_table_id_seq
                  Sequence "public.my_table_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.my_table.id

postgres=# SELECT currval('my_table_id_seq');
 currval
---------
       3
(1 row)

Отлично, у нас есть полноценный объект в Postgres, который отслеживает автоинкрементное значение ID. Если бы мы повторили то, что сделали в MySQL, удалили несколько строк и перезапустили, у нас не возникло бы такой проблемы. my_table_id_seq сохраняется на диске и не теряет своего места.

Или нет?

Обычно обновление Postgres до новой основной версии включает создание нового экземпляра Postgres на желаемой версии и настройку репликации с текущего экземпляра на новый. Затем необходимо переключить приложение на работу с новым экземпляром.

Для начала требуется перезапустить Postgres 14 с обновленной конфигурацией, чтобы активировать логическую репликацию.

$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-14-data:/var/lib/postgresql/data -p postgres:14 -c wal_level=logical

Теперь давайте запустим Postgres 15:

$ docker volume create postgres-15-data
$ docker run --network host -e POSTGRES_PASSWORD=my-secret-pw -v postgres-15-data:/var/lib/postgresql/data postgres:15 postgres:14 -c wal_level=logical -p 5431

Далее мы создадим "публикацию" на нашем экземпляре Postgres 14:

postgres=# CREATE PUBLICATION my_publication FOR ALL TABLES;
CREATE PUBLICATION

Затем мы создаем таблицу "my_table" и подписку "subscription" на нашем экземпляре Postgres 15:

postgres=# CREATE TABLE my_table (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres user=postgres password=my-secret-pw' PUBLICATION my_publication;
NOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

После этого мы должны увидеть синхронизацию данных между старым и новым экземплярами:

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
(3 rows)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
(3 rows)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "INSERT INTO my_table DEFAULT VALUES"
INSERT 0 1

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT * FROM my_table"
 id
----
  1
  2
  3
  4
(4 rows)

Так в чем же проблема? Ну...

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5432 -c "SELECT nextval('my_table_id_seq')"
 nextval
---------
       5
(1 row)

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "SELECT nextval('my_table_id_seq')"
 nextval
---------
       1
(1 row)

Значение последовательности не реплицируется. Если мы попытаемся вставить строку в Postgres 15, то получим следующее:

$ docker run -it --rm --network host postgres:15 psql -h 127.0.0.1 -U postgres -p 5431 -c "INSERT INTO my_table DEFAULT VALUES"
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (id)=(2) already exists.

Примечание: здесь мы попытались вставить id=2, потому что когда мы вызывали nextval ранее, он изменил последовательность.

Это может сделать крупные обновления версий Postgres очень сложными, если вы в значительной степени полагаетесь на автоинкрементные поля ID. Вам нужно вручную изменить значения последовательности на значения, которые, как вы точно знаете, не будут достигнуты в процессе обновления, а затем, вероятно, вам нужно будет отключить запись во время обновления в зависимости от вашей рабочей нагрузки.

Заключение

Вы можете избежать всех вышеперечисленных проблем, используя поля UUID вместо автоинкрементных целых чисел. Их преимущество в том, что они непредсказуемы и не передают чувствительной информации о таблице, если вы в используете идентификаторы за пределами базы данных (чего делать не следует).

Благодаря этой статье от замечательных ребят с Incident.io я теперь знаю о проблеме немецких танков. Стоит прочитать как статью по ссылке, так и страницу в Википедии, чтобы узнать больше причин не использовать автоинкрементные поля ID :)

P.S. Нашёл вот такую статью на Хабре, расширяющую тему

Комментарии (23)


  1. EvilShadow
    15.04.2024 10:24
    #26726129
    +2

    Обычно обновление Postgres до новой основной версии включает создание нового экземпляра Postgres на желаемой версии и настройку репликации с текущего экземпляра на новый. 

    Вы описали один из вариантов, и, по моему опыту, далеко не самый частый. Можно ещё:

    1. Остановить мастер, снять снапшот, обновить данные через pg_upgrade, запустить мастер.

    2. Сделать реплику через физическую репликацию, проделать предыдущий пункт на ней, превратить её в мастер, направить клиентов на неё.

    3. Сделать дамп из старой версии, залить в новую

    4. Использовать сторонние инструменты типа bucardo

    Значение последовательности не реплицируется. 

    Да, и это описано в документации: https://www.postgresql.org/docs/current/logical-replication-restrictions.html

    Sequence data is not replicated. The data in serial or identity columns backed by sequences will of course be replicated as part of the table, but the sequence itself would still show the start value on the subscriber. 


    1. alexmusyka Автор
      15.04.2024 10:24
      #26726357

      Спасибо за дополнение!


      1. kozachenko11
        15.04.2024 10:24
        #26727403

        благодарочка и автору статьи, и вам!


    1. tba
      15.04.2024 10:24
      #26727215
      +1

      do 
$$ 
declare 
rec record;
l_seq bigint;
l_tab_max bigint;
begin
for rec in (select format('%s.%s', table_schema, table_name) as seq, data_type from information_schema.columns 
where column_name = 'id'  and data_type in ('integer', 'bigint') order by 1)
loop 
	begin 
		SELECT nextval(pg_get_serial_sequence(rec.seq, 'id')) into l_seq;
		execute 'select coalesce(max(id), 0) from ' || rec.seq::varchar into l_tab_max;
		if l_tab_max > 0 and l_seq < l_tab_max then 
			execute format('alter sequence %s restart with %s', pg_get_serial_sequence(rec.seq, 'id'), l_tab_max);  
			raise notice 'corrected table % seq % tabmax %', rec.seq, l_seq, l_tab_max;
		end if;
	exception
		when others then
		 null;
	end;
end loop;
end;
$$

      Вот таким скриптом пользуюсь при миграции или после version upgrade. Все identity columns называются id.


  1. username-ka
    15.04.2024 10:24
    #26726215
    +7

    Забыли добавить, что проблема использования UUID в качестве первичного ключа - фрагментация индекса (aka "проблема неравномерного индекса").

    Поэтому нужно использовать UUID v7.


    1. gudvinr
      15.04.2024 10:24
      #26726717

      Поэтому нужно использовать UUID v7.

      Который пока что только черновик стандарта, практически нигде не реализован и всё еще в 2 раза больше чем автоинкремент


      1. username-ka
        15.04.2024 10:24
        #26727475
        +4

        Да, но давайте взглянем поближе.

        • "нигде не реализован" - вполне себе где надо реализован, в т.ч. во вполне себе up-to-date-либах. Все стандартные типы совместимы (т.к. это всё ещё UUID), но алгоритм генерации, конечно, приходится втаскивать. Имхо, небольшая цена за стратегически хорошее решение.

        • "в 2 раза больше, чем автоинкремент" - да - но в относительном выражении, это может быть 5% размера вашей базы, если не меньше. Опять же, цена не слишком высока, и выпячивание этого факта как основного можно рассматривать как premature optimisation и кусочничанье не там где надо.

        Зато вы избавляетесь от жирного нарушения архитектурных границ, а именно - ваши данные перестают зависеть от механизма выбранного движка хранения этих данных (sic!). Обычно автоинкременты сильно аукаются при рефакторинге и всяких других эволюционно-обусловленных штуках (денормализация данных, вынесение микросервиса, перенос части данных в другое более подходящее для данного паттерна доступа решение).

        Ну или например, бывают ситуации, когда нужно быстро вставить в таблицу большое количество свежесгенерированных данных с релейшенами. Когда для того, чтобы вставить релейшен нужно знать id родителя, который нужно вставить до этого. С автоинкрементом я фиг знает как такое сделать (наверное, как-то можно, но я даже думать о таком отказываюсь), а с PK, которые не зависят от хранилища, это довольно простой, хоть и жирный, batch insert, который БД быстренько скушает и обработает.

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


        1. michael_v89
          15.04.2024 10:24
          #26733693

          С автоинкрементом я фиг знает как такое сделать

          Автоинкремент не запрещает указывать id из приложения.


          1. FanatPHP
            15.04.2024 10:24
            #26733753

            Можно, но не нужно.


            1. michael_v89
              15.04.2024 10:24
              #26734485

              Так разговор же как раз о том, что нужно. Данные для интеграционных тестов так и делают, с заданными id, хотя в таблице используется автоинкремент.


              1. FanatPHP
                15.04.2024 10:24
                #26734757

                А при чем здесь тесты? Речь шла про пакетную вставку вообще, а не в изолированном однопользовательском окружении.


                1. michael_v89
                  15.04.2024 10:24
                  #26734965

                  Слово "свежесгенерированных" указывает на то, что значения были сгенерированы в приложении, такая ситуация обычно бывает в тестах. Ситуации, когда снаружи приходит большое количество реальных данных, обычно таким словом не описывают.

                  Когда данные приходят снаружи, главные сущности обычно обрабатывают по одной с вызовом нужных бизнес-проверок в коде. Если открывать транзакцию и коммитить каждые N сущностей, то по скорости это даже не слишком отличается от пакетной вставки. Так чтобы можно было просто сохранять без проверок происходит настолько редко, что нелогично отказываться из-за автоинкремента только из-за этого. И даже если нужно, в данных обычно есть внешний ключ, и после вставки N записей можно сделать один запрос $map = Query::from('table')->select(['id', 'uniq_key'])->where(['in', 'uniq_key', $batchExternalKeys])->indexBy('uniq_key')->all() и указать нужный id в релейшенах.


  1. gleb_l
    15.04.2024 10:24
    #26726363
    +8

    Чем короче первичный ключ, тем компактнее ссылки на него и индексы. Гуиды - сильное расточительство. Только для межсистемных (втч репликационных) взаимодействий.


  1. baldr
    15.04.2024 10:24
    #26726421
    +7

    Ожидал увидеть реальные проблемы типа "при распределенном мастер-мастер-мастер-мастер кластере транзакция будет долго согласовывать уникальность автоинкремента" или "при сплит-брейне кластера я долго восстанавливал целостность автоинкрементов и удалял дубликаты, а вот с UUID этого бы не"... или "с UUID нельзя перебрать все объекты итерируясь по номеру, а с int ключом это было бы уязвимостью" и тп.

    Оказалось что просто автор напоминает о sequences (PG) и обсасывает довольно тривиальные вопросы.


  1. Kahelman
    15.04.2024 10:24
    #26726469
    +4

    Удачи автору в общении с клиентами.

    За использование guid в качестве номера заказа -автора бить будут все и долго и возможно ногами.

    Если использовать guid, то потом все равно придётся прикрутить какой-нибудь user-friendly идентификатор.

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

    Как выше отметили guid как вариант если у вас распределённая система. И то хотелось бы чего-то более практичного -челевекочитаемого и удобно поддерживаемого.

    Кстати для PostgreSQL sequence 8 byte т.е можно пихнуть

    18,446,744,100,000,000,000 Значений.

    Guid -16 Byte куда вам столько?


  1. Akina
    15.04.2024 10:24
    #26726555
    +2

    автоинкремент в InnoDB работает так: при перезагрузке сервера он выясняет, какой следующий идентификатор будет использоваться, выполняя этот запрос

    Вы знаете, я не просто удивлён, а даже где-то изумлён... Это MyISAM работает приблизительно по описанному алгоритму - но никак не InnoDB! Не говоря уж о том, что у таблицы в метаданных есть атрибут AUTO_INCREMENT...

    К сожалению, в ближайшие две недели мне ничего, кроме online fiddle, не будет доступно, а их не перегрузишь... но я бы проверил, не является ли наблюдаемое вами следствием того, что всё происходит в контейнере докера.


    1. baldr
      15.04.2024 10:24
      #26726629
      +1

      Нет, докер там не при чём - он создает volume в самом начале и его использует.

      Я MySQL не трогал лет 10 уже, но после вашего комментария мне стало интересно и я залез в доки. Там, действительно, описывается какие костыли для автоинкремента в базе приделаны - аж три разных режима.

      Там вся страница достойна прочтения, но относительно вашего замечания будет существенным вот этот отрывок:

      In MySQL 5.7 and earlier, the auto-increment counter is stored in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

      SELECT MAX(ai_col) FROM table_name FOR UPDATE;

      In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and saved to the data dictionary on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

      On a server restart following a normal shutdown, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary.


      1. Akina
        15.04.2024 10:24
        #26727005
        +1

        Этот существенный отрывок говорит следующее: если в версии 5.7 или ранее создать InnoDB таблицу, указав некое значение опции AUTO_INCREMENT, а затем перестартовать сервер, то, поскольку показанный запрос вернёт NULL, то следующая запись получит в качестве значения автоинкремента единицу, а значение указанной выше опции будет проигнорировано. И аналогично - для случаев удаления записей с топом перед рестартом.

        Однако я что-то не припоминаю подобных фортелей на практике. И, наоборот, прекрасно помню, что SHOW CREATE TABLE показывал значение опции, которое корректировалось с каждым потенциально-генерирующим запросом и прекрасно переживало рестарт сервера.


    1. FanatPHP
      15.04.2024 10:24
      #26727305

      Нет-нет, всё правда. Точнее как раз наоборот - в myisam последний выданный id хранился перед перезагрузками, а вот в innodb-то как раз и нет. Меня в свое время это тоже поразило. И хорошо, что наконец-то приделали сохранение.


  1. chaturanga
    15.04.2024 10:24
    #26726881

    @Boomburum


    1. baldr
      15.04.2024 10:24
      #26726885

      Там какие-то голоса (мой втч) посчитались за 0.5. Я видел "+ 5.5 и -0.5". Но перепутали цифры где-то.


    1. Boomburum
      15.04.2024 10:24
      #26726919
      +3

      Знаем об этом баге, надеюсь, скоро починят.


  1. Dremkin
    15.04.2024 10:24
    #26733403

    Описанная проблема с mysql, если она существует, то давно не актуальна - вы же не будете в новых проектах использовать древнюю версию mysql ) и уж точно из-за практически нулевой вероятности проблем менять первичные ключи на guid - это it преступление )