На PgConf 2025 было три зала, в которых параллельно шли доклады. Поэтому охватить своим присутствием все доклады не представлялось возможным. В часть залов было не попасть, так как участники конференции толпились ещё на входе в зал наиболее интересных докладов. Такое произошло и с докладом «Как PostgreSQL может сделать больно, когда не ожидаешь». В докладе описывались 6 проблем, актуальных для PostgreSQL. Проблема "#1: Глобальные счетчики" затрагивает многие приложения, обновляющие строки в таблицах баз данных PostgreSQL.
Суть проблемы в том, что если в таблице имеется немного строк, которые обновляются, то скорость обновлений и запросов к таблице существенно деградирует. Например, имеется таблица из двух столбцов:
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 -P 10 -f test.sqlВ докладе приведены оценки: в результате получается стабильно tps около 5000. При переносе в продуктовую базу tps снизилось до 1000. В чём проблема? В том, что на промышленной базе есть транзакции и запросы, которые удерживают горизонт базы данных хотя бы на несколько минут. При тестировании об этом забывают. Получаемый tps зависит от длительности удержания горизонта.
Существенным для воспроизведения поведения теста на промышленной базе являются команды в параллельной сессии, указанные на 80 слайде доклада:
begin;
select tixd_current();Эти команды - один из способов удерживать горизонт базы и симулировать условия промышленных баз. При создании реальных тестов можно измерить и получить график длительности удержания горизонта конкретной промышленной базы данных и создать сессии, которые удерживают и постепенно сдвигают горизонт. Именно длительность удержания и определяет число tps=1000, которое указано в докладе. При более долгом удержании tps будет ниже. Снижение tps/(длительность удержания) нелинейно.
На 110 слайде была дпна рекомендация: "самый простой способ завалить продакшн это создать долгоиграющую транзакцию. Если у вас есть какой-нибудь QA, то скажите QAщикам когда накатываете новый релиз, попробуйте в базе создать транзакцию, очень много всего интересного найдёте."
В этой статье даны ссылки на слайды. Доклад будет выложен через какое-то время после конференции и сноски в виде номеров слайдов будут полезны. В статье я постарался описать все нюансы проблемы #1, чтобы и без доклада можно было понять, в чём состоит проблема, что делать. Более того, по моей оценке, суть проблемы понимает не так много человек (5 максимум 10). Благодаря докладу Михаила проблема становится более известной. Понять проблему - половина её решения.
Детализирую утверждение из доклада: "Индекс сохраняет ссылки на все блоки данных с копиями строк". Индекс хранит ссылки на версии строк в блоках таблицы. Серверный процесс использует оптимизацию HOT (Heap Only Tuple) update. Оптимизация применяется, если нет ни одного индекса, в который нужно внести изменения. В этом случае, в блок таблицы, где расположена обновляемая версия строки, просто вставляется новая версия строки. Получается, что в индексе на таблицу global_counters хранятся ссылки на первую версию строки, начинающую цепочку версий в каждом блоке таблицы. Ссылки на другие версии строки в том же блоке в индексе отсутствуют.
Важно ли это? Не важно. Можно создать индекс по столбцу 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 доклада). Дальше напишу крамолу, как это может поначалу показаться. Дело не в раздувании файлов. Деградация производительности не зависит от объема раздувания. Метрика (раздувшийся размер)/(размер после 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
Как видно из лога, при удержании горизонта вакуум безостановочно и безуспешно (tuples 0: removed) пытается отвакуумировать таблицы в каждом цикле (в примере цикл 12:07 и следующий в 12:09), тратя на это значительные ресурсы как процессора, так и нагружая ввод-вывод (в примере время user, system, elapsed).
После снятия удержания горизонта, очищенные блоки будут отмечены в карте видимости и автовакуум не будет на них тратить ресурсы. А вот блоки индексов, если вакуум решил их сканировать, сканируются в физическом порядке "от а до я" и полностью. Дело усугубляется тем, что блоки не входящие в логическую структуру индекса (дерево), но физически присутствующие в файлах будут подгружаться в буферный кэш вакуумом, так как кроме него никто к ним не обращается и они после вакуума будут вытеснены. Поэтому, именно перестройка индексов актуальна после непредвиденного удержания горизонта, приведшего к раздуванию.
Более того, дело не в накоплении старых версий строк (и с ними можно производительно жить) и не в том, что они копятся именно в блоках таблицы (Михеев не виноват!) и даже не в первопричине bloatа - удержании старых версий транзакциями и запросами (старые версии и в других СУБД удерживаются, они нужны). Дело в неэффективном поиске актуальных версий строк, а именно поиске по btree, как наиболее популярном методе доступа.
В докладе приводится пример инженерного решения путем добавления индекса и подзапроса. В реальной задаче это давало эффект. Понятно, что в докладе упрощенный пример. Попробуем повторить на нём. Добавим индекс:
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.
Пока проблема замедления работы при частых обновлениях даже небольшого числа строк существует в PostgreSQL, лучше избегать проектирования логики работы приложений, при которой очень часто обновляется одна и та же строка (строки). С виду кажется, что в таблице немного строк, но цепочки версий строк сильно разрастаются и доступ к таким строкам существенно замедляется. Можно использовать логику вставок, а для расчета остатков применять расширения с непрерывными представлениями PipelineDB. Функционал PipelineDB (continuous aggregates) включён в TimescaleDB. Также можно использовать инженерное решение, найденное Михаилом и описанное в докладе.
Тестирование проблемы и возможных решений
В этом разделе пример теста для воспроизведения.
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 failedtps снижаются и через час дойдут до ~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);tps существенно понизятся:
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Результат невысокий. Можно вместо ‘123’ использовать b_id, но товаров на складе может быть много, а рекомендательные блокировки исчерпывают общий пул блокировок экземпляра.
UPDATE после обсуждения что делать и кто виноват: В комментариях Владимир Ситников (коммиттер PgJDBC драйвера) подсказал, как добиться, чтобы результат и соответствовало решению, приведенному в докладе. Нужно, чтобы использовался второй индекс. Для этого можно удалить первичный ключ:
alter table g1 drop constraint g1_pkey;В этом случае план выполнения будет таким, как в докладе:
EXPLAIN (analyze, buffers) update g1 set cnt = cnt + 1, last = now() where ctid = (select ctid from g1 where id = 10 order by last desc limit 1); QUERY PLAN
Update on g1 (cost=1119.64..1123.65 rows=0 width=0) (actual time=0.135..0.166 rows=0 loops=1)
Buffers: shared hit=10
InitPlan 1
-> Limit (cost=746.55..1119.63 rows=1 width=14) (actual time=0.057..0.074 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan Backward using g1_idx on g1 g1_1 (cost=0.39..373.47 rows=1 width=14) (actual time=0.047..0.051 rows=1 loops=1)
Index Cond: (id = 10)
Buffers: shared hit=4
-> Tid Scan on g1 (cost=0.00..4.02 rows=1 width=22) (actual time=0.082..0.091 rows=1 loops=1)
TID Cond: (ctid = (InitPlan 1).col1)
Buffers: shared hit=5
Planning Time: 0.874 ms
Execution Time: 0.201 ms
(13 rows)tps перестанут деградировать:
progress: 6390.0 s, 5649.4 tps, lat 1.719 ms stddev 0.860, 0 failed
progress: 6395.0 s, 5749.6 tps, lat 1.687 ms stddev 0.880, 0 failed
progress: 6400.0 s, 5892.2 tps, lat 1.647 ms stddev 0.783, 0 failedС одной блокировкой tps останутся низкими, но стабильными:
progress: 2425.0 s, 1040.4 tps, lat 9.596 ms stddev 2.415, 0 failed
progress: 2430.0 s, 1052.8 tps, lat 9.475 ms stddev 2.203, 0 failed
progress: 2435.0 s, 1023.8 tps, lat 9.775 ms stddev 3.013, 0 failed С блокировками по идентификатору товара SELECT pg_advisory_lock(:b_id); :
\set b_id random(1,100)
\set v_delta random(-10,10)
SELECT pg_advisory_lock(:b_id);
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(:b_id);tps повысятся до 3600:
progress: 15.0 s, 3594.3 tps, lat 2.747 ms stddev 1.263, 0 failedи останутся стабильными.
В комментариях предложили решение - вместо advisory lock добавить уникальный индекс по выражению (чтобы планировщик его не использовал, при этом гарантировалась уникальность строк):
create unique index g1_pkey on g1((id+0));tps повысились почти до исходных:
\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);
progress: 5.0 s, 5359.7 tps, lat 1.790 ms stddev 0.836, 0 failed
progress: 10.0 s, 4379.4 tps, lat 2.220 ms stddev 1.024, 0 failed
progress: 15.0 s, 3832.3 tps, lat 2.543 ms stddev 1.058, 0 failed
progress: 20.0 s, 3403.5 tps, lat 2.870 ms stddev 1.214, 0 failed
progress: 120.0 s, 1527.2 tps, lat 6.441 ms stddev 2.310, 0 failed
EXPLAIN (analyze, buffers) update g1 set cnt = cnt + 1,
last = now() where ctid =
(select ctid from g1 where id = 10
order by last desc limit 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Update on g1 (cost=168.60..172.62 rows=0 width=0) (actual time=0.172..0.198 rows=0 loops=1)
Buffers: shared hit=13
InitPlan 1
-> Limit (cost=112.53..168.60 rows=1 width=14) (actual time=0.050..0.065 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan Backward using g1_idx on g1 g1_1 (cost=0.39..56.46 rows=1 width=14) (actual time=0.042..0.046 rows=1 loops=1)
Index Cond: (id = 10)
Buffers: shared hit=4
-> Tid Scan on g1 (cost=0.00..4.02 rows=1 width=22) (actual time=0.077..0.085 rows=1 loops=1)
TID Cond: (ctid = (InitPlan 1).col1)
Buffers: shared hit=5
Planning:
Buffers: shared hit=176 dirtied=2
Planning Time: 0.743 ms
Execution Time: 0.352 ms
(15 rows)
postgres=# \d g1
Table "public.g1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | not null |
cnt | bigint | | |
last | timestamp without time zone | | |
Indexes:
"g1_idx" btree (id, last)
"g1_pkey" UNIQUE, btree ((id + 0))
\di+
List of relations
Name | Type | Table Access method | Size
---------+-------+-------+---------------+--------
g1_idx | index | g1 | btree | 13 MB
g1_pkey | index | g1 | btree | 3064 kBно tps постепенно деградировали, так как индексы разрастались в размерах.
Результаты
Михаил вывел из тени актуальную (номер 1) проблему PostgreSQL и указал на причину. И это только одна из шести проблем, описанных в докладе. В статье показано, как выглядит проблема, решение, приведены примеры команд для воспроизведения.