Как выполнять сложные data-миграции, не ломая UNIQUE-ограничения?

Иногда нужно массово менять данные, временно нарушая UNIQUE, но итоговое состояние корректно:

BEGIN;

Включаем отложенную проверку конкретного ограничения, оно проверится только при COMMIT:

SET CONSTRAINTS users_email_uniq DEFERRED;

Теперь можно выполнять UPDATE/INSERT, даже если в процессе появляются дубликаты:

UPDATE users
SET email = lower(email);

Фиксация происходит один раз, если финальное состояние валидно, транзакция коммитится:

COMMIT;

Если уникальность нарушена в конце будет ROLLBACK, без частичных изменений.

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

➡️ SQL Ready | #совет

💡 ThoughtSpot SQL Tutorial — продуктивное обучение и практика!

Материал построен вокруг реальных примеров и практики: от базовых SELECT, JOIN и агрегатов до сложных подзапросов и аналитических функций. Пояснения идут шаг за шагом, с визуальными примерами и задачами, которые помогают сразу закрепить навыки на практике.

📌 Оставляю ссылочку: thoughtspot.com

➡️ SQL Ready | #ресурс

Оконные фреймы (ROWS и RANGE) — управление областью расчёта!

В оконных функциях важны не только PARTITION BY и ORDER BY, но и то, какие строки попадают в окно вычисления. За это отвечают оконные фреймы — ROWS и RANGE.

Таблица операций:

transactions(id, user_id, amount, created_at)

Накопительная сумма по пользователю:

SUM(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at
)

При наличии ORDER BY и отсутствии явного фрейма в большинстве СУБД используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Если значения created_at совпадают, сумма считается сразу по группе строк (peers).

Среднее по последним 3 операциям:

AVG(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at, id
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

ROWS формирует окно по количеству строк. Детерминированный ORDER BY необходим для воспроизводимого результата.

Сумма за последние 7 дней (пример для PostgreSQL):

SUM(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at
  RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
)

RANGE формирует окно по значениям ORDER BY. Поддержка и синтаксис интервалов зависят от СУБД; часто допускается только одно выражение в ORDER BY.

🔥 Важно помнить: ROWS — предсказуем по количеству строк, RANGE — удобен для временных интервалов и peer-групп, неявный фрейм может менять результат вычислений

➡️ SQL Ready | #практика

Согласованное чтение данных внутри одной транзакции!

Иногда несколько SELECT должны работать с одной и той же версией данных, даже если параллельно идут изменения.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

В PostgreSQL это фиксирует MVCC snapshot в момент первого statement внутри транзакции:

SELECT COUNT(*) FROM orders;

Все последующие SELECT читают один и тот же snapshot и не видят новые COMMIT из других сессий, при этом собственные изменения транзакции видны:

SELECT SUM(amount) FROM orders;

Это гарантирует согласованность результатов между запросами без блокировки пишущих транзакций:

COMMIT;

🔥 После COMMIT snapshot освобождается, транзакция завершается.

➡️ SQL Ready | #совет

📂 Напоминалка по нормальным формам баз данных!

Например, 1NF требует атомарных значений, 2NF убирает частичные зависимости, а 3NF избавляет от транзитивных зависимостей.

На картинке — основные нормальные формы, которые полезны при проектировании SQL-баз данных.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

EXPLAIN — узнаём, как СУБД реально выполняет ваш запрос!

Команда EXPLAIN показывает, что делает база данных под капотом при выполнении SQL-запроса. Это помогает найти узкие места и понять, почему запрос может работать медленно.

Допустим, у нас есть запрос:

SELECT * FROM books WHERE author = 'Толстой';

Запускаем EXPLAIN, чтобы посмотреть план выполнения:

EXPLAIN SELECT * FROM books WHERE author = 'Толстой';

Если видим Table Scan или Seq Scan — это означает, что происходит полный просмотр таблицы. Такой способ может быть медленным, особенно при большом объёме данных.

Чтобы ускорить выполнение, создаём индекс:

CREATE INDEX idx_author ON books(author);

🔥 Теперь при повторном EXPLAIN запрос может использовать Index Scan, что значительно быстрее.

➡️ SQL Ready | #практика

☕️ Смотрите что нашел — Explainshell.

Вводишь любую команду в терминале, и он по частям объясняет, что делает каждая часть. Не просто man-ка, а понятный синтакс-анализ.

Особенно кайф для тех, кто работает в Linux/DevOps/CI среде и хочет разбираться, а не наугад копипастить из StackOverflow.

📌 Оставляю ссылочку: explainshell.com

➡️ SQL Ready | #ресурс

📂 Напоминалка для работы с SQL JOIN!

Например, INNER JOIN возвращает только совпадающие строки из двух таблиц, а LEFT JOIN позволяет получить все записи из основной таблицы, даже если связанной записи нет.

На картинке — 4 самых используемых типа SQL JOIN, которые постоянно встречаются в рабочих запросах.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

IS NOT DISTINCT FROM - равенство без NULL!

Обычное сравнение через = ломается, если возможен NULL:

SELECT *
FROM users
WHERE email = 'admin@example.com';

Эта проверка никогда не вернёт строки с NULL, даже если логически они равны:

SELECT *
FROM users
WHERE email IS NOT DISTINCT FROM NULL;

IS NOT DISTINCT FROM считает NULL = NULL и работает как настоящее равенство:

sql
SELECT *
FROM users
WHERE (email, phone)
IS NOT DISTINCT FROM ('a@b.com', NULL);

Работает и для составных сравнений, без OR … IS NULL.

🔥 IS NOT DISTINCT FROM - способ сравнивать значения, когда NULL допустимое состояние, а не исключение.

➡️ SQL Ready | #совет

❤️ Нашёл замечательную статью на Хабре: «Курс молодого бойца PostgreSQL»!

В этой статье:
• Автор шаг за шагом показывает, как использовать ключевые возможности PostgreSQL;
• Приводятся понятные примеры SQL-запросов с объяснением, когда и почему применять те или иные конструкции;
• Рассматриваются полезные трюки - преобразование типов, агрегации и работа с массивами;
• Все примеры можно сразу запускать.

🔊 Продолжайте читать на Habr!

➡️ SQL Ready | #статья

Partial index по стабильному селективному срезу!

В PostgreSQL частичный индекс особенно эффективен, когда в запросах регулярно повторяется один и тот же селективный предикат WHERE, а цель — быстро получить последние N записей по времени.

Таблица:

audit_logs(id, service, level, created_at, payload JSONB)

Индекс под узкий сегмент:

CREATE INDEX idx_billing_errors
ON audit_logs (created_at DESC)
WHERE service = 'billing' AND level = 'error';

Меньший размер индекса снижает нагрузку на buffer cache, уменьшает вероятность bloat и объём фонового обслуживания, а операции с индексом требуют меньше ресурсов.

Запрос за последними ошибками за сутки:

SELECT id, created_at, payload
FROM audit_logs
WHERE service = 'billing'
  AND level = 'error'
  AND created_at > NOW() - INTERVAL '1 day'
ORDER BY created_at DESC
LIMIT 15;

Связка ORDER BY created_at DESC + LIMIT позволяет планировщику сделать Index Scan и остановиться рано, как только найдено нужное количество строк.

index-only scan возможен только если все нужные колонки читаются из индекса, и страницы таблицы помечены all-visible (visibility map).

Если вам действительно нужен index-only для части полей, можно использовать INCLUDE:

CREATE INDEX idx_billing_errors_top
ON audit_logs (created_at DESC)
INCLUDE (id)
WHERE service = 'billing' AND level = 'error';

Частичный индекс по JSONB boolean-предикату (когда совпадающих строк мало и условие повторяется):

CREATE INDEX idx_critical_errors
ON audit_logs (created_at DESC)
WHERE level = 'error'
  AND payload @> '{"critical": true}';

И быстрый top-N:

SELECT id, service, created_at
FROM audit_logs
WHERE level = 'error'
  AND payload @> '{"critical": true}'
ORDER BY created_at DESC
LIMIT 10;

🔥 Частичные индексы — простой и экономный инструмент для повторяемого узкого среза + быстрого top-N по времени.

➡️ SQL Ready | #практика

👍 DataLemur — задачи для практики и подготовки к собеседованиям!

На сайте собраны вопросы разного уровня сложности: от базовых запросов до задач с JOIN, GROUP BY, подзапросами и оконными функциями. Формат ориентирован на реальные кейсы, такие задачи часто встречаются в работе и на собеседованиях. Удобный ресурс, чтобы закрепить знания.

📌 Оставляю ссылочку: datalemur.com

➡️ SQL Ready | #ресурс

Email должен быть уникальным только для активных пользователей?

Большинство реализуют это проверками в коде, SELECT перед INSERT, транзакциями или триггерами. PostgreSQL умеет делать это на уровне индекса:

INSERT INTO users (email) VALUES ('a@b.com');

Сработает, если активного пользователя с таким email ещё нет:

UPDATE users SET deleted_at = now() WHERE email = 'a@b.com';

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

🔥 Partial UNIQUE index — отличный способ фиксировать правила прямо в PostgreSQL.

➡️ SQL Ready | #совет