Миграция с Firebird на PostgreSQL. Что может пойти не так? Часть 1 / forpes.ru

Главная
Миграция с Firebird на PostgreSQL. Что может пойти не так? Часть 1

Миграция с Firebird на PostgreSQL. Что может пойти не так? Часть 1 +45

08.07.2025 18:14

AlexeyKovyazin 14 4200 Источник

Часть 1

Не секрет, что в последние годы различные компании достаточно часто принимают решение о миграции работающей информационной системы с Firebird на PostgreSQL.

Типичная ситуация выглядит так:

Проект работает несколько лет. Заказчик "верит", что проблема не в проекте, а в СУБД. Firebird – "плохая" СУБД.

Вместо того, чтобы

привлечь внешние компании в качестве консультантов
обучить и сертифицировать собственных сотрудников
повысить их профессиональный уровень

гораздо проще убедить себя в том, что первопричина проблем - это Firebird, и принять решение о миграции на другую БД.

Эта проблема не имеет отношения к конкретной СУБД и является чисто управленческой.

Не верите? Выделенная строка – дословная цитата из доклада "7 ошибок, которые совершают разработчики при использовании СУБД PostgreSQL", с одним изменением - в оригинале вместо Firebird написано PostgreSQL.

Ситуация усугубляется тем, что очень часто те люди, кто принимают решение о подобной миграции, не представляют себе проблем и трудностей этого процесса.

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

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

Важно помнить, что на процесс миграции влияет сложность БД и степень использования специфических возможностей Firebird. Объем потраченных на переезд ресурсов зависит от многих факторов: объема и качества кода проекта, размера БД, необходимости оптимизации и других моментов.

PostgreSQL – замечательная СУБД, со множеством интереснейших функционалов, но она - другая, и поведение вашей системы сразу после миграции изменится, и на первом этапе - в худшую сторону.

Ведь мигрируете-то вы с Firebird на PostgreSQL, поэтому радости от функциональности PostgreSQL, которой нет в Firebird при переезде вы не получите, а вот слёзы от отсутствия или иного поведения функциональности Firebird в PostgreSQL получите гарантированно.

Запросы, которые при одинаковых данных хорошо работают под Firebird не обязательно будут хорошо работать на PostgreSQL. Иное тоже верно, но при миграции с Firebird на PostgreSQL Вам в первую очередь предстоит почувствовать верность первой части этого утверждения...

Давайте посмотрим вкратце с какими проблемами можно столкнуться при подобной миграции.

Часть 1. Особенности реализации MVCC

Наверное, самое неприятное и неожиданное для тех, кто привык к Firebird, вернее к тому, как в Firebird реализована работа с версиями данных.

Хотя вернее здесь будет говорить о специфичной реализации MVCC в PostgreSQL: здесь механизм управления версиями фундаментально отличается от Firebird, MS SQL, ORACLE.

Подробно об этой специфике можно прочитать как в этом форуме, посвященному PostgreSQL MVCC, так и в этой статье.

Если же кратко, то классический подход к реализации MVCC заключается в реализации некого «оптимистичного» алгоритма, который предполагает, что транзакции будут завершаться успешно, а старые версии не будут использоваться. Потому новая версия записи пишется поверх старой, а в логе отмены записывается либо дельта, либо вся старая запись, благодаря чему старую версию можно вытащить при необходимости, которая должна редко возникать. Т.е. условная классическая реализация МVСС заключается в том, чтобы хранить diff и воскрешать старую версию данных вычитая diff-ы из текущей/последней версии. В Firebird это так.

Нельзя сказать, что реализация МVСС в PostgreSQL плохая - просто она фундаментально другая. Концепция здесь: "copy-on-write", что плодит версии записей (полные копии записей со служебной информацией) со всей пролетарской ненавистью.

Реализация МVСС в PostgreSQL вообще не подразумевает обновления записи, при необходимости обновления выполняются удаление-вставка.

Каждая версия записи в PostgreSQL (tuple) – эта полная копия записи с некоторыми служебными полями.

Например: поле XMIN – в него пишется Id транзакции, создавшей запись, что позволяет понять каким транзакциям положено эту запись видеть. Или поле XMAX - в него пишется Id транзакции, удалившей запись.

Что же происходит при обновлении записи:

Сначала мы в текущей версии записи заполняем поле XMAX – куда пишем Id обновляющей транзакции, затем создаем новую строку, в которой в XMIN пишем Id обновляющей транзакции. Ну и в старую версию записи добавляется ссылка на новую.

То есть по реализации - дословно – УДАЛЕНИЕ И ВСТАВКА.

Когда вы обновляете один столбец одной строки, вся строка копируется в новую версию, вероятно, на новой странице. (PostgreSQL будет пытаться запихнуть новую версию на ту же страницу, где была старая, но это возможно далеко не всегда), а старая строка также изменяется с указателем на новую версию. Индексные записи следуют тому же: поскольку есть совершенно новая копия, все индексы должны быть обновлены указанием на новое местоположение страницы. Все индексы, даже те, кто не имеет отношения к изменяемому столбцу, обновляются только потому, что вся строка перемещается.

Позже потребуется операция для очистки старых кортежей (VACUUM). Еще одним следствием этого подхода является большой объем генерации Wal (Redo log), потому что многие блоки затрагиваются, когда кортеж перемещается в другое место.

Следствие – более высокая нагрузка на диск при выполнении обновлений, большая интенсивность при репликации.

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

Здесь - надо менять сознание… После работы с Firebird ожидаешь, что, обновление записи существенно дешевле чем "удалить и вставить", но как сказал Андрей Аршавин в 2012 после поражения от Греции: "Ваши ожидания – это ваши проблемы", а PostgreSQL точно никому ничего не должен.

Для иллюстрации всего вышеизложенного при помощи тестов создадим таблицу следующей структуры:

ТЕСТОВАЯ ТАБЛИЦА DAT (Синтаксис сокращен для читаемости):

-- поля
CREATE TABLE DAT (    ID BIGINT NOT NULL,     I1..I8 BIGINT,     N1..N8 DOUBLE PRECISION,     D1..D8 TIMESTAMP,     S1..S8 VARCHAR(100),     T1..T8 VARCHAR(1000)
);
PK$DAT PRIMARY KEY (ID);
-- индексы
X_I1 ON DAT (I1); X_I23 ON DAT (I2, I3); X_I456 ON DAT (I4, I5, I6);
X_N1 ON DAT (N1); X_N23 ON DAT (N2, N3); X_N456 ON DAT (N4, N5, N6);
X_D1 ON DAT (D1); X_D23 ON DAT (D2, D3); X_D456 ON DAT (D4, D5, D6);
X_S1 ON DAT (S1); X_S23 ON DAT (S2, S3); X_S456 ON DAT (S4, S5, S6);
X_F1 ON DAT (F1); X_F23 ON DAT (F2, F3); X_F456 ON DAT (F4, F5, F6);
X_T1 ON DAT (T1);

В таблице – 10 млн записей, первое поле группы заполнено всегда,

Оставшиеся поля группы (№2-№8) с 50% вероятностью заполнено NULL.

Данные таблица идентична в базах Firebird 5.0.2 и PostgreSQL 17.4

Сервера баз данных запущены на Linux Mint 22.1 (SSD, 32Gb RAM)

Произведено базовое конфигурирование серверов БД.

Тесты

При обновлении индексированного поля таблицы в Firebird должно произойти перестроение только тех индексов, которые построены по этому полю при обновлении в PostgreSQL должно произойти перестроение всех индексов, независимо от того, обновляет ли UPDATE-запрос поля, по которым построены эти индексы или нет.

Проверим это:

Мы видим, что практические результаты полностью коррелируют с теоретической частью: Время обновления в PostgreSQL не зависит от того сколько полей мы обновляем и индексированы ли они, в Firebird – зависит.

Разница во времени выполнения подобных массовых обновлений в Firebird и PostgreSQL очень существенна.

Сравним также скорость вставки и удаления записей:

УДАЛЕНИЕ 1 МЛН ЗАПИСЕЙ ИЗ 10 МЛН:

delete from dat where id between 3000000 and 3999999

Firebird 5.0.2	Postgres 17.4
2.4 сек	2.2 сек

Приведены усредненные данные по серии из 50 замеров, разница ожидаемо несущественна.

ВСТАВКА 1 млн записей:

insert into dat (id, i1, i2, i3, i4, i5, i6, i7, i8, f1, f2, f3, f4, f5, f6, f7, f8, n1, n2, n3, n4, n5, n6, n7, n8, d1, d2, d3, d4, d5, d6, d7, d8, s1, s2, s3, s4, s5, s6, s7, s8, t1, t2, t3, t4, t5, t6, t7, t8)
select id+10000000,  i1, i2, i3, i4, i5, i6, i7, i8, f1, f2, f3, f4, f5, f6, f7, f8, n1, n2, n3, n4, n5, n6, n7, n8, d1, d2, d3, d4, d5, d6, d7, d8, s1, s2, s3, s4, s5, s6, s7, s8, t1, t2, t3, t4, t5, t6, t7, t8 from dat where id between 1000000 and 1999999

Firebird 5.0.2	Postgres 17.4
10 минут 36 сек	4 минуты 38 сек

Здесь результат заметно лучше у PostgreSQL.

Вывод: Вам необходимо проанализировать свою информационную систему с целью выявления регламентов с подобной функциональностью перед миграцией, решить, нужны ли они, можно ли их как-то заменить или вообще полностью от них отказаться.

Дополнительно нужно учитывать, что в PostgreSQL Вы, на какое-то время начнёте использовать вдвое больший размер дискового пространства после подобного обновления и до выполнения сборки мусора, что в Firebird так же будет иметь куда меньший негативный эффект – ведь места на создания дельты при обновлении конкретного поля нужно существенно меньше, чем на создание полной копии записи.

Эта первая часть статьи, которая была написана Александром Шапошниковым (shaposh@yandex.ru) по мотивам доклада на конференции FBConf 2025. Продолжение - здесь.

Комментарии (14)

gena_k
08.07.2025 19:09
#28546300
Спасибо за статью. Позвольте пояснить про HOT Update

Реализация МVСС в PostgreSQL вообще не подразумевает обновления записи, при необходимости обновления выполняются удаление-вставка.

...

PostgreSQL должно произойти перестроение всех индексов, независимо от того, обновляет ли UPDATE-запрос поля, по которым построены эти индексы или нет.

Если по обновляемым столбцам индексов нет, то срабатывает Heap Only Tuple Update, когда новая версия Heap записывается на той же странице, что и старая. Это позволяет не обновлять индексы (они ссылаются после UPDATE на те же самые страницы, что и до UPDATE).

Почему не сработало в Вашем тесте. В реальных системах обновление идёт отдельных строк, а в приведённом тесте - таблицы целиком. Поэтому на страницах не оказалось свободного места для размешения новых версих строк в HOT Update.
1. RostislavDugin
  08.07.2025 19:09
  #28546540
  Т.е. можно сказать, что узкое место PostgreSQL в целом - это обновление полей, на которых лежат индексы?
  1. Mapar
    08.07.2025 19:09
    #28548276
    Нет, HoT update тоже влияет, как на место так и время работы.Но просто меньше, чем update индексированных полей.
1. shaposh
  08.07.2025 19:09
  #28547232
  Спасибо за замечание.
  Запустил
  update dat set n8=0 where mod(id, 10) =0
  Посмотрим насколько будут отличаться результаты при обновлении 10% таблицы
  1. mirwide
    08.07.2025 19:09
    #28548590
    Не должны отличаться, потому что 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

Ivan22
08.07.2025 19:09
#28546488
так firebird diff-ы же хранит прямо в файле данных! То же такое себе, узкое место. Приходится тоже а-ля вакуум делать, Таки отдельный undo log лучше
1. fraks
  08.07.2025 19:09
  #28546918
  Оно не узкое. После того как актуальность версий заканчивается, это место будет в дальнейшем использовано. Уборка "мусорных версий" производится при обращении к записи, сервер определяет какую из версий может видеть клиент, а какие версии уже никому не нужны. Т.е. убирать будет не тот кто эти версии создал, или удержал, а следующий за ними клиент/транзакция. От этого есть несколько решений - не держать длинных транзакций без необходимости. При удалении большого количества записей лучше тут же их прочитать, из той процедуры которая удаляла, но в новой транзакции. Таким образом замедление будет у того кто удалял а не у следующего, типа "необъяснимое торможение".
  
  Так что совершенно не факт что " отдельный undo log лучше ".
  1. Ivan22
    08.07.2025 19:09
    #28547936
    Ну как же не узкое, это как раз самое узкое место. Файл с основными данными таблиц. Уборка мусора дополнительно нагружает его, как ее не делай, отдельным вакуумом или тут же их прочитать, из той процедуры которая удаляла. Плюс фрагментация данных будет расти. Так что это совершенно факт - " отдельный undo log лучше ".
    
    sim_84
    08.07.2025 19:09
    #28548238
    Прочитайте мой комментарий. Всё устроено не совсем так как вы думаете. Сборка мусора нужна и для UNDO в InnoDB. В Oracle не нужна, но и там если пожадничать можно попасть на "ORA-01555: Snapshot too old"

mvv-rus
08.07.2025 19:09
#28546702
Поясните, пожалуйста, по поводу реализации 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 (сейчас, там, может, что-то ещё поменялось - я с тех пор за этой темой не следил).
1. kmatveev
  08.07.2025 19:09
  #28547488
  Вы всё правильно написали, схема хранения данных в Firebird осталась как в Interbase, это тоже copy-on-write, как Postgres. Но Firebird делает больше проверок и чаще избегает создания копий, как в указанном примере.
1. sim_84
  08.07.2025 19:09
  #28547524
  Если схема хранения в 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.

VladimirLiberty
08.07.2025 19:09
#28547442
IMHO: Чтобы переносить БД на другую СУБД, должны быть исключительно веские причины. Объективную оценку ситуации, можно дать обладая экспертностью в обоих СУБД и зная БД.

Всё остальное, от незнания и нежелания разбираться с вопросами, отсюда и перетягивание на вроде бы более знакомое что-то. Но это, как правило, не в интересах бизнеса, которому надо просто обеспечить себе нормальную работу с БД и корректную актуализацию структуры с трансформированием под текущие реалии. Сделать же правильные выводы, основываясь на "мы нифига не знаем СУБД X, переходим на Y", попросту невозможно. Трата времени и $.

kmatveev
08.07.2025 19:09
#28547480
Статья вроде техническая, но по языку видно, что у автора бомбит. Статью плюсанул.