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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#процессы

🎓 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

🕵️‍♂️ Когда pg_stat_activity недоговаривает

Звонок раздался внезапно.

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

Макс сделал глоток остывшего кофе.
— Могу. Только для этого нужно будет перезагрузить прод. Готовы на полчаса всё остановить?

В трубке повисла тяжёлая пауза.
— Понял, — вздохнул Сергей. — Не вариант. И что делать?

— Есть один способ, — ответил Макс, — но он не покажет то, что уже висит в воздухе.

— В смысле? — не понял Сергей. — Он покажет текущий запрос?

— Нет. Текущий — нет. В этой базе pg_stat_statements выключен, как и auto_explain. Мы не можем без танцев с бубном заставить Postgre раскрыть текст уже выполняющегося запроса, если он оказался длиннее, чем track_activity_query_size. Но вот следующий запрос — да, он попадёт в логи целиком. Сможешь запустить нужное действие в вашей программе, когда я скажу?

Сергей задумался на секунду.
— Да. А как ты это сделаешь?

— Очень просто, — Макс расшарил экран. — Включу подробное логирование, но только для одного пользователя. Команда такая:

ALTER USER a_very_busy_user SET log_statement = 'all';

— То есть всё, что он выполнит дальше, улетит в логи? — уточнил Сергей.

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

— Понял, а я запущу нужное действие на этом сервере? — подтвердил Сергей.

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

— Теперь главное не забыть выключить логирование. — напомнил Макс.

ALTER USER a_very_busy_user SET log_statement = 'none';

Сергей недоверчиво хмыкнул:
— А зачем выключать? Вроде же полезная штука.

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

Так и работали. Разработчики генерировали проблемы с помощью JPA, а админы помогали увидеть их в полный рост. Идеальный баланс во вселенной.

#best_practice #devops

🎓 100 уроков EXPLAIN. Часть 18
Параллелизм: ожидание и реальность

Лена задумчиво изучала монитор:
— Макс, вот вроде параллелизм должен ускорять запросы. Почему Postgres обычно его не включает, даже если таблица огромная? Я вижу свободные ядра, но запрос упорно ползет в один поток.

Макс кивнул:
— Это классика. Кажется, что Parallel Seq Scan — это кнопка «Турбо», но планировщик считает стоимости планов лучше любого бухгалтера. И у него есть как минимум три причины для отказа от параллелизма:

1. Лимит на сборку (Бутылочное горлышко)
Вся мощь воркеров разбивается об один процесс — Gather (Leader), который собирает результаты.
🔸 Если воркеры жестко фильтруют данные (WHERE status = 'ERROR') и отдают наверх лишь крупицы — это выгодно.
🔸 Если воркеры читают всё подряд и шлют миллионы строк Лидеру — это тормоз. Лидер захлебнется, а накладные расходы на пересылку данных между процессами (IPC) съедят весь выигрыш.

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

2. Размер имеет значение
По дефолту Postgres не параллелит таблицы меньше 8 MB (min_parallel_table_scan_size). Он считает, что накладные расходы на запуск процессов для такой «мелочи» выше выгоды.

3. Стоп-факторы
Параллелизма не будет, если:
🔸 Уровень изоляции транзакции SERIALIZABLE.
🔸 В запросе есть CTE с модификацией данных (INSERT/UPDATE/DELETE).
🔸 Используются курсоры (DECLARE ... CURSOR).
🔸 В запросе есть функции, не помеченные как PARALLEL SAFE.

— А как понять, что функция SAFE? — уточнила Лена.

— Это должен явно указать разработчик через ALTER FUNCTION. Если функция не меняет данные, не дергает сиквенсы (nextval), не лезет во временные таблицы и не имеет побочных эффектов — она может быть PARALLEL SAFE. Но если ошибешься — получишь не ускорение, а трудноуловимые баги.

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

Макс хитро улыбнулся и придвинулся ближе:
— Можно. Есть способ выдать конкретной таблице «VIP-пропуск».

--Принудительно задать число воркеров 
ALTER TABLE sales_archive SET (parallel_workers = 4);

Лена чуть подалась к экрану:
— А когда этот «VIP-пропуск» оправдан?

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

Он постучал по команде на экране:
— Планировщик увидит это и будет склонен выделить воркеров, даже если его формулы шепчут: «Дорого». Правда, выше, чем max_parallel_workers_per_gather, он всё равно не прыгнет.

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

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

Он снова улыбнулся и откинулся на спинку кресла:
— Настоящая сила не в том, чтобы включить 32 ядра… а в том, чтобы знать, когда их лучше не трогать.

#explain #parallel

🎄Новогодний чекап
Партиции

Представьте: 1 января, 00:20. Куранты пробили, праздник в самом разгаре, и тут звонок - не создаются платежи. Вся страна отмечает, а ты срочно подключаешься к vpn, и выясняешь, что в одной из журнальных таблиц... просто закончились партиции. Запись данных остановилась. Хотя есть и мониторинги и механизм автосоздания партиций.

Эта история — моя личная. И она научила меня одному: наше спокойствие в праздники — в наших руках. Лучше потратить 15 минут на проверку сейчас, чем отвлекаться на инциденты в праздники.

Поэтому я запускаю серию постов с SQL-запросами для предпраздничного чекапа PostgreSQL. Начнем с партиций.

Этот запрос покажет все партиционированные таблицы в вашей базе и выведет информацию о самой последней партиции для каждой из них:

WITH partitions AS (
    SELECT np.nspname AS parent_schema, parent.relname AS parent_table,
           nc.nspname AS partition_schema, child.relname AS partition_table,
           pg_get_expr(child.relpartbound, child.oid) AS partition_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 np ON parent.relnamespace = np.oid
      JOIN pg_namespace nc ON child.relnamespace = nc.oid
    WHERE parent.relkind = 'p'
),
parsed_bounds AS (
    SELECT parent_schema, parent_table, partition_schema, partition_table, partition_bound,
           substring(partition_bound FROM '[tT][oO]\s*\((.*?)\)' ) AS upper_bound
    FROM partitions
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_schema, parent_table 
              ORDER BY upper_bound DESC NULLS LAST, partition_bound DESC) AS rn
    FROM parsed_bounds
)
SELECT
    parent_schema || '.' || parent_table AS table_name,
    partition_schema || '.' || partition_table AS last_partition,
    partition_bound AS bound_def, upper_bound AS max_val_bound 
FROM ranked 
WHERE rn = 1
ORDER BY bound_def, max_val_bound;

На что смотреть в результатах?
Взгляните на колонку max_val_bound. Если ваша таблица партиционирована по дате, и вы видите там '2026-01-01', значит пора действовать и создавать новые партиции.

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

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

🎄Новогодний чекап
Сиквенсы

Продолжаем готовиться к праздникам.
Предлагаю скрипт, который выполняет диагностику сиквенсов (sequences):
1. Находит все пары «Таблица - Сиквенс».
2. Считает реальный max(id) в каждой таблице.
3. Сравнивает его с текущим значением сиквенса, лимитом типа данных (int/bigint) и настройками самого сиквенса.
4. Для самостоятельных (standalone) сиквенсов проверяет приближение к лимиту.

⚠️ Важно: Скрипт читает данные (max id). На огромных базах запускайте с осторожностью (или выборочно).

Как читать отчет?
Отчет сортируется по степени опасности — критичные состояния всегда сверху
В колонке status подсвечиваются опасные состояния:

⚠️ DESYNC: Table > Sequence! — Угроза! В таблице есть ID больше, чем текущий номер в сиквенсе. Лечится так:

SELECT setval('schema.seq', max_id);

🔥 CRITICAL: Overflow! — Вы уже достигли предела (типа данных или настроек сиквенса). Вставки не работают.

🔥 CRITICAL: Limit Risk — Заполнено более 85%. Если это integer, пора планировать миграцию на bigint.

Особенности:
В SQL нельзя выполнить динамический запрос внутри обычного SELECT, поэтому используется временная функция на PL/pgSQL. Это безопасно: она создается только для текущей сессии и исчезает сразу после отключения, не оставляя мусора в базе. При необходимости можете переделать в постоянную функцию заменив схему.
Фишки sequence из PG14+ (cycle, increment_by) не учитываются.
Сиквенсы не реплицируются — на реплике возможен DESYNC, это нормально. setval() там применять нельзя.
Для шардированных / distributed схем скрипт в текущем виде не применим.

#запросы #чекап #sequence

🎄 Новогодний чекап
Срок действия паролей

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

Представьте: приложение работает, база доступна, но бэкенд внезапно начинает сыпать ошибками вроде "password authentication failed". Причина банальна: у сервисного пользователя истек срок действия пароля.

Во многих компаниях действуют политики безопасности, требующие ротации паролей, в том числе и у сервисных учетных записей. Будет крайне обидно прерывать выходные только ради того, чтобы выполнить ALTER ROLE.

Давайте проверим, какие записи могут "протухнуть" в ближайший месяц.

SELECT
    rolname AS role_name,
    CASE 
        WHEN rolsuper THEN 'Superuser'
        WHEN rolcanlogin THEN 'Service/User'
        ELSE 'Group/Nologin'
    END AS role_type,
    rolvaliduntil AS valid_until,
    ROUND(EXTRACT(EPOCH FROM (rolvaliduntil - now())) / 86400) AS days_left
FROM pg_roles
WHERE rolcanlogin = true 
  AND rolvaliduntil IS NOT NULL 
  AND rolvaliduntil < (now() + INTERVAL '30 days') 
ORDER BY rolvaliduntil ASC;

Что делать с результатами?
Если список пуст — отлично, ваши пароли либо вечные (rolvaliduntil IS NULL), либо действуют еще долго.

Если нашли пользователя с days_left меньше, чем длительность ваших каникул:
🔸 Либо обновите пароль сейчас (с обновлением конфигов приложения).
🔸 Либо продлите срок действия старого пароля (если политики позволяют), чтобы спокойно заняться этим после праздников:

ALTER ROLE username VALID UNTIL '2026-02-01';

⚠️ Важно: Для продления пароля (ALTER ROLE) вам потребуются права суперпользователя. Если у вас их нет — отправьте этот список админам/DevOps, пока они еще на связи.

#запросы #чекап #безопасность

⏳ Один считает — остальные ждут

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

В кабинете сидел Денис, TechLead аналитики, с выражением лица, которое появляется после фразы «Ну тут же простой запрос, что может пойти не так?»

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

— Ну так сделайте, — Макс пожал плечами. — В чем проблема?

— В Cache Stampede, — вздохнул Денис. — Классика жанра. В 09:00 кэш пуст. Заходят 10 начальников. Код проверяет кэш — пусто. И все десять потоков одновременно запускают этот тяжелый расчет. Получается мы сами себя DDOS-им. Думаю надо городить Redis с Distributed Lock или очередь на RabbitMQ...

Макс поморщился, как от зубной боли.
— Денис, у тебя Postgres под капотом. Redis и очереди — отличные инструменты. Но не для каждого гвоздя нужен отбойный молоток. Твой Cache Stampede легко решается блокировкой в базе.

— Предлагаешь блокировать записи в таблице, пока рассчитываются данные?

— Ну зачем так грубо? — Вздохнул Макс. — Есть же Advisory Locks.

Он подошел к доске и набросал схему решения:

CREATE OR REPLACE FUNCTION get_heavy_report(p_date date) 
RETURNS jsonb AS $$
DECLARE
    l_result jsonb;
    -- Хеш от параметров — наш виртуальный "турникет"
    l_lock_key bigint := hashtext('report_' || p_date::text);
BEGIN
    -- 1. Оптимистичная проверка: вдруг уже готово?
    SELECT data INTO l_result FROM report_cache WHERE rep_date = p_date;
    IF l_result IS NOT NULL THEN RETURN l_result; END IF;

    -- 2. "Турникет". Кто успел первым — тот и молодец.
    -- Остальные встают в очередь на уровне ядра (легковесно!)
    -- функция с "_xact" гарантирует снятие блокировки при любом исходе транзакции
    PERFORM pg_advisory_xact_lock(l_lock_key);

    -- 3. Повторная проверка кэша (Самое важное!)
    -- Пока мы спали в очереди, кто-то уже всё сделал.
    SELECT data INTO l_result FROM report_cache WHERE rep_date = p_date;
    IF l_result IS NOT NULL THEN 
        -- Расчет не нужен.
        RETURN l_result; 
    END IF;

    -- 4. Если мы реально первые (или вообще единственные) — работаем.
    l_result := heavy_calculation_function(p_date);

    -- 5. Сохраняем результаты для остальных
    INSERT INTO report_cache(rep_date, data) VALUES (p_date, l_result)
    ON CONFLICT (rep_date) DO UPDATE SET data = EXCLUDED.data;
    
    RETURN l_result;
END;
$$ LANGUAGE plpgsql;

Итого:
🔸 первый поток считает,
🔸 остальные ждут,
🔸 после пробуждения — просто читают кэш,

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

— Именно, — Макс вернулся в кресло. — Это называется Advisory Lock. В отличие от блокировки строк (SELECT FOR UPDATE), он блокирует не данные, а намерение. Это джентльменское соглашение между процессами.

— А нагрузка?

— Нулевая. Блокировка живет в оперативной памяти (Shared Memory). Никакого IO, никакого bloat. Функция pg_advisory_xact_lock выполняется в транзакции вызывающего запроса и гарантированно снимается даже при ошибке.

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

— Макс, это же... Это как очередь в туалет в поезде.
— Что? — Макс поперхнулся кофе.
— Ну смотри. Если занято — ты не пытаешься построить рядом новый туалет. И не ломишься внутрь вдесятером. Ты просто стоишь и ждешь, пока освободится. Только тут еще круче: когда дверь открывается, ты заходишь и видишь, что за тебя всё уже сделали, и уходишь довольный.

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

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

🚦 CONCURRENTLY: Иллюзия свободы

В кабинет к Максу заглянул Вася с лицом человека, который только что проиграл схватку с реальностью:

— Макс, объясни. Я же делаю CREATE INDEX CONCURRENTLY, он не должен никого блокировать… а он висит уже второй час для маленькой таблицы!

Макс даже не повернул голову от монитора:

— Так он и не блокирует “как обычный” CREATE INDEX. В твоем случае он скорее всего ждет завершения какой-то транзакции. CONCURRENTLY — это не “вне законов физики”. Он строит индекс в несколько фаз и в ключевых точках обязан дождаться транзакций или снапшотов, которые ещё могут видеть старую картину данных. Поэтому какой-нибудь долгий SELECT (особенно в явной транзакции) легко превращается в шлагбаум для создания индекса.

Вася прищурился:

— Подожди… SELECT же не держит блокировку на таблицу!

— Не держит. Он просто держит твою надежду на быстрый релиз... Это не “жёсткая” блокировка таблицы, а ожидание, пока закончатся старые транзакции. В CONCURRENTLY это нормальный механизм корректности: база должна убедиться, что индекс не пропустит строки, которые кто-то ещё может видеть/менять в “старом мире”.

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

SELECT pid, query, application_name, client_addr, state,
  now() - xact_start  AS xact_age,
  now() - query_start AS query_age  
FROM pg_stat_activity
WHERE pid = ANY (
  SELECT unnest(pg_blocking_pids(pid))
  FROM pg_stat_activity
  WHERE query ILIKE 'create index concurrently%'
);

Вася посмотрел на вывод, и выражение лица стало ещё грустнее:
— Блокировщик… это же мой SELECT из отчета, который я запустил с утра для проверки.

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

Вася нахмурился:
— И что мне теперь делать? Убить сессию с CREATE INDEX?

— Ничего делать не надо, — Макс откинулся на спинку кресла. — Просто дождись завершения своего SELECT. Как только он закончится, индексация сразу продолжит работу и нормально завершится.

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

Макс покачал головой:
— Вот этого как раз делать не стоит. Если оборвёшь CREATE INDEX CONCURRENTLY, postgres оставит индекс в списке, но пометит его как INVALID — неполный и ненадёжный. Такой индекс не будет использоваться при выполнении запросов, но продолжит занимать место на диске и создавать накладные расходы при вставках и обновлениях.

— То есть получится "мёртвый груз"? — уточнил Вася.

— Именно. И тебе придётся потом либо удалять его через DROP INDEX CONCURRENTLY, либо перестраивать через REINDEX CONCURRENTLY. В любом случае — дополнительная работа и время. Гораздо проще просто дождаться, пока твой SELECT доработает, ну или убить этот твой SELECT.

Вася задумался:
— Понял. А как этого можно избежать в будущем?

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

Вася вздохнул:
— То есть CONCURRENTLY — это не волшебная кнопка "без последствий".

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

#кейс #индексы

🎭 Собеседование с «тенью»

Понедельник в банке начался не с кофе, а с просьбы руководителя управления поддержки: «Макс, подключись, пожалуйста, на техническое интервью во вторую линию поддержки. Толя в отпуске, а ты видишь людей насквозь, как EXPLAIN ANALYZE видит страдания планировщика». Макс нехотя согласился.

Кандидат, назовем его Илья, выглядел бодро. После общих разговоров подошло время для задач. Макс расшарил экран и вывел DDL таблицы:

CREATE TABLE courses (
    dt  DATE, 
    cur VARCHAR(3), 
    val NUMERIC
);

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

Пока Макс говорил, Илья смотрел прямо на экран, где красовались лаконичные dt, cur и val.

Кандидат помолчал несколько секунд, а потом уверенным голосом начал говорить:
— Запрос будет выглядеть так: SELECT course_value FROM currency_rates WHERE currency_code = 'USD' AND exchange_date <= ...

Макс чуть не поперхнулся остывшим кофе. В исходном DDL не было ни course_value, ни currency_rates, ни тем более exchange_date.

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

— Ну... это... — Илья замялся. — Это я для наглядности! Главное же логика, верно?

— Логика верная, — кивнул Макс. — Вот только твоя «логика» подозрительно похожа на типичный ответ нейросети. Она услышала мой голос, перевела его в типичный SQL из учебника, но совершенно проигнорировала DDL, который я тебе показал.

Илья заметно покраснел.

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

Когда кандидат отключился, Макс задумался: "А ведь если бы он догадался просто сделать скриншот моей демонстрации и скормить его GPT, распознать подмену было бы почти невозможно. Похоже, пора придумывать задачи, где важен не только SQL, но и инженерное чутьё".

#кейс #нейросети

🚀 Index Bloat (Разбухание индексов)

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

— Макс, отвлеку на минуту? — Вася развернул экран. — Помнишь историю с дисками? Нам их всё-таки добавили, но я тут на досуге прикрутил самодельный мониторинг размеров таблиц. И вот, смотри, какая странная штука.

Он указал на график таблицы operations. Синяя линия — объём данных — росла плавно. А вот красная линия — размер индексов — рванула вверх.

— Таблица выросла на 10%, а индексы на все 40, — Вася недоумённо почесал затылок. — Почему так происходит?

Макс отставил кружку и внимательно посмотрел на цифры.

— Это классическое «разбухание», — объяснил Макс. — Помнишь, как Postgres работает с данными? Любой UPDATE — это на самом деле создание новой версии строки. Старая помечается как «мёртвая», но в индексе запись о ней всё равно остаётся.

— И что же, это место просто пропадает? — уточнил Вася.

— Именно. Со временем в индексе появляются пустоты. Автовакуум помечает их как свободные, но сжать файл на диске он не может. В итоге индекс весит 40 Гб вместо условных 15, а база еще и тратит время на то, чтобы продираться сквозь эти «дырки» в поисках живых данных.

Вася нахмурился:
— И как это лечить? Опять всё блокировать и пересоздавать вручную?

— Зачем? Для индексов есть штатный инструмент, — Макс быстро набрал команду в консоли:

REINDEX INDEX CONCURRENTLY idx_operations_status;

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

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

— Макс, реально сработало! — Вася победно поднял большой палец вверх. — Место освободилось, ничего не блокируется. Первый индекс сжался почти в 4 раза! Я там уже скрипт на коленке набросал, чтобы вообще по всей базе пройтись и всё перестроить. Ура-а-а! Теперь моя база вдвое больше свободного места запасет!

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

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

— Намекаешь, что нам тоже нужен такой REINDEX? — усмехнулся Вася, уже открывая консоль на своём ноутбуке.

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

📌 Что запомнить:
🔸 Симптом: Индекс растёт значительно быстрее, чем сама таблица.
🔸 Причина: Частые UPDATE в полях, по которым построен индекс.
🔸 Лекарство: REINDEX INDEX CONCURRENTLY — пересобирает индекс на лету без блокировок.
🔸 Нюанс: Процесс нагружает диск и CPU, а также требует дополнительное место под новый индекс. Так что лучше планировать это на время минимальной нагрузки.

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