Потенциальные проблемы с автоинкрементным ключом. MySQL <8.0, PostgreSQL

В этом посте я расскажу о двух причинах, по которым я буду избегать автоинкрементных полей в 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. Нашёл вот такую статью на Хабре, расширяющую тему