Мини-квиз по дате и времени в PostgreSQL

1️⃣ Вопрос 1: Парсинг даты одной функцией

SELECT to_date('16-08-2024', 'DD-MM-YYYY');

a) 16/08/2024
b) 2024-16-08
c) 2024-08-16
d) Ошибка парсинга

Правильный ответ: c) 2024-08-16

2️⃣ Вопрос 2: Разница в днях

SELECT DATE '2024-03-15' - DATE '2024-03-01';

a) 13
b) 14
c) 15
d) 16

Правильный ответ: b) 14

3️⃣ Вопрос 3: Номер недели (ISO) для актуальной даты

SELECT EXTRACT(WEEK FROM DATE '2026-01-01');

a) 0
b) 1
c) 52
d) 53

Правильный ответ: b) 1

4️⃣ Вопрос 4: Переход через полночь

SELECT TIMESTAMP '2024-08-16 23:00:00' + INTERVAL '2 hours';

a) 2024-08-16 25:00:00
b) 2024-08-17 00:00:00
c) 2024-08-17 01:00:00
d) 2024-08-16 23:02:00

Правильный ответ: c) 2024-08-17 01:00:00

SQL & AI: Друзья или враги?
Еще пару лет назад мы обсуждали, заменит ли нейросеть разработчика. В 2026 году ответ стал очевиден: AI не заменил SQL-специалиста, но он радикально изменил правила игры.
Сегодня разбираемся, как превратить AI в мощного союзника и где расставлены ловушки, в которые попадают даже мидлы.

🤝 Почему AI - ваш лучший друг
🔹Прощай, шаблонный код
Написать 10 однотипных JOIN или сложную структуру CASE WHEN нейросеть может за секунды. Это экономит до 40% времени на рутине.

🔹Объяснение чужого кода
Получили в наследство legacy-запрос на 200 строк без единого комментария? AI отлично справляется с декомпозицией и объяснением логики «человеческим» языком.

🔹Генерация синтетических данных
Нужно быстро наполнить таблицу для тестов, соблюдая типы данных и связи? AI сделает это лучше любого скрипта-заполнителя.

⚠️ Почему AI - коварный враг
Главная проблема в том, что AI галлюцинирует уверенно.

🔹Каша из диалектов
Модель может предложить изящное решение на функциях PostgreSQL (например, DISTINCT ON), которые просто не существуют или работают иначе в вашей версии MySQL или ClickHouse.

🔹Производительность — не приоритет по умолчанию
Если не попросить специально оптимизировать запрос, AI выдаст код, который просто «работает». Он часто предлагает вложенные подзапросы там, где эффективнее CTE или оконные функции, и редко думает об использовании индексов.

🔹Отсутствие контекста БД
Нейросеть не знает объема ваших данных, наличия индексов и специфики «железа». Запрос, который AI посчитал идеальным, может «положить» ваш прод в пиковую нагрузку.

💡 Как выжить в эпоху AI-SQL?
Чтобы оставаться востребованным профи, фокус должен сместиться с «написания кода» на его аудит и архитектуру.

🔹Валидация — это закон
Никогда не копируйте код из чата в консоль без проверки EXPLAIN ANALYZE.

🔹AI как ревьюер
Попробуйте обратный подход — скормите нейросети свой рабочий запрос и спросите: «Как это оптимизировать?». Иногда она подсвечивает неочевидные узкие места.

🔹Изучайте теорию глубже
Чем лучше вы понимаете работу планировщика запросов и индексов, тем проще вам будет увидеть «галлюцинацию» в ответе нейросети.

AI - это не замена мозга, а мощный инструмент. Он делает вас быстрее, но очень важно валидировать самостоятельно любой ее ответ.

Views vs Materialized Views: в чем разница?
Когда вы только начинаете работать с SQL, вам быстро надоедает писать один и тот же запрос на 20 строк с кучей джоинов. Чтобы не копипастить код, его можно сохранить прямо в базе. Для этого есть два способа: обычные Views (вьюхи) и материализованные.

Что такое View?
Это просто запрос, которому дали имя. База не создает новую таблицу и не копирует данные. Она просто запоминает ваш SELECT.
Когда вы пишете SELECT * FROM my_view, база «под капотом» подставляет ваш исходный код и выполняет его заново.

Представьте, что вам постоянно нужно видеть комнаты вместе с именами владельцев. Чтобы не джойнить таблицы каждый раз руками, делаем так:

CREATE VIEW room_owners AS
SELECT r.id, r.home_type, u.name as owner_name
FROM Rooms r
JOIN Users u ON r.owner_id = u.id;

Теперь можно просто писать SELECT * FROM room_owners. База будет каждый раз пересчитывать джойн.
Плюс в том, что данные всегда актуальные.
Минус: если в таблицах миллионы строк, такая View начнет подтормаживать.

Что такое Materialized View
Здесь база выполняет запрос один раз и сохраняет результат в отдельную скрытую таблицу. Это спасение для тяжелых расчетов. Например, если мы хотим посчитать общую выручку по каждой комнате за всё время:

CREATE MATERIALIZED VIEW room_stats AS
SELECT room_id, SUM(total) as total_earned
FROM Reservations
GROUP BY room_id;

Результат читается мгновенно, потому что сумма уже посчитана и лежит на диске. Но есть нюанс: данные в ней «застывают». Если пришла новая бронь, сумма не изменится, пока вы не обновите View командой:

REFRESH MATERIALIZED VIEW room_stats;

Что выбрать?
Все зависит от того, что вам важнее: скорость или актуальность. Если данные нужны «прямо сейчас» — делайте обычную View. Если вы строите отчет за прошлый месяц, который не меняется каждую секунду — Materialized View сэкономит кучу ресурсов.

DELETE — это навсегда. Почему в разработке его избегают?
Удаление данных из базы в реальном проекте — это почти всегда плохая идея. Ошибка пользователя или случайный клик могут стоить часов работы по восстановлению данных из бэкапов. Чтобы не доводить до этого, используют Soft delete.

Вместо реального удаления мы просто помечаем строку как неактивную в колонке deleted_at. Данные остаются на месте, но приложение их «не видит».

Где начинаются проблемы
Самое неприятное — это необходимость фильтровать удаленные записи в каждом запросе. Стоит один раз забыть про WHERE deleted_at IS NULL, и в аналитике по выручке или количеству живых юзеров появятся «мертвые души».

Еще одна ловушка — уникальные индексы. Если на email стоит UNIQUE, база не даст создать новый аккаунт с почтой удаленного юзера. Для индекса эта запись всё еще существует, и ему неважно, какая дата там проставлена.

Как сделать архитектуру чище
Чтобы не загромождать код постоянными проверками на NULL, лучше вынести логику во View. Мы создаем виртуальную таблицу, которая сразу отсекает всё лишнее

CREATE VIEW active_users AS
SELECT * FROM Users WHERE deleted_at IS NULL;

Для решения конфликтов уникальности идеально подходят частичные индексы.
В Postgres это делается одной командой:

CREATE UNIQUE INDEX idx_email ON Users (email) WHERE deleted_at IS NULL;

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

Итог прост: если данные представляют хоть какую-то ценность, не удаляйте их. Используйте флаги удаления и View.
Это в разы безопаснее, чем чистить базу физически.

Один UPDATE — и данных больше нет. Как это починить?
Представьте: прибегает продакт — «Почему у клиента цена 50к? Мы же вчера ставили 40к!» А в базе уже новое значение. Старое затёрто командой UPDATE. Восстановить нельзя. Никак.

Если в проекте есть данные, которые меняются и за которыми нужно следить — цены, статусы, настройки — эта проблема рано или поздно прилетит. Это как редактировать документ без Ctrl+Z — каждое изменение уничтожает предыдущую версию. Для финансовой отчётности и аналитики — катастрофа.

Разберём три способа этого избежать — от простого к мощному.

Как это работает — коротко:

1. Таблица-клон     
Rooms → триггер → Rooms_History
2. SYSTEM_TIME      
Rooms → база сама ведёт историю
3. JSONB-слепки     
Rooms → триггер → JSON-снимок в отдельную таблицу

1️⃣ Вариант 1: Таблица-клон
Самый понятный способ. Рядом с основной таблицей (например, Rooms) создаём её копию — Rooms_History. Каждый раз, когда кто-то меняет строку в основной таблице, автоматический механизм (триггер) сначала сохраняет старую версию строки в копию, и только потом применяет изменение.

Главная боль — Schema Drift. Допустим, разработчик добавил в основную таблицу новую колонку «площадь». Если он забыл добавить эту же колонку в таблицу-клон и обновить триггер — всё ломается. Подходит только там, где структура таблиц не менялась годами и не планирует.

2️⃣ Вариант 2: SYSTEM_TIME — встроенный видеорегистратор
Некоторые современные базы данных умеют вести историю сами, без всяких таблиц-клонов и триггеров. Достаточно один раз включить функцию — и база начнёт запоминать, когда каждая строка появилась и когда изменилась.

После этого можно буквально «заглянуть в прошлое» одной командой:

SELECT * FROM Rooms FOR SYSTEM_TIME AS OF '2026-01-01';

Эта строка говорит: «Покажи мне все комнаты в том виде, в каком они были 1 января 2026 года». Идеально для отладки: можно увидеть точное состояние данных в ту секунду, когда пользователь словил баг. Если нужна серьёзная аналитика и «путешествия во времени» — это лучший выбор.

Но есть нюанс: SYSTEM_TIME поддерживают не все базы данных. В PostgreSQL и MariaDB это работает, а вот в MySQL — нет.

3️⃣ Вариант 3: История через JSONB-слепки
Идея простая: каждый раз, когда строка меняется, мы берём её текущую версию, упаковываем целиком в один JSON-объект и сохраняем в отдельную таблицу. Если комнату не трогали полгода, ни одной новой записи за это время не появится.

Допустим, у комнаты №42 три раза менялась цена. В таблице истории будет:

room  snapshot                           changed_at
42    {"price": 300, "status": "active"}  1 января
42    {"price": 400, "status": "active"}  15 марта
42    {"price": 500, "status": "paused"}  10 апреля

Хотим узнать цену в феврале? Берём последнюю запись до февраля — 300.
Почему JSON, а не обычная таблица-клон? Потому что JSON — это «резиновый» контейнер. Неважно, сколько новых колонок добавили в основную таблицу — JSON примет их все без единого изменения в таблице истории. Та самая проблема Schema Drift из первого варианта здесь просто не существует.

Ложка дёгтя: история раздувает базу
Об этом забывают чаще всего. Если данные меняются часто, таблица истории за полгода легко вырастает до сотен гигабайт.
Проверенная стратегия — разделять данные по «температуре»:
🔹«Горячая» история за последние 3 месяца — живёт в основной базе (PostgreSQL), к ней обращаются постоянно.
🔹 Всё, что старше — уезжает в «холодное» хранилище (ClickHouse, S3). Там место дешевле, а аналитика по миллионам старых строк работает даже быстрее.

Итого, что выбрать:
🔹 Простая система, структура не меняется → Таблица-клон с триггером
🔹 Нужна точная аналитика и «путешествие во времени» → SYSTEM_TIME
🔹 Структура часто меняется, средний масштаб → JSONB-слепки
И в любом случае — заранее решите, куда архивировать историю через год. Потом будет поздно.

CHECK-ограничения в MySQL: валидация прямо в таблице ✅
Когда вы хотите убедиться, что в таблицу попадают только корректные данные — не всегда нужно писать сложную логику в приложении. Иногда достаточно встроенного механизма MySQL: ограничения CHECK.

🔹 Что такое CHECK?
Это правило, которое автоматически проверяет значение в колонке при вставке (INSERT) или обновлении (UPDATE). Если правило нарушено — операция отменяется с ошибкой.
Пример:

CREATE TABLE users (
  id INT PRIMARY KEY,
  age INT,
  CHECK (age >= 0 AND age <= 120)
);

Теперь нельзя вставить пользователя с возрастом -5 или 999 — MySQL просто не даст это сделать.

Синтаксис CHECK
🔸 Можно добавлять при создании таблицы (CREATE TABLE) или позже (ALTER TABLE).
🔸 Можно использовать AND, OR, арифметику, сравнения, функции (с ограничениями).
🔸 Поддерживается в MySQL 8.0+. В старых версиях CHECK игнорировался (!).

Пример с условием на строку:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  category ENUM('book', 'game', 'toy'),
  price DECIMAL(10,2),
  CHECK (price >= 0)
);

Здесь мы запрещаем отрицательные цены.

Добавление ограничения в существующую таблицу:

ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120);

Что произойдёт при нарушении?

INSERT INTO users (id, age) VALUES (1, -10);
-- ERROR 3819 (HY000): Check constraint 'chk_age' is violated.

⚠️ Важно помнить:
🔹CHECK срабатывает только для новых данных — старые строки не проверяются.
🔹Если значение NULL, правило обычно не нарушается (NULL считается «неизвестным» и не сравнивается напрямую).
🔹Ошибку можно перехватывать в приложении, чтобы сообщить пользователю.

✅ Когда стоит использовать CHECK:
🔹 Для ограничений: возраст, положительная цена, длина строки, формат (через LIKE или REGEXP).
🔹Когда хотите, чтобы в таблице «по умолчанию» всегда были только корректные данные.
🔹Чтобы упростить валидацию и сделать БД самодостаточной (например, при работе с внешними источниками данных).

🔑 UUIDv4 тихо убивает твою базу
UUID удобен: генерируешь на бэке, ключи уникальны, и никто не подсмотрит число заказов, поменяв цифру в URL. Кажется, идеальный Primary Key.
Но обычный UUIDv4 — полностью случайный. А базе это очень не нравится.

⚙️ Почему так
Индексы живут в B-Tree, а дереву нужен порядок.
🔹 BigInt с автоинкрементом → новая запись дописывается в конец. Мгновенно.
🔹UUIDv4 → случайный ID лезет в середину дерева. База рвёт заполненную страницу пополам и перетасовывает данные. Это и есть Page Split.

📉 Что происходит в продакшене
Пока строк пара сотен тысяч — тишина.
На миллионах начинается боль:
🔹 тормозят INSERT — CPU занят не записью, а перебалансировкой
🔹 фрагментация — страницы полупустые, индекс пухнет в разы
🔹 вымывание кэша — раздутый индекс не лезет в RAM, и база уходит на диск

🚀 Решение — UUIDv7
Отказываться от UUID не нужно. Просто бери седьмую версию.
В начале строки — timestamp (время до миллисекунды), и только потом случайные биты.
🔹 Ключи всегда растут
🔹 Для базы это почти автоинкремент
🔹Записи ложатся в конец, фрагментация исчезает

Скорость вставок остаётся ровной даже на таблицах в десятки гигабайт.

💡 Стартуешь проект? Закладывай UUIDv7 сразу.

🚀 Поздний JOIN: как ускорить OFFSET на больших данных в 10 раз

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

📉 В чём проблема
🔹 Представь поиск 100-й страницы в толстой энциклопедии. Вместо оглавления ты читаешь весь текст с первой страницы, пока не дойдёшь до сотой.
🔹 Так же делает база при OFFSET 100000: она собирает с диска все данные (длинные тексты, даты), отсчитывает ненужные сто тысяч строк и просто выбрасывает их. Это огромная трата ресурсов.

🚀 Решение — Поздний JOIN (Deferred Join)
Сначала мы быстро находим нужные идентификаторы, а уже к ним присоединяем полные данные:

SELECT p.*
FROM (
    SELECT id FROM products
    ORDER BY price
    LIMIT 50 OFFSET 100000
) AS sub
JOIN products p ON p.id = sub.id;

⚙️ Как это работает
🔸 Внутренний запрос работает как оглавление. Он бежит только по легкому индексу (цене и id), мгновенно пропуская 100 тысяч записей, и выдает 50 нужных id.
🔸 Внешний JOIN обращается к самой таблице и загружает тяжелые данные только для этих 50 финальных строк.

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

🥷 Подстава от 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, которое бережёт нервы и дисковое пространство.