🪦 Кладбище забытых индексов

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

Вася горько вздохнул:
— Ха-ха. Прикинь до какого маразма довели — наша заявка уже вторую неделю находится на согласовании у инфобеза. Видимо оценивают, как добавление дисков к базе отчётности привлечет внимание хакерских группировок.

Макс покачал головой:
— А свободное место на диске как было, так и осталось на грани?

— Да, и постоянно уменьшается, — Вася выдохнул. — Но я уже не удивляюсь. Помню как в прошлом году месяц пытался получить права на просмотр логов. До сих пор помню эти бессмысленные переписки и анкеты. А потом вдруг у какого-то начальника не сформировался отчет, подняли шум, и доступ предоставили за 15 минут.

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

— Мёртвых? — Вася нахмурился. — Что ты имеешь в виду?

— Когда я изучал, что у вас там занимает место, заметил, что у многих таблиц по 10 и больше индексов. Я понимаю, что отчётность, аналитика и всё такое, но это выглядит сильно избыточным. Напомни, у вас статистика автоматически собирается?

— Да, каждое последнее воскресенье месяца. Прошлый раз был три с половиной недели назад, — ответил Вася, почесав затылок.

— Отлично. Три недели — это уже достаточно. Смотри сюда. — Макс развернул ноутбук и открыл скрипт:

SELECT s.schemaname, s.relname table_name, s.indexrelname index_name,
       s.idx_scan AS index_scans, pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       pg_get_indexdef(s.indexrelid) AS index_definition, i.indisvalid, i.indisprimary
FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;

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

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

— Ну всё же надо посмотреть на каждый индекс внимательно. Подготовить список для удаления и разослать коллегам. Чтобы не бегали потом с криками «Кто удалил? Прибью когда найду!»

Вася кивнул:
— Спасибо! Теперь у меня есть новая цель.

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

— Угу, — кивнул Вася. — Кто бы мог подумать, что забытые и никому не нужные индексы занимают столько места.

Макс отставил кружку на стол и потянулся:
— Да, всё как в жизни. Мы с возрастом тоже так накапливаем. Вроде бы тащим с собой какие-то обиды, надежды, представления о том, как должно быть. Кажется, что это важно, но на деле — только место занимает.

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

#кейс #индексы #хранение

⏳INSERT`ы тормозят

Макс только открыл мониторинг и сделал глоток кофе, как вдруг у него зазвонил телефон.

— Привет, это Сергей из поддержки BI. У нас, с базой что-то не так — инсёрты тормозят.
— Поподробнее. — Макс всё ещё не переключился с текущего графика в Zabbix.
— Очередь сообщений в шине накапливается, очень медленно идет вставка данных в базу.
— Минуту. — Макс подключился к нужной базе, заглянул в pg_stat_activity, в мониторинг шины.
— Диски, память, проц в порядке, это мы посмотрели. Блокировок тоже нет. — Между тем сообщил Сергей.

Макс открыл pg_stat_user_tables, отсортировал по n_dead_tup (количество "мертвых" строк) и с удивлением посмотрел на верхние записи с значениями в несколько десятков миллионов:
— Ого. А вот и причина.
— Что там? — Оживленно спросил Сергей.
— Да у вас там мильёны мертвых строк. Автовакуум вроде работает, но видимо не справляется. Погоди еще минуту.

Макс запустил вручную VACUUM (VERBOSE, ANALYZE). Ответ из базы был математически точен и бесполезен:
DETAIL: cannot remove dead tuples: 36M, oldest xmin: 1411878869

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

Он вывел список активных транзакций:

SELECT pid, now() - xact_start AS duration, state, usename, client_addr, application_name, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND state != 'idle'
ORDER BY duration DESC;

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

— Почему-то разработчики любят обсуждать утечки памяти, но зато BEGIN без COMMIT их обычно не очень-то волнует! — В сердцах возмутился Макс. Он быстро подключился к нужному серверу и перезапустил нужную службу. Отметив, что как раз три дня назад она и была обновлена.
После этого повторный VACUUM отработал как по маслу за несколько секунд.

— Ты что-то сделал? — Радостно сказал Сергей. — У нас все полетело как на ракете.

Макс рассказал ему причины, допил остывший кофе и с философским спокойствием заметил:
—Так и живём: одни пишут BEGIN, другие ищут, кто забыл COMMIT, а база, как невестка у свекрови, — терпит, пока может. В общем передай разрабам, что транзакции — не кредиты, закрывать их надо вовремя. И настройте мониторинг, а то однажды база вам «Out of Memory» подкинет.|

#кейс #транзакции

📩 Обычный рабочий день, Макс допивал очередной стакан кофе.
Тут в общий чат поддержки прилетело сообщение от Арсена:

— Зацените заявку. Всем отделом ломаем головы, как лучше ответить:
“Пожалуйста, улучшите память моей почты”
😂😂😂

Спустя минуту в чат посыпались варианты:

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

«Память почты — как память человека: чем больше в ней мусора, тем медленнее она работает. Рекомендуем провести цифровую медитацию и отпустить старые письма.»

«Ваш почтовый ящик, вероятно, уже все помнит. Возможно, даже слишком хорошо. Если вы хотите, чтобы он сосредоточился на текущих задачах, а не на исторических исследованиях, вот как можно аккуратно подчистить его ‘воспоминания’.»

Макс любил такие моменты, поэтому отправил в чат свой вариант:

Диагноз подтверждён: ваш почтовый ящик страдает от тяжёлой формы PTSD (Post-Traumatic Spam Disorder).
Рекомендуемое лечение:
- Экстренная детоксикация (удалить всё, где тема начинается с «FW: FW:»)
- Курс цифрового минимализма (оставить только жизненно важные письма)
- Профилактика рецидивов (настроить автоудаление старых писем)
Прилагаем инструкцию по терапии. Прогноз благоприятный, если начать сегодня (пока почтовый ящик не потребовал отпуск за свой счёт)

🤔 А как бы вы ответили на такую заявку?

🧹 Загадка пустых байтов

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

— Макс, ты не поверишь! Я нашёл ещё один способ освободить место!

Макс медленно отложил кружку, оценивая Васину одержимость.

— Ну-ка, показывай, что ты там на этот раз нашел.

— Смотри, — Вася развернул ноутбук и ткнул пальцем в экран. — В нашу базу для отчетности сливаются таблицы, которые содержат всякие файлы — PDF-ки, Excel и всякие сканы. Но для формирования отчётов они не нужны! Я уже всем всё написал и согласовал их удаление. Даже занулил уже эти поля через UPDATE SET NULL, но…

— Но размер таблиц не уменьшился? — Макс ухмыльнулся.

— Да! Как так? Я же по сути удалил данные!

Макс развернул к себе экран и быстро набрал запрос:

SELECT pg_size_pretty(pg_relation_size('avent.enclosures')) AS table_size,
       pg_size_pretty(pg_indexes_size('avent.enclosures')) AS indexes_size,
       pg_size_pretty(pg_total_relation_size('avent.enclosures')) AS total_size;

— Вот смотри, — он указал на цифры. — Таблица весит 2 Гб, индексы — 0,5 Гб, а реально места на диске занимает 1300 Гб! Ты занулил bytea-поля, но Postgres не освобождает это место просто так.

— То есть я зря старался? — Вася поник.

— Не совсем. Просто UPDATE, как и DELETE, не уменьшает физический размер таблицы. Postgres помечает старые данные как "мертвые", но они остаются на диске.

— И что делать?

Макс усмехнулся:

— Есть три пути:

✅ VACUUM FULL – перезапишет таблицу, освободив место, но заблокирует её на время.

✅ pg_repack – делает то же самое, но без блокировок (но должно быть установлено расширение).

✅ Создать новую таблицу и перелить данные – долго, но безопасно.

— А если я просто сделаю VACUUM без FULL? У нас же работает автовакуум.

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

Вася задумался:

— Ладно, VACUUM FULL в рабочее время делать рискованно, pg_repack у нас не установлен, перезагрузку базы согласовывать хлопотно… А если я скопирую нужные данные в новую таблицу, а старую дропну?

— Рабочий вариант, — кивнул Макс. — Только не забудь проверить, что никто не ссылается на эти данные через FK или в коде. Также вполне рабочий вариант — запустить VACUUM FULL через планировщик в нерабочее время, но надо учесть все ваши сервисные процессы ночью.

— Понял! — Вася уже лихорадочно строчил запросы.

— Кстати, — Макс поднял бровь, — а ты уверен, что эти файлы в основной системе вообще должны храниться в БД? Может, их лучше в объектное хранилище вынести?

— О! — Вася замер с открытым ртом. — Ты гений!

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

— …Потому что это потребует изменений в 100500 сервисах и полгода согласований?

— Бинго.

Вася вздохнул:

— Ладно, начну с VACUUM FULL. Пару терабайт точно выиграю.

Макс одобрительно кивнул:

— Главное — не увлекайся. А то скоро начнёшь сжимать VARCHAR до CHAR и партицировать системные таблицы.

— Эээ… а это плохо?

— Это путь в ад, Вася. В ад.

#кейс #хранение

🛠Миллион причин делать сразу правильно

— Макс, можешь помочь? — в дверь кабинета заглянул встревоженный разработчик Кирилл. — После прошлого обновления продакшн лег на полчаса, а сейчас нужно добавить ещё одно NOT NULL поле в таблицу с 300 млн записей. Начальник сказал сначала проконсультироваться с тобой.

Макс отложил кружку с кофе и тяжело вздохнул:
— Кирилл, кто-то же тебе говорил, что ALTER TABLE на 300 млн строк — это как прыгать с парашютом без инструктажа?

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

Кирилл отправил сообщение. Макс на секунду задумался, развернул монитор и начал рисовать схему:

1️⃣ Добавляем колонку БЕЗ NOT NULL

ALTER TABLE transactions ADD COLUMN external_id VARCHAR(255) NULL;

— Это займёт секунды, — пояснил Макс. — Потому что Postgres просто добавляет метаданные, не трогая сами данные.

2️⃣ Заполняем данные ПАКЕТАМИ
Макс написал скрипт:

DO $$
DECLARE
  batch_size INT := 50000;  -- 50K записей за раз
  max_id INT := 300000000;
  processed INT := 0;
BEGIN
  WHILE processed < max_id LOOP
    BEGIN
      UPDATE transactions 
      SET external_id = generate_external_id(id)
      WHERE id >= processed AND id < processed + batch_size
        AND external_id IS NULL;  -- на случай перезапуска
        
      COMMIT;  -- Фиксируем каждую порцию      
      
      RAISE NOTICE 'Обработано %/%', processed, max_id;      
    EXCEPTION WHEN OTHERS THEN
      RAISE WARNING 'Ошибка в пакете %: %', processed, SQLERRM;
      COMMIT;  -- Важно: выходим из транзакции при ошибке
    END;    
    -- Даём БД передохнуть
    PERFORM pg_sleep(0.2);
    processed := processed + batch_size;
  END LOOP;
END $$;

3️⃣ Финализируем: NOT NULL ночью (перед этим прогоняем скрипт на новых появившихся записях перед обновлением)

ALTER TABLE transactions ALTER COLUMN external_id SET NOT NULL;

— Если generate_external_id() — тяжелая функция, лучше сначала подготовить данные во временной таблице, тогда делать UPDATE через JOIN.

— Почему так заморочено?

Макс терпеливо объяснил Кириллу:
— Загибай пальцы. Избегаем длительных блокировок: одна большая транзакция блокирует таблицу на всё время выполнения, а короткие транзакции освобождают блокировки между пакетами. Снижаем нагрузку на WAL: Postgres не будет накапливать гигабайты WAL-логов, меньше нагрузка на репликацию. Возможность мониторинга и отмены, т.к. видим прогресс через NOTICE-сообщения, можно спокойно прервать скрипт без потери прогресса. Лёгкий откат на любом этапе.

Через два дня Кирилл зашел со стаканом кофе:
— Макс, всё прошло идеально! Спасибо!

Макс с улыбкой взял кофе:
— Поздравляю! Кофе действительно проще и дешевле, чем даунтайм :)

#кейс

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

📢 Алерт: "Блокировки в 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