Операторы манипулирования данными (DML)

1. Введение: Язык манипулирования данными (DML) и транзакционная модель

Язык манипулирования данными (Data Manipulation Language, DML) представляет собой подмножество языка SQL, предназначенное для добавления, изменения и удаления кортежей (строк) в реляционных таблицах. В отличие от команд DDL (Data Definition Language), определяющих структуру объектов, операторы DML управляют фактическим информационным наполнением базы данных.

В современных промышленных СУБД, таких как PostgreSQL, выполнение любого DML-оператора неразрывно связано с тремя системными компонентами:

• Механизм многоверсионного управления конкурентным доступом (MVCC): операции изменения данных не перезаписывают память «по месту», а создают новые версии кортежей с отметками транзакций xmin и xmax, обеспечивая изоляцию читателей от писателей.
• Журнал упреждающей записи (Write-Ahead Logging, WAL): любые физические изменения страниц данных сначала сериализуются и записываются в энергонезависимый журнал WAL на диске, и лишь затем могут быть сброшены из буферного пула (shared buffers) в файлы таблиц. Это гарантирует свойство Durability (долговечность) транзакций ACID.
• Индексные деревья (B-Tree): операторы манипулирования обязаны синхронно или асинхронно поддерживать целостность всех построенных по таблице индексов, что оказывает непосредственное влияние на задержку (latency) операций модификации.

2. Оператор INSERT INTO: Разновидности и оптимизация вставки

Оператор INSERT INTO осуществляет добавление новых кортежей. В производственной практике применяются четыре ключевых паттерна использования оператора:

2.1. Одиночная и явная вставка (Single-row INSERT)

Хорошим инженерным тоном является всегда явное перечисление целевых колонок в круглых скобках после имени таблицы. Опускание списка столбцов (конструкция INSERT INTO t VALUES (...)) является антипаттерном: добавление в таблицу нового столбца с DEFAULT приведет к аварийной остановке клиентского приложения из-за несоответствия арности кортежа.

-- Надежная одиночная вставка с явным списком полей
INSERT INTO warehouse_inventory (
  sku, 
  product_name, 
  quantity, 
  unit_price
) VALUES (
  'SKU-99001', 
  'Жесткий диск 4TB Seagate IronWolf NAS', 
  12, 
  11400.00
);

2.2. Пакетная вставка (Batch / Multi-row INSERT)

При необходимости вставки нескольких записей выполнение серии одиночных запросов INSERT создает неприемлемые накладные расходы: N сетевых раунд-трипов, N транзакционных подтверждений (fsync WAL) и N циклов парсинга запроса. Пакетная вставка объединяет наборы значений через запятую:

-- Пакетная вставка 5 позиций в одной транзакции
INSERT INTO warehouse_inventory (sku, product_name, quantity, unit_price)
VALUES 
  ('SKU-101', 'Процессор Intel Core i5-13400', 15, 18200.00),
  ('SKU-102', 'Материнская плата B760 DDR5', 20, 12500.00),
  ('SKU-103', 'Модуль памяти DDR5 32GB Kit', 35, 9800.00),
  ('SKU-104', 'Блок питания 750W Gold', 18, 8100.00),
  ('SKU-105', 'Корпус ATX Mesh Black', 12, 5400.00)
RETURNING id, sku, created_at;

2.3. Вставка результатов выборки (INSERT INTO ... SELECT)

Данная конструкция выполняет генерацию новых строк непосредственно в ядре СУБД, черпая данные из других таблиц, представлений или подзапросов без их передачи на уровень приложения.

-- Перенос закрытых договоров в архивную таблицу
INSERT INTO contracts_archive (contract_id, client_id, close_date, final_balance)
SELECT c.id, c.client_id, c.closed_at, c.balance
FROM active_contracts c
WHERE c.status = 'CLOSED' 
  AND c.closed_at < CURRENT_DATE - INTERVAL '1 year';

2.4. Секция RETURNING

Клауза RETURNING возвращает клиенту значения модифицированных строк. Это исключает необходимость выполнения последующего «угадывающего» запроса SELECT ... WHERE .... Типовое применение — возврат сгенерированного суррогатного ключа id (IDENTITY / SERIAL), временной метки по умолчанию или результатов работы серверных триггеров.

3. Атомарное разрешение конфликтов уникальности: Концепция UPSERT

Паттерн UPSERT (UPDATE + INSERT) решает классическую проблему конкурентной записи: «вставить строку, но если запись с таким уникальным ключом уже существует — не падать с ошибкой unique_violation, а обновить существующую запись либо проигнорировать операцию».

3.1. Архитектурная схема выполнения UPSERT (ON CONFLICT)

3.2. Синтаксис и семантика псевдотаблицы EXCLUDED

При возникновении конфликта управление передается блоку DO UPDATE SET. В этот момент СУБД создает специальную виртуальную псевдотаблицу EXCLUDED, содержащую значения полей, которые предлагались к вставке. Имя целевой таблицы в выражении представляет текущие значения в существующей строке на диске.

-- Идемпотентный UPSERT: суммирование остатка при повторной накладной
INSERT INTO warehouse_inventory (sku, product_name, quantity, unit_price)
VALUES ('SKU-101', 'Процессор Intel Core i5-13400', 10, 17900.00)
ON CONFLICT (sku) 
DO UPDATE SET 
  -- Суммируем старый остаток на складе с новым приходом из накладной
  quantity = warehouse_inventory.quantity + EXCLUDED.quantity,
  -- Актуализируем закупочную цену последней поставкой
  unit_price = EXCLUDED.unit_price,
  updated_at = CURRENT_TIMESTAMP
RETURNING warehouse_inventory.id, warehouse_inventory.sku, warehouse_inventory.quantity;

4. Операторы UPDATE и DELETE: Реляционная модификация

4.1. Оператор UPDATE с секцией FROM (Join-обновление)

В PostgreSQL конструкция UPDATE ... FROM позволяет связывать обновляемую таблицу со справочниками и платежными документами напрямую, без громоздких коррелированных подзапросов.

-- Обновление статуса врачебного приема по факту подтвержденной оплаты
UPDATE doctor_appointments AS a
SET 
  status = 'CONFIRMED',
  updated_at = CURRENT_TIMESTAMP
FROM payment_invoices AS p
WHERE a.id = p.appointment_id
  AND p.payment_status = 'PAID'
  AND a.status = 'PENDING_PAYMENT'
RETURNING a.id, a.patient_id, a.doctor_id, a.status;

4.2. Селективное удаление DELETE

Команда DELETE удаляет записи, удовлетворяющие предикату. В сочетании с RETURNING она позволяет реализовать паттерн архивирования или ведения аудита.

-- Удаление отмененных приемов старше 1 года с возвратом для лога аудита
DELETE FROM doctor_appointments
WHERE status = 'CANCELLED' 
  AND appointment_date < CURRENT_DATE - INTERVAL '1 year'
RETURNING id, patient_fio, appointment_date, cancellation_reason;

5. Внутренняя архитектура: Влияние DML на индексы, MVCC и журнал WAL

Для грамотного проектирования баз данных инженер обязан понимать физическую цену каждого оператора модификации.

5.1. Журнал предзаписи WAL (Write-Ahead Logging)

СУБД гарантирует сохранение данных при сбоях (Crash Recovery) благодаря принципу WAL: никакая страница данных не может быть сброшена на диск до тех пор, пока соответствующая запись об изменении не попала в журнал WAL. При выполнении COMMIT транзакция ожидает синхронного сброса буфера WAL (системный вызов fsync).

5.2. Сравнение фундаментальных механизмов: DELETE vs TRUNCATE

6. Практики безопасных пакетных модификаций в HighLoad

Выполнение массового UPDATE или DELETE по миллионам строк в одной монолитной транзакции гарантированно приводит к инциденту: исчерпанию пула блокировок, зависанию фонового autovacuum и блокировке параллельных OLTP-запросов. Безопасная модификация требует порционной обработки (Batching):

-- Паттерн безопасного порционного удаления через CTE с блокировкой порции строк
WITH rows_to_delete AS (
  SELECT id 
  FROM audit_logs
  WHERE created_at < CURRENT_DATE - INTERVAL '180 days'
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
DELETE FROM audit_logs
WHERE id IN (SELECT id FROM rows_to_delete);

Использование SKIP LOCKED предотвращает взаимные блокировки (Deadlocks), позволяя параллельным воркерам обрабатывать независимые порции устаревших записей.

7. Вопросы для самопроверки