Почему ON CONFLICT DO NOTHING тебя ненавидит и конфликтует

Оригинал https://t.me/bits_and_bicycles/22

В стандарте SQL есть такая конструкция INSERT ... ON CONFLICT (constraint) DO UPDATE, также известная как UPSERT. Нужна чтобы добавить или подобновить строку. на И есть её брат-близнец INSERT ... ON CONFLICT (constraint) DO NOTHING, которая просто превращается в no-op, если строка уже есть.

Но DO NOTHING этот работает не совсем интуитивно. По крайней мере, в #postgresql.

Представь, что у тебя есть CREATE TABLE numbers (value INT UNIQUE) и ты выполняешь одновременно два одинаковых запроса в разных сессиях (горизонтальное масштабирование и вот это вот всё): INSERT INTO numbers (value) VALUES (1) ON CONFLICT DO NOTHING.

Одной сессии повезёт больше и она добавит строку, пусть даже в транзакции. Другой сессии же придётся встать в очередь и ждать. Потому что любое предположение о другой транзакции может быть неправильным: строку могут удалить следующим запросом, да всю транзакцию могут отменить, в конце концов.

И вот ты вроде написал ON CONFLICT DO NOTHING, а де-факто в PostgreSQL получил не DO NOTHING, а DO NOTHING NOTHING OR LOCK numbers_value_unique_ix. Насколько долгий lock? На lock_timeout, который ты, конечно же, забыл настроить для своей сессии.

“Ну и что” – скажешь ты, “мы хотели одну строку и получили в итоге одну строку, пусть и подождали немного”. И, действительно, обычно нет ничего страшного если строка одна. Но если ты UPSERT’ишь с сотню одинаковых строк в нескольких сессиях, то конфликтов становится слишком много и ты рискуешь выстроить все свои INSERT’ы в очередь, которая просто не будет успевать разгребаться.