Comments 15
Спасибо за статью. Позвольте пояснить про HOT Update
Реализация МVСС в PostgreSQL вообще не подразумевает обновления записи, при необходимости обновления выполняются удаление-вставка.
...
PostgreSQL должно произойти перестроение всех индексов, независимо от того, обновляет ли UPDATE-запрос поля, по которым построены эти индексы или нет.
Если по обновляемым столбцам индексов нет, то срабатывает Heap Only Tuple Update, когда новая версия Heap записывается на той же странице, что и старая. Это позволяет не обновлять индексы (они ссылаются после UPDATE на те же самые страницы, что и до UPDATE).
Почему не сработало в Вашем тесте. В реальных системах обновление идёт отдельных строк, а в приведённом тесте - таблицы целиком. Поэтому на страницах не оказалось свободного места для размешения новых версих строк в HOT Update.
Т.е. можно сказать, что узкое место PostgreSQL в целом - это обновление полей, на которых лежат индексы?
Спасибо за замечание.
Запустил
update dat set n8=0 where mod(id, 10) =0
Посмотрим насколько будут отличаться результаты при обновлении 10% таблицы
Не должны отличаться, потому что fillfactor 100 по умолчанию. Стало интересно, решил воспроизвести. Вот с fillfactor 50.
table_name | reloptions
------------+-----------------
dat | {fillfactor=50}
postgres=# \dti+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+----------+-------+----------+-------+-------------+---------------+---------+-------------
public | dat | table | postgres | | permanent | heap | 71 GB |
public | dat_pkey | index | postgres | dat | permanent | btree | 278 MB |
public | x_d1 | index | postgres | dat | permanent | btree | 140 MB |
public | x_d23 | index | postgres | dat | permanent | btree | 242 MB |
public | x_d456 | index | postgres | dat | permanent | btree | 314 MB |
public | x_i1 | index | postgres | dat | permanent | btree | 140 MB |
public | x_i23 | index | postgres | dat | permanent | btree | 242 MB |
public | x_i456 | index | postgres | dat | permanent | btree | 314 MB |
public | x_n1 | index | postgres | dat | permanent | btree | 214 MB |
public | x_n23 | index | postgres | dat | permanent | btree | 279 MB |
public | x_n456 | index | postgres | dat | permanent | btree | 333 MB |
public | x_s1 | index | postgres | dat | permanent | btree | 666 MB |
public | x_s23 | index | postgres | dat | permanent | btree | 1302 MB |
public | x_s456 | index | postgres | dat | permanent | btree | 1904 MB |
public | x_t1 | index | postgres | dat | permanent | btree | 7846 MB |
(15 rows)
postgres=# update dat set n8 = 0;
UPDATE 9999000
Time: 169474.525 ms (02:49.475)io при fillfactor=50
io при fillfactor=100
Через час отменил запрос чтобы не издеваться над SSD
Всё верно, эффективность HOT Update сопоставима или даже превосходит INSERT. Про fillfactor очень важное замечание, однако на активно работающих системах это не нужно: необходимое пустое место на страницах появляется само.
Скрытый пример с fillfactor
bench=# select pg_stat_statements_reset();
pg_stat_statements_reset
-------------------------------
2025-07-15 23:21:17.508392+03
(1 row)
bench=# create unlogged table tst1 (id bigint, v uuid) with(fillfactor=100);
CREATE TABLE
bench=# create unlogged table tst2 (id bigint, v uuid) with(fillfactor=50);
CREATE TABLE
bench=# insert into tst1(id, v) select id, md5('' || id)::uuid from generate_series(1, 1000000, 1) as id;
INSERT 0 1000000
bench=# insert into tst2(id, v) select id, md5('' || id)::uuid from generate_series(1, 1000000, 1) as id;
INSERT 0 1000000
bench=# create unique index uni_tst1_id on tst1 using btree(id);
CREATE INDEX
bench=# create unique index uni_tst2_id on tst2 using btree(id);
CREATE INDEX
bench=# vacuum tst1;
VACUUM
bench=# vacuum tst2;
VACUUM
bench=# update tst1 set v = md5('' || v)::uuid where id %2 = 0;
UPDATE 500000
bench=# update tst2 set v = md5('' || v)::uuid where id %2 = 0;
UPDATE 500000
bench=# select query, shared_blks_hit + shared_blks_read as shared_blks from pg_stat_statements order by 2 desc;
query | calls | shared_blks | shared_blks_dirtied
-----------------------------------------------------------------------------------------------+-------+-------------+---------------------
update tst1 set v = md5($1 || v)::uuid where id %$2 = $3 | 1 | 3532711 | 14976
update tst2 set v = md5($1 || v)::uuid where id %$2 = $3 | 1 | 1025642 | 7870
insert into tst2(id, v) select id, md5($1 || id)::uuid from generate_series($2, $3, $4) as id | 1 | 1025635 | 12825
insert into tst1(id, v) select id, md5($1 || id)::uuid from generate_series($2, $3, $4) as id | 1 | 1012735 | 6370
create unique index uni_tst2_id on tst2 using btree(id) | 1 | 12928 | 3271
create unique index uni_tst1_id on tst1 using btree(id) | 1 | 6477 | 2313
create unlogged table tst2 (id bigint, v uuid) with(fillfactor=50) | 1 | 92 | 3
create unlogged table tst1 (id bigint, v uuid) with(fillfactor=100) | 1 | 90 | 0
vacuum tst2 | 1 | 58 | 0
vacuum tst1 | 1 | 56 | 0
select pg_stat_statements_reset() | 1 | 0 | 0
(11 rows)
так firebird diff-ы же хранит прямо в файле данных! То же такое себе, узкое место. Приходится тоже а-ля вакуум делать, Таки отдельный undo log лучше
Оно не узкое. После того как актуальность версий заканчивается, это место будет в дальнейшем использовано. Уборка "мусорных версий" производится при обращении к записи, сервер определяет какую из версий может видеть клиент, а какие версии уже никому не нужны. Т.е. убирать будет не тот кто эти версии создал, или удержал, а следующий за ними клиент/транзакция. От этого есть несколько решений - не держать длинных транзакций без необходимости. При удалении большого количества записей лучше тут же их прочитать, из той процедуры которая удаляла, но в новой транзакции. Таким образом замедление будет у того кто удалял а не у следующего, типа "необъяснимое торможение".
Так что совершенно не факт что " отдельный undo log лучше ".
Ну как же не узкое, это как раз самое узкое место. Файл с основными данными таблиц. Уборка мусора дополнительно нагружает его, как ее не делай, отдельным вакуумом или тут же их прочитать, из той процедуры которая удаляла. Плюс фрагментация данных будет расти. Так что это совершенно факт - " отдельный undo log лучше ".
Поясните, пожалуйста, по поводу реализации MVCC в современном Firebird.
Firebird, как известно, был основан на коде и структуры БД от Interbase, который Borland отчаявшись развивать Interbase как коммерчекий продукт, выложила в общий доступ. С Interbase я работал немало, а потому хорошо знаю, что в нем MVCC (ЕМНИП в оригинале это называлось MGA) была реализована путем хранения старых версий записей в том же файле БД (он там обычно был один) где-то рядом друг с другом, с указанием диапазона номеров транзакций, к которому эта версия записи относилась. Хранилась ли запись целиком или разбитой на отдельные поля (каждое поле - со своим собственным списком версий) - это я не выяснял, т.к. код на тот момент открыт ещё не был. Firebird же изначально унаследовал эту схему хранения.
Так вот, с тех пор в Firebord схемой хранения поменялась поменялось, или она в целом осталась принципиально той же самой?
Если схема хранения в Firebird в сравнении с изначальной, из Interbase, существенно не менялась, то объеединятт в общую категорию реализации MVCC в Firebird и в Oracle с MS SQL некорректно: в изначальной реализации MVCC (котрая появилась в первой половине 00-х - до этого там MVCC вообще не было, а для обеспецения согласованности доступа к записям использовались блокировки) в Oracle и MS SQL AFAIK разницы старых копий записей с текущей хранились отдельно: в Oracle - в журнале транзакций, в MS SQL - в tempdb (сейчас, там, может, что-то ещё поменялось - я с тех пор за этой темой не следил).
Вы всё правильно написали, схема хранения данных в Firebird осталась как в Interbase, это тоже copy-on-write, как Postgres. Но Firebird делает больше проверок и чаще избегает создания копий, как в указанном примере.
Если схема хранения в Firebird в сравнении с изначальной, из Interbase, существенно не менялась, то объеединятт в общую категорию реализации MVCC в Firebird и в Oracle с MS SQL некорректно: в изначальной реализации MVCC (котрая появилась в первой половине 00-х - до этого там MVCC вообще не было, а для обеспецения согласованности доступа к записям использовались блокировки) в Oracle и MS SQL AFAIK разницы старых копий записей с текущей хранились отдельно: в Oracle - в журнале транзакций, в MS SQL - в tempdb (сейчас, там, может, что-то ещё поменялось - я с тех пор за этой темой не следил).
Реализация MVCC везде разная. Уcловно её можно поделить по следующим критериям:
версии чего хранятся?
где хранятся версии?
Oracle единственная СУБД, которая хранит версии блоков, а не записей.
Где хранятся версии?
Firebird, Postgres - data files;
MSSQL - tempdb;
InnoDB, Oracle - Undo log.
Так вот автор написал не про эти особенности. Он всего лишь отметил, что реализация MVCC в большинстве СУБД расcчитана на успешный commit. В том числе и в Firebird. В Postgres это не совсем так, ибо старая и новая запись в принципе выглядят одинаково, занимают примерно одинаковое место на диске. Так что в этом он прав.
Теперь о хранении в data files и о том насколько это мешает. В реальности в Firebird не сильно мешает. Объясню почему. И так Firebird всегда хранит самую последнюю версию записи. Предыдущая версия записи во-первых в большинстве случае хранится в виде diff от главной (то есть она обычно намного компактней). Во-вторых она может хранится либо на той же странице, что и основная версия, либо на отдельной странице. Обычно Firebird оставляет примерно 20% свободного пространства на страницах данных под версии и фрагменты. Это позволяет в ряде случаев хранить старую версию на той же странице, на которой находится главная, что в свою очередь очень ускоряет реконструкцию старой версии записи и откат. Но если места недостаточно, то версия уходит на другую страницу. Такая страница называется вторичной, и она пропускается при Full Scan, если возможно. Таким образом хранение версий прямо в data files не сильно мешает эффективному чтению. Отмечу, что даже если версии записей всегда размещать на secondary page, то место на основной странице данных всё равно надо резервировать под фрагменты записей, так что это не такое уж сильное зло.
Другое дело сборка мусора... Но именно это не сильно отличается в Firebird и Postgres.
IMHO: Чтобы переносить БД на другую СУБД, должны быть исключительно веские причины. Объективную оценку ситуации, можно дать обладая экспертностью в обоих СУБД и зная БД.
Всё остальное, от незнания и нежелания разбираться с вопросами, отсюда и перетягивание на вроде бы более знакомое что-то. Но это, как правило, не в интересах бизнеса, которому надо просто обеспечить себе нормальную работу с БД и корректную актуализацию структуры с трансформированием под текущие реалии. Сделать же правильные выводы, основываясь на "мы нифига не знаем СУБД X, переходим на Y", попросту невозможно. Трата времени и $.
Статья вроде техническая, но по языку видно, что у автора бомбит. Статью плюсанул.
Миграция с Firebird на PostgreSQL. Что может пойти не так? Часть 1