PostgreSQL: углублённый курс
Все объяснения, задачи и лабораторная в одном месте.
Можно сохранить страницу в PDF через печать браузера. Для печати разборы ответов раскрываются автоматически.
1. Storage, MVCC и vacuum
Объяснение
MVCC хранит версии строк для согласованного чтения. UPDATE создаёт новую версию; старые версии убираются после завершения нуждающихся в них транзакций. VACUUM важен для повторного использования места и обслуживания.
Задача для самостоятельного решения
Почему долгая транзакция мешает очистке?
Показать разбор ответа
Её snapshot может требовать старые версии. Найдите long-running transactions и их причину; увеличение частоты vacuum не отменяет необходимость сохранять видимые им строки.
2. Planner и EXPLAIN ANALYZE
Объяснение
Planner оценивает стоимость вариантов по статистике. EXPLAIN показывает план, EXPLAIN ANALYZE исполняет запрос и измеряет его. Для изменяющих запросов ANALYZE имеет реальные побочные эффекты.
Задача для самостоятельного решения
Оценка rows=10, actual rows=100000. Что проверить?
Показать разбор ответа
Актуальность статистики, корреляцию полей и параметры запроса. Неверная оценка может выбрать плохой JOIN; сначала объясните расхождение, затем добавляйте индекс.
3. B-tree, GIN, GiST и составные индексы
Объяснение
B-tree подходит для равенства и диапазонов по упорядоченным значениям. GIN индексирует составные значения, например элементы массива. Индекс ускоряет некоторые чтения, но увеличивает запись и место.
Задача для самостоятельного решения
Поможет ли индекс (a,b) одинаково запросам по a и только по b?
Показать разбор ответа
Обычно лучше обслуживается ведущая часть a. Возможности конкретного плана зависят от данных и версии; проверяйте EXPLAIN, не считайте порядок колонок неважным.
4. Locks, isolation и deadlocks
Объяснение
Изоляция управляет наблюдением конкурентных изменений. SELECT FOR UPDATE блокирует выбранные строки для конкурирующих операций. Deadlock возможен при разном порядке захвата и требует повторения всей транзакции.
Задача для самостоятельного решения
Два перевода захватывают счета в разном порядке. Как снизить deadlock?
Показать разбор ответа
Блокировать счета по одинаковому порядку идентификаторов и держать транзакцию короткой. Обработчик всё равно должен уметь повторить транзакцию после конфликта.
5. Partitioning, replication и failover
Объяснение
Резервная копия полезна только при проверенном восстановлении. WAL позволяет point-in-time recovery при наличии базовой копии и непрерывного архива. Репликация не заменяет backup: ошибочное удаление тоже реплицируется.
Задача для самостоятельного решения
Защищает ли read replica от случайного DELETE?
Показать разбор ответа
Обычно нет: она воспроизведёт DELETE. Нужны независимая копия, архив WAL и проверенный выбор момента восстановления.
6. Backup, restore и наблюдаемость
Объяснение
Производительность оценивают по workload, блокировкам, I/O и планам. Медленный запрос может ждать чужую транзакцию, а не вычислять. Увеличение пула соединений иногда ухудшает конкуренцию.
Задача для самостоятельного решения
Что проверить при росте latency без роста CPU?
Показать разбор ответа
Wait events, блокировки, диски, сеть и длительные транзакции. Добавлять CPU без определения ожидания может быть бесполезно.
Лабораторная работа
Подготовка
PostgreSQL в отдельной учебной сессии. TEMP-таблица не изменяет постоянную схему.
Учебный пример
CREATE TEMP TABLE measurements AS
SELECT n AS id, n % 100 AS group_id FROM generate_series(1,100000) n;
ANALYZE measurements;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM measurements WHERE id=50000;
CREATE INDEX ON measurements(id);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM measurements WHERE id=50000;Как работает пример и что ожидать
Первый запрос обычно использует последовательный просмотр, второй может выбрать индекс. Ожидается одна строка id=50000; конкретное время зависит от среды и кеша. Сравните plan, scanned rows и buffers, не только миллисекунды одного запуска.
Итоговая работа
Добавьте составной индекс для реального запроса, опыт двух конкурирующих транзакций и восстановление backup в новую базу. Зафиксируйте план до/после и докажите целостность восстановленных данных.
Проверка результата
1. Опишите исходные данные и условия запуска, чтобы другой человек мог повторить работу.
2. Приложите результат обычного сценария и сравните его с ожидаемым.
3. Проверьте неверный вход, граничный случай и отказ зависимости, если она есть.
4. Объясните выбранное решение и известное ограничение.
5. Сохраните исправления после самопроверки вместе с примером, который раньше не работал.
Как оценить работу
По каждому пункту поставьте 0 (не выполнено), 1 (выполнено с пробелами) или 2 (результат воспроизводим и объяснён). Если обязательный сценарий не работает, вернитесь к нему независимо от общей суммы. Это рубрика самопроверки: сайт не исполняет присланный код и не выдаёт автоматическую оценку проекта.