Bloat таблицы в PostgreSQL: pgstattuple и pg_repack

После массового DELETE и обычного VACUUM таблица не стала меньше на диске: VACUUM отдаёт место страницам внутри файла, а файл остаётся прежним. Это пустое место и называют bloat. Измерить его точно можно только чтением всей таблицы (pgstattuple), дёшево — оценкой по статистике или приближённой функцией. Вернуть файл системе можно тремя способами, и они различаются не результатом, а тем, как долго таблица закрыта для запросов.

DBA / 01

Где pg_repack держит ACCESS EXCLUSIVE и сколько ждал UPDATE

Копия строится под слабой блокировкой, поэтому pg_repack закрывает таблицу трижды и ненадолго: 25 мс в сумме против 416 мс у VACUUM FULL на таблице в 117 МБ.

Что называть bloat и как его создать

В контейнере три одинаковые таблицы по миллиону строк, в каждой удалены 60 % строк так, что живые остались на каждой странице. Одна из них — t_vf; остальные (t_cl, t_rp) создаются так же, pg_repack ставится из репозитория PGDG, подключённого в образе:

docker run -d --name dba-table-bloat -e POSTGRES_PASSWORD=pg postgres:18
docker exec dba-table-bloat sh -c 'apt-get update -qq && apt-get install -y -qq postgresql-18-repack'
docker exec -it dba-table-bloat psql -U postgres

SELECT version();
CREATE EXTENSION pgstattuple;
CREATE TABLE t_vf (id int PRIMARY KEY, payload text) WITH (autovacuum_enabled = false);
INSERT INTO t_vf SELECT g, repeat('x', 80) FROM generate_series(1, 1000000) g;
CREATE TABLE t_cl (LIKE t_vf INCLUDING ALL) WITH (autovacuum_enabled = false);
INSERT INTO t_cl SELECT * FROM t_vf;
CREATE TABLE t_rp (LIKE t_vf INCLUDING ALL) WITH (autovacuum_enabled = false);
INSERT INTO t_rp SELECT * FROM t_vf;
DELETE FROM t_vf WHERE id % 5 < 3;
DELETE FROM t_cl WHERE id % 5 < 3;
DELETE FROM t_rp WHERE id % 5 < 3;
\x on
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('t_vf');
VACUUM t_vf; VACUUM t_cl; VACUUM t_rp;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('t_vf');

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

-[ RECORD 1 ]------+----------
table_len          | 117030912
tuple_percent      | 37.26
dead_tuple_percent | 55.88
free_percent       | 0.59

-[ RECORD 1 ]------+----------
table_len          | 117030912
tuple_percent      | 37.26
dead_tuple_percent | 0
free_percent       | 58.06

До VACUUM место занято мёртвыми версиями (55,88 %), после — свободно (58,06 %), а table_len не изменился: 117 030 912 байт. Живые строки лежат на каждой странице, поэтому VACUUM не нашёл пустого хвоста файла, который мог бы усечь. Bloat таблицы — это free_percent плюс dead_tuple_percent: доля файла, в которой нет живых данных. Здесь она 58 %.

pgstattuple считает именно так. В pgstat_heap (тег REL_18_6) каждая версия строки на каждой странице проверяется на видимость, поэтому функция читает всю таблицу под ACCESS SHARE:

		if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, hscan->rs_cbuf))
		{
			stat.tuple_len += tuple->t_len;
			stat.tuple_count++;
		}
		else
		{
			stat.dead_tuple_len += tuple->t_len;
			stat.dead_tuple_count++;
		}

Видимые версии идут в tuple_len, остальные — в dead_tuple_len; свободное место функция суммирует отдельно по каждой странице.

Измерить дёшево: pgstattuple_approx и оценка по статистике

На таблице в 117 МБ точная pgstattuple ещё терпима. На сотнях гигабайт читать всё ради цифры нельзя. Первая замена — pgstattuple_approx: она пропускает страницы, которые карта видимости помечает как целиком видимые, и берёт их свободное место из карты свободного места. Вторая — запрос по pg_class и pg_stats, который вообще не трогает таблицу: считает, сколько страниц заняли бы reltuples строк средней ширины, и сравнивает с relpages.

ANALYZE t_vf;
\timing on
SELECT free_percent FROM pgstattuple('t_vf');
SELECT scanned_percent, approx_free_percent FROM pgstattuple_approx('t_vf');
\timing off
SELECT c.relpages,
       ceil(c.reltuples * (24 + 4 + w.width) / (current_setting('block_size')::int - 24))::int AS pages_needed,
       round(100 * (1 - ceil(c.reltuples * (24 + 4 + w.width)
             / (current_setting('block_size')::int - 24)) / c.relpages)) AS bloat_pct
FROM pg_class c
JOIN (SELECT sum(avg_width) AS width FROM pg_stats WHERE tablename = 't_vf') w ON true
WHERE c.relname = 't_vf';

 free_percent 
--------------
        58.06

Time: 27.408 ms
 scanned_percent | approx_free_percent 
-----------------+---------------------
               0 |    57.81334763929721

Time: 1.491 ms
 relpages | pages_needed | bloat_pct 
----------+--------------+-----------
    14286 |         5534 |        61

Три способа дали 58,06 %, 57,81 % и 61 %. pgstattuple_approx с scanned_percent 0 не прочитал ни одной страницы данных (все страницы были видимы после VACUUM) и ответил за 1,5 мс против 27,4 мс. Оценка по статистике завысила на три пункта: в формуле 24 байта заголовка версии и 4 байта указателя на строку, а выравнивание не учтено. Она подходит для обхода сотни таблиц и выбора подозрительных, но не для решения «перепаковать»: цифра держится на reltuples и avg_width из последнего ANALYZE.

Три способа вернуть файл

VACUUM FULL и CLUSTER проходят один путь. rebuild_relation создаёт новую таблицу, копирует в неё живые версии (в порядке индекса у CLUSTER), подменяет файлы и пересобирает индексы. Старая таблица на всё это время закрыта. Режим блокировки задаёт vacuum_rel:

	lmode = (params->options & VACOPT_FULL) ?
		AccessExclusiveLock : ShareUpdateExclusiveLock;

Обычный VACUUM берёт SHARE UPDATE EXCLUSIVE, который не мешает чтению и записи. VACUUM FULL берёт ACCESS EXCLUSIVE, несовместимый даже с SELECT. CLUSTER берёт его же.

Чтобы увидеть цену, на каждую таблицу идёт поток UPDATE (pgbench), а отдельная сессия раз в пару миллисекунд проверяет, не ждёт ли какой-то UPDATE блокировку, и держит ли кто-нибудь AccessExclusiveLock на таблице. Скрипты s.sql и watch.sql кладутся в контейнер, имя таблицы передаётся параметрами:

-- s.sql
\set i random(0, 39999) * 5 + 3
UPDATE :tbl SET payload = payload WHERE id = :i;

-- watch.sql
DO $$
DECLARE
  stop timestamptz := clock_timestamp() + interval '11 s';
  t0 timestamptz; t1 timestamptz; waiting bool; locked bool; held bool := false;
BEGIN
  WHILE clock_timestamp() < stop LOOP
    PERFORM pg_stat_clear_snapshot();
    SELECT EXISTS (SELECT 1 FROM pg_stat_activity
                   WHERE wait_event_type = 'Lock' AND query LIKE 'UPDATE %') INTO waiting;
    IF waiting THEN
      t0 := coalesce(t0, clock_timestamp()); t1 := clock_timestamp();
      SELECT bool_or(mode = 'AccessExclusiveLock') INTO locked FROM pg_locks
        WHERE relation = current_setting('x.tbl')::regclass AND granted;
      held := held OR coalesce(locked, false);
    ELSIF t0 IS NOT NULL THEN
      RAISE NOTICE 'UPDATE ждал % мс, ACCESS EXCLUSIVE выдан: %',
                   round(extract(epoch FROM t1 - t0) * 1000), held;
      t0 := NULL; held := false;
    END IF;
    PERFORM pg_sleep(0.002);
    COMMIT;
  END LOOP;
END $$;

Скрипты кладутся в контейнер (docker exec -i dba-table-bloat sh -c 'cat > /tmp/s.sql', так же watch.sql). Для каждой таблицы pgbench (9 секунд) и наблюдатель стартуют вместе, а перепаковка — через 3 секунды. Команды для t_vf; для t_cl и t_rp меняются таблица и команда перепаковки:

docker exec dba-table-bloat pgbench -U postgres -n -T 9 -D tbl=t_vf -f /tmp/s.sql postgres &
docker exec -e PGOPTIONS='-c x.tbl=t_vf' dba-table-bloat psql -U postgres -q -f /tmp/watch.sql &
sleep 3
docker exec dba-table-bloat psql -U postgres -c 'VACUUM FULL t_vf'
docker exec dba-table-bloat psql -U postgres -Atc "SELECT pg_relation_size('t_vf')"
wait

Для t_cl вместо VACUUM FULL идёт CLUSTER t_cl USING t_cl_pkey, для t_rp — docker exec dba-table-bloat pg_repack -U postgres -d postgres -t t_rp. Вывод наблюдателя и размер файла после перепаковки, по порядку: VACUUM FULL, CLUSTER, pg_repack:

46817280
psql:/tmp/watch.sql:23: NOTICE:  UPDATE ждал 416 мс, ACCESS EXCLUSIVE выдан: t

46817280
psql:/tmp/watch.sql:23: NOTICE:  UPDATE ждал 354 мс, ACCESS EXCLUSIVE выдан: t

INFO: repacking table "public.t_rp"
46825472
psql:/tmp/watch.sql:23: NOTICE:  UPDATE ждал 16 мс, ACCESS EXCLUSIVE выдан: t
psql:/tmp/watch.sql:23: NOTICE:  UPDATE ждал 7 мс, ACCESS EXCLUSIVE выдан: t
psql:/tmp/watch.sql:23: NOTICE:  UPDATE ждал 2 мс, ACCESS EXCLUSIVE выдан: t

Все три способа сократили файл со 117 030 912 до 46 817 280–46 825 472 байт, в 2,5 раза. Команды шли 0,59 с, 0,47 с и 0,71 с по часам оболочки. VACUUM FULL и CLUSTER держали ACCESS EXCLUSIVE почти всё это время: UPDATE ждал 416 и 354 мс. pg_repack работал дольше всех, но закрыл таблицу тремя окнами по 16, 7 и 2 мс. В других запусках на таких же таблицах VACUUM FULL давал 464 мс, а три окна pg_repack в сумме 27 и 47 мс. Таблица небольшая, 117 МБ: окно VACUUM FULL равно времени переписывания всей таблицы, а pg_repack переписывает её под слабой блокировкой. Как окна pg_repack ведут себя на таблице в десятки гигабайт, опыт не проверял.

Как pg_repack обходится почти без блокировки

Копирование долгое, и pg_repack не держит его под ACCESS EXCLUSIVE. Последовательность записана номерами в комментариях bin/pg_repack.c (тег ver_1.5.3, он соответствует установленному пакету 1.5.3): 1 setup, 2 copy, 3 indexes, 4 apply log, 5 swap, 6 drop, 7 analyze.

setup. Под коротким ACCESS EXCLUSIVE создаёт таблицу лога repack.log_<oid> и триггер repack_trigger на INSERT, UPDATE, DELETE исходной таблицы, затем понижает блокировку до SHARE UPDATE EXCLUSIVE.
copy и indexes. Копирует живые строки в новую таблицу и строит на ней индексы. Все изменения исходной таблицы за это время триггер пишет в лог.
apply log. Применяет лог к копии, пока в нём не останется записей, и ждёт завершения транзакций, начавшихся раньше.
swap. Повышает блокировку до ACCESS EXCLUSIVE:

	/*
	 * 5. Swap: will be done with conn2, since it already holds an
	 *    ShareUpdateExclusiveLock lock.
	 */
	elog(DEBUG2, "---- swap ----");
	/* Bump our existing ShareUpdateExclusive lock to AccessExclusive */

	if (!(lock_exclusive(conn2, utoa(table->target_oid, buffer),
						 table->lock_table, false)))

Затем догоняет остаток лога и меняет файлы местами (repack_swap). На шаге drop снова берёт ACCESS EXCLUSIVE и стирает старые файлы. Три окна из опыта соответствуют setup, swap и drop: на исходную таблицу lock_exclusive вызывается ровно в этих трёх местах (строки 1299, 1622 и 1655 того же файла); привязку каждого окна к шагу наблюдатель не измерял.

Цену этой схемы показывают три вещи. Первая: на шаге apply log pg_repack ждёт завершения всех транзакций, которые начались раньше него. Чтобы это увидеть, одна сессия открывает транзакцию на 8 секунд, а pg_repack запускается на таблице t_wait (200 000 строк, 60 % удалено):

docker exec -i dba-table-bloat psql -U postgres -At <<'SQL' &
BEGIN;
SELECT 'old transaction pid ' || pg_backend_pid();
SELECT pg_sleep(8);
COMMIT;
SQL
sleep 1
time docker exec dba-table-bloat pg_repack -U postgres -d postgres -t t_wait

INFO: repacking table "public.t_wait"
NOTICE: Waiting for 1 transactions to finish. First PID: 1151
(всего 7 таких строк)
docker exec dba-table-bloat pg_repack -U postgres -d postgres -t t_wait  0.01s user 0.01s system 0% cpu 7.303 total

old transaction pid 1151

PID в сообщении совпал с PID старой транзакции, а перепаковка небольшой таблицы заняла 7,3 с. Пока ожидание не кончилось, подмены нет: забытая idle in transaction откладывает её на всё своё время.

Вторая: у таблицы должен быть первичный ключ или уникальный индекс по столбцам NOT NULL, иначе лог нечем связывать с исходными строками:

WARNING: relation "public.t_nopk" must have a primary key or not-null unique keys

Третья: на время работы занято и старое, и новое место (копия, её индексы, лог), а триггер добавляет работу каждому INSERT, UPDATE и DELETE на исходной таблице до подмены.

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

Сначала измерь, потом перепаковывай. pgstattuple_approx или оценка по статистике находят кандидатов за миллисекунды на таблицу, pgstattuple подтверждает цифру для одной таблицы. Высокий процент сам по себе не повод: если таблица каждую неделю снова доходит до прежнего объёма, перепаковка только сдвигает проблему.
pg_repack — для таблиц, закрыть которые нельзя. Три окна по единицам и десяткам миллисекунд против сотен у VACUUM FULL. Платой остаются место, ожидание старых транзакций и триггер на время копирования.
VACUUM FULL и CLUSTER — для окна обслуживания и для малых таблиц. Они не требуют расширений. CLUSTER ещё и упорядочивает версии по индексу, но порядок не поддерживается: новые строки лягут как придётся.
Лечи причину, а не файл. Bloat возвращается, если UPDATE не делают HOT, а autovacuum не успевает. Если раздулись только индексы, таблицу перепаковывать не нужно: хватит REINDEX CONCURRENTLY.

Чек-лист

Оцени bloat таблиц запросом по статистике или pgstattuple_approx; для кандидатов проверь pgstattuple.
Перед VACUUM FULL и CLUSTER проверь, что окно ACCESS EXCLUSIVE приемлемо: оно равно времени переписывания всей таблицы.
Перед pg_repack проверь первичный ключ, свободное место под копию и индексы и отсутствие долгих транзакций (pg_stat_activity, xact_start).
После перепаковки замерь pg_relation_size и pgstattuple заново, чтобы увидеть, вернулся ли bloat.
Убери контейнер вместе с данными: docker rm -f -v dba-table-bloat.

Bloat таблицы в PostgreSQL: измерить pgstattuple и убрать без простоя pg_repack