pg_dump изнутри: общий снимок, параллельный дамп по таблицам и порядок pg_restore

pg_dump -j 8 отработал за те же 12,6 с, что и -j 4, а таблицы под нагрузкой копили мёртвые строки, которые VACUUM не мог убрать. Причина общая: дамп — одна транзакция REPEATABLE READ, снимок которой делят все воркеры, а работа делится по таблицам, не по строкам. Снимок на время дампа держит горизонт xmin.

DBA / 01koddo

Один снимок на пять соединений и потолок по самой большой таблице

Стенд

Место дампа среди других способов бэкапа разобрано в стратегии бэкапов; здесь устройство самого pg_dump.

Одна база, пять таблиц: big на 6 млн строк и t1–t4 по 1,5 млн, у каждой первичный ключ и индекс по a, у t2 и t3 внешний ключ на t1. Одна большая таблица плюс несколько средних нужна, чтобы параллельность имела смысл: на единственной таблице -j не даёт ничего. Это заметно и в опыте 1.

docker run -d --name dba-pg-dump-internals --memory 2g -e POSTGRES_PASSWORD=pg postgres:18 \
  -c autovacuum=off -c max_connections=50
docker exec -i dba-pg-dump-internals psql -U postgres

CREATE TABLE big (id int PRIMARY KEY, a int, b text);
INSERT INTO big SELECT i, i % 1000, md5(i::text) || md5((i*7)::text) FROM generate_series(1, 6000000) i;
CREATE INDEX big_a ON big (a);
-- t1..t4: те же столбцы, по 1 500 000 строк, PRIMARY KEY, индекс t1_a..t4_a
ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1 (id);
ALTER TABLE t3 ADD FOREIGN KEY (id) REFERENCES t1 (id);
VACUUM ANALYZE;

 relname |  tbl   |  idx   
---------+--------+--------
 big     | 625 MB | 169 MB
 t1      | 156 MB | 43 MB
 t2      | 156 MB | 43 MB
 t3      | 156 MB | 43 MB
 t4      | 156 MB | 43 MB

Всё вместе занимает 1597 МБ (pg_database_size). Замеры сделаны на общей машине, на которой параллельно шли чужие задачи (load average 6–9 на 20 потоках), поэтому приведён разброс по нескольким запускам, а не одно число. Команды запускались внутри контейнера, дамп писался в его /var/tmp.

Опыт 1: время дампа и каталог дампа

pg_dump -U postgres -Fd -j 4 -f /var/tmp/d postgres
ls -l /var/tmp/d; du -sm /var/tmp/d

-rw-r--r--. 1 root root 255310885 Oct 28 00:28 3468.dat.gz
-rw-r--r--. 1 root root  63749355 Oct 28 00:28 3469.dat.gz
-rw-r--r--. 1 root root  63749355 Oct 28 00:28 3470.dat.gz
-rw-r--r--. 1 root root  63749355 Oct 28 00:28 3471.dat.gz
-rw-r--r--. 1 root root  63749355 Oct 28 00:28 3472.dat.gz
-rw-r--r--. 1 root root      6188 Oct 28 00:28 toc.dat
487	/var/tmp/d

Формат directory — это toc.dat с оглавлением и по одному сжатому файлу на таблицу. Номер в имени файла — идентификатор записи TABLE DATA в оглавлении (опыт 3). Индексов в каталоге нет, в toc.dat лежат только команды их создания, а данные сжаты gzip: из 1597 МБ базы вышло 487 МиБ. Время (три запуска каждого варианта, TIMEFORMAT из bash):

Дамп ускорился вдвое при переходе от одного потока к четырём и не ускорился при восьми. Причина видна, если дампить только big: pg_dump -Fd -t big в одном потоке занял 13,7 с, то есть столько же, сколько весь дамп с -j 4. Остальные четыре таблицы укладываются в то время, пока один воркер копирует big; добавленные воркеры простаивают. Воркер берёт таблицу целиком, одну COPY на таблицу, поэтому потолок параллельности задаёт самая большая из них. Секционирование (partitioning-postgres) превращает её в несколько заданий.

Порядок заданий тоже задан: самые большие таблицы идут первыми. pg_dump записывает в каждую запись длину данных в страницах (pg_dump.c, строки 2894–2906, тег REL_18_6):

		/*
		 * Set the TocEntry's dataLength in case we are doing a parallel dump
		 * and want to order dump jobs by table size.  We choose to measure
		 * dataLength in table pages (including TOAST pages) during dump, so
		 * no scaling is needed.
		 *
		 * However, relpages is declared as "integer" in pg_class, and hence
		 * also in TableInfo, but it's really BlockNumber a/k/a unsigned int.
		 * Cast so that we get the right interpretation of table sizes
		 * exceeding INT_MAX pages.
		 */
		te->dataLength = (BlockNumber) tbinfo->relpages;
		te->dataLength += (BlockNumber) tbinfo->toastpages;

Длина берётся из pg_class.relpages, то есть из статистики: у таблицы, которую давно не анализировали, порядок может быть неверным.

Опыт 2: один снимок на N+1 соединений

Во время -j 4 в другом сеансе читается pg_stat_activity:

pg_dump -U postgres -Fd -j 4 -f /var/tmp/ds postgres &
sleep 4
psql -U postgres -c "SELECT pid, backend_type, state, backend_xmin, left(query, 42) AS query
  FROM pg_stat_activity WHERE application_name = 'pg_dump' ORDER BY pid"

 pid  |  backend_type  |        state        | backend_xmin |                 query                 
------+----------------+---------------------+--------------+---------------------------------------
 1389 | client backend | idle in transaction |         1420 | EXECUTE getColumnACLs('6100')
 1394 | client backend | active              |         1420 | COPY public.big (id, a, b) TO stdout;
 1395 | client backend | idle in transaction |         1420 | COPY public.t1 (id, a, b) TO stdout;
 1396 | client backend | idle in transaction |         1420 | COPY public.t2 (id, a, b) TO stdout;
 1397 | client backend | active              |         1420 | COPY public.t4 (id, a, b) TO stdout;
(5 rows)

Пять соединений при -j 4, как пишет документация (njobs + 1), и у всех backend_xmin 1420. Воркеры t1 и t2 уже закончили COPY, но остались «idle in transaction» с тем же xmin: транзакция воркера живёт до конца всего дампа, а не до конца его таблицы. С включённым log_statement = 'all' лог показывает, как это устроено:

      5 statement: BEGIN
      1 statement: SELECT pg_catalog.pg_export_snapshot()
      5 statement: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
      4 statement: SET TRANSACTION SNAPSHOT '0000001B-00000079-1'

Лидер один раз вызвал pg_export_snapshot(), получил идентификатор, и каждый из четырёх воркеров принял его командой SET TRANSACTION SNAPSHOT. В коде это setup_connection (строки 1523–1537):

	if (AH->sync_snapshot_id)
	{
		PQExpBuffer query = createPQExpBuffer();

		appendPQExpBufferStr(query, "SET TRANSACTION SNAPSHOT ");
		appendStringLiteralConn(query, AH->sync_snapshot_id, conn);
		ExecuteSqlStatement(AH, query->data);
		destroyPQExpBuffer(query);
	}
	else if (AH->numWorkers > 1)
	{
		if (AH->isStandby && AH->remoteVersion < 100000)
			pg_fatal("parallel dumps from standby servers are not supported by this server version");
		AH->sync_snapshot_id = get_synchronized_snapshot(AH);
	}

Воркер приходит с уже известным sync_snapshot_id и выполняет первую ветку; лидер при numWorkers > 1 идёт во вторую и сам запрашивает снимок. Без общего снимка воркеры могли бы увидеть разные состояния базы, и документация называет такой дамп потенциально несогласованным.

Опыт 3: порядок восстановления

pg_restore -l печатает оглавление в том порядке, в каком объекты создаются:

pg_restore -l /var/tmp/d | grep -E 'big|t2'

219; 1259 16384 TABLE public big postgres
221; 1259 16402 TABLE public t2 postgres
3468; 0 16384 TABLE DATA public big postgres
3470; 0 16402 TABLE DATA public t2 postgres
3306; 2606 16391 CONSTRAINT public big big_pkey postgres
3312; 2606 16409 CONSTRAINT public t2 t2_pkey postgres
3304; 1259 16392 INDEX public big_a postgres
3310; 1259 16410 INDEX public t2_a postgres
3319; 2606 16429 FK CONSTRAINT public t2 t2_id_fkey postgres

Сначала пустые таблицы (pre-data), потом данные (data: 3468 — это файл 3468.dat.gz из опыта 1), потом ограничения, индексы и внешние ключи (post-data). В оглавлении 5, 5 и 12 записей в секциях. Индексы строятся после загрузки, потому что построить индекс по готовым данным дешевле, чем поддерживать его при каждой вставке. Секции можно запускать по отдельности (--section); время в секундах:

Это один прогон по секциям (повтор с -j 1 отличался на 0,1 с); сумма не обязана совпадать с полным восстановлением, потому что замеры отдельные, а хост шумный. Главное в таблице другое: post-data дольше загрузки данных даже при -j 1. Индексы и ограничения — большая часть восстановления, и параллелят её зависимости. Записи post-data зависят от TABLE DATA своей таблицы, и размер данных передаётся им по цепочке (repoint_table_dependencies):

/*
 * Change dependencies on table items to depend on table data items instead,
 * but only in POST_DATA items.
 *
 * Also, for any item having such dependency(s), set its dataLength to the
 * largest dataLength of the table data items it depends on.  This ensures
 * that parallel restore will prioritize larger jobs (index builds, FK
 * constraint checks, etc) over smaller ones, avoiding situations where we
 * end a restore with only one active job working on a large table.
 */

Так pg_restore -j строит индексы big первыми и не остаётся в конце с одним воркером на самой большой таблице.

Полное восстановление при -j 8 не оказалось быстрее -j 4: 9,0–14,7 с против 7,6–10,4. В отдельных запусках pg_stat_checkpointer показал по три запрошенных checkpoint и при -j 4, и при -j 8: дело не в них. С PGOPTIONS='-c max_parallel_maintenance_workers=0' (без параллельных воркеров внутри построения индекса) разброс сузился: -j 4 дал 7,9 и 9,1 с, -j 8 дал 9,2 и 10,4 с. Возможное объяснение: параллельное построение индекса внутри каждого из восьми воркеров занимает больше процессов, чем есть свободных ядер. Загрузка ядер соседними задачами хоста не измерялась, поэтому причина не доказана. Практический вывод один: -j подбирается замером на своём железе, а не ставится «по числу ядер».

Опыт 4: дамп держит горизонт xmin

Снимок дампа живёт до его конца, и горизонт xmin не движется. Чтобы дамп шёл дольше, он запущен с -j 1 (25 с). Через две секунды pgbench обновляет случайные строки t4 на протяжении 10 секунд:

\set id random(1, 1500000)
UPDATE t4 SET a = a + 1 WHERE id = :id;

pgbench -U postgres -n -c 4 -T 10 -f /var/tmp/upd.sql postgres

n_dead_tup до дампа, во время и после, плюс VACUUM (VERBOSE) t4 в двух состояниях. Autovacuum выключен, чтобы число менял только ручной VACUUM:

--- до дампа
 n_live_tup | n_dead_tup 
------------+------------
    1500000 |          0
--- pgbench
number of transactions actually processed: 3145
--- во время дампа
 pid  | state  | backend_xmin | xmin_age 
------+--------+--------------+----------
 1425 | active |         1420 |     3145
 n_live_tup | n_dead_tup 
------------+------------
    1500000 |       3145
tuples: 0 removed, 1503142 remain, 3145 are dead but not yet removable
removable cutoff: 1420, which was 3145 XIDs old when operation ended
--- после дампа
tuples: 3145 removed, 1499855 remain, 0 are dead but not yet removable
removable cutoff: 4565, which was 0 XIDs old when operation ended
 n_live_tup | n_dead_tup 
------------+------------
    1499855 |          0

Вывод собран скриптом; из VACUUM (VERBOSE) оставлены строки tuples и removable cutoff основной таблицы, строки TOAST-таблицы опущены. 3145 обновлений дали ровно 3145 мёртвых версий, VACUUM во время дампа не удалил ни одной, а граница очистки встала на backend_xmin дампа: 1420. Сразу после завершения дампа та же команда удалила все 3145 версий, граница ушла на 4565. Это маленькая нагрузка (около 314 обновлений в секунду на шумной машине), поэтому число небольшое; важна граница, а не величина.

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

• Потолок параллельного дампа — самая большая таблица. Опыт 1: -j 4 и -j 8 дали одно время (12,5 и 12,6 с), а big одна в одном потоке — 13,7 с. Считай -j по числу таблиц сравнимого размера, а гигантскую таблицу секционируй.
• Дамп, как и любая долгая транзакция, держит горизонт. Опыт 4: пока шёл дамп, 3145 мёртвых версий нельзя было убрать, и все -j + 1 соединений держат один xmin. Дампи в окно с малой записью; про реплику с hot_standby_feedback см. статью про горизонт.
• Снимок общий, поэтому дамп согласован между таблицами. Опыт 2: пять соединений с xmin 1420.
• Восстановление — это в основном индексы. Опыт 3: post-data занимает 10,3 с при -j 1 против 6,5 с загрузки данных. Если нужно быстрее, проверяй maintenance_work_mem, max_parallel_maintenance_workers и -j вместе.
• -j на восстановлении не гарантирует выигрыша. Опыт 3: -j 8 не лучше -j 4, причина осталась неизвестной. Подбери -j на своём железе.

Чек-лист

1. Для параллельного дампа нужен -Fd. Проверь max_connections: нужно -j + 1 соединений.
2. Замерь pg_dump -j 1, -j 4 и -j 8 на своей базе и найди таблицу, которая определяет потолок.
3. Выполни pg_restore -l и посмотри, какие индексы и ограничения создаются после данных. Измерь --section=post-data отдельно.
4. На время дампа смотри backend_xmin в pg_stat_activity и n_dead_tup самых обновляемых таблиц.
5. Убери контейнер вместе с томом: docker rm -f -v dba-pg-dump-internals.