По следам PgConf: обзор проблемы #1 из доклада «Как PostgreSQL может сделать больно, когда не ожидаешь»

На PgConf 2025 было три зала, в которых параллельно шли доклады. Поэтому охватить своим присутствием все доклады не представлялось возможным. В часть залов было не попасть, так как толпа участников толпилась на входе. Такое произошло и с докладом Михаила Жилина «Как PostgreSQL может сделать больно, когда не ожидаешь». В докладе описывалось 6 актуальных проблем. Пересматривая запись, я поймал себя на мысли, что не было желания ускорить воспроизведение, что обычное дело для «мокрых» докладов, обильно разбавленных водой. При просмотре доклада я понял, что проблема (не проблематика, проблематика она для тех, кого аффектят), поднятая в проблеме «#1: Глобальные счетчики» нуждается в детализации и является наиболее актуальной, от которой испытывают страдания почти все приложения, обновляющие строки в таблицах баз данных. В этой статье раскрывается актуальность и важность поднятой в докладе проблемы.

Как можно догадаться, название проблемы «#1: Глобальные счетчики» выбрано по старой академической традиции минимализма. Примерно, как название статьи «Зависит ли инерция тела от содержания в нем энергии?», в которой малоизвестным (в то время) физиком была раскрыта формула E=mc2.

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

create table global_counters (id int primary key, cnt bigint); insert into global_counters select id, 1000000 from generate_series(1,100) as id; update global_counters set cnt = cnt + ? where id = ?; 

Таблица что-то напоминает? Таблица похоже на таблицу теста TPC-B, только без лишнего третьего столбца. То есть задача хранить, изменять и выбирать баланс товара на складе, счёте актуальна.

create table pgbench_branches (bid int primary key, bbalance int, filler char(88)); insert into pgbench_branches(bid,bbalance) values(1,0); update pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

Казалось бы, какая проблема может вытечь из простейших и известных десятки лет команд. Проблема была выявлена, изолирована и описана в докладе Михаила. Более того, проблеме подвержены любые приложения, в которых имеется команда UPDATE, активно меняющая строки. Что значит активно? Чаще, чем самый долгий запрос или транзакция в базе данных. То есть, чаще, чем могут вычищаться старые версии строк. Старые версии строк могут вычищаться автовакуумом или серверным процессом (HOT cleanup), поэтому частота автовакуума не имеет значения для данной проблемы.

В таблице 100 строк, то есть немного. Можно сделать и 10 и 1 строку. В одной или нескольких сессиях с большой частотой (товары активно продаются) выполняются команды, обновляющие строки таблицы и меняющие остатки товаров в полях второго столбца (cnt или bbalance).

Тест для pgbench, приведенный в докладе:

\set b_id random(1,100) \set v_delta random(-10,10) update global_counters set id=id, cnt = cnt + :v_delta where id = :b_id;

Тест сохраняется в файл test.sql и запускается тест:

pgbench -n -T 3600 -c 10 -j 10 -P 1 -f test.sql

Параметр -j 10 необязателен. При небольшом числе соединений многопоточность на результат не влияет. -P можно увеличить, чтобы не замусоривать экран. В результате получается стабильно tps около 5000. При переносе в продуктовую базу tps снизилось до 1000. В чём проблема?

В том, что на промышленной базе есть транзакции и запросы, которые удерживают горизонт базы данных. При тестировании об этом забывают. Получаемый tps зависит от длительности удержания горизонта. В докладе рассматривается, как исследовали проблему и приходили к этому выводу с помощью расширения pg_query_state. Эту часть можно опустить, так как расширение это как бубен, оно позволяет сконцентрироваться на процессах в динамке. И без него цифры будут теми же.

Существенным для воспроизведения поведения теста на промышленной базе являются команды в параллельной сессии, указанные на 80 слайде доклада:

begin; select tixd_current();

Эти команды — один из способов удерживать горизонт базы и симулировать условия промышленных баз. При создании реальных тестов можно измерить и получить график длительности удержания горизонта конкретной промышленной базы данных и создать сессии, которые удерживают и постепенно сдвигают горизонт. Именно длительность удержания и определяет число tps=1000, которое указано в докладе. При более долгом удержании tps будет ниже. Снижение tps/(длительность удержания) нелинейно.

Как правильно напутствовал тестировщиков Михаил на 110 слайде: «самый простой способ завалить продакшн это создать долгоиграющую транзакцию. Если у вас есть какой-нибудь QA, то скажите QAщикам когда накатываете новый релиз, попробуйте в базе создать транзакцию, очень много всего интересного найдёте.»

В этой статье даны ссылки на слайды. Революционный доклад будет выложен через какое-то время после конференции и сноски в виде номеров слайдов будут полезны. Пока доклады не обработаны, их могут смотреть участники конференции. В статье я постарался описать все нюансы проблемы #1, чтобы и без доклада можно было понять, в чём состоит эта проблема, что делать и кто виноват. Более того, по моей оценке, суть проблемы понимает не так много человек: 5 максимум 10. Такая оценка взята из сценки, описанной в предыдущей статье, где Фёдора Сигаева окружили для допроса 5, максимум 10 человек. Ни в коей степени не осуждаю такое положение дел: в конце концов выйдут патчи, всё заработает быстрее. Знания о том, как что работает никак не приблизят выход патчей (когда-то же они выйдут).

Детализирую утверждение из доклада: «Индекс сохраняет ссылки на все блоки данных с копиями строк». Но ведь все знают, что индекс хранит ссылки на версии строк в блоках таблицы. Серверный процесс использует оптимизацию HOT update. Она применяется, если нет ни одного индекса, в который нужно внести изменения. В этом случае, в блок таблицы, где расположена обновляемая версия строки, просто вставляется новая версия строки. Получается, что в индексе на таблицу global_counters хранятся ссылки на первую версию строки, начинающую цепочку версий в каждом блоке таблицы. Ссылки на другие версии строки в том же блоке в индексе отсутствуют.  В этом и состоит оптимизация HOT (Heap Only Tuple update).

Важно ли это? Не важно. Можно создать индекс по столбцу cnt и оптимизация перестанет работать. В индексе global_counters_pkey будет создаваться ссылка на каждую версию строки. Это снизит tps, оптимизация полезна. Почему же тогда она не важна? Потому, что в заголовке строки есть ссылка  t_ctid на следующую версию строки, даже если она находится в другом блоке. Однако, в текущей реализации индексного доступа это не используется:

select lp, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_data from heap_page_items(get_raw_page('pgbench_branches','main',0)) order by lp desc limit 3 ;  lp  |  t_xmin  |  t_xmax  | t_ctid  | t_infomask2 | t_infomask |       t_data         -----+----------+----------+---------+-------------+------------+--------------------  226 | 37665765 | 37665766 | (1,1)   |       32771 |       9473 | \x01000000e1000000  225 | 37665764 | 37665765 | (0,226) |       49155 |       9473 | \x01000000e0000000  224 | 37665763 | 37665764 | (0,225) |       49155 |       9473 | \x01000000df000000 

В примере версия строки с адресом ctid=(0,226)  (слот lp=226 в нулевом блоке) созданная транзакцией t_xmin=37665765 и удалённая командой UPDATE в транзакции номер t_xmax=37665766 ссылается первый слот в блоке номер один: t_ctid=(1,1).

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

В индексах btree есть оптимизация, которая позволяет процессу вернуться в блок индекса и очистить ссылку, сделав ее мертвой, но только, если версии строк вышли за горизонт базы данных. Теоретически, можно было бы создать оптимизацию, выставляющую признак в btree индексе, которым помечались бы индексные записи, что они не являются последними в общей (между блоками) цепочке версий этой строки. Новая версия всегда, на всех уровнях изоляции транзакций, независимо от отката или фиксации транзакции, породившей версию, обновляет последнюю версию строки в цепочке. В «идеальном мире» (можно же помечтать о создании такой оптимизации) процесс, выполняющий UPDATE или DELETE, видя такой признак в индексной записи, доходил бы до индексной записи без признака и сильное торможение доступа к строке бы исчезло из PostgreSQL, как страшный сон. Это единственный способ устранить торможение и вековую многолетнюю «боль» от MVCC. Эту боль списывают на вакуум, но вакуум ни в чём не виноват. Пенять на вакуум, это всё равно что пенять на иммунитет, а не на вредителей, с которыми иммунитет борется. При отключении иммунитета (как и автовакуума) становится легко и хорошо, но потом резко плохеет. Почему пеняют на автовакуум? Потому, что в случае удержания горизонта базы, автовакуум в каждом цикле будет повторно вакуумировать таблицу. При этом эффекта не будет — строки не могут вычищаться. Эффекта мало, а ресурсы используются. Но проблема не а автовакууме, а в транзакциях и запросах, удерживающих горизонт. О чём в докладе Михаила и было прямо сказано в проблеме #2: какая-то транзакция отвалилась и проблемы ушли.

В докладе правильно указано, что от MVCC не уйти. Последняя попытка была сделана в методе доступа zheap, упомянутом в докладе. Компания EnterpriseDB не смогла осилить столь революционное начинание. Что примечательно, расширение zheap рекламировалось, «как защищающее от bloatingа» (слайд 58 доклада). Дальше напишу крамолу, как это может поначалу показаться.  Меня часто спрашивают , нет не так,  буквально из каждого утюга про bloat. Дело не в раздувании файлов. Деградация производительности не зависит от объема раздувания. Метрика (раздувшийся размер)/(размер после vacuum full) далеко нелинейно связана с производительностью. Раздувание (помимо занятого на диске места) больше всего влияет на сканирование индексов при вакуумировании, но, что хорошо, такое сканирование выполняется не каждый раз («indexscans: 0» в логе вакуума).

Скрытый текст

2025-04-07 12:07:12.189 MSK [27079] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_attribute»: index scans: 0
        pages: 0 removed, 516654 remain, 516600 scanned (99.99% of total)
        tuples: 0 removed, 29961465 remain, 29961421 are dead but not yet removable, oldest xmin: 13261300
        removable cutoff: 13261300, which was 8561122 XIDs old when operation ended
        frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
        index scan bypassed: 1 pages from table (0.00% of total) have 7 dead item identifiers
        avg read rate: 145.546 MB/s, avg write rate: 0.000 MB/s
        buffer usage: 516512 hits, 516876 misses, 0 dirtied
        WAL usage: 0 records, 0 full page images, 0 bytes
        system usage: CPU: user: 8.20 s, system: 1.20 s, elapsed: 27.74 s
2025-04-07 12:08:21.734 MSK [27084] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_attribute»: index scans: 0
        system usage: CPU: user: 10.32 s, system: 0.21 s, elapsed: 30.83 s
2025-04-07 12:08:29.379 MSK [27084] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_depend»: index scans: 0
        system usage: CPU: user: 2.25 s, system: 0.69 s, elapsed: 7.64 s
2025-04-07 12:08:35.111 MSK [27084] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_class»: index scans: 0
        system usage: CPU: user: 1.21 s, system: 0.12 s, elapsed: 5.73 s
2025-04-07 12:08:47.361 MSK [27087] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_type»: index scans: 0
        system usage: CPU: user: 2.54 s, system: 1.11 s, elapsed: 12.63 s
2025-04-07 12:09:17.275 MSK [27087] LOG:  automatic vacuum of table «postgres.pg_catalog.pg_attribute»: index scans: 0
        pages: 0 removed, 516654 remain, 516600 scanned (99.99% of total)
        tuples: 0 removed, 29961465 remain, 29961421 are dead but not yet removable, oldest xmin: 13261300
        removable cutoff: 13261300, which was 8561122 XIDs old when operation ended
        frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
        index scan bypassed: 1 pages from table (0.00% of total) have 7 dead item identifiers
        avg read rate: 131.222 MB/s, avg write rate: 0.000 MB/s
        buffer usage: 531890 hits, 501498 misses, 0 dirtied
        WAL usage: 0 records, 0 full page images, 0 bytes
        system usage: CPU: user: 10.71 s, system: 0.15 s, elapsed: 29.85 s

Как видно из лога, при удержании горизонта вакуум пытается отвакуумировать таблицы в каждом цикле, тратя на это значительные ресурсы как процессора, так и ввода-вывода (user, system elapsed). После снятия удержания, очищенные блоки будут отмечены в карте видимости и автовакуум не будет на них тратить ресурсы. А вот блоки индексов, если вакуум решил их сканировать, сканируются в физическом порядке «от а до я» и полностью. Дело усугубляется тем, что блоки не входящие в логическую структуру индекса (дерево), но физически присутствующие в файлах будут подгружаться в буферный кэш вакуумом, так как кроме него никто к ним не обращается и они после вакуума будут вытеснены. Поэтому, именно перестройка индексов актуальна после непредвиденного удержания горизонта, приведшего к раздуванию.

Более того, дело не в накоплении старых версий строк (и с ними можно производительно жить) и не в том, что они копятся именно в блоках таблицы (Михеев не виноват!) и даже не в первопричине bloatа — удержании старых версий транзакциями и запросами (старые версии и в других СУБД удерживаются, они нужны). Дело в неэффективном поиске актуальных версий строк, а именно поиске по btree, как наиболее популярном методе доступа.

В докладе приводится пример инженерного решения путем добавления индекса и подзапроса. Возможно, в реальной задаче это давало эффект, но в приведённом в докладе примере это решение приводит к уменьшению tps. Пример добавления индекса:

drop table if exists global_counters; create table global_counters (id int primary key, cnt bigint, last timestamp); create index global_counters_idx on g1(id, last); insert into global_counters select id, 1000000, now() from generate_series(1,100) as id;

Пример файла теста:

\set b_id random(1,100) \set v_delta random(-10,10) update g1 set cnt = cnt + :v_delta, last = now() where ctid =  (select ctid from g1 where id = :b_id order by last desc limit 1);

Михаил на 55 слайде предложил: «Давайте что-то думать. Вот. И давайте это обсуждать вместе. Как нам реорганизовать рабкрин. Что же нам делать?» Инженерные решения типа добавления столбца или индекса не улучшат ситуацию. При добавлении индекса производительность падает (тест приведен ниже). Только оптимизация индексов btree спасёт отца русской демократии  PostgreSQL от резкой деградации производительности при появлении новых версий строк в результате UPDATE. Это наиболее актуальная проблема в PostgreSQL, которую Михаил вывел из тени (вторая — коррелированные подзапросы, но до нее ещё не добрались).

Последний раз в индексы btree вносила изменения Анастасия Лубенникова. Было добавлено дедуплицирование: https://commitfest.postgresql.org/patch/2202/

Анастасия сейчас работает в NEON. По моему мнению, кроме неё, компетенцией улучшать индексы обладает разве что Алёна Рыбакина. С моей точки зрения, только она в состоянии спасти PostgreSQL от проблемы #1.

Детальный тест

В этом разделе пример теста для воспроизведения. То есть раздел можно пропустить, если не планируется проверять результаты самостоятельно.

drop table if exists global_counters; create table global_counters (id int primary key, cnt bigint); insert into global_counters select id, 1000000 from generate_series(1,100) as id; analyze global_counters;

Содержимое файла теста:

\set b_id random(1,100) \set v_delta random(-10,10) update global_counters set cnt = cnt + :v_delta where id = :b_id;

Перед тестом надо запустить в параллельной сессии транзакцию:

begin; select pg_current_xact_id();

Тест:

pgbench -n -T 30 -c 10 -P 5 -f test.sql progress: 5.0 s, 7228.1 tps, lat 1.341 ms stddev 0.678, 0 failed progress: 10.0 s, 6591.6 tps, lat 1.484 ms stddev 0.723, 0 failed progress: 15.0 s, 6011.2 tps, lat 1.633 ms stddev 0.729, 0 failed progress: 20.0 s, 5503.5 tps, lat 1.784 ms stddev 0.828, 0 failed progress: 25.0 s, 4574.6 tps, lat 2.154 ms stddev 0.826, 0 failed progress: 30.0 s, 4360.8 tps, lat 2.261 ms stddev 0.878, 0 failed

tps снижаются и через час дойдут до ~1000.

Проверим инженерное решение из доклада. Таблицу создадим новую, называние таблицы не играет роли:

drop table if exists g1; create table g1 (id int primary key, cnt bigint, last timestamp); create index g1_idx on g1(id, last); insert into g1 select id, 1000000, now() from generate_series(1,100) as id; analyze g1;

Файл теста:

\set b_id random(1,100) \set v_delta random(-10,10) update g1 set cnt = cnt + :v_delta, last = now() where ctid =   (select ctid from g1 where id = :b_id order by last desc limit 1);

Тест инженерного решения:

pgbench -n -T 30 -c 10 -P 5 -f test-a.sql progress: 5.0 s, 5340.2 tps, lat 1.822 ms stddev 0.964, 0 failed progress: 10.0 s, 4592.3 tps, lat 2.145 ms stddev 0.913, 0 failed progress: 15.0 s, 3873.7 tps, lat 2.550 ms stddev 1.208, 0 failed progress: 20.0 s, 3540.9 tps, lat 2.795 ms stddev 1.186, 0 failed progress: 25.0 s, 3227.5 tps, lat 3.067 ms stddev 1.195, 0 failed progress: 30.0 s, 2376.6 tps, lat 4.166 ms stddev 1.620, 0 failed

Стало хуже: tps стали меньше и так же продолжают уменьшаться.

С добавлением advisory lock всё станет совсем печально:

\set b_id random(1,100) \set v_delta random(-10,10) SELECT pg_advisory_lock(123); update g1 set cnt = cnt + :v_delta, last = now() where ctid =   (select ctid from g1 where id = :b_id order by last desc limit 1); SELECT pg_advisory_unlock(123);

После снятия удержания горизонта, пересоздания, вакуумирования, анализа и снова запуска транзакции для удержания горизонта:

pgbench -n -T 30 -c 10 -P 5 -f test-b.sql progress: 5.0 s, 1176.4 tps, lat 8.399 ms stddev 1.522, 0 failed progress: 10.0 s, 1182.8 tps, lat 8.450 ms stddev 1.273, 0 failed progress: 15.0 s, 1117.8 tps, lat 8.941 ms stddev 1.451, 0 failed progress: 20.0 s, 1117.4 tps, lat 8.945 ms stddev 0.980, 0 failed progress: 25.0 s, 1034.8 tps, lat 9.659 ms stddev 2.960, 0 failed progress: 30.0 s, 994.2 tps, lat 10.053 ms stddev 1.556, 0 failed

Результат говорит сам за себя. Вероятно, использование стандартных механизмов блокирования СУБД PostgreSQL имеют преимущества.Можно вместо ‘123’ исплльзовать b_id, но товаров на складе может быть много, а рекомендательные блокировки исчерпывают общий пул блокировок экземпляра. Да и в транзакции может обновляться несколько товаров.

Заключение

В статье рассмотрена проблема #1 производительности PostgreSQL из доклада на конференции PgConf Михаила Жилина. В докладе проблема обозначена кратко, чтобы вписаться в рамки доклада. В этой статье проблема разобрана более детально и приведены примеры для воспроизведения проблемы. Также статья позволяет понять причины проблемы и сделать немного более понятным доклад.