PostgreSQL 9.3 вместо MS SQL: мигрируем несколько схем и смотрим что получилось

В апреле к нам пришёл клиент с задачей, которую мы раньше видели только в виде теоретических вопросов на конференциях: взять несколько рабочих схем с MS SQL Server 2008 R2 и переехать на PostgreSQL. Не исследование, не пилот - именно рабочие базы, с которыми работают приложения прямо сейчас.

Мотив у клиента смешанный: лицензионные расходы на MS SQL растут, а на фоне разговоров про импортозамещение вопрос «а зачем нам платить Microsoft» стал звучать острее. PostgreSQL в этом контексте выглядит привлекательно: открытый, есть российские дистрибутивы на его основе, и версия 9.3 - это уже не «интересный проект», а полноценная СУБД с функциями, которых в MS SQL либо нет, либо они стоят отдельных денег.

Мы взялись. Рассказываем что в итоге.

Что мигрировали

Три схемы разного характера:

• Основная операционная база - несколько десятков таблиц, хранимые процедуры, джобы SQL Server Agent, репликация на читающую реплику внутри MS SQL.
• Аналитическая схема - витрины данных, которые строились через SSIS из операционной базы, несколько агрегирующих представлений.
• Небольшая схема для хранения конфигураций - полуструктурированные данные, которые раньше жили в XML-столбцах MS SQL.

Именно третья схема навела нас на мысль посмотреть на JSON в PostgreSQL 9.3 внимательнее.

JSON: не игрушка, но и не замена реляционной модели

В MS SQL XML-столбцы использовались без особого энтузиазма - разработчики туда писали конфиги и настройки приложения, потому что «удобно не плодить столбцы под каждый параметр». Запросить что-то из этого XML в MS SQL 2008 - отдельное удовольствие с XPath и CROSS APPLY, которое никто особо не любил.

В PostgreSQL 9.3 появился тип json и набор операторов для работы с ним: -> и ->> для извлечения полей, #> для вложенных путей. Мы переложили конфигурационную схему в JSON-столбцы, переписали запросы - синтаксис заметно чище, и разработчики оценили.

Важная оговорка: json в 9.3 хранит документ как текст и парсит при каждом запросе. Индекс по содержимому JSON в 9.3 требует выражения-функции, что работает, но не так удобно. Это не Oracle с его XMLDB и не документная база - это реляционная СУБД с приличной поддержкой JSON для конкретных задач. Там, где данные всё-таки структурированы - нормальные столбцы лучше. Мы с клиентом заодно навели там порядок в схеме.

Материализованные представления: то, чего не было в MS SQL 2008

Аналитическая схема строилась через SSIS-пакеты, которые по расписанию перегоняли данные в витрины. Это работало, но было хрупким: пакеты зависели от конкретных версий компонентов, разбирались в чужом коде тяжело.

В PostgreSQL 9.3 появились материализованные представления - CREATE MATERIALIZED VIEW. Для ряда наших витрин это оказалось прямой заменой: создаёшь MATERIALIZED VIEW с нужным SELECT, настраиваешь REFRESH MATERIALIZED VIEW по расписанию через внешний планировщик (cron, pgAgent), и витрина обновляется без SSIS.

Ограничение, которое нас укусило: в 9.3 REFRESH MATERIALIZED VIEW блокирует чтение на время обновления. Для небольших витрин это не критично, но для тяжёлых агрегатов с долгим рефрешем это проблема. Мы обошли это через промежуточную схему с переименованием таблиц, но это костыль. Пришлось честно объяснить клиенту, что для витрин с долгим обновлением схема немного другая.

Streaming replication вместо SQL Server Replication

В MS SQL 2008 у клиента стояла транзакционная репликация на читающую реплику для отчётов. Настройка это исторически выглядела как несколько дней работы специалиста и постоянное внимание к мониторингу задержки.

PostgreSQL 9.3 streaming replication в сравнении с этим - приятная неожиданность. Настройка физической потоковой репликации занимает несколько часов, конфигурация достаточно прозрачная: на первичном сервере разрешаешь репликационные соединения в pg_hba.conf, задаёшь wal_level = hot_standby и max_wal_senders, на реплике настраиваешь recovery.conf с адресом мастера - и всё.

Реплика поднялась, отчёты переключили на неё. Задержка репликации видна через pg_stat_replication - у нас она держится в секундах при обычной нагрузке. Мониторинг задержки добавили в Zabbix через запрос к pg_stat_replication.sent_location и replay_location.

Что не умеет физическая репликация в 9.3 - нельзя реплицировать только часть таблиц или отдельную схему. Это физическая копия кластера. Для клиента это не было проблемой - реплика нужна вся - но если нужна репликация подмножества данных, это другой разговор.

Что оказалось сложнее ожидаемого

Хранимые процедуры. Это был самый трудоёмкий кусок. В MS SQL процедуры на T-SQL, в PostgreSQL - PL/pgSQL, синтаксис похожий, но не идентичный. Все процедуры переписывали вручную: курсоры, обработка ошибок, временные таблицы - везде есть отличия. Автоматической конвертации T-SQL в PL/pgSQL нет, pgloader и ora2pg для схем и данных помогли, но не для процедурной логики.

Типы данных несовпадают в неочевидных местах. DATETIME в MS SQL и timestamp в PostgreSQL - казалось бы одно и то же, но поведение при граничных значениях и при сортировке NULL отличается. Поймали несколько мест, где приложение молча давало неправильный результат.

Джобы SQL Server Agent переехали в pgAgent - он ставится отдельно, работает нормально, но настраивается через pgAdmin, что немного непривычно.

Что в итоге

Интеграционный проект занял около шести недель с учётом тестирования и переключения. Две схемы из трёх работают на PostgreSQL в продакшне. Аналитическая схема - в финальном тестировании, переключение планируем в следующем месяце.

Общее впечатление: PostgreSQL 9.3 - это серьёзная СУБД, которая закрывает большинство задач, которые раньше требовали MS SQL или Oracle. JSON-поддержка и материализованные представления - не маркетинговые фичи, а реальный инструментарий. Streaming replication проще в настройке, чем MS SQL репликация, и задержку держит хорошо.

Но это не «просто переустановить». Хранимые процедуры, несовместимость типов данных в деталях, разница в поведении оптимизатора - всё это требует времени и внимания. Клиент это понимал с самого начала, что сделало проект честным.

Смотрим что покажет аналитическая схема под реальной отчётной нагрузкой.