Архитектура PostgreSQL целиком: процессы, память backend, пять стадий запроса и точки расширения

В top у PostgreSQL десяток процессов с одним именем, память растёт с каждым новым соединением, а запрос к представлению в плане выглядит как запрос к таблице. Это одна и та же архитектура: один процесс на соединение, общая память под страницы и локальные контексты под всё остальное, пять стадий между текстом запроса и строками. Здесь каждый из этих трёх слоёв показан на живом кластере, а механизмы разобраны в статьях раздела: хранение начинается со страницы 8 КБ и заголовка строки, видимость версий строк — с MVCC, надёжность — с WAL.

DBA / 01koddo

Один SELECT к представлению: пять стадий в одном backend

Стенд

Таблица на 4 млн строк (261 МБ): для процессов и стадий размер не важен, для памяти сортировки достаточно.

docker run -d --name dba-postgresql-architecture-overview --memory 1g -e POSTGRES_PASSWORD=pg postgres:18
docker exec dba-postgresql-architecture-overview sh -c 'apt-get update -qq; apt-get install -y -qq procps'
docker exec -i dba-postgresql-architecture-overview psql -U postgres -Atq <<'SQL'
select version();
create table t as select g as id, md5(g::text) as v from generate_series(1, 4000000) g; analyze t;
create view v_big as select id, v from t where id % 1000 = 0;
show io_method; show io_workers;
SQL

Вывод:

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
worker
3

Опыт 1: какие процессы запущены

Два соединения держат pg_sleep(20), за эти секунды снимаем список процессов.

docker exec -d dba-postgresql-architecture-overview psql -U postgres -c "select pg_sleep(20)"
docker exec -d dba-postgresql-architecture-overview psql -U postgres -d postgres -c "select pg_sleep(20)"
docker exec dba-postgresql-architecture-overview ps -o pid,ppid,cmd -ax --sort=pid

Вывод (строки самих ps, sort и клиентов psql убраны):

    PID    PPID CMD
      1       0 postgres
     68       1 postgres: io worker 0
     69       1 postgres: io worker 1
     70       1 postgres: io worker 2
     71       1 postgres: checkpointer 
     72       1 postgres: background writer 
     74       1 postgres: walwriter 
     75       1 postgres: autovacuum launcher 
     76       1 postgres: logical replication launcher 
    119       1 postgres: postgres postgres [local] SELECT
    126       1 postgres: postgres postgres [local] SELECT

PID 1 — postmaster, родитель всех процессов postgres:. Backend на соединение — 119 и 126 с PPID 1: два соединения, два процесса. Остальные подписаны так:

• checkpointer пишет грязные страницы по контрольной точке (Checkpoint), background writer подчищает буферы между точками (shared_buffers и clock sweep).
• walwriter сбрасывает WAL, не дожидаясь коммита (WAL), autovacuum launcher запускает воркеры очистки (VACUUM изнутри, настройка autovacuum).
• logical replication launcher стартует и без подписок: логическая репликация.
• io worker 0–2 новые в 18: при io_method = worker асинхронные чтения выполняют они, io_workers = 3 (вывод стенда): fsync и путь ввода-вывода.

walsender, archiver и parallel worker в списке нет: их порождают реплика, archive_mode и параллельный запрос (потоковая репликация, архив WAL, параллельные запросы). Два из них появятся в опыте 4.

Опыт 2: память одного backend

Backend держит память не только в общей области (shared_buffers), но и в локальных контекстах MemoryContext. Из README модуля:

MessageContext --- this context holds the current command message from the
frontend, as well as any derived storage that need only live as long as
the current message (for example, in simple-Query mode the parse and plan
trees can live here).  This context will be reset, and any children
deleted, at the top of each cycle of the outer loop of PostgresMain.  This
is kept separate from per-transaction and per-portal contexts because a
query string might need to live either a longer or shorter time than any
single transaction or portal.

(src/backend/utils/mmgr/README, строки 214–221, тег REL_18_6.) ExecutorState живёт только пока идёт запрос, поэтому тяжёлую сортировку держим открытым курсором: после FETCH все 4 млн строк отсортированы, а запрос к pg_backend_memory_contexts видит их контексты.

select name, level, total_bytes, used_bytes from pg_backend_memory_contexts
where name in ('TopMemoryContext','CacheMemoryContext','MessageContext','ExecutorState') or total_bytes > 1000000
order by level, name, total_bytes;
select sum(total_bytes) as total, count(*) as contexts from pg_backend_memory_contexts;
-- на свежем соединении: А и Б; затем:
set work_mem = '512MB';
begin;
declare c cursor for select v from t order by v;
fetch 1 from c;
-- снова А и Б; затем close c; commit; и Б в третий раз

Вывод:

== до
        name        | level | total_bytes | used_bytes 
--------------------+-------+-------------+------------
 TopMemoryContext   |     1 |       99456 |      93528
 CacheMemoryContext |     2 |      524288 |     472056
 MessageContext     |     2 |      131072 |      37752
 ExecutorState      |     4 |       49200 |      35952

  total  | contexts 
---------+----------
 1557144 |      125

== курсор открыт
        name        | level | total_bytes | used_bytes 
--------------------+-------+-------------+------------
 TopMemoryContext   |     1 |      132224 |      99248
 CacheMemoryContext |     2 |      524288 |     518024
 MessageContext     |     2 |       65536 |      38160
 ExecutorState      |     4 |       16384 |      10736
 ExecutorState      |     4 |       49200 |      35952
 TupleSort main     |     5 |   100671536 |  100667032
 Caller tuples      |     7 |   167772048 |  160000960

   total   | contexts 
-----------+----------
 270098008 |      135

== после COMMIT
  total  | contexts 
---------+----------
 1617560 |      129

Что видно:

• Новое соединение занимает около 1,5 МБ: 125 контекстов, 1 557 144 байта. Крупнейшие: CacheMemoryContext (кэши каталога) 524 288, MessageContext 131 072, TopMemoryContext 99 456. Цену сотен таких соединений на хосте считает статья про хост под PostgreSQL.
• Сортировка добавила TupleSort main с массивом указателей на строки (100 671 536 байт) и Caller tuples с самими кортежами (167 772 048, по 40 байт на строку). Вместе 268 443 584 байта; total вырос до 270 098 008. ExecutorState курсора занимает лишь 16 384 байта: объём создают узлы плана внутри него; строка с 49 200 — ExecutorState самого запроса к pg_backend_memory_contexts.
• После COMMIT total вернулся к 1 617 560 (129 контекстов); CacheMemoryContext по README не сбрасывается до конца соединения. RSS процесса в ОС не измерялся. Что делает сортировка, когда work_mem не хватает, — в work_mem и спилле.

Опыт 3: путь запроса через представление

Параметры debug_print_parse, debug_print_rewritten и debug_print_plan печатают дерево после каждой стадии; весь вывод занимает сотни строк, поэтому grep оставляет узлы, по которым видна подмена.

docker exec -e PGOPTIONS="-c client_min_messages=log -c debug_print_parse=on -c debug_print_rewritten=on -c debug_print_plan=on" \
  dba-postgresql-architecture-overview psql -U postgres -Atq -c "select v from v_big where id < 5000" 2>&1 | grep -E 'LOG:|:aliasname|:rtekind|:relkind|\{GATHER|\{SEQSCAN'

Вывод:

LOG:  parse tree:
         :aliasname v_big 
      :rtekind 0 
      :relkind v 
LOG:  rewritten parse tree:
         :aliasname v_big 
      :rtekind 1 
               :aliasname t 
            :rtekind 0 
            :relkind r 
      :relkind v 
LOG:  plan:
      {GATHER 
         {SEQSCAN 
         :aliasname v_big 
      :rtekind 1 
      :relkind v 
         :aliasname t 
      :rtekind 0 
      :relkind r 

После анализа v_big — обычное отношение (rtekind 0) с relkind v. После переписывания та же v_big стала подзапросом (rtekind 1), внутри которого лежит таблица t (relkind r). Это сделало правило _RETURN: представление в PostgreSQL — правило ON SELECT DO INSTEAD. Проверка: select rulename, ev_type, is_instead from pg_rewrite where ev_class = 'v_big'::regclass выдаёт _RETURN, ev_type = 1 (SELECT) и is_instead = t. В плане v_big осталась только в rtable, а узлы — GATHER и SEQSCAN.

Как читать такие планы, показано в EXPLAIN продвинуто; здесь хватает explain (verbose, costs off) select v from v_big where id < 5000.

Gather
  Output: t.v
  Workers Planned: 2
  ->  Parallel Seq Scan on public.t
        Output: t.v
        Filter: ((t.id < 5000) AND ((t.id % 1000) = 0))

Условие представления id % 1000 = 0 и условие пользователя id < 5000 слились в один фильтр. Откуда у Gather стоимость — в модели стоимости, как узел делит работу между процессами — в параллельных запросах. Время стадий дают log_parser_stats, log_planner_stats и log_executor_stats:

docker exec -e PGOPTIONS="-c client_min_messages=log -c log_parser_stats=on -c log_planner_stats=on -c log_executor_stats=on" \
  dba-postgresql-architecture-overview psql -U postgres -Atq -c "select v from v_big where id < 5000" 2>&1 | awk '/LOG:/{n=$0} /elapsed/{sub(/LOG:  /,"",n); print n"  "$(NF-2)" s"}'

PARSER STATISTICS  0.000048 s
PARSE ANALYSIS STATISTICS  0.000163 s
REWRITER STATISTICS  0.000024 s
PLANNER STATISTICS  0.000196 s
EXECUTOR STATISTICS  0.030600 s

Эти пять чисел из одного прогона стоят на схеме; от прогона к прогону они плавают (в соседнем анализ занял 0,000522 с). Порядок вызовов задаёт exec_simple_query в src/backend/tcop/postgres.c: разбор в строке 1065, анализ с переписыванием в 1190–1191, планирование в 1193–1194, PortalRun в 1274.

		querytree_list = pg_analyze_and_rewrite_fixedparams(parsetree, query_string,
															NULL, 0, NULL);

		plantree_list = pg_plan_queries(querytree_list, query_string,
										CURSOR_OPT_PARALLEL_OK, NULL);

Анализ и переписывание — две стадии одной функции: parse_analyze_fixedparams, затем pg_rewrite_query (строки 666–695). Четыре стадии до исполнителя занимают доли миллисекунды на каждый запрос; как не повторять планирование у prepared-запросов, разобрано в регрессиях планов. Исполнитель вызывает ExecProcNode у корня плана (src/include/executor/executor.h, строки 308–316): каждый узел просит строку у дочернего и отдаёт одну наверх.

Опыт 4: точки расширения

Методы доступа перечислены в pg_am: индексные (amtype = 'i') и табличные ('t'); запрос select amname, amtype, amhandler from pg_am order by amtype, amname даёт:

 amname | amtype |      amhandler       
--------+--------+----------------------
 brin   | i      | brinhandler
 btree  | i      | bthandler
 gin    | i      | ginhandler
 gist   | i      | gisthandler
 hash   | i      | hashhandler
 spgist | i      | spghandler
 heap   | t      | heap_tableam_handler

Шесть индексных методов разобраны в B-tree изнутри и GIN, GiST, BRIN и hash. Табличный пока один, но с PostgreSQL 12 хранение вынесено в структуру TableAmRoutine (примечания к выпуску 12, документация): страница и строка стоят за указателями на функции.

	TableScanDesc (*scan_begin) (Relation rel,
								 Snapshot snapshot,
								 int nkeys, struct ScanKeyData *key,
								 ParallelTableScanDesc pscan,
								 uint32 flags);

(src/include/access/tableam.h, строки 326–330; вся структура — 288–843, рядом tuple_insert в строке 509 и остальные операции над таблицей.) Расширения — это pg_extension плюс библиотеки, которые сервер загружает при старте, а хуки — указатели в ядре: planner_hook в planner() (planner.c, строка 305) и ExecutorStart_hook (executor.h, строки 75–76). pg_stat_statements ставит оба в _PG_init (contrib/pg_stat_statements/pg_stat_statements.c, строки 476–479; разбор в статье о нём).

Загрузим pg_prewarm и pg_stat_statements и включим архив WAL (как он используется при восстановлении). Список в ALTER SYSTEM пишут без кавычек вокруг всего значения: строка в кавычках сохраняется как одно имя файла, и сервер не стартует (FATAL: could not access file "pg_prewarm, pg_stat_statements").

docker exec -i dba-postgresql-architecture-overview psql -U postgres -Atq \
  -c "alter system set shared_preload_libraries = pg_prewarm, pg_stat_statements" \
  -c "alter system set archive_mode = on" -c "alter system set archive_command = 'true'" \
  -c "create extension pg_stat_statements"
docker restart dba-postgresql-architecture-overview
docker exec dba-postgresql-architecture-overview sh -c 'mkdir -p /tmp/wal; pg_receivewal -D /tmp/wal -U postgres >/dev/null 2>&1 & P=$!; sleep 2; psql -U postgres -Atq -c "select pg_switch_wal()" >/dev/null; sleep 2; ps -o pid,ppid,cmd -ax --sort=pid | grep -E "archiver|walsender|autoprewarm"; kill $P'

Вывод (строки самих sh и grep убраны):

     35       1 postgres: archiver last was 000000010000000000000019
     36       1 postgres: autoprewarm leader 
     80       1 postgres: walsender postgres [local] streaming 0/1A000060

archiver появился из archive_mode, walsender — из подключения pg_receivewal (потоковая репликация), а autoprewarm leader — из shared_preload_libraries: библиотека зарегистрировала фоновый воркер, и postmaster запустил его ещё одним ребёнком. Политика релизов: новая мажорная версия выходит примерно раз в год, поддержка длится пять лет (политика версий).

Что из этого следует

• Соединение — это процесс и около 1,5 МБ контекстов в самом начале, а тяжёлый узел плана добавляет сотни мегабайт (опыт 2: 268 МБ на сортировку 4 млн строк). max_connections считай вместе с work_mem и ценой простаивающих соединений; сотни клиентов собирают в десятки backend пулером соединений.
• Запрос к представлению планируется как запрос к таблицам под ним: условие представления и условие пользователя сливаются в один фильтр (опыт 3), поэтому читай Filter целиком.

Чек-лист

1. На новом сервере сними ps -o pid,ppid,cmd и подпиши каждый процесс; лишние parallel worker и walsender — признак нагрузки, которой ты не ждал.
2. Память одного backend снимай pg_backend_memory_contexts из этой же сессии; ExecutorState виден только пока запрос или курсор открыт.
3. Убери стенд вместе с томом данных: docker rm -f -v dba-postgresql-architecture-overview.