PostgreSQL 15 Beta 1: тестируем MERGE на реальных ETL-задачах

В мае PostgreSQL Project выпустил Beta 1 пятнадцатой версии. Главная новость, которая обсуждается в сообществе, - команда MERGE. Она ждала своего часа в PostgreSQL неприлично долго: SQL-стандарт описал её ещё в 2003 году, Oracle и SQL Server поддерживали её давно, а Postgres упорно отвечал «используйте INSERT ... ON CONFLICT». Теперь - нет. Мы подняли бета-инстанс и прогнали на нём несколько реальных кейсов из клиентского DWH.

Что такое MERGE и чем он отличается от ON CONFLICT

INSERT ... ON CONFLICT DO UPDATE - это атомарный upsert: либо вставляем строку, либо обновляем при конфликте по индексу. Инструмент рабочий, но у него есть ограничение - логика разветвляется только по факту конфликта, и сам конфликт обязательно должен быть на уникальном индексе.

MERGE работает иначе: он принимает источник данных, соединяет его с целевой таблицей по произвольному условию и дальше позволяет описать ветви WHEN MATCHED, WHEN NOT MATCHED и в PG15 - WHEN NOT MATCHED BY SOURCE. В каждой ветви - своё действие: UPDATE, INSERT, DELETE или DO NOTHING. Это значит, что в одном запросе можно описать логику вида «если строка есть - обнови, если нет - вставь, если в цели есть строка которой нет в источнике - удали».

Для ETL это не академическая разница. В нашем случае у клиента есть staging-таблица куда пачками прилетают данные из источника, и таблица фактов которую нужно привести в соответствие. Раньше этот процесс выглядел как три отдельных запроса: UPDATE по совпадению, INSERT по несовпадению, DELETE по отсутствию. Или хранимая процедура с CTE, которую все понимали по-разному после написания.

Как выглядит MERGE на практике

Сокращённый пример из нашего кейса - синхронизация таблицы dim_products из staging:

MERGE INTO dim_products AS target
USING stg_products AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.updated_at > target.updated_at THEN
    UPDATE SET
        name = source.name,
        price = source.price,
        updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, updated_at)
    VALUES (source.product_id, source.name, source.price, source.updated_at)
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET is_active = false;

Три ветви - три действия, один запрос. На PG14 этот же результат требовал либо трёх отдельных DML-операторов в транзакции, либо хитрого CTE с INSERT ... ON CONFLICT плюс отдельный DELETE. Читабельность при этом была, мягко говоря, условной.

WHEN NOT MATCHED BY SOURCE - это дополнение сверх SQL-стандарта, PostgreSQL-специфичная ветвь. Позволяет обрабатывать строки, которые есть в цели, но отсутствуют в источнике - то самое «мягкое удаление» которое нам нужно.

Тестируем на клиентском ETL

Взяли реальную аналитическую инфраструктуру клиента: DWH на PG14, несколько измерений размером от нескольких тысяч до пары сотен тысяч строк, ETL на Python с psycopg2. Подняли идентичный инстанс на PostgreSQL 15 Beta 1 (собрали из исходников на тестовой машине), восстановили туда снэпшот базы и переписали ETL-скрипты под MERGE.

Что заметили:

• Код уменьшился ощутимо. Один MERGE заменяет конструкцию из трёх операторов плюс явного управления транзакцией. Не нужно отдельно думать про порядок - сначала UPDATE чтобы не создавать конфликт, потом INSERT. MERGE атомарен и делает это сам.

• По скорости - немного быстрее. На измерениях среднего размера прогон ETL-шага оказался быстрее, чем трёхоператорный аналог на PG14. Разница некритична, но приятна. Скорее всего дело в том, что MERGE проходит по join один раз, тогда как три отдельных оператора делали три прохода с разными условиями.

• Планировщик ведёт себя адекватно. EXPLAIN ANALYZE на MERGE показывает нормальный план с hash join на staging-таблице. Никаких странностей.

Одно место где пришлось подумать: условие WHEN MATCHED AND source.updated_at > target.updated_at. В трёхоператорном варианте мы просто делали UPDATE всех совпавших строк - и пусть они обновляются пустым набором изменений. С MERGE захотелось добавить условие «обновляй только если источник новее», и оно встроилось органично прямо в ветвь. Так даже честнее.

Что ещё есть в Beta 1

MERGE - главное, но не единственное. Бегло прошлись по остальному:

• Улучшения сортировки. PG15 использует новый алгоритм для ORDER BY со многими колонками - incremental sort расширили, и для ряда запросов с сортировкой по составному ключу прирост заметен. На наших аналитических запросах прощупали, но эффект зависит от конкретного запроса - не везде.

• Публикация по колонкам в logical replication. Теперь можно публиковать только часть колонок таблицы: CREATE PUBLICATION ... FOR TABLE t (col1, col2). Для нас это интересно в контексте репликации между схемами - можно не тащить тяжёлые blob-колонки через replication slot.

• pg_walinspect. Новое расширение для инспекции WAL прямо из SQL. Удобно при отладке репликации - раньше нужен был pg_waldump из командной строки.

Бета есть бета

Производить в продакшн мы пока ничего не собираемся - бета есть бета, GA ожидается осенью. Но сам факт того, что MERGE можно пощупать сейчас, полезен: к моменту выхода стабильной версии у нас уже будет понимание как переписывать ETL-логику и где это имеет смысл.

Наш вывод по результатам тестирования: MERGE - не серебряная пуля, INSERT ON CONFLICT он не заменяет везде. Для простого upsert по одному ключу ON CONFLICT проще и привычнее. MERGE оправдывает себя там где нужна нетривиальная логика с несколькими ветвями - обновить одно, вставить другое, деактивировать третье за один проход. В ETL-контексте такие задачи есть регулярно.

Следим за беррасом и ждём RC.