PostgreSQL: Путь к совершенству

Архитектура PostgreSQL: процессы, память и модель MVCC

PostgreSQL — это не просто хранилище данных, а сложная операционная система внутри вашей операционной системы. Чтобы писать эффективные запросы и администрировать базу данных, необходимо понимать, как она устроена «под капотом». В этой статье мы разберем три кита, на которых держится PostgreSQL: процессная модель, управление памятью и механизм многоверсионности (MVCC).

Процессная модель: Client-Server

В отличие от многих современных приложений, использующих потоки (threads), PostgreSQL опирается на процессы. Это фундаментальное архитектурное решение обеспечивает высокую стабильность: сбой в одном процессе обработки запроса не обрушивает всю базу данных.

Главный процесс (Postmaster)

Когда вы запускаете PostgreSQL, первым стартует процесс postgres (часто называемый Postmaster). Его задачи:

Слушать сетевой порт (по умолчанию 5432).

Управлять выделением общей памяти.

Запускать фоновые процессы.

Принимать новые подключения.

Обслуживающие процессы (Backends)

Для каждого нового клиента Postmaster создает (форкает) отдельный процесс — Backend. Если у вас 100 активных клиентов, в системе будет 100 процессов postgres, обслуживающих эти соединения. Именно поэтому в PostgreSQL так важно использовать пулеры соединений (например, PgBouncer), чтобы не перегружать сервер созданием тысяч процессов.

Фоновые процессы (Background Workers)

Помимо работы с клиентами, база данных выполняет множество служебных задач. Этим занимаются специализированные процессы:

* Background Writer: сбрасывает «грязные» (измененные) страницы из памяти на диск, чтобы освободить место для новых данных. * Checkpointer: выполняет контрольные точки (checkpoints), гарантируя, что все данные до определенного момента записаны на диск. * Autovacuum Launcher: следит за необходимостью очистки таблиц от мусора. * WalWriter: записывает журнал предзаписи (WAL) на диск.

!Взаимодействие процессов в PostgreSQL

Управление памятью

Память в PostgreSQL строго разделена на две категории: общая (доступная всем процессам) и локальная (личная память каждого процесса).

Общая память (Shared Memory)

Это область, выделяемая при старте сервера. Самый важный компонент здесь — Shared Buffers (общий буферный кеш).

PostgreSQL не работает с данными напрямую на диске. Чтобы прочитать или изменить строку, база данных сначала должна загрузить страницу (блок данных размером обычно 8 КБ), содержащую эту строку, в Shared Buffers. Если вы делаете UPDATE, изменение происходит именно в памяти. На диск оно попадет позже (благодаря Checkpointer или Background Writer).

Также в общей памяти находится WAL Buffers — буфер для журнала предзаписи, куда временно сохраняются транзакции перед фиксацией на диске.

Локальная память (Local Memory)

Каждый Backend-процесс запрашивает память для выполнения своих запросов. Ключевые параметры:

work_mem: память для операций сортировки (ORDER BY), хеширования (Hash Join) и группировки. Важно: этот лимит выделяется на каждую операцию* внутри запроса. Если сложный запрос имеет 5 сортировок, он может потребовать . * maintenance_work_mem: память для операций обслуживания, таких как создание индексов (CREATE INDEX) или VACUUM. * temp_buffers: буфер для временных таблиц.

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

Самая интересная и сложная часть PostgreSQL — это MVCC (Multi-Version Concurrency Control). Это механизм, который позволяет множеству пользователей читать и писать данные одновременно, не блокируя друг друга.

Главное правило MVCC: > «Пишущие не блокируют читающих, а читающие не блокируют пишущих».

В классических системах блокировок, если кто-то редактирует строку, никто другой не может её прочитать до завершения транзакции. В PostgreSQL это не так. Каждый видит согласованный снимок данных (Snapshot) на момент начала своей транзакции.

Как это работает: Версии строк

Когда вы делаете UPDATE, PostgreSQL не перезаписывает старую строку на диске. Вместо этого он:

Помечает старую строку как «неактуальную» (но не удаляет её физически).

Создает новую версию строки (новую версию кортежа) с измененными данными.

Таким образом, в таблице могут одновременно существовать несколько версий одной и той же строки. Транзакция А может видеть старую версию, а транзакция Б — новую.

!Принцип создания версий строк при обновлении

Служебные поля: xmin и xmax

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

* xmin: ID транзакции, которая создала эту версию строки. * xmax: ID транзакции, которая удалила (или обновила) эту версию строки.

Рассмотрим пример на числах. Допустим, текущий ID транзакции = 100.

INSERT: Мы вставляем строку. У неё xmin = 100, xmax = 0 (или NULL). Это значит: строка жива, создана транзакцией 100.

DELETE: Транзакция 101 удаляет строку. PostgreSQL не стирает данные, а проставляет xmax = 101. Теперь строка считается удаленной для всех транзакций, начавшихся после 101.

UPDATE: Транзакция 102 обновляет строку. Это выполняется как DELETE + INSERT.

* Старая версия: xmin = 100, xmax = 102 (помечена как удаленная). * Новая версия: xmin = 102, xmax = 0 (актуальная).

Когда вы делаете SELECT, PostgreSQL проверяет эти поля и сравнивает их с ID вашей текущей транзакции, чтобы решить: «Вижу я эту строку или нет?».

Более подробно о механике версионности можно прочитать в книге postgrespro.ru или в статье про MVCC.

Проблема «мертвых душ» и VACUUM

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

Для решения этой проблемы существует процесс VACUUM (и его автоматическая версия Autovacuum). Он сканирует таблицы, находит мертвые версии строк и помечает занимаемое ими место как свободное для повторного использования.

Изоляция транзакций

MVCC является основой для уровней изоляции транзакций. В PostgreSQL по умолчанию используется уровень Read Committed. Это означает, что запрос видит данные, зафиксированные на момент начала выполнения этого запроса.

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

Итоги

Процессы: PostgreSQL использует модель «один клиент — один процесс». Главный процесс Postmaster управляет всей системой.

Память: Данные кешируются в общей памяти (Shared Buffers), а сложные операции (сортировки) выполняются в локальной памяти процессов (work_mem).

MVCC: Обновление данных (UPDATE) создает новую версию строки, а не перезаписывает старую. Это позволяет читать данные без блокировок со стороны пишущих транзакций.

Очистка: Старые версии строк накапливаются и требуют очистки процессом VACUUM, иначе производительность падает.

Профессиональный SQL: рекурсивные CTE, оконные функции и конструкция LATERAL

В предыдущей статье мы разобрали архитектуру PostgreSQL: как процессы взаимодействуют с памятью и как MVCC обеспечивает конкурентный доступ. Теперь, понимая, как база данных работает «под капотом», пришло время научиться отдавать ей команды, достойные профессионала.

Многие разработчики используют SQL лишь как простой инструмент для SELECT * FROM table. Однако PostgreSQL предлагает мощнейшие инструменты, которые позволяют перенести сложную логику обработки данных с уровня приложения (Python, Java, Go) на уровень СУБД, где данные обрабатываются быстрее и эффективнее.

В этой статье мы разберем три инструмента, отличающих новичка от эксперта: рекурсивные обобщенные табличные выражения (CTE), оконные функции и соединение LATERAL.

Рекурсивные CTE (Common Table Expressions)

Обычные CTE (конструкция WITH) делают код чище, позволяя именовать подзапросы. Но рекурсивные CTE (WITH RECURSIVE) — это способ работы с иерархическими структурами данных: деревьями категорий, организационными структурами, графами связей или комментариями к постам.

Анатомия рекурсии

Рекурсивный запрос всегда состоит из двух частей, объединенных оператором UNION ALL:

Нерекурсивная часть (Anchor member): Стартовая точка. Это запрос, который выполняется один раз и создает первый набор строк.

Рекурсивная часть (Recursive member): Запрос, который ссылается на само CTE. Он выполняется многократно, каждый раз используя результаты предыдущей итерации, пока не перестанет возвращать новые строки.

!Принцип выполнения рекурсивного запроса: от стартовой точки через цикл до пустого результата

Пример: Организационная структура

Представьте таблицу employees (id, name, manager_id). Нам нужно найти всех подчиненных конкретного начальника (ID = 1) на всех уровнях вложенности.

Как это работает:

PostgreSQL выполняет первую часть и находит сотрудника с ID=1.

Затем он берет этот результат и подставляет его в JOIN subordinates s во второй части запроса, находя прямых подчиненных.

Затем он берет найденных подчиненных и снова прогоняет их через вторую часть, находя подчиненных второго уровня.

Процесс останавливается, когда JOIN перестает находить новые записи.

> Важно: Всегда следите за тем, чтобы рекурсия имела условие выхода. Если данные содержат циклические ссылки (А начальник Б, а Б начальник А), запрос уйдет в бесконечный цикл. Начиная с PostgreSQL 14, для контроля циклов можно использовать специальный синтаксис CYCLE.

Подробнее о синтаксисе можно прочитать в документации postgrespro.ru.

Оконные функции

Оконные функции — это, пожалуй, самый часто используемый инструмент в аналитическом SQL. В отличие от GROUP BY, который схлопывает строки в одну (например, считая сумму зарплат по отделу), оконные функции позволяют сохранить исходные строки, но добавить к ним агрегированную информацию.

Синтаксис

Ключевое слово — OVER. Оно определяет «окно» (набор строк), к которому применяется функция.

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

Фрейм окна (Window Frame)

Самая мощная часть оконных функций — это возможность считать скользящие агрегаты (например, накопительный итог). Для этого используется уточнение границ окна.

Рассмотрим формулу накопительного итога:

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

В SQL это реализуется так:

Разберем ROWS BETWEEN ...: * UNBOUNDED PRECEDING: начало окна — самая первая строка в выборке. * CURRENT ROW: конец окна — текущая строка.

Популярные функции

* row_number(): нумерация строк внутри окна (1, 2, 3...). * rank(): ранжирование (1, 2, 2, 4...) — если значения равны, ранг одинаковый, следующий пропускается. * dense_rank(): плотное ранжирование (1, 2, 2, 3...) — пропусков нет. * lag(column, offset): доступ к значению из предыдущей строки (полезно для поиска разницы с прошлым месяцем). * lead(column, offset): доступ к значению из следующей строки.

!Визуализация работы функций LAG и LEAD для доступа к соседним строкам

Конструкция LATERAL

LATERAL — это ключевое слово, которое меняет правила игры в соединениях (JOIN).

В обычном JOIN подзапрос справа не может ссылаться на таблицу слева. Они независимы до момента соединения. LATERAL позволяет подзапросу справа видеть строку, которую в данный момент обрабатывает запрос слева.

Проще всего думать о LATERAL как о цикле for-each в программировании.

Пример: Топ-3 товара в каждой категории

Допустим, у нас есть таблицы categories и products. Нам нужно выбрать 3 самых дорогих товара для каждой категории. Обычным JOIN это сделать сложно (нужно использовать оконные функции и фильтрацию). С LATERAL это выглядит естественно:

Что здесь происходит:

PostgreSQL берет строку из таблицы categories (переменная c).

Для этой строки запускает подзапрос внутри LATERAL, передавая туда c.id.

Подзапрос находит 3 дорогих товара именно для этой категории.

Результаты объединяются.

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

Больше примеров использования можно найти в статье на habr.com.

Итоги

Рекурсивные CTE позволяют обрабатывать иерархические данные (деревья, графы) прямо в базе данных, используя конструкцию UNION ALL внутри WITH RECURSIVE.

Оконные функции (OVER) дают возможность выполнять вычисления по набору строк, связанному с текущей строкой, не уменьшая общее количество строк в выборке (в отличие от группировки).

LATERAL позволяет создавать коррелированные подзапросы в секции FROM, работая по принципу цикла: «для каждой строки левой таблицы выполни запрос справа».

Использование этих инструментов снижает объем кода на бэкенде и часто повышает производительность за счет оптимизаций внутри PostgreSQL.