🗑 Удалили миллион строк, а место на диске не вернулось

Ты радостно делаешь DELETE FROM logs, ждёшь освобождения диска, но ничего не происходит. Современные базы данных нас обманывают.

⚙️ Почему база ничего не удаляет
🔹 В PostgreSQL и MySQL работает механизм MVCC (многоверсионность).
🔹 При операции DELETE физического стирания нет. База просто ставит на строку невидимую метку «удалено».
🔹 Это как вычеркнуть запись ручкой в блокноте: прочитать её уже нельзя, но физическое место на странице она всё ещё занимает.

📉 Зачем нужны «мёртвые зоны»
Ради безопасности и параллельной работы. Пока ты удаляешь записи, другой процесс может строить по ним отчёт. База заботливо хранит старую версию данных, пока все текущие запросы не завершатся.

🚀 Как реально освободить диск
Встроенные фоновые процессы очищают старые метки и отдают место внутри таблицы для новых строк, но не возвращают его операционной системе. Чтобы ужать файл и вернуть гигабайты диску, нужны команды:
🔹 В PostgreSQL:

VACUUM FULL logs;

🔹 В MySQL:

OPTIMIZE TABLE logs;

⚠️ Осторожно: эти команды полностью блокируют таблицу на всё время своей работы.

💡 Операция DELETE только прячет данные, а реальное место возвращают лишь тяжёлые команды полного сжатия.

🤯 Парадокс 3VL: почему WHERE блокирует NULL, а CHECK — радостно пропускает?

Ты добавил условие CHECK (salary > 0), чтобы в базу не попадали кривые зарплаты. Но вдруг обнаруживаешь там пустые значения. Как так вышло?

⚙️ Трехзначная логика (3VL)
В SQL кроме TRUE и FALSE есть состояние UNKNOWN (неизвестно). Если сравнить пустоту с нулем (NULL > 0), результат будет не ложь, а именно неизвестность. И механизмы базы реагируют на это по-разному.

🛡️ Двойные стандарты SQL
🔹 Фильтр WHERE — строгий охранник. Он возвращает строку, только если условие дало TRUE. Состояние UNKNOWN он воспринимает как FALSE и скрывает запись.
🔸 Валидация CHECK — ленивый вахтер. Она отклоняет запись, только если условие дало FALSE. Если результат UNKNOWN, ограничение пожимает плечами и пропускает значение в таблицу.

CREATE TABLE emp (
  salary INT CHECK (salary > 0)
);
-- Запишется без ошибок!
INSERT INTO emp (salary) VALUES (NULL);

🚀 Решение проблемы
Одного CHECK бывает мало. Если поле не должно содержать пустоту, всегда страхуй его правилом NOT NULL.

💡 Ограничения базы строго судят неверные данные, но пасуют перед неизвестностью.

💣 Цепная реакция: почему ON DELETE CASCADE запрещают на проде

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

⚠️ В чем главная опасность
🔹 Невидимая угроза. Одна неточность в запросе:

DELETE FROM users WHERE status = 'banned';

И каскад автоматически стирает связанные платежи, историю и профили.
🔹 Тишина в логах. Приложение даже не узнает, что база удалила еще сотни строк в других таблицах. Восстановить хронологию ошибки будет очень сложно.
🔹 Блокировки. Удаление одной строки тянет за собой скрытые проверки в десятках связанных таблиц, замедляя остальные операции.

🚀 Как делают правильно
🔸 Явное удаление. Сначала код приложения отдельными запросами удаляет зависимости, пишет подробные логи, и только потом удаляет самого пользователя.
🔸 Мягкое удаление (Soft Delete). Вместо DELETE строке просто меняют статус на удаленную: is_deleted = true.
🔸 Защита от ошибки. Внешние ключи настраивают с ON DELETE RESTRICT. База физически не даст удалить родительскую строку, пока существуют дочерние.

💡 Удобство автоматической зачистки не окупает риск потерять данные без следа — управляй удалением явно.

🧮 Математика с подвохом: почему сложение обнуляет зарплату, а SUM() — нет

Считаешь итоговую выплату сотруднику как salary + bonus? Если премии нет (в базе лежит NULL), математика сыграет злую шутку, и человек останется вообще без денег в итоговом отчёте.

⚠️ Ловушка прямого сложения
🔹 NULL — это не ноль, а полная «неизвестность».
🔹 Любая математическая операция с неизвестностью заражает итог. Если оклад 1000, а бонус NULL, выражение 1000 + NULL вернёт пустоту (NULL).

🛡 Почему SUM() ведёт себя иначе
🔸 Агрегатные функции (SUM(), AVG()) созданы для работы с группами строк и спроектированы так, чтобы игнорировать пустые ячейки.
🔸 Если сделать SUM(bonus) для значений 100, 200 и NULL, функция просто перешагнёт через пустоту и спокойно выдаст 300.

🚀 Как спасти строчные вычисления
Используй COALESCE — функцию, которая перехватит NULL и заменит его на запасной вариант.

SELECT 
  salary + COALESCE(bonus, 0) AS total_pay
FROM employees;

💡 При прямом сложении колонок всегда страхуй необязательные поля нулём, чтобы пустота не съела твои данные.

🪤 Ловушка Foreign Key: почему удаление строки вешает базу

Ты удаляешь одного пользователя, а внезапно зависает весь проект. Если ты работаешь в PostgreSQL, обычный внешний ключ может стать причиной жесткой блокировки.

⚙️ В чём подвох?
Многие уверены, что FOREIGN KEY автоматически создаёт индекс на колонку связи.
🔹 В MySQL это действительно так.
🔹 А вот в PostgreSQL — нет. Индекс нужно создавать вручную.

📉 Что происходит при удалении
Когда ты удаляешь запись из родительской таблицы (например, юзера), базе нужно убедиться, что на него нет ссылок в дочерней таблице (например, в заказах).
🔸 Без индекса база делает Full Table Scan — перебирает каждую строку в заказах. Это как искать нужное предложение, читая всю книгу целиком.
🔸 На время этого долгого поиска дочерняя таблица блокируется. Очередь запросов растёт, пока база не сломается от перегрузки.

🚀 Решение
Просто добавь индекс на колонку, которая ссылается на другую таблицу:

CREATE INDEX idx_orders_user_id ON orders(user_id);

💡 Всегда вручную индексируй колонки с внешними ключами в PostgreSQL, чтобы связи работали безопасно и быстро.

🤡 Иллюзия оптимизации: индекс на is_active только вредит

Кажется логичным: если в запросах часто есть WHERE is_active = true, на эту колонку нужен индекс. Но на деле база его проигнорирует, а ты лишь замедлишь работу системы.

⚙️ Почему база его не использует
Индексу важно разнообразие значений (кардинальность). Представь предметный указатель в конце книги: если слово встречается на 90% страниц, проще пролистать всю книгу целиком.
Оптимизатор рассуждает так же. Если значений всего два (true/false), он выберет полное сканирование таблицы (Seq Scan). Читать данные подряд намного быстрее, чем постоянно прыгать туда-сюда между индексом и таблицей.

📉 В чем реальный вред
Проигнорированный индекс становится мертвым грузом:
🔹 При каждом INSERT, UPDATE и DELETE базе приходится тратить время на обновление этой ненужной структуры.
🔹 Индекс впустую съедает место на диске и вытесняет из оперативной памяти действительно полезные данные.

🚀 Что делать
Если нужно быстро находить очень редкие статусы (например, 1% ошибок в логах), создавай частичный индекс:

CREATE INDEX idx_errors ON logs(status) WHERE status = 'error';

Он займет минимум места и будет реально использоваться.

💡 Индекс полезен, только когда он отсеивает подавляющее большинство строк, а не делит их пополам.

🙈 Повесил индекс, а база его игнорит

Ты создал индекс на колонку status, запускаешь поиск, а EXPLAIN нагло выдаёт Seq Scan. Кажется, база сломалась, но на самом деле она спасает твоё время.

⚙️ Почему это происходит
Индекс работает как алфавитный указатель в конце книги. Он идеален, если тебе нужно найти 5 строк из миллиона. База быстро находит ссылки и точечно забирает данные.

📉 Когда Full Scan быстрее
Если ты ищешь активных пользователей (например, is_active = true), а их в таблице 85%, индекс становится врагом оптимизатора.
🔹 Читать таблицу подряд (Full Scan) — это очень быстро. Как читать книгу страницу за страницей.
🔹 Использовать индекс для 85% строк — это сотни тысяч хаотичных прыжков туда-сюда за каждой отдельной строкой (Random I/O). Это слишком медленно, базе гораздо дешевле прочитать всё подряд и отфильтровать лишнее.

🚀 Что делать
🔸 Не строй индексы на колонки, где всего пара вариантов значений (статусы, пол, флаги).
🔸 Если часто ищешь редкие статусы (например, ошибки), используй частичный индекс:

CREATE INDEX idx_errors ON users(status) WHERE status = 'error';

💡 Индекс нужен для точечного поиска редких данных, а не для перекапывания всей таблицы.

🛑 Как Materialized View может повесить продакшен

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

⚙️ Почему всё зависло
Обычная команда REFRESH MATERIALIZED VIEW работает очень грубо.
🔹 Она вешает эксклюзивную блокировку. База полностью запрещает чтение, пока собирает новые данные. Это как закрыть магазин на переучёт — внутрь никого не пускают.

🚀 Как обновить без даунтайма
Чтобы отдавать пользователям старые данные, пока база в фоне строит новые, используй эту команду:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_report;

⚠️ Главный подвох
Слово CONCURRENTLY не сработает просто так. Базе нужно точно понимать, как сопоставлять старые и новые строки при фоновом обновлении.
🔸 Обязательное условие: на вьюшке должен быть создан уникальный индекс (например, по ID). Без него запрос завершится с ошибкой.

CREATE UNIQUE INDEX ON my_report (id);

💡 Уникальный индекс плюс обновление в фоне — и пользователи больше не увидят зависаний.

🪓 Убили индекс одной функцией: почему YEAR(date) тормозит базу

Ты создал идеальный индекс по дате, но запрос всё равно работает целую вечность. Причина кроется всего в одной функции.

⚙️ Почему база игнорирует индекс
🔹 Как только ты пишешь WHERE YEAR(order_date) = 2023, база перестаёт видеть исходные значения дат.
🔹 Она не может искать по индексу (как по алфавитному указателю в книге), потому что ей нужно сначала вычислить год для каждой строки таблицы.
🔹 Итог — долгое полное сканирование (Seq Scan), даже если нужных строк всего пара штук.

🚀 Как починить (правило SARG)
Чтобы индекс сработал, колонка должна оставаться «чистой» — без математики и функций. Перенеси все условия в правую часть от знака равенства.

🔸 Плохо: индекс сломается

SELECT * FROM orders 
WHERE YEAR(order_date) = 2023;

🔸 Хорошо: моментальный поиск

SELECT * FROM orders 
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

💡 Оставляй проиндексированную колонку в одиночестве слева от условия, и база ответит моментально.

📚 Скидка к началу учёбы
Обещал себе выучить SQL «с сентября»? Вот и сентябрь.
По промокоду SEPT26 — минус 25% на премиум до 4 сентября.

🪞 Иллюзия скорости: почему обычный VIEW не ускорит запросы

Спрятал огромный JOIN в представление, чтобы база работала быстрее? Плохие новости: обычный VIEW вообще ничего не кэширует.

⚙️ Как это работает на самом деле
Многие путают представление с физической таблицей. Но обычный VIEW — это просто текстовый макрос.
🔹 Каждый раз, когда ты делаешь SELECT * FROM my_view, база берёт исходный запрос и выполняет его с нуля.
🔹 Никакие результаты на диск не сохраняются. Все тяжёлые вычисления будут происходить заново при каждом обращении.

⚠️ Опасность «матрёшки»
Самая частая ошибка — строить одни вьюшки поверх других.
🔸 База вынуждена разворачивать этот клубок в один гигантский запрос.
🔸 Планировщик запросов теряется в абстракциях и может выбрать худший план выполнения. То, что должно работать секунду, выполняется часами.

🚀 Что делать?
🔹 Для реального ускорения и сохранения результата на диск используй:

CREATE MATERIALIZED VIEW my_cache AS
SELECT ...

🔹 Обычный VIEW оставляй только для удобства: чтобы скрыть сложную логику от других разработчиков или упростить чтение кода.

💡 Обычное представление создано для чистоты кода, а не для производительности.

♾️ Дырявый UNIQUE: как индекс пропускает дубликаты

Ты создал ограничение UNIQUE и уверен, что база под надёжной защитой. Но она радостно пропустит в таблицу хоть миллион строк с пустыми значениями.

⚙️ Почему так происходит
🔹 По стандарту SQL значение NULL означает «неизвестность».
🔹 Равна ли одна неизвестность другой? Нет. Правило гласит: NULL != NULL.
🔹 Раз пустые значения не равны друг другу, ограничение уникальности их пропускает. PostgreSQL, MySQL и Oracle позволят создавать бесконечные пустые профили.

CREATE TABLE users (
    email VARCHAR(100) UNIQUE
);
-- База сохранит обе строки:
INSERT INTO users (email) VALUES (NULL), (NULL);

⚠️ Сюрприз от SQL Server
В MS SQL Server логика другая. Он считает NULL конкретным значением и разрешает добавить его только один раз. Вторая пустая строка выдаст ошибку.

🚀 Что делать
🔸 Если поле всегда должно быть заполнено — добавляй ограничение NOT NULL.
🔸 В PostgreSQL 15+ можно явно запретить дубликаты пустых строк правилом UNIQUE NULLS NOT DISTINCT.

💡 Уникальность защищает только известные данные, а для пустых нужны свои правила.

⏳ Конец хаосу: почему UUIDv7 — хороший выбор для Primary Key
Обычный автоинкремент (ID=1, 2, 3), если показывать его наружу, позволяет конкурентам легко высчитать ваши продажи. А популярный случайный UUIDv4 незаметно снижает производительность базы.

⚠️ Две крайности старых подходов
🔷 Автоинкремент: быстрый и компактный, но раскрывает объёмы. Если зарегистрироваться дважды с разницей в день, конкурент узнает точное число ваших новых клиентов.
🔷 UUIDv4: абсолютно случаен. Каждая новая запись попадает в случайное место индекса. Это как вписывать новые контакты на случайные страницы бумажной телефонной книги: страницы приходится постоянно делить, индекс раздувается, а INSERT на больших таблицах замедляется.

🚀 Магия UUIDv7
Свежий стандарт (RFC 9562, 2024) решает обе проблемы. В начало ключа записывается текущее время (Timestamp) с точностью до миллисекунды, а остальная часть остаётся случайной.

-- PostgreSQL 18+
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  amount NUMERIC
);

В версиях до 18 нужно расширение pg_uuidv7 с функцией uuid_generate_v7().

📈 Что мы получаем
🔶 Новые записи ложатся в B-дерево почти последовательно, в конец индекса.
🔶 Индекс меньше раздувается, и вставка заметно быстрее, чем с UUIDv4.
🔶 Идентификаторы уникальны, и посчитать по ним объёмы нельзя. Но время создания записи из них читается.
💡 UUIDv7 — хороший компромисс: вставка почти как у счётчика, и объёмы не утекают. Но он вдвое больше bigint и не секрет: доступ к данным всё равно проверяйте на сервере.