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

SUSPEND и RETURN NEXT

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

В PostgreSQL это не так

Из документации:

В текущей реализации RETURN NEXT (Аналог SUSPEND в Firebird) результирующее множество накапливается целиком, прежде чем будет возвращено из функции. Если множество очень большое, то это может отрицательно сказаться на производительности, так как при нехватке оперативной памяти данные записываются на диск.

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

Использование автономных транзакций

Основная, (но не единственная) область использования автономных транзакций – это аудит, который нельзя откатить.

Вот пример использования автономной транзакции для Firebird в триггере на событие подключения к базе данных для регистрации всех попыток соединения, в том числе и неудачных (Взято из источника Руководство_по_языку_SQL_СУБД_Firebird_5.0)

CREATE TRIGGER TR_CONNECT ON CONNECT AS
BEGIN
  -- Все попытки соединения с БД сохраняем в журнал
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(MSG) VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  IF (CURRENT_USER IN (SELECT USERNAME FROM BLOCKED_USERS)) THEN
  BEGIN
    -- Сохраняем в журнал, что попытка соединения с БД оказалась неудачной
    IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO LOG(MSG) VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
      POST_EVENT 'CONNECTION ATTEMPT' || ' BY BLOCKED USER!';
    END
    -- теперь вызываем исключение
    EXCEPTION EX_BADUSER;
  END
END

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

CREATE FUNCTION log_dblink(msg text) RETURNS void LANGUAGE sql
AS $function$  
select dblink('host=/var/run/postgresql port=5432 user=postgres  dbname=postgres', 
              format('insert into log select %L, %L', msg, clock_timestamp()::text))
$function$

Только в Postgres Pro Enterprise (даже не Standard!) реализована полноценная поддержка автономных транзакций с выполнением автономной транзакции внутри того же серверного процесса.

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

Диаграмма показывает производительность выполнения обычного SQL-запроса (соответствует 100% на диаграмме), запроса в автономной транзакции версии Postgres Pro Enterprise и при помощи различных расширений (Из материалов Postgres Pro).

Если Вы активно используете автономные транзакции в своем проекте под Firebird для аудита – учтите это.

Ограниченный функционал анонимного PSQL блока в PostgreSQL

Из документации PostgreSQL (46.4. Анонимные блоки кода):

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

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

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

EXECUTE BLOCK(x DOUBLE PRECISION=?, y DOUBLE PRECISION=?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN  
gmean = sqrt(x*y);  
SUSPEND;
END

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

Процессы и Потоки

PostgreSQL создает один новый процесс (не поток) для каждого соединения. Без надлежащей настройки и подходящего сервера из-за незапланированных всплесков использования база данных может быстро стать перегруженной.

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

У текущей модели есть ряд недостатков: статически выделяемая общая память не позволяет на лету менять размер таких структур, как буферный кеш; параллельные алгоритмы сложны в реализации и менее эффективны, чем могли бы быть; сеансы жестко привязаны к процессам. Использование потоков выглядит многообещающе, хотя и чревато сложностями с изолированностью, совместимостью c операционными системами, управлением ресурсами. Не говоря уже о том, что переход потребует радикальных изменений в коде и годы работы. Пока побеждает консервативный взгляд, и в ближайшее время никаких изменений не предвидится.

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

Firebird же не особо чувствителен к большому количеству неактивных соединений, по собственному многолетнему опыту эксплуатации информационной системы под Firebird (причем в архитектуре Classic Server, наиболее близкой к архитектуре PostgreSQL) могу сказать, что не 500, ни 1000, ни 1500 открытых соединений не вызывали каких то заметных проблем производительности. Архитектура Classic – как раз "процессная". Архитектуры, обеспечивающие работу с потоками в Firebird - SuperClassic и SuperServer. Последняя архитектура настоятельно рекомендуется к использованию.

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

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

Отметим также, что независимо от операционной системы PostgreSQL "не любит" большого количества одновременно открытых соединений. Отсюда и отдельный пакет pgbouncer (настоятельно рекомендуется к использованию) для управления пулом соединений и дефолтное значение параметра max_connections=100

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

Счетчик транзакций

В ядре PostgreSQL счетчик транзакций 32-разрядный, это значит, более чем до 4 миллиардов им досчитать невозможно. Это приводит к проблемам, которые решаются «заморозкой» — специальной процедурой регламентного обслуживания VACUUM FREEZE

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

Система понимает, что эта строчка была создана когда-то давно и номер транзакции создавший её уже не имеет значения. Значит этот номер транзакции можно использовать повторно. Замороженные номера транзакций можно повторно использовать.

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

Впрочем, если вы можете позволить себе остановить ваш кластер БД в любое время, запустить его в однопользовательском режиме для выполнения команды VACUUM FREEZE и подождать несколько часов, то данная проблема вас не касается.

64-разрядные счетчики транзакций - это принципиальная переделка ядра СУБД, к тому же и нужна только для сильно нагруженных систем, но для них она не просто желательна. Она необходима.

Именно поэтому во ВСЕХ коммерческих версиях PostgreSQL сейчас реализован 64-битный счетчик транзакций.

Наиболее известной и успешной является реализация от компании Postgres Professional, которые первыми в мире предложили решение.

Есть 4 причины, по которым это решение не только до сих пор отсутствует в ванильной версии.

1. Рефакторинг большого объема кода - весь остальной код PostgreSQL ожидает, что в счетчике будет 32-битное число

2. В каждой версии строчки есть заголовок. Если счетчик будет 64 битным, то будет слишком много служебной информации на каждую версию строки.

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

4. "Инертность" сообщества. Решение от Postgres Professional комьюнити отказались принимать из-за размера и сложности. Текущий статус загадочен… "Returned with feedback".

Что же касается Firebird, то при "исчерпании" счетчика транзакций понадобиться полное обслуживание БД, однако счетчики транзакций в Firebird 48-битные и описанная проблема – скорее теоретическая, чем имеющая отношение к реальной жизни.

Особенности работы с СХД

Рынок систем хранения данных (СХД) показывает устойчивый рост, где основные инвестиции наблюдаются со стороны корпоративного сегмента. Программы цифровой трансформации и новые онлайн-сервисы требуют постоянной модернизации инфраструктуры, что повышает спрос на высокопроизводительные системы.

Если у вас в системе есть запросы, которые должны выполнятся очень быстро (десятки миллисекунд), но очень часто, то вы столкнетесь с заметным ростом общего времени выполнения таких запросов после миграции в случае, если база находиться на СХД.

Причина в том, что база PostgreSQL может содержать сотни тысяч файлов, и на каждый файл при передаче информации между сервером и СХД будут возникать дополнительные расходы на передачу данных об имени файла, служебной информация и т.д.

Очень грубо это можно сравнить с ситуацией, когда вы копируете один файл в 10 ГБ по сети или 1000 файлов по 10 МБ, надеюсь все знают, какую разницу во времени этих операций вы получите.

Подробнее о данной ситуации можно прочитать здесь https://infostart.ru/1c/articles/1883272/

Особенности резервного копирования

В PostgreSQL с бэкапами все совсем не так, как в других БД – настолько, что это требует перелома психики и всех знаний, полученных при работе с другими СУБД.

Gbak & pg_dump

Аналог gbak для полного резервного копирования в PostgreSQL есть: pg_dump/pg_restore, и тут все вроде как примерно одинаково:

• Бэкап одной БД

• Можно выбрать нужные таблицы

• Многопоточно (в PostgreSQL – с ограничениями по формату выходного файла)

• нет инкремента

• Возможность получения сжатой копии

Получение резервной копии на примере БД с таблицей DAT:

Но pg_dump не подходит для создания резервных копий баз данных большого объема в реальном времени, так как может потребовать значительных ресурсов и времени на выполнение, что может негативно сказаться на производительности системы. С Gbak подобной проблемы не наблюдается со времен Firebird 2.5.

Nbackup & pg_basebackup

Для создания резервных копий в режиме реального времени в Firebird есть nbackup, в PostgreSQL pg_basebackup

И вот тут действительно по-другому абсолютно все:

pg_basebackup всегда дампит целый кластер, при восстановлении одной базы будет восстановлен весь ваш кластер PostgreSQL, в котором будет только одна база. Файлы всех остальных ваших баз будут нулевого размера. Недостатки: ограниченные возможности при работе в многопоточном режиме, ограниченность при создании инкрементальных резервных копий, меньшая гибкость по сравнению с pg_dump в выборе отдельных объектов для резервирования, невысокая скорость копирования.

pg_prоbackup от Postgres Professional обладает множеством преимуществ, по сравнению с pg_basebackup, но большинство из них – в коммерческой версии данной утилиты.

На самом деле достаточно характерно – кому придет в голову писать коммерческую версию nBackup?

Чтобы делать быстрые и надежные бэкапы в PostgreSQL нужно понять главное: термин «база данных» в резервном копировании PostgreSQL – это обман. Назовите ее чем угодно, но работайте только с кластером. Разбивайте большие базы данных по отдельным кластерам на других портах. Поверьте, это меньшее зло (если учитывать расход памяти) чем держать все "базы" в одном кластере.

Даже холодный бэкап\восстановление не так прост, а online бэкап\восстановление через pg_basebackup еще сложнее, поскольку это по сути подъем реплики.

Более подробно – здесь https://habr.com/ru/articles/791726/

Nbackup в Firebird проще, понятнее и главное - удобнее.

Это вторая часть статьи "Миграция с Firebird на PostgreSQL. (первая часть - здесь) Что может пойти не так?", написанной Александром Шапошниковым (shaposh@yandex.ru) по мотивам доклада на конференции FBConf2025. Третья и финальная часть статьи - здесь.