Третья часть статьи посвященной трудностям миграции с Firebird на PostgreSQL. (1ая часть, 2я часть).

Проблемы миграции кода, связанного с использованием временных таблиц

Есть в PostgreSQL проблема, с которой приходится сталкиваться достаточно часто. К сожалению, реализация логики временных таблиц в нем имеет ряд недостатков, которые отрицательно сказываются на производительности системы.

Этот факт общеизвестен, как и то, что вопрос «Мы использовали временные таблицы в базе данных <КАКОЙ‑ТО> SQL, и проблем не было. Почему это происходит в PostgreSQL?» встречается в форумах достаточно часто.

Причинами этого является:

• Активное создание, модификация и удаление временных таблиц (включая работу с индексами) может привести к существенной деградации производительности

• AVTOVACUUM не видит временные таблицы — Соответствующие операции очистки и анализа нужно выполнять вручную

• Характерный паттерн работы с временными таблицами ведёт к переполнению кеша ОС мусором

• pg_class (и прочие системные таблицы) разрастаются в процессе работы

• Запрет записи во временные таблицы в ReadOnly-реплике (при построении отчетности)

Учитывая широкое использование временных таблиц в современных решениях это может стать проблемой.

Возможные пути решения:

• Размещение временных таблиц на RAM-диске

• Использование нежурналируемых (UNLOGGED) таблиц вместо временных

• Использование Postgres Pro, где механизм временных таблиц существенно переработан

• Ограничение использования временных таблиц в своих приложениях

В Firebird:

• Временные таблицы создаются вне базы во временных файлах

• Режим Forced Writes для них всегда OFF

• Их очистка производится моментально

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

Еще раз отметим, что если используется pg_bouncer, то код, ранее использовавший GTT уровня соединения, может перестать работать.

Поведение планировщика запросов

У PostgreSQL относительно простой, но в то же время быстрый, алгоритм планирования запросов. Однако, у этого алгоритма есть одна большая проблема. Он строит план, и затем придерживается его, даже если он оказался ошибочным. В худшем случае может получится ситуация, когда в промежуточных вычислениях PostgreSQL ожидает 1–2 записей, а фактически оказывается в тысячи раз больше. В результате выполнение Nested Loop приводит к огромной сложности алгоритма, что приводит к зависанию процесса с большой загрузкой CPU. К сожалению, по умолчанию в ванильном PostgreSQL нет возможности указывать hint'ы, как в некоторых других СУБД.

Ради объективности укажем о существовании специальных расширений для решения этой проблемы — например pg_hint_plan.

Аргументируется это тем, что «нужно не подправлять проблемные запросы, а писать правильные». Ну вот переписать подобные проблемные запросы как «правильные» с точки зрения планировщика PostgreSQL Вам возможно предстоит при миграции.

Те из вас кто выполнял миграции с одной версии Firebird на другую, знают, что поведение планировщика запросов может поменяться — что уж говорить об изменении планов запросов при смене СУБД.

Размеры данных

В PostgreSQL отсутствует сжатие на уровне страниц (page level compression). Сжимаются только TOAST-данные. Если в БД много записей с относительно небольшими текстовыми полями, то сжатием можно было бы в несколько раз уменьшить размер БД, что помогло бы не только сэкономить на дисках, но и повысить производительность работы СУБД. Особенно эффективно могут ускоряться за счет сокращения операций ввода-вывода аналитические запросы, читающие много данных с диска и не слишком часто изменяющие их.

Кроме того не надо забывать, что на больших продах архив транзакций за день может превышать объем базы в несколько раз.

В PostgreSQL-сообществе предлагают использовать для сжатия файловые системы с поддержкой компрессии. Но это не всегда удобно и возможно. Отметим, что в решении Postgres Pro Enterprise постраничное сжатие реализовано.

Для проверки данного тезиса была взята таблица объединенных справочников с базы данных формата Firebird 5 у некоей транспортно-логистической компании. Она содержала около 700 000 строк и множество полей Данная таблица была конвертирована в БД формата PostgreSQL 17. Таблица содержала в себе данные следующих типов: строки, даты, флаги, целые и вещественные числа, uuid-ы. Кодировка строк — utf8.

Был произведен замер размера данных таблицы до и после конвертации.

Результат: после конвертации размер занимаемый, данными этой таблицы в БД увеличился ровно в полтора раза.

Отметим, что до выхода Firebird 5, упаковка текстовых данных кодировки utf8 в Firebird было заметно менее эффективным, чем сейчас.

Также надо помнить, что из-за реализации MVCC при полном обновлении одного поля во всей таблице PostgreSQL на какое-то время потребует для выполнения операции дисковое пространство, равное по размеру всей таблицы.

Приведу ссылку на статью, в которой проводилось более подробное исследование и сравнение.

Последствия зависших транзакций VACUUM FULL и GFIX -sweep

Некоторые проблемы в PostgreSQL, возникающие из-за зависших транзакций (раздувание таблиц и индексов) устраняются только при помощи VACUUM FULL, а он:

• выполняется гораздо дольше чем VACUUM

• запрашивает исключительную блокировку таблицы

• требует дискового пространства (записывает новую копию таблицы и не освобождает старую до завершения операции)

Подобные проблемы, характерные для многоверсионной архитектуры утилита Firebird gfix -sweep устраняет в режиме online и является многопоточным и быстрым решением.

Необходимо отметить расширение pg_repack от Postgres Pro выполняющее реорганизацию таблиц без исключительной блокировки.

Одна транзакция на соединение

Firebird поддерживает множество транзакций на соединение. Если в вашей информационнойсистеме, работающей под управлением Firebird есть решения, использующие эту функциональность, то они должны быть переписаны — в PostgreSQL (как и в других СУБД) этой возможности нет.

Подобная функциональность позволяет в рамках одного соединения запускать несколько транзакций с разным уровнем изоляции.

Наиболее частое применение — чтение данных в длинной read‑only транзакции, а изменение этих данных — в коротких пишущих транзакциях. Подобное поведение всегда поддерживалось популярными компонентами доступа к Firebird, как устаревшими (FibPlus), так и современными (FireDac, UniDac). Не забудьте про этот момент при миграции вашего приложения с Firebird на PostgreSQL.

Отсутствие имплементации пакетов

Пакеты… Как же они удобны при разработке. Возможность объединить процедуры и функции по бизнес-логике или типу, возможность обновления пакета целиком, а не обновлять набор процедур и функций... К примеру — у нас в системе есть документ «Заказ Поставщику». Все функции и процедуры, которые работают с этим документом реализовываем внутри пакета. Еще одним реальным примером объединения функций по бизнес‑логике является объединение в пакет функций, отвечающих за модуль взаимодействия с 1С.

Обновляя систему после внесения изменений — мы обновляем в целевой БД этот пакет целиком, а не множество процедур‑функций БД, которые мы изменили выпуская новую версию.

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

Модификация таблиц

Захотите после миграции добавить поле в таблицу — без проблем! Но только в конец таблицы. Если Вы хотите, чтобы поля в таблице шли в определенном порядке (т. е. вставить поле куда‑то в серединку таблицы) — то этого можно добиться только пересоздав и полностью перезалив таблицу. На самом деле в Firebird на уровне формата — поля тоже добавляются в конец таблицы. Просто в системных таблицах есть дополнительное поле, которое отвечает за порядок вывода полей в IDE или при выполнении SELECT запроса со *. Вроде небольшая доработка для СУБД. Но в PostgreSQL ее нет.

Проверка связей при компиляции процедур

Если Вы привыкли к проверке связей и метаданных при компиляции хранимых процедур и триггеров – отвыкайте. В PostgreSQL вы узнаете, что ошиблись в имени таблицы в коде процедуры только в момент её выполнения. Не очень-то приятно в production.

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

Миграция кода SQL

Даже при отсутствии необходимости переписывать множество хранимых процедур, UDF или UDR Вы столкнетесь с несовместимостью синтаксиса SQL между Firebird и PostgreSQL. Причем на этапе миграции Вам предстоит не насладиться тем синтаксическим сахаром, который есть в PostgreSQL, но нет в Firebird, а огрести из-за отсутствия в PostgreSQL каких-то плюшек Firebird или наличия в запросах каких-то синтаксических конструкций специфичных для Firebird (Например SELECT FIRST ... SKIP). Мне, более всего не хватает очень удобной синтаксической конструкции Firebird UPDATE OR INSERT, реализация которой в PostgreSQL через on conflict... по моему мнению далеко не так удобна.

Реализация UPSERT в Postgres:

INSERT INTO
director (id, name, fd1, fd2, fd3, fd4, fd5) 
VALUES (1, 'director Name',1,2,3,4,5)
ON CONFLICT (id)
DO UPDATE
SET name = EXCLUDED.name
, fd1 = EXCLUDED. fd1
, fd2 = EXCLUDED. Fd2
, fd3 = EXCLUDED. Fd3
, fd4 = EXCLUDED. Fd4
, fd5 = EXCLUDED. Fd5;

Сравните с реализацией в Firebird:

update or insert
into director (id, name, fd1, fd2, fd3, fd4, fd5)
VALUES (1, 'director Name',1,2,3,4,5)
matching (Id)

Добавив в процедуре к указанной конструкции предложение returning old.id, new.id into :old_id, :new_id получим возможность определить была ли очередная строка вставлена или обновлена.

Несоответствие типов

В PostgreSQL их заметно больше, однако это как раз при миграции мало помогает. А вот наличие типов Firebird, которые в PostgreSQL нужно будет объявлять по-другому (BLOB, DECIMAL, INT128, DECFLOAT ...) может доставить некоторые трудности.

Особо стоит отметить тип DECFLOAT, который является числовым типом из стандарта SQL:2016 и точно хранит числа с плавающей запятой. В отличие от DECFLOAT типы FLOAT или DOUBLE PRECISION обеспечивают двоичное приближение предполагаемой точности. Насколько я знаю, реализация этого типа есть только у Firebird и DB2.

Ну и нет у Firebird простого и прозрачного объявления типа UUID. Наверное разработчикам религия не позволяет реализовать синоним UUID для CHAR(16) CHARACTER SET OCTETS или BINARY(16), по другому его отсутствие объяснить крайне сложно. Это приводит к тому, что разнообразные утилиты конвертации баз данных (например Full Convert от Spectral Core) просто не понимают, что это UUID при миграции. Впрочем, в Firebird 6 объявление типа UUID именно как UUID все-таки обещают. Но при миграции сейчас — это может стать проблемой.

Различная реализация триггеров

Триггер в PostgreSQL состоит из двух компонентов: условия, которое определяет, когда триггер должен сработать, и действия (триггерная функция), которое должно быть выполнено, когда триггер срабатывает. В Firebird — это единый компонент-функция. Реализация триггеров в PostgreSQL шире и функциональнее, но при миграции – это переработка кода. Если в вашей системе большое количество триггеров то их переработка с учетом специфики PostgreSQL может занять какое-то время.

CREATE TABLE orders (
  order_id bigint, 
  name VARCHAR(100), 
  quantity INT,
  updated_at TIMESTAMP
);

Триггер в Firebird:

CREATE OR ALTER TRIGGER ORDERS_TBI FOR ORDERS
ACTIVE BEFORE INSERT POSITION 0 
AS
begin    
new.updated_at = localTimestamp;
end

Триггер в PostgreSQL:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN    
NEW.updated_at := NOW();    
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_update_orders
BEFORE UPDATE ON orders
FOR EACH ROW  EXECUTE FUNCTION set_updated_at();

Менеджер для работы с БД

Если Вам нужен менеджер для работы с PostgreSQL, ориентированный на разработку‑отладку хранимых процедур, то Ваш выбор: «EMS SQL Manager для PostgreSQL», «Postgres SQL Maestro» или «Navicat Premium». Все они сильно проигрывают тем инструментам, к которым мы привыкли при работе с Firebird.

Более сложное администрирование

Будьте готовы к тому, что Вам понадобятся дополнительные людские ресурсы на администрирование‑поддержку ваших баз данных после миграции... Администрировать PostgreSQL сложнее чем Firebird и он очень быстро обрастает разными утилитами и обвесами — pgbouncer, pgpool, patroni и еще десятки прочих, которые тоже нуждаются в «присмотре».

Как следствие — ежемесячный бюджет на развитие‑сопровождение системы может существенно возрасти.

Привычка — вторая натура

В отличие от Firebird в коде процедур и функций PostgreSQL входные‑ выходные параметры не маркируется «:». При возникновении неоднозначности это приводит к ошибке «column is ambiguous» и поначалу вызывает легкий ступор и дискомфорт – как с этим бороться?

Как победить данный дискомфорт можно узнать из этой статьи.

Ну и привыкнуть к тому, что если есть IF, то должен быть END IF тоже быстро не получиться. Поначалу забываешь постоянно, ну а ошибка определяется в любом месте, но только не там, где ты этот END IF забыл поставить.

Заключение

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

PS. Стоимость лицензирования и поддержки

Нужно быть готовым к тому, что стоимость Postgres Pro как версии Standard, так и версии Enterprise окажется существенно выше, чем аналогичные промышленные версии Firebird.

Ниже представлены базовые цены — обратите внимание, что расчет идет по количеству ядер в случае PostgreSQL и РедБазы.

По сравнению с ними, стоимость лицензии от HQBird с моделью лицензирования "На сервер" выглядит почти бесплатной.

Прайсы взяты из следующих источников:

• https://www.softprof-it.ru/katalog/postgres-pro

• https://shop-redsoft.ru/prajs-list-na-red-baza-dannyh/

• https://www.ibase.ru/hqbird-editions

Интересные материалы:

• Неочевидные для начинающих тонкости Postgres

• PostgreSQL и временные таблицы

• Инструкция по бэкапу одной базы в Postgres – миф или реальность?

• Различия Postgres Pro Enterprise и PostgreSQL

• Тонкости эксплуатации PostgreSQL

• Как PostgreSQL может сделать больно, когда не ожидаешь?

• [Типовые ошибки в приложениях, которые ведут к bloat в postgresql

• 10 Things I Hate About PostgreSQL

• Странное поведение планировщика запросов PostgreSQL

Это третья и последняя часть статьи, написанной Александром Шапошниковым по мотивам доклада на конференции FBConf2025. Предыдущие части тут: первая, вторая.