🚀 Неполный 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 #индексы

🔪 Хирург, а не мясник: Искусство последнего взгляда

Стажер Коля сидел перед монитором, как сапёр перед бомбой. Задача была простой: удалить тестовые записи, созданные до 1 сентября. В его редакторе горели два слова, от которых у любого инженера холодеет в груди: DELETE FROM.

Он перепроверил условие WHERE трижды. created_at < '2025-09-01' и status = 'test'. Логично. Безопасно. Но палец замер над клавишей Enter. А что, если?.. Что, если в полночь системное время сбоило? Или какой-то старый импорт пометил реальные заказы как тестовые?

В этот момент рядом бесшумно возник Макс с кружкой кофе. Он не заглядывал в монитор. Он посмотрел на Колю.

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

Коля нервно усмехнулся:
— Да просто старые данные чищу. Но чувство именно такое.

Макс кивнул, и в его взгляде не было ни капли осуждения. Только полное, тотальное понимание.
— Все мы держим в руках скальпель. Каждый день. И грань между хирургом и мясником — не в умении резать. Она — в искусстве последнего взгляда.

Он мягко подвинул к себе клавиатуру.

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

Макс не стал ничего исправлять. Он просто скопировал всю строчку DELETE FROM table WHERE... и вставил её ниже. А затем одним движением заменил DELETE FROM на SELECT * FROM.

— Посмотри им в глаза, — сказал он. — Не как строкам. А как историям, которые ты собираешься стереть.

На экране появились сотни записей. Коля пробежался по ним взглядом... и замер. Одна из записей. Статус — 'test'. Дата — августовская. Но сумма — огромная. И комментарий: «Демо для ключевого клиента N, не трогать до согласования». Эту запись завели вручную для презентации, и она попала под все формальные критерии удаления.

Он молча добавил в WHERE ещё одно условие: AND source != 'manual_demo'. Снова запустил SELECT. На этот раз опасной записи в списке не было.

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

Макс сделал глоток кофе.
— Видишь? — он улыбнулся. — Ты не просто удалил мусор. Ты спас ценность. Это и есть работа хирурга. Один лишний взгляд — одна спасённая вселенная.

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

#best_practice

🎓 100 уроков EXPLAIN. Часть 13
Порядок столбцов в индексе: грамматика запроса

Лена вошла в кабинет Макса и без предисловий развернула ноутбук. На этот раз в её глазах не было замешательства. Был азарт.

— Макс, смотри. Индекс по (user_id, status). Запрос: WHERE user_id = ? AND status = ?. План идеальный, Index Cond по обоим полям. Я меняю их местами в индексе — (status, user_id) — план почти не меняется. Логично. Но стоит мне поменять равенство на диапазон WHERE user_id = ? AND status > ? — и магия ломается. Индекс (user_id, status) справляется, а (status, user_id) — нет, улетает в Filter. Я, кажется, понимаю, почему. Но я не чувствую, почему.

Макс откинулся в кресле. Он смотрел не на экран, а на Лену. Это уже был вопрос не только о правилах, но о природе самых правил.

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

Он взял карандаш и лист бумаги.

— Представь, что индекс — это словарь. Индекс (фамилия, имя) — это идеально отсортированный справочник. Сначала по фамилии, потом, внутри каждой фамилии, по имени.
🔸 WHERE фамилия = 'Иванов' AND имя = 'Пётр'. Ты открываешь секцию «И», находишь «Иванов», внутри неё — «Пётр». Это прямой путь. Index Cond по обоим полям.
🔸 WHERE фамилия = 'Иванов'. Ещё проще. Находишь всех Ивановых. Index Cond по первому полю.

— Это очевидно, — кивнула Лена.

— А теперь, — Макс поднял палец, — WHERE имя = 'Пётр'. Что ты будешь делать с этим словарём?

Лена нахмурилась.
— Придётся... пролистать весь словарь от А до Я и на каждой странице искать Пётр.

— Вот. Ты только что описала Seq Scan. А теперь — самое интересное. WHERE фамилия > 'Иванов'. Ты находишь Иванов и просто читаешь дальше. Данные в индексе уже лежат в нужном порядке. Запрос не просто находит, он получает отсортированный результат. И если ты попросишь WHERE фамилия = 'Иванов' AND имя > 'Пётр', он сделает то же самое: найдёт точку старта и пойдёт вперёд по уже отсортированному маршруту.

Он вернулся к её примеру.

— Твой индекс (user_id, status) — это словарь, отсортированный по пользователям. Внутри каждого пользователя — по статусу. Он «понимает» фразу «дай все статусы для этого юзера, начиная с processed». А индекс (status, user_id) отсортирован по статусам. Он идеально ответит на запрос «дай всех юзеров со статусом processed. Но на фразу «дай для всех статусов, начиная с processed, юзера с id=5» он ответить не может. Он может найти все нужные статусы, но user_id ему придётся проверять вручную. Filter.

Лена молчала, глядя в одну точку. Потом медленно произнесла:
— Порядок столбцов — это не просто приоритет. Это структура повествования. Мы заранее говорим базе, по какому сценарию будем запрашивать данные. (A, B) — это история про А, в которой есть детали про Б. А (B, A) — совсем другая история.

— Именно, — кивнул Макс. — Ты перестала искать «правильный» порядок. Ты начала думать о том, какую историю хочешь рассказать. И это уровень, на котором оптимизация становится искусством.

📌 Что запомнить
🔸 Порядок столбцов в индексе — это не приоритет, а лексикографический порядок, как в словаре.
🔸 Индекс можно эффективно использовать только по его префиксу. Условие по второму столбцу без условия по первому — часто приводит к Filter или Seq Scan.
🔸 Правильный порядок позволяет не только искать, но и избегать сортировки (ORDER BY), если порядок в запросе совпадает с порядком в индексе.

#explain #индексы

🎓 100 уроков EXPLAIN. Часть 14
Почему Hash Join — не всегда спасение

— Макс, странный случай, — Лена подошла к его столу без ноутбука, с одной лишь распечаткой плана. — Запрос выполняется две секунды. Это быстро. Но страница в интерфейсе «подвисает» на все две секунды, прежде чем показать первые 20 строк. В плане — обычный Hash Join. Я думала, он — наш друг.

Макс отставил кружку.

— Ты привыкла, что Hash Join — это мощная фабрика, которая перемалывает миллионы строк. Это так. Но ты забыла, что прежде чем фабрика выпустит первый товар, она должна быть полностью построена, — он взял листок. — Hash Join — это блокирующий узел. Он не может отдать ни одной строки наверх, пока полностью не обработает одну из таблиц и не построит из неё в памяти хеш-таблицу.

— То есть… все эти две секунды он не ищет, а готовится к поиску? — медленно проговорила Лена.

— Именно. Он строит свой «справочник». Для отчёта, который должен обработать всё, это идеальная стратегия: потратить время на подготовку, чтобы потом соединить гигантские объёмы данных молниеносно. Но для твоего интерфейса, которому нужна первая страница записей прямо сейчас, это катастрофа. Ты ждёшь первого результата почти столько же, сколько всего результата. Это time-to-first-row против time-to-total. Планировщик обычно учитывает обе метрики, но в сложных случаях или при неточностях в статистике может выбрать план, оптимизированный на общее время.

Лена нахмурилась.
— А какая альтернатива? Nested Loop? Но он же «тупой» и перебирает всё для каждой строки!

— Не совсем, — Макс начертил схему. — Nested Loop с хорошим индексом по ключу соединения — это не перебор. Это снайперская стрельба. Он берёт одну строку из внешней таблицы и мгновенно, по индексу, находит соответствующую во внутренней. И сразу же отдаёт результат наверх. Он может вернуть первую строку за миллисекунды. Да, на обработку миллиона строк он потратит гораздо больше времени, чем Hash Join. Но для пагинации он бесценен.

— Получается, Hash Join — это спринтер, который долго разминается, а Nested Loop — марафонец, который стартует сразу?

— Хорошая аналогия. Но есть и вторая ловушка Hash Join. Он требует, чтобы хеш-таблица поместилась в оперативную память (work_mem). Если планировщик ошибся в оценке числа строк, и хеш-таблица не влезает в RAM, начинаются «проливы на диск».

— А где это видно?

— В плане у узла Hash (составляющая Hash Join) ты увидишь batches: N (originally 1). Это сигнал, что Postgres пришлось разбить хеш-таблицу на несколько частей и записывать их во временные файлы на диске. Скорость падает в десятки, а то и в сотни раз.

Лена посмотрела на свой план. Там не было проливов, но она поняла главное.
— Получается, Hash Join — не только блокирующий, но и чувствительный к памяти инструмент. А Nested Loop, который мы привыкли считать «плохим», в мире интерактивных интерфейсов может быть настоящим спасением.

— Да. Важно видеть не просто узлы плана, а компромиссы, которые за ними стоят, — кивнул Макс.

📌 Что запомнить
🔸 Высокая цена запуска: Hash Join — блокирующая операция. Он не вернёт первую строку, пока не построит хеш-таблицу. Это делает его неэффективным для запросов с LIMIT или для быстрой отдачи первой страницы данных.
🔸 Зависимость от work_mem: Если хеш-таблица не помещается в оперативную память, Hash Join начинает использовать диск. Для проверки смотрите EXPLAIN (ANALYZE, BUFFERS): у Hash будет Batches: … (originally 1), рост nbatches и временные файлы
🔸 Nested Loop с индексом — отличная «стриминговая» альтернатива для быстрого старта, но без индекса по ключу соединения он сильно деградирует.
🔸 Merge Join — ещё один потоковый способ соединения. Он тоже может быстро отдавать первые строки, если оба входа уже отсортированы (например, после Index Scan).
🔸 Блокирующий Sort — главный враг быстрого старта. Если в плане есть Sort перед LIMIT, польза от «стриминговых» соединений теряется. Решение — индекс, поддерживающий нужный ORDER BY, или keyset-пагинация.

Hash Join — это не серебряная пуля. Это тяжёлая артиллерия, которую нужно применять с умом.

#explain

🛠 Сбой обязательно произойдёт

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

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

Макс спокойно отхлебнул кофе:
— И что вы уже сделали?

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

— То есть, вы решили проблему для вчерашнего дня, — кивнул Макс. — А что будете делать, когда это случится снова? Опять будете в спешке писать скрипты?

Анна нахмурилась:
— Ну... мы надеемся, что это не повторится.

— Надёжность не строится на надежде, — Усмехнулся Макс. — Хотя слова и похожи.

Он развернул к ней монитор.
— Ваш подход правильный, но неполный. Мониторинг — жизненно необходим. Он как ЭКГ, показывает, что что-то пошло не так. Скрипты для исправления — это как экстренная операция. А где профилактика?

— И что ты предлагаешь? — спросила Анна.

— Сделайте вашу систему «самовосстанавливающейся», — сказал Макс. — Доработайте ваше приложение. Пусть сервис при старте и периодически фоновым воркером проверяет: «Нет ли в базе операций, для которых не появилось транзакций?». Если находит — берёт их в обработку и достраивает недостающее. Главное - у вас в приложении уже есть нужный функционал, не нужно руками писать скрипты и множить вероятность ошибки из-за поспешных действий.

Анна на секунду задумалась.
— Не задублируем ли мы что-то при повторной обработке?

— Нет, если обработка идемпотентна: используйте уникальные ключи и дедупликацию, повторы — с контролем (ограниченные попытки, паузы), а на нерешаемые случаи — письмо с алертом.

— То есть, сервис сам будет исправлять последствия сбоев?

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

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

#процессы

🎓 100 уроков EXPLAIN. Часть 15
Партицированные таблицы

Лена подошла к столу Макса с новым планом. На этот раз её вид был озадаченным.

— Макс, я смотрю на запрос к нашей таблице events. И план выглядит… как-то странно. Тут появился узел Append, под которым куча Index Scan по разным таблицам. Это нормально?

Макс взглянул на план:

Append  (...)
  ->  Index Scan using idx_events_2025_08_dt on events_2025_08 ...
  ->  Index Scan using idx_events_2025_09_dt on events_2025_09 ...

— Поздравляю! — улыбнулся он. — Ты столкнулась с тем, как EXPLAIN показывает работу с партицированными таблицами. Это очень хитро устроенная таблица, которая на самом деле состоит из множества разных таблиц. Узел Append в плане это склейка результатов из релевантных партиций. Каких именно — видно по дочерним узлам.

— Я читала об этом, это похоже на шкаф с одинаковыми документами, где каждый ящик — отдельный месяц?

— Отличная аналогия! — кивнул Макс. — И самая главная магия оптимизатора здесь — это отсечение партиций. При правильном запросе Postgres заранее знает, в какие ящики ему нужно заглянуть, а какие можно проигнорировать. Это и есть ключ к производительности.

Макс открыл два примера, чтобы показать разницу.

1. "Правильный" запрос с фильтром по ключу партицирования

EXPLAIN SELECT * FROM events WHERE event_date >= '2025-08-15';

В плане будет узел Append, но под ним сканируются только те партиции (ящики), которые содержат нужные даты. Например, events_2025_08, events_2025_09 и так далее. Postgres "отсёк" все партиции за июль и более ранние, даже не заглядывая в них. Это называется partition pruning.

2. "Неправильный" запрос, который всё портит

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

EXPLAIN SELECT * FROM events WHERE date_trunc('month', event_date) = '2025-08-01';

Лена нахмурилась:
— План изменился! Теперь под Append сканируются все партиции с 2022 года!

— Именно! — подтвердил Макс. — И это уже плохо для производительности.

— Получается, главный секрет — помочь Postgres понять, какие партиции ему нужны? — спросила Лена.

— Точно!

— А что если мне нужно выбрать все события пользователя? — спросила Лена. — Например: WHERE user_id = 5.

Макс показал ей план:

Append (...)
  -> Index Scan using idx_events_2022_01_user_id on events_2022_01 ...
  -> Index Scan using idx_events_2022_02_user_id on events_2022_02 ...
  -> ...
  -> Index Scan using idx_events_2025_10_user_id on events_2025_10 ...

— Видишь? — пояснил Макс. — Ключ партиционирования — event_date, а фильтр по нему отсутствует. Значит, отсечение партиций не сработает, и Postgres вынужден проходить по каждой партиции отдельно: для каждой — свой Index Scan, а затем результаты «склеиваются» узлом Append. Иногда это будет Parallel Append (если включён параллелизм) или Merge Append при ORDER BY, но суть та же — много мелких сканов вместо одного большого.

— То есть партиции могут даже замедлять? — уточнила Лена.

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

📌 Что запомнить
🔸 Фильтр по ключу партицирования сильно повышает эффективность. Без него Postgres будет сканировать все партиции подряд.
🔸 Избегай функций над ключом. Переписывай запросы так, чтобы ключ партицирования был в "чистом" виде. Вместо date_trunc или extract используй чистую проверку интервалов.
🔸 Проверяй план через EXPLAIN. Всегда смотри, какие партиции сканируются под узлом Append. Если видишь там все партиции таблицы — это верный признак того, что отсечение не работает.

Лена задумчиво кивнула:
— Значит, партицирование — это инструмент, который требует правильного обращения. И EXPLAIN — лучший способ проверить, правильно ли ты его используешь.

— В точку! — подытожил Макс. — Теперь ты знаешь, что Append в плане — это твой друг, который показывает, насколько эффективен твой запрос.

#explain #партицирование

Запрос для анализа партиций в PostgreSQL

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

WITH parts AS (
  SELECT
    child.oid                               AS part_oid,
    nmsp_child.nspname                      AS part_schema,
    child.relname                           AS part_name,
    pg_get_partkeydef(parent.oid)           AS partitioning,
    pg_get_expr(child.relpartbound, child.oid) AS part_bound
  FROM pg_inherits
  JOIN pg_class parent       ON pg_inherits.inhparent = parent.oid
  JOIN pg_class child        ON pg_inherits.inhrelid  = child.oid
  JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
  JOIN pg_namespace nmsp_child  ON nmsp_child.oid  = child.relnamespace
  WHERE parent.relkind = 'p'                 -- гарантируем, что parent — партиционированная таблица
    AND parent.relname = 'table_name'        -- <<< Укажите имя родительской таблицы
    AND nmsp_parent.nspname = 'schema_name'  -- <<< Укажите схему родительской таблицы
)
SELECT
  p.part_schema,
  p.part_name,
  p.partitioning,
  p.part_bound,
  pg_size_pretty(pg_total_relation_size(p.part_oid)) AS total_size,
  pg_size_pretty(pg_table_size(p.part_oid))          AS table_size,
  pg_size_pretty(pg_indexes_size(p.part_oid))        AS index_size,
  (SELECT count(*) FROM pg_index i WHERE i.indrelid = p.part_oid) AS index_count,
  COALESCE(NULLIF(st.n_live_tup, 0), c.reltuples)::bigint         AS rows_estimate,
  ROUND(100.0 * st.n_dead_tup / NULLIF(st.n_live_tup + st.n_dead_tup, 0), 2) AS fragmentation_percent,
  st.last_analyze,
  st.last_vacuum,
  COALESCE(ts.spcname, 'pg_default') AS tablespace
FROM parts p
  LEFT JOIN pg_class c            ON c.oid   = p.part_oid
  LEFT JOIN pg_stat_all_tables st ON st.relid = p.part_oid
  LEFT JOIN pg_tablespace ts      ON c.reltablespace = ts.oid
ORDER BY p.part_schema, p.part_name;

На скриншоте пример выполнения на тестовых данных.

Нужна такая рубрика #запросы ?
Жми 👍 если да, 👎 — если это лишнее и не стоит тратить время.

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

#запросы #партицирование

Партиции не панацея

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

В переговорке повисла одобрительная тишина. Идея прозвучала как спасение. Макс медленно поднял глаза.

— Илья, хорошая инициатива. Давай проясним. По какому ключу мы будем делить таблицу?

— Ну, по дате подачи заявки, конечно. По полю created_at, помесячно. Это же логи, по сути.

— Логично, — кивнул Макс. — А теперь скажи, какой самый важный и самый "больной" для нас запрос к этой таблице? Ради чего мы вообще храним всю эту историю?

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

— Допустим, — прервал его Макс. — А еще? Для чего нашему банку нужна история заявок за 5 лет?

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

— Именно! — Макс подался вперед, и его голос обрел стальную твердость. — Мы ищем все заявки по passport_number или client_id за все время. Чтобы построить полную кредитную историю. А теперь вопрос, как партицирование по дате поможет нам ускорить этот поиск?

Взгляд Ильи дрогнул. Стало очевидно, что никак.

— Ребята, я понимаю, откуда это идет. Мы слышим "большая таблица — партицируй" и думаем, что это аксиома. Но партицирование — это не волшебная палочка. Это сложный, мощный и опасный инструмент. Точно как скальпель хирурга.

Он обвел взглядом команду.

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

— Первая и главная проблема — деградация важного запроса. Чтобы оценить кредитную историю, нам придется заглянуть в каждую месячную партицию за все годы. Вместо одного быстрого поиска по индексу passport_number мы получим 60 поисков. Мы не ускорим, а убьем производительность скоринговой системы.

— Вторая проблема — ограничения. Мы хотим, чтобы app_id был глобально уникальным, верно? А если мы партицируем по created_at, то Postgres потребует, чтобы дата стала частью этого уникального ключа. То есть app_id перестанет быть по-настоящему уникальным, что абсурдно для банковской системы. Напомню, что на pgPro мы еще не перешли, а у них есть поддержка глобальных индексов.

— Третья — сложность. Это не "сделал и забыл". Придется писать и поддерживать скрипты для создания новых партиций. Партицирование это дополнительные накладные расходы на администрирование.

Макс откинулся на спинку стула.

— Поэтому, прежде чем мы произнесем слово "партицирование", мы должны, как врачи перед операцией, пройтись по чек-листу:
🔸 Какую проблему мы решаем? Если это медленное регулярное удаление старых записей (DELETE) — да, это наш кандидат. Но действительно ли мы их удаляем, или они нужны нам для истории?
🔸 По какому полю фильтруют наши самые частые и критичные для бизнеса запросы? Если это отчеты по датам — хорошо. А если это скоринг по client_id за все время — СТОП. Мы рискуем все испортить.
🔸 Каков жизненный цикл наших данных? Да, это временной ряд. Но все ли данные со временем становятся "холодными"? Или заявка пятилетней давности так же важна для оценки кредитной истории, как и вчерашняя?
🔸 Нужна ли нам глобальная уникальность по полю, не входящему в ключ партицирования? Да, app_id должен быть уникален. И это уже техническое препятствие.

Он сделал глоток воды.

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

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

#процессы #партицирование

🧐 Опасный serial

— Макс, привет! — стажер Коля появился у стола Макса. — Минутка есть?

Макс оторвался от монитора, и с улыбкой кивнул:
— Привет, Коля. Для тебя — всегда. Что на этот раз? Опять VACUUM что-то не убрал?

— Нет, с этим я, кажется, разобрался, спасибо! — с гордостью ответил стажер. — У меня вопрос по созданию таблиц. Я тут делаю новую табличку. По привычке для первичного ключа написал id serial PRIMARY KEY. А наш тимлид посмотрел и сказал переделать на GENERATED AS IDENTITY. Говорит, serial — это «легаси». Я почитал, но не понял, в чем соль? И то, и другое просто автоинкремент, разве нет?

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

Он открыл консоль.
— Смотри. Что такое serial по своей сути? Это просто синтаксический сахар. Когда ты пишешь id serial, PostgreSQL за кулисами создает для тебя последовательность (sequence) и ставит ее как значение DEFAULT для твоего столбца id. Удобно, быстро, но есть подвох.

Макс создал тестовую таблицу:

CREATE TABLE legacy_invoices (
    id serial PRIMARY KEY,
    amount numeric
);

— Теперь смотри, — он вставил одну строку как положено.

INSERT INTO legacy_invoices (amount) VALUES (100); -- id стал 1

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

INSERT INTO legacy_invoices (id, amount) VALUES (2, 200);

— Видишь? Запись вставилась без проблем. А теперь главный фокус. Что будет, если мы снова вставим запись обычным способом?

INSERT INTO legacy_invoices (amount) VALUES (300);
-- ERROR:  duplicate key value violates unique constraint "legacy_invoices_pkey"
-- DETAIL: Key (id)=(2) already exists.

Глаза Коли округлились.
— Ой! Как так?
— А вот так. Наш sequence ничего не знает о том, что мы вручную вставили значение 2. Его счетчик остался на 1, и при следующей вставке он честно выдал следующее значение — 2. А так как запись с id=2 уже есть, мы получили конфликт. Это одна из классических проблем, которая может «всплыть» ночью и остановить важный процесс.

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

CREATE TABLE modern_invoices (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric
);

— Ключевые слова здесь GENERATED ALWAYS AS IDENTITY. Это уже не просто DEFAULT, а жесткое свойство столбца, прописанное в стандарте SQL. Оно говорит базе: «Значения для этого столбца *всегда* генерируются системой. Руками не трогать!». Попробуем повторить наш трюк.

INSERT INTO modern_invoices (id, amount) VALUES (2, 200);
-- ERROR: cannot insert a value into an identity column
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

— Вот! — Макс торжествующе указал на экран. — PostgreSQL просто не дал нам совершить ошибку. Он защищает нас от случайных или необдуманных вставок. Если тебе действительно нужно вставить свое значение, ты должен явно указать OVERRIDING SYSTEM VALUE. Это заставляет подтвердить, что ты понимаешь, что делаешь.

— А что за режим BY DEFAULT? — тут же спросил Коля.
— Хороший вопрос! GENERATED BY DEFAULT AS IDENTITY — это мягкий режим. Он позволит тебе вставить свое значение без OVERRIDING SYSTEM VALUE, как serial. Но для большинства обычных таблиц, особенно с первичными ключами, GENERATED ALWAYS — самый безопасный и предсказуемый выбор.

Коля задумчиво смотрел на экран.
— Теперь понятно. serial — это просто удобная обертка со скрытой проблемой, а GENERATED AS IDENTITY — это автоинкремент с защитой от дурака.

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

#best_practice #ddl #sequence

🎓 100 уроков EXPLAIN. Часть 16
GROUP BY и DISTINCT в плане запроса

Лена вошла в кабинет Макса с задумчивым видом.

— Макс, я снова запуталась. У меня два почти одинаковых запроса. В одном SELECT user_id ... GROUP BY user_id, в другом — SELECT DISTINCT user_id. Я думала, результат будет идентичным, но планы совершенно разные! В одном — какой-то HashAggregate, а в другом — Sort и Unique. Почему так?

Макс отпил кофе и улыбнулся.
— Это одна из интересных задач для планировщика — как эффективно сгруппировать данные или найти уникальные значения. Он использует для этого две основные стратегии. Я называю их конвейер и большой котел.

Макс взял лист бумаги и ручку.

1. HashAggregate: Большой котел

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

HashAggregate работает так же:
🔸 Что делает: Читает строки одну за другой и строит в памяти хеш-таблицу, где ключ — это значение из колонки для группировки (GROUP BY key).
🔸 Когда хорош: Эффективен для группировки больших объемов данных, когда порядок не важен.
🔸 Слабое место: Требует много памяти. Если хеш-таблица не умещается в work_mem, он начинает сбрасывать данные на диск, что резко снижает производительность.

Пример плана:

EXPLAIN SELECT status, count(*) FROM orders GROUP BY status;

-- HashAggregate
--   -> Seq Scan on orders

Здесь Postgres читает всю таблицу (Seq Scan) и "на лету" группирует данные в хеш-таблице.

2. GroupAggregate / Sort + Unique: Конвейерная сортировка

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

GroupAggregate работает по этому принципу:
🔸 Что делает: Объединяет строки, которые уже отсортированы по ключу группировки. Если порядок не гарантирован, планировщик добавляет операцию Sort.
🔸 Когда хорош: Когда есть индекс по полю, которое используется в GROUP BY. Postgres может просто читать данные из индекса в нужном порядке, избегая дорогостоящей операции сортировки (Sort).
🔸 Как выглядит в плане: Ты можешь увидеть GroupAggregate или Unique (для DISTINCT).

Пример плана с индексом:

-- Есть индекс по status
EXPLAIN SELECT DISTINCT status FROM orders;

-- Unique
--   -> Index Only Scan using orders_status_idx on orders

Postgres читает данные прямо из индекса, где они уже отсортированы, и просто отсекает дубликаты (Unique). Никаких лишних затрат на сортировку или хеширование.

GROUP BY против DISTINCT

Для планировщика SELECT DISTINCT a FROM table — это почти то же самое, что и SELECT a FROM table GROUP BY a. Он выберет ту же стратегию (HashAggregate или Sort + Unique), основываясь на наличии подходящего индекса и объеме данных.

— Так вот почему у меня планы были разные! — воскликнула Лена. — Для GROUP BY у меня не было индекса, но хватало work_mem, и он использовал HashAggregate. А для DISTINCT по другой колонке индекс был, и он выбрал Index Only Scan + Unique!

— Именно! — подтвердил Макс. — Postgres нашел короткий путь с помощью индекса и воспользовался им.

📌 Что запомнить
🔸 GROUP BY и DISTINCT решают похожую задачу, и для них используются одинаковые стратегии.
🔸 HashAggregate: Быстрый, но требовательный к памяти. Используется, когда нет отсортированных данных.
🔸 GroupAggregate / Sort + Unique: Эффективен, если данные уже отсортированы (например, взяты из индекса). Позволяет избежать больших затрат памяти.
🔸 Наличие индекса по ключу группировки — главный фактор, который помогает Postgres избежать дорогостоящих Sort или HashAggregate.
🔸 Нет индекса и видите Sort — попробуйте увеличить work_mem для этого сеанса, чтобы задействовать HashAggregate.

#explain #группировка

🗑 Большая чистка

— Макс, привет! Слушай, у меня беда. Пытаюсь почистить нашу log_table, удалить данные за два года, оставить только два последних месяца. Запустил DELETE... уже третий час жду. Можешь посмотреть - что с базой не так?

— Привет, Денис! О, это же классика. Ты не делаешь ничего «не так», просто ты выбрал самый долгий путь.

— В смысле? DELETE же для этого и создан!

— Ага, но не для промышленных масштабов. Представь, что у тебя есть огромная библиотека, и тебе нужно убрать 95% книг. Твой DELETE — это как заставить библиотекаря вычеркивать каждую старую книгу из картотеки. Он будет бегать по стеллажам, находить карточку, ставить штамп «УДАЛЕНО», потом идти к самой книге, вешать на нее табличку... Работа адская, а книги-то физически все еще на полках стоят и занимают место.

— Хм, то есть база данных так же «вычеркивает» строки, но не освобождает место сразу?

— Именно! Она помечает их как удаленные, а место освободится только после «уборки» — VACUUM. Это уже ночная смена клининга, которая придет и унесет помеченные книги в подвал. Но полки-то останутся. Чтобы реально уменьшить библиотеку, понадобится полная перестройка (VACUUM FULL), а это еще дольше и заблокирует всю работу.

— Звучит ужасно. И что делать?

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

Миграция через теневую таблицу: три шага к успеху

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

Шаг 1: Быстрый перенос основного объема

Самую большую скорость мы получим, если будем копировать данные в «голую» таблицу.

🔸 Создаем дублера: Делаем копию структуры нашей таблицы (log_table_new), но без индексов, триггеров и внешних ключей. Это наш пустой, быстрый склад.
🔸 Переливаем свежее: Копируем в log_table_new основной массив данных за последние два месяца. Без индексов и проверок это пройдет на порядок быстрее.

Шаг 2: Создаем индексы и догоняем

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

🔸 Строим индексы: Теперь, когда данные на месте, создаем на log_table_new все необходимые индексы. Это может занять время, но это никого не блокирует.
🔸 Промежуточный долив: Если первый этап копирования и создание индексов заняли много времени, то можно выполнить еще один, промежуточный, перенос данных, которые успели накопиться в старой таблице. Это сократит объем данных для финального рывка.

Шаг 3: Финальный рывок и рокировка

Это самый ответственный момент, который требует короткого «окна тишины».

🔸 Навешиваем триггеры и ограничения: Добавляем на log_table_new все недостающие триггеры и внешние ключи.
🔸 Блокировка и финальный долив: На несколько секунд блокируем старую таблицу, чтобы в нее перестали поступать новые данные. Быстро копируем последние записи, которые успели появиться с момента последнего долива.
🔸 Фокус с переименованием: Одной транзакцией меняем таблицы местами: старая log_table становится log_table_old, а новая log_table_new — нашей основной log_table. Если есть внешние ключи на таблицу их потребуется пересоздать.
🔸 Снимаем блокировку: Приложения теперь работают с новой, маленькой и быстрой таблицей. Общее время простоя — всего несколько секунд.
🔸 Удаляем старую таблицу: После проверки делаем DROP TABLE log_table_old - это самая приятная процедура в этом способе.

— Круто! А что с ID?

— А ты шаришь! Конечно же нужно не забыть подкрутить sequence на новой таблице, установив его на максимальное значение, которое у нас есть.

— Макс, спасибо! Я вот что еще подумал - через такую теневую таблицу можно же не только удалять данные, но и делать какие-то масштабные изменения.

— Да, сам принцип универсален, но главное, вовремя остановиться. А то так и до log_table_new3_final_v2_for_real недалеко :)

#best_practice #shadow_table

🎓 100 уроков EXPLAIN. Часть 17
Что скрывают DML-запросы

Кофе давно остыл. Монитор гипнотизировал Лену мигающим курсором.

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

— Это не я, это UPDATE, — вздохнула Лена. — Просто меняет статусы у старых задач. Что тут может быть не так?

— Ты думаешь, это простая операция. А я говорю: твой UPDATE — это приговор производительности.

Лена развернулась на стуле.
— Приговор? Макс, это же не сложный SELECT с кучей JOINов. Это просто UPDATE...

— Ну и что? Прежде чем обновить, база должна найти. А как она ищет? Через планировщик — фактически тем же способом, как если бы выполняла SELECT.

Он подошёл ближе и кивнул на монитор:
— Попробуй EXPLAIN, без ANALYZE. Тогда база просто покажет план, не выполняя сам запрос.

— Хочешь сказать, что EXPLAIN работает и с UPDATE?

— Конечно. Все DML операторы можно анализировать через EXPLAIN.

— И EXPLAIN ANALYZE можно? он же фактически выполняет запрос.

— Можно и EXPLAIN ANALYZE, — кивнул Макс. — Он действительно выполнит твой UPDATE. Но можно завернуть его в транзакцию и потом сделать ROLLBACK. Получишь реальное время и детальный план, но потратишь ровно столько же времени, сколько и при настоящем запуске.

Лена нахмурилась.
— Но это же разовая ручная операция. Не создавать же индекс ради неё?

— Я знаю, что я зануда, — улыбнулся Макс. — Конечно ты права, для единичных действий индекс — только балласт. Просто обратил твое внимание, что анализировать и оптимизировать можно и нужно не только SELECTы.

Он сделал глоток остывшего кофе и добавил:
— Вот взять INSERT. Думаешь это быстро. А потом смотришь — на каждую вставку срабатывает триггер: журналирование, проверка, рассылка. EXPLAIN ANALYZE всё честно покажет .

— Аж захотелось посмотреть, как работает INSERT с ON CONFLICT. — задумалась Лена.

— Да, UPSERT тот ещё артист. Сначала проверит конфликт — фактически ищет строку через индекс, — потом решит, вставлять или обновлять. И план все покажет.

— Значит, любая DML-операция…

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

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

#explain #dml

🚀 Как разогнать загрузку данных в PostgreSQL

Макс только собирался налить себе кофе, как в мессенджере всплыло сообщение от Саши:
— Привет, нужна консультация.

— Что случилось?

— Ничего критичного, просто загрузка данных в analytics.daily_events всё медленнее и медленнее.

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

Саша расшарил экран: обычные INSERT ... VALUES (...) в батче.
— Логично и просто, — заметил Макс. — Но PostgreSQL — не любит «по чуть-чуть». Если хочешь, чтобы он ел быстро, подавай ему целыми блюдами, а не по крошкам.

1️⃣ COPY — главный ускоритель

— Для загрузки данных у PostgreSQL есть специнструмент — COPY. Он пишет сразу большими блоками, минимизируя транзакционные накладные расходы.

— То есть быстрее, чем INSERT?

— В разы. Главное — подготовить файл CSV, но вам, программистам, это как два байта переслать. Дальше — магия расположения. Если пишешь COPY в SQL-скрипте, то файл должен физически лежать на сервере с PostgreSQL, и у базы должны быть права на его чтение. А если ты, как настоящий джедай, работаешь через консоль psql и используешь команду \copy (с косым слешем), то файл может лежать прямо у тебя под рукой — на той машине, где ты эту команду выполняешь.

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

psql -h db_host -U etl_user -d analytics_db -c "\copy analytics.daily_events FROM '/data/events.csv' WITH (FORMAT csv, HEADER)"

— А можно как-то мониторить прогресс?

— Конечно, через запрос к pg_stat_progress_copy.

2️⃣ Выключаем пользовательские триггеры

— Если вставляешь гарантированно корректные данные, можно на время отключить пользовательские триггеры, — продолжил Макс.

ALTER TABLE analytics.daily_events DISABLE TRIGGER USER;

— Это не затронет системные ограничения и FOREIGN KEY, но снимет нагрузку с кастомных проверок и логирования. После загрузки не забудь включить обратно:

ALTER TABLE analytics.daily_events ENABLE TRIGGER USER;

3️⃣ Индексы: не всегда зло, но иногда тормоз

Саша нахмурился:
— А индексы мешают?

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

— А как понять, что объем большой?

— Можно, конечно, сесть с калькулятором и рассчитать точный порог, где пересоздание выгоднее, но это путь джедая. Я для себя давно вывел простое правило: если загружаешь больше 10-15% от текущего размера таблицы — смело сноси индексы. Если меньше — скорее всего, овчинка выделки не стоит.

🧩 Финальный штрих

— После загрузки обязательно обнови статистику, — добавил Макс.

ANALYZE analytics.daily_events;

— Ага. Что-то еще?

— Если обрабатываешь временные данные, можно использовать UNLOGGED таблицы. Они не пишут в WAL и работают быстрее, но не реплицируются на стендбаи и теряют данные при сбое. Так что с ними осторожно.

Саша кивнул:
— Понял. Значит, сначала COPY, потом — отключаем триггеры при необходимости, а индексы только если объём реально большой?

— Именно. Быстрее, безопаснее и без сюрпризов.

Через пару дней Саша снова написал:
«Макс, теперь заливка за 18 минут! Спасибо!»
Макс улыбнулся. Иногда, чтобы ускорить процесс, достаточно просто знать, в каком порядке открывать двери.

#best_practice #dml