Администрирование PostgreSQL, часть 3 - Архитектура и MVCC

Третья часть курса по PostgreSQL - смотрим, как PostgreSQL устроен внутри: процессы и память, подготовленные операторы, курсоры, а также механизм MVCC, снимки данных и уровни изоляции транзакций 🐧.

Предисловие

В первой и второй частях мы говорили об инструментах - как поднять сервер и как нему подключиться. Но сегодня мы заглянуть под капот: почему PostgreSQL умеет одновременно обслуживать сотни клиентов, что происходит в оперативной памяти при выполнении запроса, и почему две транзакции могут одновременно менять базу и не видеть “грязных” данных друг друга.

Второй вопрос в этом уроке - MVCC (Multiversion Concurrency Control), многоверсионность. Тема без картинок понимается тяжело, а с живым стендом в Docker - гораздо проще: заведём две параллельные сессии psql и своими глазами увидим, как одна и та же строка выглядит по-разному для разных транзакций.

Вводные данные

ПО, используемое в статье:

Стенд - тот самый контейнер postgres из первой части, запущенный и доступный на 127.0.0.1:5432.

Клиент-серверная архитектура

PostgreSQL устроен по классической клиент-серверной модели. Сервер - это управляющий процесс postmaster, который слушает порт и на каждое новое подключение форкает отдельный процесс - backend. Именно поэтому у PostgreSQL “дорогие” подключения: не поток внутри одного процесса, а полноценный процесс ОС на каждого клиента.

Убедиться в этом можно, не отходя от своего же контейнера:

cd ~/Postgres

docker compose exec postgres ps -eHf

Вывод будет примерно таким:

UID          PID    PPID  C STIME TTY          TIME CMD
postgres       1       0  0 Sep08 ?        00:00:11 postgres
postgres      27       1  0 Sep08 ?        00:00:00   postgres: io worker 0
postgres      28       1  0 Sep08 ?        00:00:00   postgres: io worker 1
postgres      29       1  0 Sep08 ?        00:00:00   postgres: io worker 2
postgres      30       1  0 Sep08 ?        00:00:00   postgres: checkpointer 
postgres      31       1  0 Sep08 ?        00:00:00   postgres: background writer 
postgres      33       1  0 Sep08 ?        00:00:00   postgres: walwriter 
postgres      34       1  0 Sep08 ?        00:00:01   postgres: autovacuum launcher 
postgres      35       1  0 Sep08 ?        00:00:00   postgres: logical replication launcher

Заметьте - ни одного backend-процесса под конкретного клиента здесь нет, только сам postmaster (PID 1) и фоновые служебные процессы. Откроем подключение и повторим:

docker compose exec postgres psql -U ivan -d raven

SELECT pg_backend_pid();

\! ps -eHf

В списке появится новая строка postgres: ivan raven [local] idle - это наш backend, тот самый PID, который вернул pg_backend_pid(). Как только сессия завершится - процесс исчезнет.

UID          PID    PPID  C STIME TTY          TIME CMD
root       52196       0  0 17:08 pts/1    00:00:00 /usr/lib/postgresql/18/bin/psql -U ivan -d raven
root       52341   52340  0 17:10 pts/1    00:00:00     ps -eHf
postgres       1       0  0 Sep08 ?        00:00:11 postgres
postgres      27       1  0 Sep08 ?        00:00:00   postgres: io worker 0
postgres      28       1  0 Sep08 ?        00:00:00   postgres: io worker 1
postgres      29       1  0 Sep08 ?        00:00:00   postgres: io worker 2
postgres      30       1  0 Sep08 ?        00:00:00   postgres: checkpointer 
postgres      31       1  0 Sep08 ?        00:00:00   postgres: background writer 
postgres      33       1  0 Sep08 ?        00:00:00   postgres: walwriter 
postgres      34       1  0 Sep08 ?        00:00:01   postgres: autovacuum launcher 
postgres      35       1  0 Sep08 ?        00:00:00   postgres: logical replication launcher 
postgres   52202       1  0 17:08 ?        00:00:00   postgres: ivan raven [local] idle

В DBeaver прямого доступа к ps -eHf нет (да и не нужен), но список активных backend-ов виден в разделе Sessions свойств подключения (клик правой кнопкой по соединению в Tools → Session Manager) - по сути та же информация, что в pg_stat_activity, но в табличке с кнопкой “прибить” сессию.

Память: общая и локальная

У каждого backend-процесса есть доступ к двум видам памяти:

• Общая память (shared memory) - буферный кеш (shared_buffers), таблица блокировок и прочие структуры, видимые всем backend-ам одновременно;
• Локальная память - то, что принадлежит только одному backend-у: подготовленные операторы, курсоры, память для сортировок (work_mem) и так далее.

SHOW shared_buffers;

SHOW work_mem;

В DBeaver все параметры сервера сразу видны в свойствах подключения, вкладка Configuration - удобный табличный вид того же самого pg_settings, который мы трогали во второй части.

Подготовленные операторы

Подготовленный оператор (PREPARE) разбирает запрос один раз и сохраняет план выполнения в локальной памяти сессии. Основная польза PREPARE - избежать повторного парсинга/планирования запроса при многократном выполнении с разными параметрами.

PREPARE get_now AS SELECT now();

EXECUTE get_now;

SELECT * FROM pg_prepared_statements;

DEALLOCATE get_now;

• PREPARE get_now AS SELECT now(); - создаётся именованный запрос get_now, который при выполнении вернёт текущую дату/время. Запрос парсится и планируется заранее, один раз;
• EXECUTE get_now; - выполняется ранее подготовленный запрос. Если бы у запроса были параметры ($1, $2 и т.д.), их нужно было бы передать здесь;
• SELECT * FROM pg_prepared_statements; - системное представление, показывающее список всех подготовленных выражений в текущей сессии (имя, текст запроса, время создания, типы параметров и т.д.);
• DEALLOCATE get_now;- удаляет подготовленное выражение, освобождая память.

Курсоры

Курсор позволяет забирать результат запроса по частям, не вычитывая всё в память сразу:

BEGIN;

DECLARE c CURSOR FOR SELECT * FROM pg_roles;

FETCH 2 FROM c;
FETCH 2 FROM c;

CLOSE c;
COMMIT;

DBeaver и так работает через курсоры внутри - когда результат запроса большой, он подгружает строки порциями, размер которых настраивается в Preferences → Editors → Data Editor (ResultSet fetch size). Разница лишь в том, что там это происходит автоматически, а не по явной команде FETCH.

MVCC: многоверсионность

PostgreSQL не перезаписывает строки при UPDATE - вместо этого он создаёт новую версию строки, а старую помечает как устаревшую. Каждая версия хранит два системных столбца:

• xmin - номер транзакции, создавшей эту версию;
• xmax - номер транзакции, удалившей эту версию (0, если версия ещё актуальна).

Заведём демонстрационную таблицу - никакого отношения к будущей схеме raven она не имеет, просто песочница для примера:

CREATE TABLE demo_mvcc (id INT PRIMARY KEY, note TEXT);

INSERT INTO demo_mvcc VALUES (1, 'первая версия');

SELECT xmin, xmax, * FROM demo_mvcc;

Теперь для демо работы MVCC откроем две сессии psql в терминале:

docker compose exec postgres psql -U ivan -d raven

В первой сессии (назовём её A) начинаем транзакцию и меняем строку, но не коммитим:

BEGIN;

UPDATE demo_mvcc SET note = 'вторая версия' WHERE id = 1;

Во второй сессии (B) смотрим на ту же строку:

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  777 |    0 |  1 | первая версия

Сессия B видит старую версию - первая версия, хотя A уже выполнила UPDATE.

Возвращаемся в A и коммитим:

COMMIT;

Повторяем SELECT в сессии B - теперь она видит новую версию.

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  778 |    0 |  1 | вторая версия

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

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

Снимок данных, который видит транзакция, зависит от уровня изоляции:

Повторим предыдущий эксперимент, но в сессии B явно укажем REPEATABLE READ:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  775 |    0 |  1 | вторая версия

BEGIN;

UPDATE demo_mvcc SET note = 'третья версия' WHERE id = 1;

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc; -- видим ту же версию, что и в начале

 xmin | xmax | id |     note      
------+------+----+---------------
  775 |  776 |  1 | вторая версия

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc; -- а теперь уже новую

 xmin | xmax | id |     note      
------+------+----+---------------
  776 |    0 |  1 | третья версия

В Read Committed (по умолчанию) второй SELECT в сессии B увидел бы новую версию сразу после COMMIT сессии A, даже не выходя из своей транзакции - именно поэтому уровень изоляции не задаётся один раз “на весь проект”, а осознанно выбирается под задачу.

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

Коротко про блокировки в PostgreSQL

Если два клиента одновременно пытаются изменить одну и ту же строку - MVCC здесь не спасает, придётся подождать. Повторим эксперимент с сессиями A и B, но теперь обе будут писать:

BEGIN;

UPDATE demo_mvcc SET note = 'от A' WHERE id = 1;

UPDATE demo_mvcc SET note = 'от B' WHERE id = 1; -- зависнет

Сессия B встанет в ожидание:

Посмотреть, кто кого блокирует, можно из третьей сессии:

SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event IS NOT NULL;

SELECT pg_blocking_pids(pid), * FROM pg_stat_activity WHERE pid = <PID сессии B>;

Как только сессия A выполнит COMMIT или ROLLBACK - сессия B немедленно продолжит работу.

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id | note 
------+------+----+------
  784 |  784 |  1 | от B

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id | note 
------+------+----+------
  784 |  784 |  1 | от B

Полноценный разговор про блокировки, pg_locks и дедлоки будет отдельным уроком - здесь важно только зафиксировать сам факт: чтение никогда не блокирует и не блокируется, а вот конкурентная запись одной и той же строки - блокируется всегда, MVCC тут ни при чём.

DDL - тоже транзакции

Отдельная приятная особенность PostgreSQL: команды CREATE, ALTER, DROP - такие же транзакционные, как и обычный INSERT. В отличие, например, от MySQL, где DDL коммитится немедленно.

BEGIN;

CREATE TABLE ddl_demo (id INT);

\dt ddl_demo -- таблицы не видно, хотя A её уже создала

Did not find any tables named "ddl_demo".

ROLLBACK; -- таблица не создастся вообще

Если бы вместо ROLLBACK был COMMIT - таблица бы появилась и в сессии B. Работает это и для нескольких DDL-команд подряд в одной транзакции - весь блок либо применится целиком, либо не применится вообще.

Уборка за собой

Демонстрационная таблица больше не нужна:

DROP TABLE demo_mvcc;

💡 Совет

При выполнении DROP в DBeaver, Бобёр любезно запросит у нас подтверждение удаления таблицы.

Возможные проблемы

• DBeaver “подвисает” на простом SELECT

Почти всегда причина в том, что в другом SQL-редакторе того же подключения осталась открытая транзакция без COMMIT/ROLLBACK (особенно легко забыть при выключенном Auto-commit).

• тестовый дедлок между сессиями A и B

Если в примере выше поменять порядок и в сессии B сначала попытаться изменить строку, которую уже держит A, а потом дать A попытаться изменить то, что держит B - получите классический deadlock, и PostgreSQL сам прервёт одну из транзакций с ошибкой deadlock detected.

ERROR:  deadlock detected
DETAIL:  Process 49 waits for ShareLock on transaction 800; blocked by process 42.
Process 42 waits for ShareLock on transaction 797; blocked by process 49.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,8) in relation "demo_mvcc"

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

Послесловие

Постепенно погружаемся в недра PostgreSQL…

MVCC довольно абстрактная вещь, особенно на словах. Но в этом уроке мы наглядно увидели, как две сессии смотрят на одну строку. Возможно вы уже где-то слышали и про VACUUM - механизм уборки тех самых старых версий строк, которые мы сегодня смотрели через xmin/xmax.

Следующая тема - конфигурирование PostgreSQL: postgresql.conf, ALTER SYSTEM и то, откуда PostgreSQL на самом деле берёт действующие значения параметров. Не пропустите.

Спасибо, что читаете. Успехов в изучении PostgreSQL! 🐧

Используемые материалы

• Документация: обзор архитектуры PostgreSQL
• Документация: параллелизм через MVCC
• Документация: уровни изоляции транзакций

👨‍💻Ну и…

Не забывайте про нашу телегу📱и чат 💬

Или может хотите стать соавтором? Тогда клик сюда🔗

Всех благ✌️

That should be it. If not, check the logs 🙂