Ответ на вопрос.

Псевдотаблица EXCLUDED в PostgreSQL представляет собой строку, предложенную для вставки во время операции ON CONFLICT. Исторически разработчики, мигрировавшие из окружений MySQL или Oracle, часто предполагают, что прямые сравнения на равенство (=) достаточны для обнаружения изменений значений в паттернах upsert. Однако трехзначная логика SQL устанавливает, что NULL представляет собой неизвестное состояние, что означает, что NULL = NULL оценивается как NULL (неизвестно), а не как TRUE.

Это создает критическую проблему, когда условие разрешения конфликта пытается оптимизировать обновления, добавляя условие WHERE, такое как WHERE EXCLUDED.phone != users.phone. Если как существующая строка, так и предложенная строка содержат NULL для столбца phone, сравнение возвращает NULL, что приводит к провалу предиката WHERE. Следовательно, база данных пропускает обновление, даже если значения могут быть действительно разными в контексте бизнес-логики, или не может различить NULL в новых данных и NULL в старых данных.

Решение заключается в использовании оператора IS DISTINCT FROM, который рассматривает NULL как сопоставимое значение. Структурировав предложение обновления с WHERE EXCLUDED.column IS DISTINCT FROM table.column, сравнение возвращает FALSE, когда оба значения равны NULL (что указывает на отсутствие изменения) и TRUE, когда одно значение NULL, а другое — нет. Это гарантирует детерминированное поведение и предотвращает ненужные записи.

INSERT INTO patient_records (clinic_id, external_id, phone, updated_at)
VALUES (101, 'P-2024-001', NULL, NOW())
ON CONFLICT (clinic_id, external_id) 
DO UPDATE SET
  phone = COALESCE(EXCLUDED.phone, patient_records.phone),
  updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;

Ситуация из жизни

Сеть больниц нуждалась в синхронизации данных ежедневного поступления пациентов из 50 внешних клиник в центральный PostgreSQL дата-склад. Каждая клиника экспортировала CSV файлы, где отсутствующие номера телефонов пациентов появлялись в виде пустых строк, которые команда COPY преобразовывала в NULL во время погружения. Существующий Python ETL скрипт использовал SQLAlchemy для выполнения массовых upsert с ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.

Проблема возникла, когда сотрудники клиники сообщили, что действительные номера телефонов, введенные непосредственно в центральную систему, таинственным образом исчезали после ночной синхронизации. Расследование показало, что когда внешний поток отправлял NULL (указывая на неизвестный телефон), он перезаписывал существующие действительные номера, поскольку команда SET исполнялась без условий. Добавление наивного фильтра WHERE EXCLUDED.phone != patient_records.phone провалилось, потому что когда оба были NULL, сравнение возвращало NULL (неизвестно), заставляя обновление пропуститься неправильно, и когда новое значение было NULL, а старое — нет, логика вела себя непоследовательно в различных минорных версиях PostgreSQL.

Были оценены три решения.

Первый подход использовал COALESCE исключительно в команде SET: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Это предотвратило перезапись с NULL, но вынуждало обновление при каждом конфликте, вызывая дорогостоящие перестройки индекса B-Tree на столбце phone и срабатывание триггеров аудита, которые записывали "no-op" изменения как законные модификации. Это увеличивало трафик WAL (Write-Ahead Log) на 300%, угрожая запаздыванию репликации и насыщая операции ввода-вывода на диске.

Второе решение пыталось использовать явную булевую логику для обработки NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Хотя логически верно, этот многословный паттерн требовал тщательного обслуживания по 15 столбцам, допускающим NULL, и путал оптимизатор запросов. Планировщик отказался от индексации и предпочел последовательные сканирования по таблице на 20 миллионов строк, что заставило задание ETL превысить его шестичасовой временной лимит.

Третье решение реализовало IS DISTINCT FROM для всех столбцов, допускающих NULL, в команде WHERE. Это обеспечивало краткое, sargable предикат, который правильно идентифицировал подлинные изменения данных, включая переходы NULL. Это позволяло обновлениям происходить только при необходимости, устраняя избыточные срабатывания триггеров и генерацию WAL, при этом поддерживая согласованные планы запросов.

Команда выбрала третье решение для критических полей контакта и первое решение для некритической метаданных, где защита от перезаписи имела большее значение, чем производительность. Результат оказался драматическим: продолжительность задания синхронизации сократилась с 45 минут до 12 минут, запаздывание репликации стабилизировалось ниже пяти секунд, и инциденты с "исчезающим номером телефона" полностью прекратились в течение первой недели внедрения.

Что кандидаты часто упускают

Почему WHERE EXCLUDED.column != table.column пропускает строки, когда оба значения равны NULL, и как это взаимодействует с механизмом обновления PostgreSQL?

Многие кандидаты предполагают, что если два NULL не равны, то сравнение должно вернуть TRUE и разрешить обновление. Однако SQL использует трехзначную логику: NULL представляет собой неизвестное значение. Любое сравнение с NULL (включая NULL = NULL или NULL != NULL) дает NULL (неизвестно), а не логическое TRUE или FALSE. В WHERE предложении PostgreSQL только строки, оценивающиеся как TRUE, продолжают выполнение; NULL рассматривается как FALSE. Таким образом, при сравнении двух NULL номеров телефонов результат будет NULL, обновление будет пропущено, и система ошибочно предполагает, что изменения не требуется. IS DISTINCT FROM возвращает FALSE для NULL против NULL, правильно указывая, что они идентичны, и пропуская обновление только тогда, когда это уместно, в то время как возвращает TRUE, когда одно значение NULL, а другое — нет.

Как порядок столбцов в уникальном ограничении на несколько столбцов влияет на производительность разрешения ON CONFLICT, и что происходит, если целевой конфликт не совпадает с определением индекса точно?

Кандидаты часто упускают из виду, что PostgreSQL требует, чтобы целевой конфликт (столбцы, указанные в ON CONFLICT (...)) точно соответствовали определению уникального индекса, включая порядок столбцов и любые функциональные выражения. Если уникальный индекс существует на (clinic_id, external_id), но запрос указывает ON CONFLICT (external_id, clinic_id), планировщик может не суметь вывести индекс, вызвав ошибку "согласно спецификации ON CONFLICT нет уникального или исключающего ограничения". Даже если это сработает, несоответствие порядка столбцов может предотвратить оптимизатор от использования только индекса для поиска конфликтующей кортежа, принуждая к получению кучи и значительно увеличивая затраты ввода-вывода.

Какова разница между использованием COALESCE(EXCLUDED.column, table.column) в команде SET и использованием WHERE EXCLUDED.column IS DISTINCT FROM table.column, особенно в отношении выполнения триггеров и версионирования строк?

Использование COALESCE в команде SET безусловно записывает значение в строку (либо новые данные, либо сохраненные старые данные). Эта операция генерирует новую версию строки (CTID), записывает в WAL и вызывает все BEFORE и AFTER триггеры, связанные с таблицей, даже если конечное значение остается идентичным предыдущему состоянию. Это создает "шум" в таблицах аудита и увеличивает нагрузку репликации. Напротив, условие WHERE с IS DISTINCT FROM предотвращает модификацию строки полностью, если фактическое изменение не произошло. Новая версия кортежа не создается, триггеры не срабатывают, и генерация WAL избегается. Это различие критически важно для высокопроизводительных систем с аудиторской записью или каскадными внешними ключами, где обновления "no-op" создают значительные накладные расходы.