⚡Монотонность и PostgreSQL
Когда возникает потребность в монотонно возрастающих колонках обычно используются bigserial, timestamp и т.п.
На первый взгляд все хорошо, но рассмотрим такую ситуацию:
Для примера возьмем bigserial (bigint, который берет значения из определенного сиквенса)
Получается ситуация, что после шага 5 у нас коммитится сущность с id=2, и только после шага 6 коммитится с id=1. Иными словами, для внешнего наблюдателя айдишники будут добавляться не в монотонном порядке
Последствия: например, фоновые выгрузки данных, которые с пагинацией вычитывают данные, сохраняя последний обработанный id. Может произойти ситуация, что сохранили last_processed_id=5, и после этого добавляется сущность с id=4, которую мы благополучно пропустим
Как с таким можно бороться:
1. Ограничивать временной промежуток
При выгрузках делать условие на
2. Брать айдишники не из секвенса, а из таблицы
И получать айдишник как
В таком случае постгрес с помощью блокировок при update-ах гарантирует строгую монотонность появления новых айдишников, но очевидно в таком случае эта таблица становится боттлнеком для всего, что ее задействует
3. Использование transaction id
Основная суть:
1) В сущности добавляем колонку transaction_id - xid8 транзакции, которая последняя ее проапдейтила. PG гарантирует, что он строго возрастает (подобно значениям из сиквенса)
2) В выгрузках делаем условие
Такой подход при отсутствии долгих транзакций в БД позволяет получить одновременно и “монотонность” и почти реалтаймовость. Однако требует дополнительных усилий с добавлением transaction_id, навешеванием триггера, который будет ее обновлять, и написанием довольно странных запросов с pg_current_snapshot()
Когда возникает потребность в монотонно возрастающих колонках обычно используются bigserial, timestamp и т.п.
На первый взгляд все хорошо, но рассмотрим такую ситуацию:
Для примера возьмем bigserial (bigint, который берет значения из определенного сиквенса)
1. tx1: begin
2. tx1: save(entity) // взяли id=1 из сиквенса
3. tx2: begin
4. tx2: save(entity) // взяли id=2 из сиквенса
5. tx2: commit
6. tx1: commit
Получается ситуация, что после шага 5 у нас коммитится сущность с id=2, и только после шага 6 коммитится с id=1. Иными словами, для внешнего наблюдателя айдишники будут добавляться не в монотонном порядке
Последствия: например, фоновые выгрузки данных, которые с пагинацией вычитывают данные, сохраняя последний обработанный id. Может произойти ситуация, что сохранили last_processed_id=5, и после этого добавляется сущность с id=4, которую мы благополучно пропустим
Как с таким можно бороться:
1. Ограничивать временной промежуток
При выгрузках делать условие на
created_time < now() - interval ’n seconds’, у которого цель — гарантировать, что в данном промежутке появление новых айдишников/таймстемпов будет выглядить монотонно. Подразумевается, что за n секунд все “старые” транзакции закоммитятся. Вполне рабочий способ, если реалтаймовость необязательна2. Брать айдишники не из секвенса, а из таблицы
create table id (
value bigint not null
);
И получать айдишник как
update id set value = value + 1 returning *;
В таком случае постгрес с помощью блокировок при update-ах гарантирует строгую монотонность появления новых айдишников, но очевидно в таком случае эта таблица становится боттлнеком для всего, что ее задействует
3. Использование transaction id
Основная суть:
1) В сущности добавляем колонку transaction_id - xid8 транзакции, которая последняя ее проапдейтила. PG гарантирует, что он строго возрастает (подобно значениям из сиквенса)
2) В выгрузках делаем условие
transaction_id < pg_snapshot_xmin(pg_current_snapshot()). Оно гарантирует, что в выбранных сущностях не появится изменений с меньшим transaction_id (т.е. которые логически произошли раньше)Такой подход при отсутствии долгих транзакций в БД позволяет получить одновременно и “монотонность” и почти реалтаймовость. Однако требует дополнительных усилий с добавлением transaction_id, навешеванием триггера, который будет ее обновлять, и написанием довольно странных запросов с pg_current_snapshot()