DML: INSERT, UPDATE, DELETE, UPSERT

6 секунд, которые уничтожили 300 ГБ

31 января 2017 года. 23:00 UTC. Инженер GitLab SRE Дьёрдь Дачо пытается остановить спам-атаку на production базу. Командная строка вместо тестового сервера. Одна команда: DELETE FROM. Без WHERE. Нажатие Enter. Шесть секунд. 300 гигабайт данных - история репозиториев, issues, merge requests сотен тысяч пользователей - стёрта. Резервная копия? Последняя валидная была за 6 часов до этого. Инцидент задокументирован публично в gitlab.com/gitlab-com/gl-infra/runbooks. GitLab потерял примерно 5000 проектов безвозвратно. DML - самый опасный инструмент в руках разработчика. И самый сильный.

INSERT добавляет строку. UPDATE меняет. DELETE удаляет. Это синтаксис. За ним - архитектурные паттерны: upsert решает race condition при параллельных запросах, RETURNING убирает лишний SELECT после вставки, CTE превращает серию мутаций в атомарную операцию. И один забытый WHERE в DELETE может стоить шести секунд и трёхсот гигабайт.

• **Upsert в счётчиках**: UPDATE page_views SET count = count + 1 ON CONFLICT DO UPDATE - атомарный инкремент без race condition
• **RETURNING в API**: INSERT ... RETURNING id, created_at - получить сгенерированный PK без второго SELECT
• **Batch INSERT**: вставка 10000 строк одним INSERT VALUES (...), (...) вместо 10000 отдельных запросов - 50-100x быстрее
• **Soft delete через UPDATE**: UPDATE records SET deleted_at = NOW() WHERE id = $1 - данные не теряются, можно восстановить
• **CTE с DML**: WITH inserted AS (INSERT ... RETURNING) UPDATE ... - многошаговые мутации как одна атомарная операция

INSERT и RETURNING

2017 год. Разработчик добавил пользователя и сразу хочет вернуть его ID в API response. Классический путь: INSERT, затем SELECT WHERE email = $1. Два запроса. Два round-trip к базе. Возможная гонка если другой процесс вставил того же пользователя за это время. PostgreSQL закрыл вопрос в 2001 году одним ключевым словом: RETURNING.

RETURNING работает не только с INSERT. UPDATE и DELETE тоже возвращают строки - до или после изменения. Это меняет паттерн 'найди-потом-удали' на один атомарный запрос: DELETE FROM jobs WHERE id = (SELECT id FROM jobs ORDER BY created_at LIMIT 1) RETURNING *. Так работают очереди задач на базе PostgreSQL - Que, Solid Queue, pgqueuer.

**Производительность batch INSERT:** вставка 10000 строк одним INSERT VALUES (...), (...), ... в 50-100 раз быстрее чем 10000 отдельных INSERT. Причина: один round-trip к базе, один parse/plan, одна транзакция. Node.js/Python обёртки типа `unnest()` + prepared statement дают ещё лучший результат для очень больших объёмов.

UPDATE и UPDATE FROM

UPDATE - на вид простая команда. На деле - скрытые ловушки. PostgreSQL не обновляет строку на месте: MVCC создаёт новую версию строки, старая помечается как 'мёртвая' и уберётся VACUUM. Каждый UPDATE - это фактически INSERT + 'пометить старую строку'. На write-heavy таблицах это означает рост bloat и необходимость настройки autovacuum. Но это тема урока о MVCC. Здесь - синтаксис и паттерны.

UPDATE FROM - PostgreSQL-специфичное расширение стандарта. Позволяет JOIN в UPDATE. Стандартный SQL требует коррелированного подзапроса - PostgreSQL позволяет FROM clause с явным JOIN. Разница в читаемости и в плане выполнения: планировщик строит hash join вместо nested loop для подзапроса.

**UPDATE без WHERE - катастрофа.** UPDATE orders SET status = 'cancelled' без WHERE обновит все строки в таблице. В production: всегда открывать транзакцию (BEGIN), выполнять SELECT с тем же WHERE перед UPDATE чтобы убедиться в правильности выборки, затем UPDATE, проверять rowCount, и только тогда COMMIT.

DELETE и безопасные паттерны удаления

GitLab потерял 300 ГБ за 6 секунд именно потому, что DELETE FROM без WHERE выполняется мгновенно - PostgreSQL не итерирует строки, он помечает все как удалённые в один проход. Транзакционность спасла бы ситуацию - но инженер не открыл BEGIN. Postgresql не имеет 'корзины'. Без PITR-бэкапа данные пропадают навсегда.

**Soft delete vs Hard delete:** DELETE физически удаляет строку (MVCC создаёт 'мёртвую' версию, VACUUM потом её уберёт). Soft delete - UPDATE SET deleted_at = NOW() - данные остаются, можно восстановить, но таблица растёт. Компромисс: soft delete + периодическая архивация старых 'удалённых' строк в archive-таблицу.

Upsert: INSERT ON CONFLICT

Классическая проблема высоконагруженных систем: нужно вставить строку если её нет, обновить если есть. Наивный подход - SELECT, проверить, INSERT или UPDATE - разваливается при concurrency: между SELECT и INSERT два процесса могут оба увидеть 'строки нет' и оба попытаться INSERT. Один упадёт с ошибкой unique violation. PostgreSQL решил это в 2015 году с PostgreSQL 9.5: INSERT ... ON CONFLICT.

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

**ON CONFLICT требует unique constraint или index:** PostgreSQL должен знать по каким полям определять конфликт. ON CONFLICT (column) работает только если есть UNIQUE constraint или UNIQUE index на этих колонках. ON CONFLICT ON CONSTRAINT требует именованный constraint.

CTE с DML: атомарные цепочки мутаций

CTE (WITH) в PostgreSQL - не только для SELECT. Начиная с PostgreSQL 9.1 WITH-блок может содержать INSERT, UPDATE, DELETE с RETURNING. Результат одной мутации становится входом для следующей. Всё в одной транзакции, один round-trip к базе. Паттерн незаменим когда нужно: создать запись в одной таблице и сразу использовать её ID в другой, переместить строку между таблицами атомарно, реализовать 'деку' (очередь с двух сторон) без хранимых процедур.

Атомарное перемещение строки между таблицами - классический use case. DELETE из source с RETURNING, INSERT в destination с данными из RETURNING. Если любая часть упадёт - вся операция откатится. Никаких промежуточных состояний, никаких дублей, никаких потерь. Так работает архивация горячих данных в холодные таблицы без риска потери строк.

**DML CTE и изоляция:** все DML-операции в одном WITH видят snapshot данных на начало запроса, а не изменения других CTE. То есть UPDATE в одном CTE не увидит строки, вставленные INSERT в другом CTE того же запроса - они разделяют один snapshot. Это важно при проектировании сложных CTE-цепочек.

Ключевые идеи

• **INSERT VALUES / SELECT**: один или много; INSERT ... SELECT для копирования данных между таблицами
• **RETURNING**: возвращает строки после INSERT/UPDATE/DELETE - убирает лишний SELECT
• **ON CONFLICT DO NOTHING / UPDATE**: upsert - атомарный 'вставь или обнови' без race condition
• **UPDATE FROM / DELETE USING**: JOIN в DML операциях - обновление/удаление по данным из другой таблицы
• **CTE с DML**: WITH ... AS (INSERT/UPDATE/DELETE) - цепочка мутаций в одном атомарном запросе
• **Правило GitLab**: всегда проверять WHERE перед DELETE/UPDATE; в транзакции - всегда

Связанные темы

DML - центр работы с данными в PostgreSQL, связан с транзакциями, оптимизацией и миграциями:

• Транзакции и ACID — BEGIN / COMMIT / ROLLBACK оборачивают DML - без них DELETE без WHERE необратим
• SELECT и чтение данных — INSERT ... SELECT и RETURNING связывают запись и чтение
• CTE (Common Table Expressions) — WITH ... AS (INSERT/UPDATE/DELETE RETURNING) - многошаговые мутации
• Блокировки — UPDATE без индекса по WHERE берёт Sequential Scan и RowExclusiveLock на многие строки
• Миграции схемы — Flyway и Liquibase содержат DML для backfill данных при миграциях

Вопросы для размышления

• GitLab удалил данные за 6 секунд. Какие три технических меры могли бы предотвратить катастрофу на уровне SQL-интерфейса?
• INSERT ... ON CONFLICT DO UPDATE и SELECT + INSERT - оба добавляют строку если её нет. Почему первый лучше при высоком concurrency?
• CTE с RETURNING позволяет сделать INSERT и сразу использовать его результат в следующем UPDATE. Когда это лучше двух отдельных запросов?

Связанные уроки

• pg-04-ddl — DDL создаёт таблицы - DML работает с их содержимым
• pg-06-select — SELECT читает то, что DML записывает
• pg-10-cte — CTE c DML - атомарные многошаговые мутации
• pg-23-transactions — Безопасный DML всегда в транзакции с rollback
• pg-26-locks — UPDATE без WHERE берёт RowExclusiveLock на весь диапазон
• pg-49-migrations — Миграции данных - это прикладной DML в управляемом виде
• db-05-sql-basics