PostgreSQL: от устройства движка до продакшена
Вы научитесь находить по плану запроса, где база тратит время, и приносить правку с замером до и после, которую на ревью принимают по цифрам. Запрос вчера отвечал быстро, сегодня держит страницу, а новый индекс планировщик не берёт. Ставить ничего не нужно: каждая лабораторная поднимает свежий PostgreSQL 16 под вашу попытку, а вы пишете только SQL. Один индекс без правки текста запроса превращает 215 мс в 0,1 мс на таблице в два миллиона строк.
Чему вы научитесь
- Читать EXPLAIN (ANALYZE, BUFFERS): сравнивать оценку с фактом, видеть Rows Removed by Filter и страницы буферов, находить узел, на котором уходит время.
- Собирать составной индекс по форме запроса: сначала равенство, потом диапазон, потом ORDER BY, а для Index Only Scan добавлять INCLUDE.
- Выбирать частичный индекс, индекс по выражению, GIN, GiST, BRIN или hash там, где обычный B-tree не помогает.
- Объяснять выбор планировщика через pg_stats и селективность, различать Nested Loop, Hash Join и Merge Join, замечать сортировку, ушедшую на диск из-за work_mem.
- Переписывать медленные запросы: EXISTS и анти-джойны, keyset вместо OFFSET, оконные функции вместо самообъединений, партиционирование с отсечением секций.
- Находить в pg_stat_statements запросы, которые съедают время сервера, даже если каждый вызов по отдельности быстрый.
- Выбирать уровень изоляции, читать pg_locks и pg_blocking_pids, строить очередь заданий на SKIP LOCKED и писать код без deadlock.
- Держать таблицы без раздувания: VACUUM и autovacuum на уровне таблицы, HOT-обновления и fillfactor, поиск транзакции, которая мешает уборке.
- Мерить WAL по LSN, поднимать streaming-реплику со слотом, настраивать логическую репликацию, снимать pg_dump и pg_basebackup и восстанавливаться на момент времени.
- Искать по jsonb, полнотекстово с русской морфологией и с опечатками через pg_trgm.
Сервис стоит, а база спокойна
Приложение пишет, что база висит, а процессор и диск спокойны. Вы идёте по порядку дежурного: находите ожидающих по wait_event_type = 'Lock', получаете держателя через pg_blocking_pids и смотрите, что он делает и как давно.
На картинке виновник — транзакция, которую открыли, изменили строку и бросили. За ней встают UPDATE той же строки, миграция с ALTER TABLE и даже обычные SELECT. Миграцию вы учитесь заворачивать в lock_timeout, чтобы она падала через две секунды, а не вставала в очередь.
Что показывает таблица после каждого UPDATE
UPDATE не правит строку на месте, а пишет новую версию. Обычный VACUUM возвращает место таблице, а не диску: в эксперименте файл остаётся 20 МБ во всех четырёх раундах.
Если открыта чужая транзакция со снимком, VACUUM не имеет права убрать мёртвые версии, и файл растёт: 20, 30, 40, 50 МБ. Виновника вы находите по backend_xmin. VACUUM FULL вернёт файл к 10 МБ, но на время перестройки блокирует таблицу, поэтому сначала подтверждают причину.
Что нужно знать
SQL на уровне SELECT с JOIN, GROUP BY и подзапросами, DDL вида CREATE TABLE и ALTER TABLE.
Готовность читать вывод сервера дословно: планы и сообщения об ошибках здесь главный рабочий инструмент.
Администрировать Linux не нужно: даже реплику в лабораторных поднимает песочница.
Где живёт отставание реплики
Репликация переносит на вторую машину журнал WAL. Разность sent_lsn и replay_lsn в pg_stat_replication показывает, сколько журнала реплика получила, но ещё не применила к данным. Поэтому «репликация работает» подтверждают не по запущенному процессу, а по state = streaming, отставанию в байтах и строкам, которые действительно есть на реплике.
Слот репликации заставляет primary хранить журнал, пока реплика его не заберёт. Забытый слот без потребителя растит pg_wal и может остановить primary по месту на диске.
Правка, которую принимают по плану
Для витрины категории вы разбираете три индекса под один запрос на одних данных. Индекс по (category_id, created_at) убирает сортировку, но оставляет проверку status после чтения. Индекс с status в середине закрывает запрос целиком, но весит больше. Частичный индекс даёт 22 страницы вместо 569.
У решения есть цена: для status = 'draft' частичный индекс не подходит, и сервер снова читает всю таблицу. Одна гипотеза, одна правка, замер до и после на тех же данных и проверка, что выдача не изменилась.
Для кого
Backend-разработчикам, которые уверенно пишут SQL, но не могут объяснить по плану, почему запрос стал медленным.
Тем, кто отвечает за базу своего сервиса: блокировки, раздутые таблицы, реплики, таймауты и миграции без простоя.
Аналитикам и инженерам данных, которым нужны планы, оконные функции и партиционирование.
Расследование на настоящем PostgreSQL
Каждая попытка — новый кластер PostgreSQL 16 в контейнере. Чекер создаёт его через initdb, заливает данные детерминированно, выполняет ваш solution.sql и сверяет результат запроса на двух наборах данных, план после ANALYZE или состояние системных каталогов. Ставить ничего не нужно, Linux администрировать тоже.
В модуле про репликацию песочница сама поднимает два кластера и делает pg_basebackup, а вы пишете только SQL: слоты, публикации, подписки и запросы к pg_stat_replication.
В итоговом проекте вы разгоняете медленный каталог без подсказок: топ запросов из pg_stat_statements, план узла-виновника, гипотеза с предиктом, одна правка и замер до и после. Завершает работу чек-лист надёжности перед выкладкой.
Фидбэк говорит, на каком наборе и что разошлось, а сообщения ERROR приходят от сервера дословно. Число попыток не ограничено.
Поддержка
Вы также можете задать любой вопрос по курсу у поддержки t.me/mentorix_support
Программа курса
Модулей: 12, уроков: 55. Задачи с кодом проверяются автоматически.
Как PostgreSQL устроен изнутри: процессы, память, MVCC
- Добро пожаловать: как устроен курс и живая песочница
- Архитектура: postmaster, бэкенды и общая память
- MVCC: версии строк вместо блокировок чтения
- Страницы, TOAST и то, как строки лежат на диске
B-tree вглубь: составные, частичные и покрывающие индексы
- Устройство B-tree и цена одного поиска
- Селективность: когда планировщик берёт индекс
- Составные индексы: порядок колонок решает
- Частичные индексы и индексы по выражению
- Покрывающие индексы и Index Only Scan
Специальные индексы: GIN, GiST, BRIN и выбор между ними
- GIN: массивы, jsonb и полнотекстовый поиск
- GiST и диапазоны: пересечения и исключающие ограничения
- BRIN: индексы для очень больших упорядоченных таблиц
- Hash-индексы и итоговая карта выбора индекса
Планы запросов: EXPLAIN, статистика и методы соединений
- EXPLAIN и EXPLAIN ANALYZE: читаем дерево плана
- Статистика планировщика: pg_stats и ANALYZE
- Соединения: Nested Loop, Hash Join, Merge Join
- Сортировки, агрегации и память work_mem
- Типовые убийцы планов: функции, OR, LIKE и неявные приведения
Оптимизация запросов на практике
- Переписывание запросов: EXISTS, IN, анти-джойны
- Пагинация: почему OFFSET убивает и что такое keyset
- CTE: удобство против материализации
- Оконные функции вместо самообъединений
- Партиционирование и pg_stat_statements
Транзакции, блокировки и VACUUM
- Уровни изоляции на живых аномалиях
- Блокировки строк и таблиц: кто кого ждёт
- Deadlock: как возникает и как проектировать без него
- VACUUM и bloat: куда девается место
- HOT-обновления и fillfactor
WAL, репликация и восстановление
- WAL: журнал, который спасает данные
- Streaming-репликация: поднимаем реплику руками
- Слоты репликации и отставание
- Логическая репликация: publication/subscription
- Бэкапы: pg_dump, pg_basebackup и точка восстановления
Поиск и полуструктурированные данные: jsonb, FTS, нечёткий поиск
- jsonb вглубь: операторы, пути и индексация
- Полнотекстовый поиск: tsvector, tsquery, конфигурации
- Генерируемые колонки и поддержка индекса поиска
- Нечёткий поиск: pg_trgm, similarity и LIKE с двумя процентами
- Массивы, hstore и выбор модели данных
Процедурный PostgreSQL: функции, триггеры, представления, права
- Функции и процедуры на PL/pgSQL
- Триггеры: аудит изменений без правки приложения
- Представления и материализованные представления
- Ограничения как защита данных
- Роли, права и мультиарендность через RLS
База и приложение: соединения, таймауты, идемпотентность
- Соединения: сколько их можно и сколько нужно
- Таймауты как контракт с базой
- Идемпотентность: ON CONFLICT и повторные запросы
- Очереди, outbox и уведомления
Эксплуатация: мониторинг, обслуживание, миграции без простоя
- Что смотреть каждый день: pg_stat и размеры
- Диагностика инцидента: кто виноват прямо сейчас
- Обслуживание: раздутые индексы, REINDEX, autovacuum
- Миграции без простоя
- Партиционирование в эксплуатации
Итоговый проект: разгоняем медленный сервис
- Диагностика: находим узкие места по данным
- Оптимизация: индексы плюс переписывание
- Надёжность и что дальше
Стоимость курса: 11990 ₽
Открыть курс на Mentorix · Все курсы