🚀 Как разогнать загрузку данных в 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, а также требует дополнительное место под новый индекс. Так что лучше планировать это на время минимальной нагрузки.

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

Сегодня без Макса
Обычно здесь всё происходило с Максом. Сегодня напишу я.
Меня зовут Дмитрий. Я веду этот канал и уже довольно давно делаю pgtools — свою IDE для PostgreSQL.
Канал молчит четыре месяца. Я влез в большой дополнительный проект, свободного времени стало сильно меньше. Но pgtools я не бросил. Иногда вечером открываю список задач с намерением поправить один баг, а через два часа обнаруживаю, что добавляю очередную настройку.
Рассказать о программе здесь я собирался давно. И каждый раз находилась причина отложить.
То кнопка стоит не там.
То окно выглядит недостаточно аккуратно.
То надо сначала доделать ещё одну функцию.
То кажется, что человек скачает приложение, посмотрит пять минут и молча удалит.
Последний вариант почему-то пугал сильнее всего.
В какой-то момент пришлось признать неприятную вещь: я могу улучшать программу ещё несколько лет и всё равно найду, что в ней переделать.
Поэтому вот.
Представляю pgtools — бесплатное desktop-приложение для ежедневной работы с PostgreSQL.
Не революцию в управлении базами данных. Не убийцу существующих клиентов. Просто программу, которую я сам хотел иметь под рукой на работе.
В ней можно писать и выполнять SQL-запросы, работать сразу с несколькими базами, смотреть результаты в таблице, редактировать данные и другие привычные вещи.
Редактор сделан на Monaco. Есть подсветка, автодополнение таблиц и полей, тёмная и светлая темы.
Есть графический и текстовый просмотр EXPLAIN. Планы сохраняются в истории вместе с запросами. Иногда это помогает в некоторых ситуациях.
Есть просмотр сессий и блокировок. Дерево блокировок строится в самом приложении. Я специально сделал локальное построение без рекурсивных запросов к базе. Когда в базе уже несколько тысяч ожидающих процессов, последнее, что хочется сделать, — отправить туда ещё один сложный запрос, который тоже зависнет.
Есть сравнение двух баз или схем. Полезно, когда есть несколько контуров и надо найти отличие.
Есть поиск по таблицам, полям, комментариям, функциям и представлениям. В больших старых базах комментарий иногда оказывается полезен чтобы понять, что автор вообще имел в виду.
Есть история запросов и DDL. Она хранится локально, без ограничения глубины.
Есть инструмент для удаления и пересоздания зависимых объектов. Он появился когда мне понадобилось изменить тип поля в таблице, от которой зависело больше двухсот представлений. PostgreSQL честно сообщил, что сделать этого не позволит. А отсутствие решений в интернете и затем пара часов ручной работы сообщили, что такой инструмент точно нужен.
Теперь pgtools умеет строить дерево зависимостей, сохранять скрипты отката, удалять зависимые представления и пересоздавать их после изменения исходного объекта.
Кроме этого, есть:
— история DDL для объектов;
— встроенные системные запросы;
— работа в нескольких окнах в том числе в сплит-режиме;
— автоматический мониторинг блокировок;
— шифрованное хранение подключений с мастер-паролем;
— перенос настроек и истории между рабочими компьютерами.

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

Неудобно копировать строчки в таблице.

Или:

Хотелось бы при просмотре DDL матвью видеть также их индексы

Или просто скриншот с красной ошибкой без пояснений.
На самом деле это лучшая обратная связь. После неё приложение становится чуть менее моим и чуть больше пригодным для других людей.
Скачать pgtools можно здесь:
https://pgtools.ru/
Мне сейчас нужны не слова поддержки и не оценка идеи.
Мне нужны люди, которые установят программу и попробуют сделать в ней обычную рабочую задачу.
Подключитесь к тестовой базе. Откройте несколько вкладок. Выполните запрос. Посмотрите план. Найдите блокировку. Сравните две схемы.
А потом напишите мне одну вещь, которая раздражает. Одну вещь, из-за которой захотелось закрыть программу и вернуться в привычный клиент.
С этого и начнём.
А Макс ещё вернётся. Теперь у него появился инструмент, с которым он сможет гораздо быстрее выяснить, кто именно положил production. Даже если это был он.
#pgtools