🔒Блокировка по расписанию

📢 Алерт: "Блокировки в prod-db-sigma: 27 сессий ждут AccessExclusiveLock более 10 минут"

Макс потянулся за кофе, но, увидев цифры, передумал. В его голове тут же всплыли возможные причины:
- Долгая миграция? Нет, сегодня релизов не было.
- Кто-то забыл транзакцию? Проверим.

Запустив типовой запрос он увидел виновника, который держал всех в заложниках: "REFRESH MATERIALIZED VIEW some_analytics_view;", запущенный из pg_cron scheduler.

Макс мысленно вздохнул:
"Ну конечно, матвью. Почему-то все забывают, что обычный REFRESH блокирует ВСЁ, как будто это не 2025 год, и будто мы еще на постгре версии 12."

Набросав в общий чат: "Занимаюсь блокировкой в prod-db-sigma", он начал действовать.

Почему нельзя просто взять и сделать CONCURRENTLY?

Потому что PostgreSQL — не волшебник, а строгий бухгалтер. Для REFRESH MATERIALIZED VIEW CONCURRENTLY нужен уникальный индекс (хотя бы на одно поле, без NULL-ов). Есть и другие условия, например, матвью должна содержать данные, но в данном случае они все выполнялись.

Макс проверил есть ли индексы:

SELECT * FROM pg_indexes WHERE tablename = 'some_analytics_view';

Результат: "0 rows" — вот и ответ.

Убедившись. что во вью есть подходящее поле, Макс приступил к созданию индекса. Для начала он удалил блокирующую сессию, осознавая, что потом еще придется колдовать с pg_cron, который очень не любит, когда с его сессиями расправляются вот так вот безжалостно.

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

CREATE UNIQUE INDEX CONCURRENTLY idx_some_analytics_view_id
ON some_analytics_view (id);

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

REFRESH MATERIALIZED VIEW CONCURRENTLY some_analytics_view;

Убедившись, что команда выполняется и блокировок нет, Макс мысленно представил, как pg_cron теперь смотрит на него с укоризной:
"Ты убил мой процесс… Теперь я либо зависну в подвешенном состоянии, либо начну плодить дубликаты заданий, как сумасшедший. Спасибо, Макс."

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

А впереди оставались еще более скучные дела:

📝 Оформить тикет ("Какой ужасный день для этого...").

📚 Добавить запись в базу знаний ("Напоминание: матвью без индекса = боль").

👨 Найти разработчика и провести короткий ликбез.

Спустя час Макс откинулся в кресле, глядя на зеленые графики в мониторинге. Очередной пожар потушен, база знаний пополнена, разработчик проинформирован.

"Ну вот, теперь можно и кофе допить… Хотя стоп, что это за новый алерт?.."

#кейс #блокировки #матвью

🎓 100 уроков EXPLAIN. Начало

Лена из поддержки аккуратно постучала в открытую дверь Макса:
— Ты обещал научить меня разбираться с тормозящими запросами. Может, начнём? А то я даже не понимаю, куда смотреть, когда пишут «у вас там план кривой».

Макс отложил кружку с остывшим кофе и убрал с экрана монитора три вкладки с алертами:
— Окей, давай по порядку. Ты видишь в логах, что «запрос выполняется 10 секунд» — это симптом болезни. А EXPLAIN — это рентген, который покажет, где конкретно проблема.

Лена нахмурилась:
— Я так понимаю, что это… инструкция, как Postgres выполняет запрос?

— Почти! — Макс развернул монитор. — Но есть нюансы.

🔍 Три вида «рентгена»
Макс открыл новое окно и быстрым движением набрал:

EXPLAIN SELECT * FROM orders WHERE order_type = 10;

1. EXPLAIN — «Теоретический план»
— Смотри, это как маршрут на бумажной карте. Postgres говорит: «Я бы сделал так», но не факт, что в реальности будет так же. Видишь Seq Scan? Это значит, он планирует читать всю таблицу подряд.

2. EXPLAIN ANALYZE — «Реальная дорога»
Макс добавил ANALYZE:

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_type = 10;

— А это уже как навигатор с пробками. Тут есть реальные цифры: сколько времени заняло, сколько строк обработано. Если видишь Actual Rows=1, а Rows=1000 — значит план ошибся в прогнозе в 1000 раз!

Лена кивнула:
— То есть если запрос тормозит, сначала смотрим тут?

— Да, но… — Макс ухмыльнулся. — Есть третий уровень.

3. EXPLAIN (ANALYZE, BUFFERS) — «Разбор аварии»

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_type = 10;

— Это уже разбор ДТП с вызовом экспертов. BUFFERS покажет, сколько данных прочитано с диска (очень медленно!) или из кеша (быстро). А VERBOSE — дополнительные детали.

— Но запомни: BUFFERS — это мощный допинг для ANALYZE. На проде запускать только после согласования с командой. — Добавил он немного подумав.

🚀 Практика
Макс подвинул клавиатуру:
— Давай попробуем. Напиши любой свой запрос, и сделаем к нему EXPLAIN ANALYZE.

Лена ввела что-то вроде:

SELECT count(*) FROM tasks WHERE status = 'new' AND created_at > '2025-01-01';

— Ого! — она указала на цифры. — Seq Scan on tasks, а потом Filter… И он прогнал 500 тыс. строк!

— Вот и кандидат на оптимизацию, — Макс сохранил план в файл. — В следующий раз разберём, как заменить это на Index Scan.

📌 Что запомнить
🔹 EXPLAIN — только план (без выполнения запроса)
🔹 EXPLAIN ANALYZE — выполнит запрос + покажет фактические замеры
🔹 EXPLAIN (ANALYZE, BUFFERS) — выполнит запрос + добавит данные о памяти/диске
❗️Важно: BUFFERS работает только с ANALYZE! В продакшене используйте аккуратно — запрос изменит состояние кеша!
📉 Разница между Rows и Actual Rows — если сильно отличаются, план ошибся

Лена потянулась за своим ноутбуком:
— Ладно, теперь я хотя бы понимаю, как смотреть. А что дальше?

Макс ухмыльнулся и показал на строку подключения к тестовой базе:
— Дальше — практика без теории. Просто гоняй любые запросы через EXPLAIN на тестовой базе и смотри, что получается. Не пытайся сразу всё понять — просто привыкай к структуре.

— Даже если я ничего не пойму?

— Особенно если не поймешь! — Макс подвинул ей список команд. — Записывай вопросы:
• «Что такое Seq Scan, а что такое Index Scan?»
• «Что значит cost=100500?»
• «Почему Actual Rows меньше, чем Rows?»
Вопросы присылай — разберём. Главное — перестать бояться этих «иероглифов».

— Окей, попробую. Но если я сломаю тестовую базу…

— Если сломаешь — значит, учишься, — Макс допил кофе. — До завтра!

#explain

P.S. Задание для смелых:
Попробуйте прямо сейчас сделать EXPLAIN любого вашего запроса в тестовой базе. Не ищите ответы — просто фиксируйте, что кажется странным.

Шпаргалка

🎓 100 уроков EXPLAIN. Урок 2
Как читать дерево планов и не сойти с ума

Лена ворвалась в кабинет Макса с горящими глазами:
— Я сделала 20 EXPLAIN'ов! И теперь у меня ещё больше вопросов! Почему тут Seq Scan, а тут Index Scan? Что за цифры в скобках? И почему это похоже на дерево?

Макс смахнул крошки от печенья с клавиатуры:
— О, ты уже заметила главное! План запроса — это действительно дерево. Но не простое, а перевёрнутое.

🌳 Дерево плана: корни вверх, листья вниз
Макс открыл пример:

EXPLAIN SELECT * FROM orders JOIN users ON users.id = orders.user_id;

Hash Join  (cost=100.50..150.20 rows=500 width=200)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on orders  (cost=0.00..50.00 rows=1000 width=100)
  ->  Hash  (cost=70.50..70.50 rows=2000 width=100)
        ->  Seq Scan on users  (cost=0.00..70.50 rows=2000 width=100)

— Видишь эту лесенку? Читаем снизу вверх:

Листья (низ): Сначала сканируем таблицы (Seq Scan)
Ветки (середина): Хешируем данные (Hash)
Ствол (верх): Соединяем результаты (Hash Join)

Лена постучала пальцем по экрану:
— То есть Postgres сначала выполняет то, что глубже в дереве?

— Бинго! — Макс достал маркер. — Это как сборка бургера:

Сначала достаём булочки и котлету (читаем таблицы)
Потом собираем (JOIN, сортировка)
В конце подаём (возвращаем результат)

🔢 Цифры в скобках: что значит cost=100.50..150.20?
— Первое число — стартовая стоимость, второе — полная. Важно: стоимость накапливается снизу вверх!

Лена кивнула:
— То есть 150.20 — это сумма всего, что ниже плюс сам Hash Join?

— Именно! — Макс ухмыльнулся. — Как чек в ресторане: итоговая сумма = закуски + основное + десерт.

Лена нахмурилась:
— А почему иногда cost=0.00..50.00?

— А, это особенность расчётов! — махнул рукой Макс. — Postgres не учитывает подготовку к Seq Scan как затраты.
Но на самом деле:
- 0.00 — не значит «бесплатно», просто нет дополнительных расходов как у индексов
- 50.00 — полная цена чтения всей таблицы

📊 Четыре важных типа узлов (для начала)
Макс нарисовал на доске:

Seq Scan (Трактор) 🚜
— Читает таблицу от начала до конца, как трактор пашет поле.
— Когда встречается: Нет подходящего индекса / читается вся таблица.

Index Scan (Спорткар) 🏎
— Быстро находит данные по индексу, но если строк много — делает много «поездок» туда-сюда.
— Когда встречается: WHERE по индексированному полю.

Sort (Конвейерная лента) 🔀
— Сортирует данные перед выдачей (ORDER BY) или для соединения.
— Когда встречается: ORDER BY без индекса / GROUP BY / DISTINCT.

Hash Join (Фабрика) 🏭
— Сначала строит хеш-таблицу из одной таблицы, потом ищет совпадения.
— Когда встречается: JOIN больших таблиц по равенству (=).

💡 Практика: разберём реальный план
Макс открыл заготовленный пример:

Limit  (cost=150.21..150.21 rows=1)
  ->  Sort  (cost=150.20..155.20 rows=2000)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..50.00 rows=2000)

— Видишь? — ткнул он в Sort. — Без индекса по created_at Postgres:

Сначала трактором (Seq Scan) читает все заказы,
Потом конвейером (Sort) сортирует,
И только потом обрезает (Limit).

— Если добавить индекс — Sort исчезнет, — Макс хитро улыбнулся. — Но это уже следующий урок!

📌 Что запомнить
🌳 План — перевёрнутое дерево (читаем снизу вверх)
🔢 cost=старт..конец — как быстро получим первую и все строки
🚜 Seq Scan vs 🏎 Index Scan — трактор или спорткар?

Лена потянулась за ноутбуком:
— Окей, я попробую еще «почитать» планы. Если у меня будут еще вопросы?

— Присылай скриншоты, — Макс вручил ей шоколадку. — Разберём вместе. А завтра я покажу, как заставить Postgres использовать индекс!

#explain

P.S. Задание:
Сделайте EXPLAIN для любого запроса с JOIN
Попробуйте «прочитать» дерево плана снизу вверх
Найдите самый «дорогой» узел (максимальное увеличение cost)

⚖️ Скорость или качество?

Типичное утро в опенспейсе. Кофемашина хрипела, выдавая очередную порцию. Макс, с красными от недосыпа глазами работал над каким-то скриптом. На втором мониторе Zabbix радостно подсвечивал десяток алертов красным.

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

Таня (тимлид, за его спиной):
— Макс, ну что там с нашим "горячим" фиксом? Клиенты уже в чате истерику закатывают.

— Не торопи меня, Тань! — Макс развернулся к ней. — Петя из маркетинга вчера кричал, что фичу нужно "еще вчера". Коля из разработки сегодня вдруг понял, что "упустил один момент". И с кого теперь спрос? Правильно — с меня!

В этот момент подошел Сергей, старший разработчик.

Сергей:
— А я говорил Ане, что нельзя было обещать клиентам эту фичу без полноценного тестирования. Но нет же, "time to market превыше всего"!

Аня (продукт-менеджер, входя в комнату):
— Эй, я тут ни при чем! Сергей, ты же сам сказал, что "в принципе должно работать"! Да и протестировали же все!

Оля (тестировщица, из-за перегородки):
— А ничего, что меня в час ночи сегодня подняли тестировать ваш "срочный" релиз, который "нужно выкатить до утра любой ценой"?!

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

Сергей (скрестив руки):
— Я поддерживаю. Может хватит уже гнаться за сроками? Давайте делать все нормально.

Таня:
— Легко сказать, когда конкуренты уже две недели как выпустили аналогичный функционал...

Оля (неожиданно мягко):
— Знаете что, ребята... Мы все постоянно разрываемся между сроками и качеством. Это нормально. Но главное — не винить друг друга, а системно улучшать процессы. Я вот на прошлом проекте...

Макс (перебивает с ухмылкой):
— Да ладно, Оль, ты же знаешь, что мы тут все перфекционисты-невротики. Но спасибо, что напомнила — мы действительно не одни такие.

Сергей (кивает):
— Точно. Главное — не повторять одни и те же ошибки. И фиксировать техдолги.

Макс откинулся на спинку кресла:
— Тогда предлагаю простые правила:
1️⃣ Деньги и безопасность — тестируем до посинения
2️⃣ Не прячем ошибки. За EXCEPTION WHEN OTHERS NULL; — расстрел
3️⃣ Если делаем быстро "любой ценой" — сразу записываем техдолг с дедлайном

Аня:
— Но как тогда успевать за рынком?

— А вот смотрите, — Макс показал на список открытых заявок в helpdesk. — Если тратить 30% времени на профилактику проблем, мы экономим 70% на их исправлении.

Все задумались. Кофемашина одобрительно булькала .

Таня (подводя итог):
— Значит, так: не виним себя, фиксируем проблемы, и... Макс, сделай уже тот график зависимости багов от релизов — будем тыкать в него маркетологам.

Все смеются. Напряжение спадает.

#процессы

P.S. Макс вечером записал в дневник: "День 149. Оля неожиданно оказалась голосом разума. Надо чаще её слушать. И купить ей шоколадку."

🎓 100 уроков EXPLAIN. Урок 3
Почему Postgres игнорирует индекс

Утро. Макс заходит в офис с двумя кружками кофе — одна для себя, вторая для Лены. Та уже сидит за компьютером и хмуро разглядывает EXPLAIN своего запроса.

— Ну что, готова узнать, почему твой запрос до сих пор тормозит? — Макс ставит кружку перед ней. — Вчера я обещал рассказать про индексы.

Лена (показывая на экран):
— Вот! Я сделала табличку на тесте, создала индекс по user_id, но постгрес всё равно делает Seq Scan! Он что, тупой?

Макс разворачивает монитор и смотрит на план запроса Лены:

EXPLAIN SELECT * FROM my_tasks WHERE user_id = 1;

— Да нет же, — Макс смеётся. — Он просто знает то, чего не знаешь ты. Есть четыре ключевых ситуации, когда оптимизатор сознательно выбирает полное сканирование таблицы вместо использования индекса:

1️⃣ Маленькие таблицы (Seq Scan выгоднее)
Почему: При малом объёме данных стоимость полного чтения всей таблицы часто ниже, чем прыжки по индексу с последующими обращениями к таблице.

2️⃣ Фильтрация по функции от поля (требуется специальный индекс - функциональный)
Примеры:

SELECT * FROM users WHERE concat(first_name, ' ', last_name) = 'Макс Логинов';
SELECT * FROM users WHERE age + 5 > 30;

Несмотря на то, что есть индексы по полям first_name, last_name и age - они не будет использованы.
Почему: Обычный индекс хранит исходные значения. Postgres не может сопоставить вычисляемое выражение с обычным индексом.
Есть и исключения, когда Postgres может преобразовать условие так, чтобы применялся индекс, либо используется Index Only Scan, но об этом в другой раз.
Решение: Создать функциональный индекс, который заранее вычисляет нужное преобразование:

CREATE INDEX idx_users_fullname ON users (concat(first_name, ' ', last_name));

3️⃣ Неселективные условия (индекс не даёт выгоды)
Примеры:

-- Плохо (нет селективности):
SELECT * FROM my_tasks WHERE status = 'closed'; -- 98% строк

-- Хорошо (высокая селективность):  
SELECT * FROM my_tasks WHERE status = 'new'; -- < 1% строк

Почему: Проще прочитать всю таблицу, чем выбирать все эти строки по одной по индексу. Postgres собирает и использует статистику о распределении данных в таблицах. При подготовке плана он проверяет селективность условий и выбирает оптимальный вариант.
Обычно индекс используется, если условия должны вернуть не больше чем 5-10% от общего числа строк.

4️⃣ Устаревшая статистика (оптимизатор "не в курсе")
Обычно Postgres самостоятельно обновляет статистику (через autovacuum). Но в некоторых ситуациях статистика может устареть или быть неактуальной. Например, если ты только что залила миллион строк — Postgres живёт в прошлом. Посмотреть можно, например, так:

SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE relname = 'твоя_таблица';  -- или без условия для списка всех таблиц

Собрать статистику принудительно:

ANALYZE твоя_таблица;

Лена отставила кружку:
— Спасибо, теперь стало понятнее, где копать. А покажешь, что делать, если индекс есть, но запрос всё равно медленный?

— Конечно! — Макс ухмыляется. — Завтра разберём Partial Indexes и Index Only Scan.

#explain #индексы #статистика

P.S. Макс вечером записал в своем дневнике:
«Лена уже сама создает индексы . Теперь главное — не дать ей создать индекс на каждое поле...»

🎓 100 уроков EXPLAIN. Практика
join + group by против подзапроса

Переработав очередной алерт о долгом запросе, Макс задумался.
— Лене стоило бы это увидеть, — пробормотал он, подключаясь к тестовой базе.

Через 10 минут был готов демо-стенд для объяснения утренней проблемы. На экране красовались два одинаковых по смыслу запроса. Один — медленный, как сонный слак-бот, другой — стремительный, как "kill -9"

Когда Лена пришла в оговоренное время, Макс загадочно улыбнулся:
— Сегодня я покажу тебе, как JOINы превращают 10к строк в 150 миллионов, чтобы потом снова выдать 10к строк... и как это можно обойти. Это реальный случай, но мы разберем его на простом синтетическом примере.

— Ой, Макс, только не начинай снова про эти твои "простые примеры"! — Лена закатила глаза, но тут же заинтересованно придвинула стул. — В прошлый раз твой "синтетический пример" заставил мой комп 10 минут гудеть вентиляторами...

Макс улыбнулся и показал ей оригинальный запрос и план выполнения:

EXPLAIN ANALYZE
SELECT count(*), avg(t1.value), max(t2.value), avg(t3.value), u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table2 t2 ON t2.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;

Лена внимательно посмотрела на экран, и её брови поползли вверх:

— Погоди-ка... Это что, серьёзно?! — Лена ткнула пальцем в строку с actual rows=15000000. — Ты хочешь сказать, что из-за одного кривого JOIN'а мы таскаем в памяти пятнадцать миллионов строк, чтобы потом найти какой-то дурацкий максимум и потом вернуть 1000 строк?!

Макс одобрительно кивнул, скрывая улыбку — Лена начинала мыслить как настоящий performance-инженер.

— Ладно, гений, — Лена скрестила руки на груди, — и какое же твоё волшебное решение?
В её глазах читался вызов: "Ну-ка, удиви меня!"

Макс переключился на следующую вкладку, показывая переработанный запрос:

EXPLAIN ANALYZE
SELECT count(*), avg(t1.value), avg(t3.value), u.id
  (SELECT max(t2.value) FROM table2 t2 WHERE t2.user_id = u.id) -- Вот он, фокус!  
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;

— Видишь разницу? — его глаза загорелись азартом:
+ Время выполнения сократилось в 73 раза (с 48 сек до 0.65 сек)
+ Потребление памяти уменьшилось в 6 раз (с 5.7 Мб до 1 Мб)
Исходный запрос использовал дорогой HashAggregate с полным сканированием (Parallel Seq Scan), тогда как оптимизированный:
+ Точечно обращается к данным через индексы (Bitmap Index Scan)
+ Использует кэширование (Memoize)
+ Работает без параллелизации, но эффективнее

Лена нахмурилась:
— То есть вместо того чтобы сваливать в кучу все транзакции всех клиентов, а потом их сортировать, теперь мы просто смотрим максимальный платеж каждого клиента в подзапросе?

— Именно! — Макс щелкнул пальцами. — Первый подход — это как взять чеки у всех клиентов и потом раскладывать 15 млн чеков. Второй — заглянуть в картотеку каждого клиента, где уже есть нужная информация.

— Поняла, — Лена кивнула, — но почему оптимизатор сам не догадается до такого простого решения?

Макс усмехнулся:
— Потому что он как новый кассир — знает правила, но не знает лайфхаков. Наша задача — подсказать ему короткий путь.

Лена задумалась на секунду:
— Значит, когда нужно одно значение из большой таблицы — лучше использовать подзапрос. GROUP BY с огромными JOIN'ами — плохой вариант.

— Все именно так! — Макс удовлетворенно кивнул.

— Кстати, а если нужны будут несколько агрегатов из table2? Неужели придется делать кучу подзапросов?

Макс хитро улыбнулся:
— Об этом — поговорим в другой раз. А пока предлагаю поэкспериментировать с демо-стендом, так как практика в этом деле бесценна.

#explain

PS. Полный скрипт (таблицы, генерация данных и запросы) доступен по ссылке https://pastebin.com/yu4r8sb9
Попробуйте воссоздать ситуацию у себя в тестовой базе и пройтись по всем шагам самостоятельно.

🎓 100 уроков EXPLAIN. часть 5
CTE vs LATERAL

На следующий день Лена ворвалась в кабинет Макса с горящими глазами:
— Смотри, что я придумала! Тот случай с table2, я нашла быстрый способ получить несколько агрегатов без отдельных подзапросов!

На экране ее ноутбука красовался новый вариант запроса.

EXPLAIN ANALYZE
WITH aggregated_t2 AS (
    SELECT user_id,
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,  -- Новое поле!
        min(new_date_field) AS min_date     -- И ещё одно!
    FROM table2
    GROUP BY user_id
)
SELECT avg(t1.value), t2.max_val, t2.avg_num, t2.min_date, avg(t3.value), u.id
FROM users u 
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN aggregated_t2 t2 ON t2.user_id = u.id  -- Вот где магия!
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2.max_val, t2.avg_num, t2.min_date;

— Видишь? — торжествующе произнесла Лена. — Вместо трёх подзапросов — одно CTE! И план показывает, что данные из table2 берутся за один проход!

Макс одобрительно кивнул, скрывая улыбку. Лена явно демонстрировала успехи.
— Ты молодец! — сказал он, — Классный результат. Кстати, есть еще и альтернативный вариант.

— Есть еще варианты?

— Да, можно использовать LATERAL JOIN. Вот смотри.

EXPLAIN ANALYZE
SELECT avg(t1.value), t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date, avg(t3.value), u.id
FROM users u 
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN LATERAL (
    SELECT 
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,
        min(new_date_field) AS min_date
    FROM table2 t2
    WHERE t2.user_id = u.id
) t2_agg ON true
JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date;

— Хм и чем это отличается от моего варианта? — Нахмурилась Лена. — Ты мой подзапрос из CTE убрал за этот странный JOIN.

— Почти. Главное отличие, что теперь вместо группировки по user_id используется фильтрация по этому полю внутри подзапроса. Это очень важное преимущество lateral join - возможность обращаться к полям других таблиц запроса до соединения результатов.

— Интересный вариант. Спасибо, я запомню его. Кстати, план запроса у тебя получается компактнее, однако время выполнения немного больше моего.

— Да, потому что твой вариант с CTE читает большую таблицу линейно (Parallel Seq Scan) и дальше использует агрегацию в памяти (HashAggregate), а LATERAL хоть и использует индекс по user_id (Bitmap Heap Scan), но использует множество отдельных чтений. В этом разница между ними.

— Но все говорят, что seqScan это плохо, индексы же специально созданы для ускорения запросов!

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

🔹 CTE лучше подходит для массовой обработки
🔹 LATERAL JOIN - для точечных запросов с фильтрацией
⚠️ Золотое правило - Всегда смотри на план выполнения!

Лена задумчиво кивнула. В её глазах разгорался огонек настоящего исследователя баз данных.

#explain #lateral

PS. новые поля добавлены в скрипт генерации https://pastebin.com/yu4r8sb9. Рекомендую выполнить шаги из этого урока самостоятельно.

⏳Как избежать хаоса в параллельной обработке

В переговорке идет обсуждение новой доработки.
Таня (тимлид):
— Эту новую обработку поручений нужно обязательно распараллелить. Без дублей. Ваши предложения?

— Предлагаю добавить в таблицу payments поле locked_by. — оживилась Катя. — И каждый процесс будет обновлять это поле вот так:

BEGIN;
  -- Шаг 1: Читаем не обработанные данные.
  SELECT id FROM payments 
  WHERE status = 'new' AND locked_by IS NULL 
  -- Шаг 2: Захватываем запись
  UPDATE payments 
    SET locked_by = 'worker_123'
    WHERE id = <выбранная запись>;
  COMMIT; -- Чтобы другие процессы увидели, что запись обрабатывается!
  -- Шаг 3: Основная обработка ...
END;

— Мне не нравится промежуточный COMMIT — нахмурился Сергей — Если процесс упадёт на третьем шаге, запись останется заблокированной. Придется следить за "зависшими" платежками.

— И как быть с конкурентным доступом? — спросила Оля — Несколько процессов могут прочитать одинаковые записи с NULL до того, как один из них ее захватит!"

— Ребята, зачем изобретать велосипед? В Postgres уже есть готовые инструменты для этого. — перехватил инициативу Макс. — Вот так это будет выглядеть:

BEGIN;
  -- Берём  нужное число незаблокированных записей
  SELECT * FROM payments
  WHERE status = 'new'
  ORDER BY created_at  -- если нам важно для FIFO
  LIMIT 10
  FOR UPDATE SKIP LOCKED;  -- Магия здесь!

  -- Обрабатываем...
  COMMIT;  -- Записи автоматически разблокируются при завершении транзакции
END;

— И никаких флагов? — удивилась Катя. — Но как это работает?

— Тут простая магия — ответил Макс:

1. FOR UPDATE сразу блокирует прочитанные строки.
2. SKIP LOCKED пропускает уже заблокированные записи (они просто не попадут в результаты следующих запросов, пока действует блокировка)
3. LIMIT определяет количество блокируемых записей для каждого воркера.

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

— Хорошо! — похвалила Таня. — Есть какие-то особенности или ограничения, которые следует учесть?

— Конечно, они есть всегда — откликнулся Макс и начал загибать пальцы:
1. Нужно помнить, что SKIP LOCKED реально пропускает данные, т.е. это хороший инструмент для обработки событий, но совершенно не годится для анализа данных.
2. Этот подход не работает с некоторыми типами JOIN`ов и оконными функциями. При этом в сложных запросах можно явно указать блокируемую таблицу.
3. Если в обработке будут блокироваться другие таблицы в разном порядке - могут быть проблемы, вплоть до deadlock'ов. Но это скорее общая проблема параллельной обработки.

— Подожди, Макс. — задумалась Оля. — А что если обработка платежа занимает не 5 секунд, а 5 часов? Или если нужен ручной контроль менеджера?

— Да, для долгих операций SKIP LOCKED - не лучший выбор. — кивнул Макс. — Тогда лучше через поле locked_by. Для надежности тогда добавить поле locked_until, которое обновлять Heartbeat-механизмом. Это позволит отслеживать зависшие задачи.

— Не отвлекайтесь. — остановила их Таня. — у нас обработка каждого платежа будет очень быстрой, так что решено: используем FOR UPDATE SKIP LOCKED, быстро проверяем на тестовом стенде и идем в продакшн. Главное — ничего не сломать. Макс, проверка на тебе. Переходим к следующему пункту.

#best_practice #блокировки

🎓 100 уроков EXPLAIN. Часть 6
Как оптимизатор принимает решения

— Макс, ты уже говорил, что в некоторых случаях Seq Scan быстрее чем Index Scan! — напомнила Лена на следующий день. — Но везде пишут, что нужно избавляться от Seq Scan. Так как понять, когда Seq Scan хорошо, а когда плохо?

— А давай разберёмся, как Postgres вообще принимает такие решения. — отодвинул кружку с кофе Макс. — В этом нет никакой магии, обычная математика.

1. Как работает оптимизатор
Postgres оценивает планы через стоимость и статистику:

🔸 Стоимость: 1 cost = чтение 1 блока с диска - самая дорогая операция. Обработка в памяти в десятки и сотни тысяч раз быстрее
🔸 Статистика: для таблиц это прежде всего количество строк и информация о том, как распределены данные по столбцам:

-- Посмотрим статистику для таблицы orders:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'orders';

→ relpages = 100 (блоков), reltuples = 10000 (строк)

2. Почему Seq Scan в ряде случаев быстрее
Пример запроса:

SELECT * FROM orders WHERE status = 'completed';  -- Допустим, 80% от всех строк

Seq Scan:
- Читает все 100 блоков (cost = 100).
- Фильтрует строки в памяти (быстро).

Index Scan:
1. Читает индекс (допустим, 20 блоков, cost = 20).
2. Для каждой найденной индексом строки:
- Читает блок таблицы (80% строк = 8000 обращений × 1 cost ≈ 8000).
- Итого: cost ≈ 8020!

Вывод: Seq Scan (100) «дешевле», чем Index Scan (8020).
Конечно в процессе такого выполнения почти все блоки таблицы окажутся в кэше в памяти, и операция не будет реально эквивалентна чтению 8000 блоков. Но это уже отдельная тема.

— Действительно все просто и логично, — отметила Лена. — Выбирается вариант с наименьшими затратами.

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

— А что это такое "корреляция индекса"? — заинтересовалась Лена.

— Это такая статистика, которая показывает, насколько физический порядок строк в таблице соответствует реальному порядку в данных. Это важно, когда есть индекс и выборка идет по диапазону (BETWEEN). Может иметь значения от -1 до 1:
🔸 1.0 — идеальная кластеризация (строки в таблице лежат в логическом порядке).
🔸 0.0 — нет корреляции.
🔸 -1.0 — обратная кластеризация.

-- Посмотреть correlation для столбца:
SELECT tablename, attname, correlation FROM pg_stats 
WHERE tablename = 'orders' AND attname = 'created_at';

Эта информация используется для оценки стоимости Index Scan:
🔸 Если correlation ближе к 1.0, оптимизатор понимает, что Index Scan будет читать данные последовательно;
🔸 Если correlation ближе к 0.0, то Index Scan потребует много случайных чтений, что повышает cost.

Конечно, на современных SSD разница между последовательным и случайным чтением не такая большая, как на обычных дисках, но она все же есть.

— Кстати, есть возможность повлиять на этот параметр. — добавил Макс, пока Лена переваривала информацию. — Для этого есть команда CLUSTER. Но надо понимать, что выровняв данные по одному столбцу, корреляция по другим может сильно ухудшиться. Это имеет смысл только для редких случаев. К тому же эта команда блокирует таблицу, так что на проде такие эксперименты под запретом.

— Поняла! — Лена сделала заметку в блокноте. — Значит, главное не слепо гнаться за индексами, а понимать логику оптимизатора.

— Хорошо сказано! — Макс одобрительно кивнул. — Для закрепления предлагаю тебе поизучать статистику для известных тебе таблиц. В pg_stats еще много информации. Готовь вопросы - обсудим.

#explain #статистика

🎓 100 уроков EXPLAIN. Часть 7
Index Only Scan, и что показывают “heap fetches”

Утро. Лена зашла в кабинет Макса с блокнотом:
— Макс, привет! А что такое «сканирование только индекса»? Я видела в плане узел Index Only Scan. Расскажи, как он работает?

Макс повернулся к Лене:
— Доброе утро! Твое упорство и интерес меня восхищают.

— Ну мне правда интересно. — смутилась Лена. — Ну а все же, что это?

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

Он набросал простой пример:

EXPLAIN ANALYZE
SELECT login FROM users 
WHERE login LIKE 'z%';

План запроса:

Index Only Scan using users_login_uindex on users  (cost=0.28..156.60 rows=1 width=10) (actual time=2.690..2.709 rows=17 loops=1)
  Filter: ((login)::text ~~ 'z%'::text)
  Rows Removed by Filter: 5961
  Heap Fetches: 469
Planning Time: 0.144 ms
Execution Time: 2.745 ms

— Кажется классно, — кивнула Лена. — Получается таблица в таком случае вообще не нужна, достаточно только индекса?

— А вот это, Лена, самый коварный миф про Index Only Scan. Индекс хранит не всё. Обрати внимание на строку Heap Fetches.

— Вижу там цифры, а что они значат?

— Дело в том, что сам индекс не хранит информацию о видимости строк. Есть специальная карта видимости, куда VACUUM помечает «чистые» страницы — те, где все версии строк таблицы видны всем транзакциям. Если страница помечена, Postgres пропускает проверку в heap и сразу отдаёт данные из индекса. Если же пометка не стоит — он идёт в таблицу, проверяет видимость и увеличивает счётчик Heap Fetches.

— Ничего не понятно. — Честно призналась Лена.

— Смотри, таблица - это книга, индекс - глоссарий. Но в этой книге постоянно ведутся правки - кто-то стирает строки, кто-то дописывает новые и оставляет об этом отметки. Сам глоссарий содержит только слово для поиска и номера страниц, где оно встречалось. Но он сам не знает, зачистили ли эти страницы от пометок «старое» или «удалено». — Макс выжидающе смотрел на Лену.

— Продолжай.

— Чтобы не перелистывать каждую страницу (то есть не заглядывать в основную таблицу), Postgres ведёт отдельный список — «карта видимости». В этой карте отмечены страницы, которые точно чистые и в порядке.
🔸 Если страница в карте помечена как «чистая», то сразу берём данные из индекса и не ходим в таблицу.
🔸 Если страницы там нет, значит нужно проверить: заглянуть в таблицу и убедиться, что строка «видна» (это и считается одной «вынужденной проверкой» — Heap Fetch).

Чем чаще делается VACUUM, тем больше страниц помечено чистыми, и тем реже Postgres будет лишний раз заглядывать в таблицу. — подвел итог Макс.

— То есть, чтобы Heap Fetches ушли в ноль, нужен покрывающий индекс и актуальная карта видимости, правильно?

— Именно. В Postgres есть статистика, по которой это можно отслеживать - это поля relpages (всего страниц) и relallvisible (сколько видимых) в pg_class. Можешь потренироваться (строго на тесте) - засечь эти значения для какой-нибудь таблицы, затем выполнить VACUUM и посмотреть как они изменятся.

#explain #индексы

🚀 Неполный refresh Materialized View

— Вась, ты чего такой хмурый? — спросил Макс, заметив коллегу у кофе-поинта.

— Да вот, дали задачу, — вздохнул Вася. — Есть матвью, которая рефрешится почти полтора часа. А бизнес требует обновлять её каждые 10 минут, потому что, — он противным голосом передразнил Евгения Вадимовича, — «нам нужны максимально оперативные данные». А там данные по проводкам за 5 лет, оптимизировать уже некуда.

— Классика, — кивнул Макс. — Сделай им отдельную матвью и отдельный отчет на оперативном периоде.

— Если бы так просто. Требуют, чтобы данные были все в одном месте.

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

— Это мысль! — оживился Вася. — А основную матвью заменить на VIEW, которая их через UNION ALL объединяет! Но как избежать дублей и дыр в данных на стыке?

— Да, нужен буфер, — согласился Макс, подходя к доске. — Сделай матвью с небольшим пересечением по датам, чтобы застраховаться.

-- 1. Исторические данные
-- Обновляется редко (раз в сутки)
CREATE MATERIALIZED VIEW mv_historical AS
SELECT * FROM source_transactions
WHERE operation_date < (CURRENT_DATE - INTERVAL '7 days');

-- 2. Оперативные данные (с буфером в 1 день)
-- Обновляется часто
CREATE MATERIALIZED VIEW mv_current AS
SELECT * FROM source_transactions
WHERE operation_date >= (CURRENT_DATE - INTERVAL '8 days');

— Отлично, с матвью разобрались. А как теперь их правильно "склеить" во VIEW, чтобы не было дублей из-за буферного дня? — спросил Вася. — UNION без ALL не подойдет, т.к. объем данных огромный, производительность просядет сразу.

— А вот тут у нас есть два пути, — Макс нарисовал на доске две стрелки. — Выбирай любой.

— Первый способ — самый очевидный, — начал Макс. — Мы просто жёстко задаём границу во VIEW с помощью CURRENT_DATE.

CREATE OR REPLACE VIEW v_full AS
-- Берем все из оперативной части
SELECT * FROM mv_current
WHERE operation_date >= CURRENT_DATE - INTERVAL '8 days'; -- исключаем буферные данные
UNION ALL
-- И добавляем историю, отсекая по той же границе
SELECT * FROM mv_historical
WHERE operation_date < (CURRENT_DATE - INTERVAL '8 days');

— Выглядит просто, — кивнул Вася. — И должно быть быстро, планировщик легко поймет такое условие. А почему исключаем буферные данные из обеих матвью? тут нет ошибки?

— Тут есть нюанс — улыбнулся Макс. — Если наступит новый день, а ни одна из матвью не успеет обновиться, тогда будет дырка в данных. Поэтому такое хитрое условие.

— Есть еще и второй путь, — продолжил Макс. — Границу отсечения брать из самих данных.

CREATE OR REPLACE VIEW v_full_safe AS
-- Берем все из оперативной части
SELECT * FROM mv_current
UNION ALL
-- А историю отсекаем по МИНИМАЛЬНОЙ дате из оперативных данных
SELECT * FROM mv_historical h
WHERE h.operation_date < (SELECT min(t.operation_date) FROM mv_current t);

— Понял! — воскликнул Вася. — Граница определяется данными из оперативной матвью. Но... этот подзапрос в WHERE... Он же может свести с ума планировщик на сложных отчётах, верно?

— В точку, — одобрительно кивнул Макс. — Этот подзапрос может помешать оптимизатору правильно оценить объём данных и построить эффективный план.

Вася задумчиво посмотрел на доску.
— Так что же выбрать? Какой способ лучше?

— А это тебе и предстоит решить, — улыбнулся Макс. — Создай обе версии VIEW и протестируй на реальных отчётах с помощью EXPLAIN ANALYZE. Сравни планы и время выполнения. У тебя на руках будут цифры, а с ними — и осознанное решение. Нет идеальных рецептов, есть только подходящие для конкретной задачи.

#кейс #матвью

🎓 100 уроков EXPLAIN. Часть 8
Битва JOIN'ов — Hash vs Nested Loop vs Merge

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

— Макс, я снова с EXPLAIN'ом. Смотри, какая странность! — она развернула свой ноутбук. — Вот запрос с JOIN, он летает. Я написала почти такой же, но поменяла одно условие в ON — и он стал в 10 раз медленнее! План совершенно другой.

Макс отхлебнул утренний кофе и взглянул на экран. На одном плане красовался знакомый Hash Join, на другом — какой-то Nested Loop Join.

— Поздравляю, — усмехнулся он. — Ты только что столкнулась с большой тройкой стратегий соединения таблиц. Postgres не просто выполняет JOIN, он сначала выбирает, как именно его выполнить. А твое условие и статистика — ключ к его выбору.

Макс взял маркер, подошел к доске и начал рисовать.

1. Nested Loop Join
— Это самый простой и «тупой» способ. Postgres берёт первую запись из внешней таблицы и начинает просматривать всю внутреннюю таблицу в поисках совпадений. Потом берёт вторую запись и так далее.
— Когда хорош: Когда внутренняя таблица очень маленькая (например, 5 строк). Тогда «пробежаться» по ней недолго. Или когда для поиска во внутренней таблице есть очень селективный индекс.
— Когда плох: На больших таблицах это катастрофа. Две таблицы по 100 тысяч записей дадут 10 миллиардов итераций.

2. Hash Join
— Это твой старый знакомый. Postgres сначала берёт меньшую из двух таблиц и строит по ней в памяти «справочник» — хеш-таблицу. А потом просто прогоняет по этому справочнику вторую таблицу, мгновенно находя совпадения. Как в том мультфильме - день потерять, потом за 5 минут долететь. Для 5 записей строить хеш-таблицу невыгодно, а для тысяч - очень даже.
— Когда хорош: Когда таблицы большие и соединяются по условию равенства (=). Это самый частый и эффективный способ.
— Ограничение: Работает только для равенства. Для условий вроде > или LIKE он бесполезен.

3. Merge Join
— А это самый хитрый способ. Если обе таблицы отсортированы по ключу соединения, Postgres может соединить их за один проход, просто идя по обоим спискам одновременно, как застёжка-молния.
— Когда хорош: Когда соединяются большие, уже отсортированные таблицы (например, по выходу из Index Scan).
— Ограничение: Требует сортировки. Если данные не отсортированы, то узел Sort может съесть всю выгоду.

Лена хлопнула себя по лбу:
— Точно! В быстром запросе у меня было t1.user_id = t2.user_id, и он выбрал Hash Join. А в медленном — t1.value > t2.limit, и для этого неравенства Hash Join не подошел. Ему пришлось использовать медленный Nested Loop!

Макс кивнул:

— Это закономерно: для неравенства Hash Join не работает. Но вот где главное — Nested Loop не всегда беда. Проблема часто не в самом JOIN, а в том, что планировщик не угадал, сколько реально вернёт строк и выбрал неподходящий алгоритм.

— Да? — удивилась Лена.

— Вот смотри: если в плане у Nested Loop написано rows=2, а на деле actual rows=1500 — это тревожный признак. Планировщик думает, что с каждой внешней строки получит всего пару совпадений, а реально их гораздо больше. Потому и делает медленный вложенный цикл. Чаще всего причина такого - устаревшая или неточная статистика.

— А как понять, что дело именно в статистике?

— По разнице между ожидаемым (rows) и реальным (actual rows). Если actual rows сильно больше ожидаемого, значит пора запускать ANALYZE — статистика не отражает реальную картину. Планировщик ошибается в выборе стратегии, думая, что оптимальный путь — Nested Loop.

— Получается, не всегда условие виновато? Иногда плохой план — это просто сбившаяся статистика?

— Да. статистика очень важна для выбора правильного алгоритма соединения. Это как информация о длине улиц и пробках для навигатора.

— Поняла. Теперь буду внимательнее к статистике. На всякий случай пробегусь по таблицам с ANALYZE!

Макс улыбнулся:
— Обычно база сама заботится о статистике через autovacuum. Руками ANALYZE нужен только при массовых изменениях или явных проблемах с производительностью.

#explain

⚖️ Искусство «разбора полётов»
Как найти причины и не найти виноватых

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

— Ладно, проехали, — сказала Таня, тимлид, на утреннем стендапе. — Главное, что всё работает. Не будем тратить время, у нас куча задач на спринт.

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

— Минутку, — вмешался Макс, спокойно отпивая кофе. — Я думаю, время как раз потратить стоит. Предлагаю в 11 собраться в переговорке на полчаса. Проведём post-mortem.

В воздухе повисло неловкое молчание. Слово «post-mortem» прозвучало как «вызов на допрос».

— Макс, да что там разбирать? — не выдержала Таня. — Сергей ошибся, мы поправили. Все устали, не надо никого тыкать носом.

— А я и не собираюсь, — ответил Макс. — Моя цель — не найти виноватого, а понять, почему наша система позволила этой ошибке случиться.

В 11:00 в переговорке было тихо, как в библиотеке. Сергей сидел, скрестив руки, готовый к обороне.

— Итак, — начал Макс и подошёл к доске. — Прежде чем мы начнём, одно правило.

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

— Цель этой встречи, — продолжил он, — сделать нашу систему сильнее. Давайте разберём не «кто», а «почему». Итак, что случилось?

— Сервис упал из-за NullPointerException, — буркнул Сергей. — В поле userProfile пришёл NULL, а код этого не ожидал.

— Окей, это триггер. А теперь давайте раскрутим цепочку, — Макс нарисовал первую стрелку. — Почему в коде не было проверки на NULL?

— Я был уверен, что профиль есть всегда, — ответил Сергей. — В требованиях не было указано, что он может отсутствовать. Меня торопили с задачей, я не стал добавлять избыточных проверок.

— Понятно, — сказал Макс. — Это не ошибка, это фактор №1: неполные требования и спешка.

Он нарисовал вторую стрелку. — Почему тесты пропустили такой сценарий?

— У нас в тестовых данных у всех пользователей есть профили, — вступила Оля, тестировщик. — Скрипт генерации тестовых данных старый, он не покрывает этот случай.

— Фактор №2: устаревшая тестовая среда. Записал. Идём дальше. Почему мы узнали о проблеме от пользователей, а не от мониторинга?

— Алерт сработал, но уже когда сервис перестал отвечать на health-чеки, — ответил Макс сам себе. — У нас нет алерта на конкретную ошибку в логах. Фактор №3: неэффективный мониторинг.

Напряжение в комнате начало спадать. Стало очевидно, что дело не только в строчке кода Сергея.

— И последний вопрос, — сказал Макс. — Почему мы выкатывали критичное изменение в сервис авторизации в пятницу в 15:00?

Таня вздохнула.
— Потому что маркетинг обещал клиентам запуск акции на выходных. Было давление сверху.

— Фактор №4: нарушение внутренних процессов под давлением бизнеса.

Макс обвёл все четыре пункта на доске.
— Смотрите. Падение — это не вина Сергея. Это результат совпадения четырёх факторов. Любой из них мог бы его предотвратить. Наша задача — исправить систему, чтобы эта цепочка больше не собралась.

Дальше встреча пошла конструктивно. За 15 минут команда накидала конкретные задачи:

💻 Код: Добавить проверку на NULL в сервис. отв.: Сергей.

🧪 Тесты: Обновить скрипт генерации тестовых данных. отв.: Оля.

📈 Мониторинг: Настроить алерт на NullPointerException в логах сервиса. отв.: Макс.

📋 Процессы: Внести в регламент обновлений раздел "Критичные сервисы" и правило, запрещающее релизы критичных сервисов в нерегламентное время. отв.: Таня.

Когда все расходились, атмосфера была совершенно другой. Вместо поиска виноватых команда работала вместе над улучшением системы.

Сергей подошёл к Максу.
— Спасибо. Я думал, меня сейчас будут распинать.

— Запомни, — ответил Макс, — хороший инженер не тот, кто не ошибается, а тот, кто делает выводы из ошибок, чтобы система стала надёжнее. Сегодня мы все стали немного лучшими инженерами.

#процессы

NOT EXISTS vs NOT IN vs анти-LEFT JOIN

— Запрос тормозит, — выдохнул Алекс, — Использую NOT IN, чтобы отсеять ненужные записи.

— Попробуй NOT EXISTS. Все знают, он быстрее. — посоветовала Ольга, сидевшая напротив.

Макс, проходивший мимо, остановился с улыбкой:
— А что, если я скажу вам, что это — главный миф PostgreSQL, а настоящая ловушка скрыта в вашем NOT IN?

Он опёрся о стол.
— Давайте поиграем в детективов. Подозреваемый №1: миф, что NOT EXISTS быстрее, чем LEFT JOIN + IS NULL. Когда-то так и было, но современный PostgreSQL умнее. Ему важна не форма, а суть: найти строки без пары в другой таблице. Для него оба варианта — это команда: "выполни анти-соединение".

Алекс набросал два варианта своего запроса и запустил EXPLAIN для обоих. На его лице проступило удивление. «Планы… идентичны. Hash Anti Join в обоих случаях».

— Именно, — подтвердил Макс. — Миф разрушен. Поэтому NOT EXISTS или LEFT JOIN + IS NULL — это вопрос стиля, а не скорости.

— А теперь — к настоящему виновнику, — голос Макса стал серьезнее. — Ваш NOT IN. Он коварен. Что будет, если в таблице, по которой вы исключаете записи появится хотя бы один NULL?

—Запрос его проигнорирует? — предположил Алекс.

— Хуже, — ответил Макс. — Логика SQL превращает условие id NOT IN (1, 2, NULL) в id <> 1 AND id <> 2 AND id <> NULL. А любое сравнение с NULL дает результат UNKNOWN. И строки, для которых условие UNKNOWN, в выборку не попадают. Ваш запрос не сломается. Он молча вернет ноль строк. Это мина замедленного действия.

Алекс откинулся на спинку стула и медленно выдохнул.
— Вот это да. Я лет десять писал NOT IN, уверенный, что это нормально. И всегда ругал LEFT JOIN за громоздкость.

— Идея не в том, чтобы запомнить, что «NOT EXISTS и LEFT JOIN — молодцы, а NOT IN — плохой». — Улыбнулся Макс. — Главное — понять, почему. И всегда спрашивать: «А что, если здесь будут NULL?». И, конечно, проверять с помощью EXPLAIN.

«Выбирайте, что вам ближе: NOT EXISTS или LEFT JOIN», — подытожил Макс. — «Они надежны. А NOT IN оставьте для случаев, когда вы готовы поклясться на своей любимой кружке, что в данных никогда не будет NULL. Но мы-то с вами знаем: в данных нельзя быть уверенным ни в чём».

#explain #МифыSQL

🎓 100 уроков EXPLAIN. Часть 10
Загадочный Bitmap Scan

Лена вошла в кабинет Макса, но на этот раз без паники в глазах. На её лице было скорее любопытство исследователя, наткнувшегося на неопознанный артефакт.

— Макс, я тут смотрела один из наших старых запросов. Он работает быстро, но план меня смутил. Там написано Bitmap Heap Scan, а под ним — Bitmap Index Scan. Это как вообще? Он что, не смог выбрать между индексом и полной проверкой таблицы и решил сделать и то, и другое?

Макс отложил клавиатуру и улыбнулся.
— Ты наткнулась на самый элегантный компромисс, который придумали Postgres. Это не нерешительность, это стратегия. Помнишь наши аналогии про трактор (Seq Scan) и спорткар (Index Scan)?

Лена кивнула.

— Так вот, представь, что тебе нужно собрать урожай не со всего поля, а, скажем, с 30% грядок, которые разбросаны по всей территории.

Трактор (Seq Scan) будет обрабатывать всё поле целиком. Долго.

Спорткар (Index Scan) будет мотаться к каждой грядке отдельно. Тысячи поездок туда-сюда убьют время и бензин.

— И что делать? — Лена прищурилась.

— А теперь представь, что мы сначала запускаем дрон. Он пролетает над полем, находит все нужные грядки и составляет их точную карту. А уже потом по этой карте мы отправляем трактор, который едет по оптимальному маршруту от точки к точке, собирая урожай.

Bitmap Scan работает именно так:

Bitmap Index Scan (Дрон): Postgres бежит по индексу и не дёргает таблицу. Вместо этого он создаёт в памяти «карту» — битовую маску страниц, на которых есть нужные нам строки.

Bitmap Heap Scan (Трактор с картой): Затем он берёт эту карту и последовательно, по порядку, читает только нужные страницы из таблицы. Это гораздо быстрее случайных «прыжков» спорткара.

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

— Бинго! — подтвердил Макс. — Это золотая середина. И знаешь, что самое крутое? Дрон может облететь поле по нескольким картам от разных агрономов. Bitmap Scan умеет комбинировать результаты нескольких индексов для одного сканирования. Но это уже совсем другая история.

📌 Что запомнить:
🔸 Bitmap Scan — это двухэтапный процесс: сначала создаём карту по индексу, потом читаем по ней таблицу.
🔸 Это гибрид: эффективнее, чем Seq Scan (читаем не всё) и быстрее, чем Index Scan (избегаем хаотичных чтений с диска).
🔸 Идеален для запросов, которые возвращают «среднее» количество строк — уже слишком много для точечного поиска, но ещё слишком мало для полного сканирования.

#explain #индексы

🎓 100 уроков EXPLAIN. Часть 11
Загадочный Recheck Cond — доверять или проверять?

— Макс, я опять с планом, и он меня смущает, — Лена сдвинула на экране окно с результатами EXPLAIN. — Вот смотри: Bitmap Heap Scan, а под ним Recheck Cond. Выглядит так, будто Postgres сначала нашел что-то по индексу, а потом усомнился в себе: «А вдруг я ошибся? Дай-ка перепроверю». И тут в плане еще Heap Blocks: exact=502 lossy=1621. Как на это реагировать? Просто махнуть рукой и довериться оптимизатору?

Макс усмехнулся.
— Ты задала один из самых тонких вопросов. Это не недоверие, а наоборот — признак умной и хорошо продуманной подстраховки.

— Подстраховки от чего?

— От неидеальности мира, — Макс сделал глоток кофе. — Помнишь наш дрон, который составлял карту урожая (Bitmap Index Scan)? Представь, что поле огромное, а память под карту (work_mem) у нас ограничена. Дрон не может отметить на карте каждый отдельный куст.

Он сделал паузу, давая Лене время представить картину.

— Когда ему не хватает work_mem, он обводит на карте целые сектора (блоки), где точно есть нужные нам кусты. Но в этих секторах могут расти и другие растения. Такая карта называется неточной, или lossy. И вот когда наш трактор (Bitmap Heap Scan) приезжает в отмеченный сектор, он вынужден перепроверять (Recheck Cond) каждый куст, чтобы собрать только нужные.

— Ааа, то есть это плата за то, что у нас была неточная карта? — осенило Лену.

— Именно! Это не провал оптимизатора, а его штатный план «Б». Он сэкономил память на карте, но заплатил за это дополнительной проверкой на месте. В плане он это выделяет:
▪️ Heap Blocks: exact — это страницы, для которых карта содержит точную информацию о размещении каждой подходящей строки.
▪️ Heap Blocks: lossy — это “грубые” сектора карты. В памяти не хватило места, чтобы обозначить каждую строку, и Postgres вынужден помечать целую страницу целиком: “Может, здесь есть что-то подходящее — перепроверь вручную”.

— И что мне с этим делать?

— Вот тут самое главное. Не паниковать, а смотреть в EXPLAIN ANALYZE на строку Rows Removed by Index Recheck. Если там маленькое число, значит, всё отлично. Карта была почти точной. А вот если там тысячи отброшенных строк, это сигнал: наш трактор тратит слишком много времени на добычу и проверку «мусора».

— Поняла. Но у меня там как раз очень большое число! Почему?

— Ну это может означать, что индекс не очень хорошо подходит для этого запроса, и карта получается слишком «грязной». А может это сигнал, что в таблице накопились “мертвые” строки, удаленные или обновлённые, но ещё не убранные автовакуумом. Если страница полна мусора, точность битмапы падает: Postgres “не видит” какие строки актуальны, и приходится читать лишнее.

📌 Что запомнить:
🔸 Recheck Cond — это не ошибка, а плановая перепроверка для неточных (lossy) битовых карт.
🔸 Много lossy-блоков — нехватка памяти для построения точной карты (work_mem).
🔸 Чрезмерный Rows Removed by Index Recheck — повод проверить селективность индекса, а также проверить работает ли autovacuum, не растет ли таблица быстрее, чем Postgres успевает её подчищать?

#PostgreSQLДляНачинающих #EXPLAIN

PS: В комментариях есть пример, как получить план с Bitmap Index Scan. Добавил пример - как воспроизвести его с Recheck Cond и как повлиять на него.

#explain #индексы

🎯 Индекс для избранных

Лена, ведущий аналитик, уже час смотрела в монитор, как в бездну.

— Макс, это парадокс, — сказала она, когда Макс подошёл с неизменной кружкой кофе. — Я построила индекс, чтобы ускорить поиск. А он замедлил всё. Вставку. Обновление. И даже сам поиск.

Макс молча взглянул на её экран. Таблица operations, 500 миллионов строк. Индекс по полю status, занимающий 25 гигабайт.

— Какой статус ты ищешь? — спросил он, хотя уже знал ответ.
— active, — выдохнула Лена. — Но их там доли процента. Остальные 99.9% — это completed.

Макс кивнул. Он сделал глоток кофе и посмотрел на Лену. Не как на коллегу, а как на соучастника фундаментального заблуждения.

— Ты ищешь иголку в стоге сена. Тогда зачем ты индексируешь сено?

Лена моргнула. Вопрос был настолько простым, что казался абсурдным.
— Но… индекс работает по всей колонке. Так устроены базы данных.

— Так устроен мир по умолчанию, — поправил Макс. — Мир, который пытается быть справедливым ко всем и в итоге неэффективен ни для кого. Твой индекс — это огромная, подробная карта города, где отмечен каждый дом. Но ты каждый день ходишь только в одно здание. Зачем тебе карта всего города? Тебе нужен прямой, короткий путь к единственной нужной двери.

Он придвинул её клавиатуру. Пальцы легко пробежали по клавишам.
— Мы не будем составлять карту для всех. Мы создадим её только для избранных.

CREATE INDEX idx_operations_only_active
ON operations (status)
WHERE status = 'active';

— Этот индекс, — Макс показал на экран, — проигнорирует 499 миллионов твоих completed-записей. Он будет существовать только для тех немногих, кто действительно важен прямо сейчас. Он будет крошечным. Мгновенным. И не будет мешать остальным.

Он нажал Enter. После того как команда выполнилась Макс бросил взгляд на статистику:
- Старый индекс:25 Гб 🔴
- Новый индекс: 100 Мб 🟢

— Обнови свой дашборд, — тихо сказал Макс.

Лена нажала F5. Графики, которые раньше вырисовывались несколько секунд, появились раньше, чем она успела убрать палец с клавиши.
Она молчала.

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

#best_practice #индексы

🎓 100 уроков EXPLAIN. Часть 12
Index Cond vs. Filter: битва за эффективность

— Макс, я сломала мозг, — подошла Лена. — Смотри: у меня есть составной индекс по (status, created_at). Я делаю запрос с условиями по обоим этим полям. Но в плане запроса поле status попадает в Index Cond, а created_at — в Filter. Почему он не использует индекс целиком? Это же неэффективно!

Макс отвлекся от своего монитора.
— Поздравляю. Ты только что нашла «серую зону» оптимизации, где запрос вроде бы использует индекс, но не на все сто. Это как купить швейцарский нож и использовать его только для открывания бутылок.

— То есть Filter — это плохо?

— Ну не то чтобы это плохо, но это важный сигнал. Давай на аналогии. Представь, что индекс — это картотека в библиотеке.
🔸 Index Cond (условие индекса): Ты подходишь к ящику с буквой «Т», потому что ищешь Толстого. Это мгновенный поиск. Ты сразу отсекаешь огромную часть книг.
🔸 Filter (фильтр): А теперь ты достаешь всю огромную пачку карточек на «Толстой» и начинаешь вручную перебирать их в поисках той, где год издания равен заданному. Это уже дополнительная работа. Это все еще быстрее, чем обыскивать всю библиотеку, но медленнее, чем если бы у тебя был отдельный ящик для «Толстой, 1900».

Лена кивнула:
— Да это я понимаю. Index Cond — это навигация, Filter — это перебор. Но почему created_at ушло в Filter, если оно входит в мой индекс?

Макс развернул её ноутбук.
— Покажи запрос.

SELECT *
FROM orders
WHERE status = 'processed'
  AND date_trunc('month', created_at) = '2025-08-01';

— Вот! — воскликнул он. — Ты ищешь не по created_at, а по функции от него. Индекс хранит точные значения created_at, но ничего не знает о результате date_trunc. Поэтому Postgres делает так:
1. Index Cond: status = 'processed': Быстро находит по индексу все строки с нужным статусом.
2. Filter: date_trunc('month', created_at) = ...: Для каждой найденной строки он вычисляет функцию и проверяет, подходит ли она под условие.

📌 Когда ещё условие попадает в Filter?
🔸 Неявное приведение типов: Если у тебя поле phone текстовое, а ты ищешь WHERE phone = 79991234567 (без кавычек), Postgres будет вынужден приводить тип для каждой строки, и индекс не сработает для прямого поиска.
🔸 Поле в INCLUDE индекса: Если ты создала индекс ... ON t(a) INCLUDE (b), то поле b не является частью ключа поиска. Условие WHERE b = 5 уйдет в Filter.
🔸 Не «левая» часть индекса: Если индекс создан по (city, street), а ты ищешь WHERE street = 'Ленина', планировщик не сможет «перепрыгнуть» через city. Он будет сканировать весь индекс и применять Filter.
🔸 Частая ловушка — когда условия включаются через OR или NOT. Индекс использует только то, что соответствует структуре (обычно первое поле в ключе), а всё остальное фильтруется вручную.

— Так что же, я зря создавала составной индекс? — расстроилась Лена.

— Нет! Ты все еще можешь его использовать эффективно. Например, перепиши условие по created_at без использования функции:

SELECT *
FROM orders
WHERE status = 'processed'
  AND created_at BETWEEN '2025-07-01'::date AND ('2025-07-01'::date + interval '1 month') ;

— Если же нужно использовать именно условие по функции, то можно создать подходящий индекс:

CREATE INDEX ON orders (status, date_trunc('month', created_at));

С таким индексом оба твоих условия попадут в Index Cond, и Filter исчезнет.

📌 Что запомнить
🔸 Index Cond: Прямое использование индекса для навигации. Это высшая лига.
🔸 Filter: Дополнительная проверка строк, уже полученных после сканирования по Index Cond.
🔸 Появление Filter — это не катастрофа, а подсказка: возможно, твой индекс или запрос можно улучшить.

#explain #индексы