INSERT, UPDATE, DELETE: изменение данных

INSERT, UPDATE, DELETE: изменение данных

Главная идея

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

1. Простой INSERT с RETURNING: INSERT INTO users (email, full_name) VALUES ('alice@example.com', 'Alice Smith') RETURNING id, created_at. PostgreSQL вставляет строку и сразу возвращает сгенерированный id и время создания. Не нужен отдельный SELECT lastval() или CURRVAL().
2. Массовая вставка нескольких строк за один запрос: INSERT INTO tags (name, slug) VALUES ('SQL', 'sql'), ('PostgreSQL', 'postgresql'), ('Backend', 'backend') RETURNING id, name. Один INSERT гораздо быстрее трёх отдельных — одна round-trip, одна транзакция, меньший WAL overhead.
3. INSERT из SELECT — вставка результата другого запроса: INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2023-01-01'. Полезно для архивирования, копирования данных, создания снимков. RETURNING работает и здесь.
4. UPDATE с WHERE — всегда с WHERE: UPDATE users SET full_name = 'Alice Johnson', updated_at = now() WHERE id = 42 RETURNING id, full_name. WHERE id = 42 — обязательное условие. В psql для безопасности: BEGIN; UPDATE users SET ...; SELECT * FROM users WHERE id = 42; — проверить результат, потом COMMIT или ROLLBACK.
5. UPDATE нескольких столбцов: UPDATE products SET price = price * 1.1, updated_at = now() WHERE category = 'electronics' AND price < 10000 RETURNING id, name, price. Увеличение цены на 10% для электроники дешевле 10000. RETURNING показывает новые цены.
6. UPDATE FROM — обновление с данными из другой таблицы: UPDATE order_items oi SET price_at_purchase = p.price FROM products p WHERE oi.product_id = p.id AND oi.order_id = 42. Обновляем цену в позициях заказа из актуальных цен продуктов.
7. Soft delete — мягкое удаление через пометку: UPDATE posts SET deleted_at = now(), deleted_by = 7 WHERE id = 123 AND deleted_at IS NULL RETURNING id. Строка остаётся в БД, но фильтруется WHERE deleted_at IS NULL. Преимущества: восстановление возможно, аудит-история сохранена, нет каскадных удалений.
8. DELETE с RETURNING: DELETE FROM sessions WHERE expires_at < now() RETURNING id, user_id. Удаляем устаревшие сессии и получаем список удалённых — например для логирования или уведомлений. DELETE FROM ... USING — аналог UPDATE FROM для удаления с JOIN.
9. ON CONFLICT (UPSERT) — вставка или обновление: INSERT INTO user_preferences (user_id, key, value) VALUES (42, 'theme', 'dark') ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value, updated_at = now() RETURNING *. EXCLUDED — специальная псевдотаблица с данными которые пытались вставить. Атомарная операция без race conditions.
10. ON CONFLICT DO NOTHING — идемпотентная вставка: INSERT INTO event_log (event_id, user_id, type) VALUES ('evt-123', 42, 'login') ON CONFLICT (event_id) DO NOTHING. Повторная вставка одного события ничего не сделает. Полезно для обработки дублей событий в event-driven системах.
11. TRUNCATE для быстрой очистки таблицы: TRUNCATE TABLE sessions RESTART IDENTITY CASCADE. TRUNCATE удаляет все строки мгновенно (не построчно), сбрасывает sequences (RESTART IDENTITY), и каскадно очищает дочерние таблицы (CASCADE). НЕ активирует row-level триггеры. Поддерживает ROLLBACK в PostgreSQL (в отличие от некоторых других СУБД).
12. COPY для массовой загрузки данных: COPY users (email, full_name, created_at) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true, NULL 'null'). COPY — самый быстрый способ загрузки данных в PostgreSQL: в 10-100 раз быстрее INSERT за счёт минимального WAL и отключённых проверок во время загрузки. Из приложения: COPY ... FROM STDIN.

Примеры кода

Безопасный UPDATE/DELETE — транзакция + проверка

-- Золотое правило: перед UPDATE/DELETE в psql
-- сначала смотришь SELECT с тем же WHERE

-- 1. Проверяем что найдём нужные строки
SELECT id, email, role FROM users
WHERE email LIKE '%@old-company.com';
-- Видим: 47 строк — ожидаем именно столько

-- 2. Начинаем транзакцию, делаем UPDATE
BEGIN;

UPDATE users
SET is_active = false, updated_at = now()
WHERE email LIKE '%@old-company.com'
RETURNING id, email, is_active;
-- Видим 47 строк с is_active = false — всё верно

-- 3. Если всё правильно — фиксируем
COMMIT;
-- Если ошибка — откатываем
-- ROLLBACK;

-- Для автоматизации: idle_in_transaction_session_timeout
-- предотвращает зависшие транзакции в psql
SET idle_in_transaction_session_timeout = '5min';

ON CONFLICT (UPSERT) — реальные паттерны

-- Паттерн 1: Обновить если уже есть, вставить если нет
INSERT INTO user_settings (user_id, setting_key, setting_value, updated_at)
VALUES (42, 'notifications_email', 'true', now())
ON CONFLICT (user_id, setting_key)
DO UPDATE SET
  setting_value = EXCLUDED.setting_value,
  updated_at    = now()
RETURNING *;

-- Паттерн 2: Счётчик просмотров — инкремент при конфликте
INSERT INTO page_views (page_id, date, view_count)
VALUES (101, CURRENT_DATE, 1)
ON CONFLICT (page_id, date)
DO UPDATE SET
  view_count = page_views.view_count + 1;
-- page_views.view_count — текущее значение в таблице
-- EXCLUDED.view_count — значение из INSERT (1)

-- Паттерн 3: Идемпотентная вставка событий
INSERT INTO processed_events (event_id, processed_at, result)
VALUES ('evt-abc123', now(), 'success')
ON CONFLICT (event_id) DO NOTHING
RETURNING id;
-- Если NULL вернулось — событие уже было обработано

-- Паттерн 4: Обновить только если значение изменилось
INSERT INTO product_prices (product_id, price, updated_at)
VALUES (55, 1499.99, now())
ON CONFLICT (product_id)
DO UPDATE SET
  price      = EXCLUDED.price,
  updated_at = now()
WHERE product_prices.price <> EXCLUDED.price;
-- WHERE в DO UPDATE — условие на само обновление

Массовая вставка и COPY — производительность

-- МЕДЛЕННО: 1000 отдельных INSERT (1000 round-trips)
-- for i in range(1000): execute("INSERT INTO logs VALUES (%s)", i)

-- БЫСТРО: один INSERT с множеством VALUES
INSERT INTO logs (user_id, action, created_at) VALUES
  (1, 'login',  '2024-03-01 10:00:00'),
  (2, 'view',   '2024-03-01 10:01:00'),
  (3, 'logout', '2024-03-01 10:02:00')
  -- ... тысячи строк
RETURNING id;

-- ЕЩЁ БЫСТРЕЕ: INSERT из подзапроса
INSERT INTO monthly_summary (user_id, month, order_count, total_spent)
SELECT
  user_id,
  DATE_TRUNC('month', created_at)::DATE AS month,
  COUNT(*)                              AS order_count,
  SUM(total_amount)                     AS total_spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id, DATE_TRUNC('month', created_at)
ON CONFLICT (user_id, month)
DO UPDATE SET
  order_count  = EXCLUDED.order_count,
  total_spent  = EXCLUDED.total_spent;

-- МАКСИМАЛЬНО БЫСТРО для загрузки данных: COPY
-- В psql:
-- \COPY users(email, full_name, created_at) FROM 'users.csv' CSV HEADER;

-- В Python (psycopg2):
-- with conn.cursor() as cur:
--     cur.copy_expert("COPY users FROM STDIN WITH CSV HEADER", file)

Что происходит под капотом

• INSERT в PostgreSQL: создаёт новый heap tuple в странице данных. xmin = ID текущей транзакции (строка видна только после COMMIT). При вставке также обновляются все индексы таблицы. Если INSERT нарушает ограничение (NOT NULL, UNIQUE, FK, CHECK) — операция отклоняется, строка не создаётся, транзакция может быть прервана в зависимости от настроек.
• UPDATE: физически создаёт НОВУЮ версию строки в heap (новый tuple с новым xmin = текущей транзакции) и помечает СТАРУЮ версию как удалённую (xmax = ID текущей транзакции). Старая версия остаётся в файле данных — это MVCC. Читающие транзакции, начавшиеся до UPDATE, продолжают видеть старую версию. VACUUM потом очистит старые версии.
• HOT (Heap Only Tuple) update: если обновляемый столбец НЕ индексирован и новая версия строки помещается в ту же страницу — PostgreSQL делает HOT update: создаёт новый tuple в той же странице с указателем от старого tuple к новому. Индексы при этом не обновляются. HOT значительно быстрее и производит меньше WAL. Это происходит автоматически — разработчику ничего делать не нужно.
• DELETE: помечает tuple как удалённый (xmax = ID текущей транзакции). Физически строка остаётся в файле до VACUUM. Читающие транзакции с более ранним snapshot продолжают видеть строку. После COMMIT + VACUUM: tuple помечается как доступное для переиспользования (free space). VACUUM FULL переписывает таблицу — освобождает место на диске, но блокирует таблицу.
• RETURNING — PostgreSQL-расширение. Данные возвращаются из уже изменённого состояния строки (для INSERT и UPDATE) или из удалённой строки (для DELETE). RETURNING не выполняет дополнительный SELECT — данные берутся из того же heap scan. Это гарантирует атомарность: нет race condition между INSERT и SELECT id.
• ON CONFLICT (upsert) реализован через speculative insert: PostgreSQL пытается INSERT, если обнаруживает конфликт уникальности — выполняет UPDATE или ничего не делает (DO NOTHING). Это атомарная операция, безопасная при конкурентных вставках. Нельзя безопасно эмулировать через SELECT + INSERT/UPDATE — между двумя операциями другая транзакция может вставить запись.
• Массовый INSERT vs COPY: INSERT INTO t VALUES (...),(...),... парсирует SQL, проверяет ограничения построчно, пишет WAL для каждой строки. COPY использует бинарный протокол, минимизирует парсинг, буферизует WAL. Для загрузки миллионов строк COPY в 5-20 раз быстрее INSERT. COPY FREEZE делает строки сразу замороженными (избегает wraparound scan) — для начальной загрузки данных.
• TRUNCATE vs DELETE: DELETE помечает строки одну за другой через MVCC, обновляет индексы построчно, активирует row-level триггеры, поддерживает WHERE. Это O(N). TRUNCATE удаляет все страницы данных таблицы мгновенно (создаёт новый пустой файл), сбрасывает sequence (RESTART IDENTITY), не активирует row-level триггеры. O(1). В PostgreSQL TRUNCATE поддерживает ROLLBACK (в отличие от Oracle).
• UPDATE FROM — обновление с данными из другой таблицы: UPDATE t1 SET col = t2.val FROM t2 WHERE t1.id = t2.id. PostgreSQL выполняет это как JOIN: строит Hash Join или Nested Loop между t1 и t2, обновляет matching строки в t1. Эффективнее цикла UPDATE по одной строке. Эквивалент MySQL UPDATE t1 JOIN t2.
• Функция триггера BEFORE INSERT/UPDATE позволяет изменить данные перед записью: SET updated_at автоматически через триггер. AFTER INSERT/UPDATE/DELETE — для side effects: обновление кеша, отправка уведомлений, аудит-лог. INSTEAD OF — для модифицируемых представлений (views).
• Параллельный INSERT и lock: несколько процессов могут параллельно делать INSERT в одну таблицу — PostgreSQL удерживает ROW EXCLUSIVE LOCK, который не конфликтует с другими ROW EXCLUSIVE LOCK. Конкуренция появляется только при INSERT в одну страницу heap (buffer pin). ON CONFLICT с конкурентными INSERT безопасен благодаря speculative insert protocol.

Термины урока

Мини-разбор реальной ситуации

Команда разрабатывает систему учёта рабочего времени. Паттерн: каждый раз когда сотрудник приходит или уходит — нужно записать событие. Первая реализация: SELECT → если запись есть, UPDATE; иначе INSERT. Под нагрузкой появляются дубликаты: два параллельных процесса оба делают SELECT (не находят), оба делают INSERT — UNIQUE violation или дублирование данных. Фикс добавляет try/except и retry — код становится сложным. Правильное решение: INSERT INTO attendance (employee_id, date, first_entry, last_exit) VALUES (42, '2024-03-15', '09:00', '09:00') ON CONFLICT (employee_id, date) DO UPDATE SET last_exit = EXCLUDED.last_exit WHERE EXCLUDED.last_exit > attendance.last_exit RETURNING *. Один атомарный запрос без race condition, без retry-логики. Производительность: 3мс против 15мс с retry-логикой. Строк кода: 3 против 20.