🥷 Подстава от LIKE: почему 'user_1' находит чужие данные

Все знают, что знак процента (%) в поиске заменяет любой кусок текста. Но многие забывают про скрытую угрозу — нижнее подчёркивание.

⚙️ В чём ловушка
В SQL символ _ (underscore) — это тоже спецсимвол. Он означает «ровно один любой знак».
Если ты попытаешься найти конкретного пользователя:

SELECT * FROM users WHERE login LIKE 'user_1';

База радостно вернёт не только user_1, но и user-1, userA1 или user91. Подчёркивание сработает как джокер в колоде карт.

🚀 Как починить
Чтобы база искала именно сам символ подчёркивания, его нужно экранировать с помощью оператора ESCAPE.

SELECT * FROM users 
WHERE login LIKE 'user!_1' ESCAPE '!';

🔹 Мы сами выбираем символ для экранирования (здесь это !).
🔹 Теперь комбинация !_ воспринимается базой как обычный текст, а не спецсимвол поиска.

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

🧟‍♂️ Подстава Soft Delete: почему удалённый юзер ломает регистрацию

Ты внедрил мягкое удаление (is_deleted = true). Пользователь удаляет аккаунт, а через месяц решает вернуться с тем же email. И тут регистрация падает с ошибкой!

⚙️ В чём проблема
🔹 Обычно на колонке email висит уникальный индекс, чтобы в базе не было клонов.
🔹 Но для базы «удалённый» юзер всё ещё существует. Строка физически никуда не делась, поэтому уникальный индекс блокирует новую регистрацию с этим же адресом.

🚀 Идеальное решение
Можно усложнять код бэкенда, но лучше поручить эту задачу самой базе с помощью частичного индекса (Partial Index). Он будет следить за уникальностью только среди активных пользователей.

CREATE UNIQUE INDEX active_users_email_idx 
ON users (email) 
WHERE is_deleted = false;

📈 Почему это круто
🔸 Вся логика защиты от дублей остаётся на уровне БД — никаких костылей в коде.
🔸 Индекс работает быстрее и занимает меньше места, так как просто игнорирует удалённые записи.

💡 Частичный индекс — самое изящное решение для Soft Delete, которое бережёт нервы и дисковое пространство.

🕳️ Один NULL — и запрос пустой. Скрытая ловушка NOT IN

Это классические грабли и частая задача на собеседованиях. Ты пишешь обычный фильтр, ожидаешь сотню строк, а база возвращает абсолютный ноль.

⚙️ Почему так происходит
🔹 Оператор NOT IN разворачивается базой в цепочку проверок: id != 1 AND id != 2 AND id != NULL.
🔹 В SQL любое сравнение с NULL даёт не False, а статус Unknown (неизвестно).
🔹 Логика базы строгая: True AND Unknown превращается в Unknown. Строка отбрасывается, так как условие не выполнилось на 100%.

📉 Как это выглядит в коде

SELECT * FROM users
WHERE id NOT IN (
  SELECT banned_id FROM ban_list
);

Если в таблице ban_list затесался хотя бы один NULL, запрос ничего не вернёт. База не может гарантировать, что пользователь чист, если один из банов «неизвестен».

🚀 Надёжное решение
Используй NOT EXISTS. Этот оператор проверяет сам факт наличия связи, а не сравнивает значения напрямую. Пустые значения его не сломают:

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM ban_list b
  WHERE b.banned_id = u.id
);

💡 Всегда выбирай NOT EXISTS для фильтрации по подзапросам, чтобы не наступить на мину неявных пустых значений.

👻 Забытые кавычки: почему одно число ломает индекс

Ты ищешь пользователя по номеру телефона, пишешь простой запрос, а база внезапно задумывается на долгие секунды. Причина — всего лишь пропущенные кавычки.

⚙️ Как появляется ошибка
Обычно номера телефонов хранят в строковых колонках (VARCHAR). Но при поиске разработчик может передать число без кавычек:

SELECT * FROM users WHERE phone = 79991234567;

📉 Что происходит под капотом
🔹 База видит несовпадение типов: колонка — строка, а условие поиска — число.
🔹 Чтобы их сравнить, база решает неявно привести всю колонку к числу. По сути, выполняет скрытый CAST(phone AS BIGINT) для каждой строки.
🔹 Любое преобразование данных в колонке моментально отключает её индекс.
🔹 Это как переписать всю телефонную книгу в другой формат ради поиска одного абонента. Запрос уходит в долгое и тяжелое сканирование всей таблицы от начала до конца (Full Table Scan).

🚀 Как правильно
🔸 Всегда передавай строковые значения в кавычках, даже если они состоят только из цифр:

SELECT * FROM users WHERE phone = '79991234567';

💡 Неявное приведение типов — тихий убийца производительности, поэтому всегда следи за типами данных в условиях поиска.

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

Ты радостно делаешь 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);

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