Oracle или PostgreSQL: отличия и миграция

← назад к разделу

Разговор о переезде обычно начинается не с технологий, а со счёта: лицензия Oracle считается по ядрам процессора, партиционирование, сжатие и продвинутая диагностика идут отдельными платными опциями, и продление на следующий год стоит как половина команды. PostgreSQL умеет почти то же самое бесплатно, и вопрос «переезжать или нет» встаёт сам. Ответ на него зависит не от списка возможностей, а от того, сколько в базе кода, который знает, что он работает в Oracle. Эта статья про то, где именно базы расходятся, что из этого переписывают руками, как проверить, что переехали правильно, и когда переезд не стоит своих денег.

Пустая строка, которая была NULL

Самая дорогая строка в этой статье звучит безобидно: Oracle не различает пустую строку и NULL. Приложение пишет в поле комментария '', Oracle сохраняет NULL, и запрос WHERE comment IS NULL годами находил все заказы без комментария. После переезда тот же код пишет '', PostgreSQL честно хранит пустую строку, и тот же запрос находит ноль строк. Тесты при этом зелёные: они проверяли, что строка сохранилась, а не что она стала NULL.

Лечится в двух местах сразу. Данные при переносе нормализуют: UPDATE orders SET comment = NULL WHERE comment = '' по каждой строковой колонке, где приложение полагалось на старое поведение. А приложение учат писать NULL там, где имеется в виду «нет значения»: в коде это null вместо пустой строки в параметре запроса, и это тот код, который ora2pg не найдёт, потому что он лежит не в базе.

Вторая половина той же темы прячется в индексах. Обычный индекс по одной колонке в Oracle строк с NULL не содержит, PostgreSQL кладёт их наравне со всеми. Отсюда два сюрприза в разные стороны: в Oracle WHERE col IS NULL индексом не пользуется, а уникальный индекс не ограничивает строки с целиком пустым ключом; в PostgreSQL поиск по IS NULL идёт по индексу, а уникальность до PostgreSQL 15 считала два NULL разными, и только UNIQUE NULLS NOT DISTINCT делает их равными. Схема, где на пустом ключе держалась «мягкая уникальность», после переезда ведёт себя иначе, и заметно это станет на первой же вставке.

Типы: числа, даты и длина строк

NUMBER без точности ora2pg переводит в numeric, и это правильно по смыслу и плохо по скорости: numeric считается программно и в несколько раз медленнее bigint, а индекс по нему заметно толще. Идентификаторы, счётчики и всё, что в Oracle было NUMBER(10) или NUMBER(19), при переезде переводят в integer и bigint руками, оставляя numeric деньгам и величинам с дробной частью.

DATE в Oracle хранит и дату, и время до секунды, поэтому его переводят в timestamp, а не в date: иначе время молча отбрасывается, и отчёты за «сегодня до 15:00» перестают сходиться. VARCHAR2(100) по умолчанию ограничивает сто байт, а varchar(100) в PostgreSQL сто символов; для кириллицы это разница вдвое, и строки, которые в Oracle не влезали, в PostgreSQL влезут, а обратно уже нет.

SQL, который придётся переписать

Половина запросов в приложении, выросшем на Oracle, не запустится в PostgreSQL вовсе, и это хорошая новость: такие места видно сразу. Пары «было, стало» одни и те же от проекта к проекту.

Первые N строк через ROWNUM требуют подзапроса, потому что ROWNUM присваивается до сортировки:

SELECT * FROM (SELECT id, total_amount FROM orders ORDER BY total_amount DESC)
WHERE ROWNUM <= 5;

Стандартный вариант короче и работает в обеих базах, в Oracle начиная с 12c, так что запросы, которые ещё не переписаны, дешевле сразу писать так:

живой пример

SELECT id, total_amount FROM orders
ORDER BY total_amount DESC
FETCH FIRST 5 ROWS ONLY;

NVL становится стандартным COALESCE, DECODE разворачивается в CASE, SYSDATE превращается в now() или current_timestamp, а CONNECT BY PRIOR для иерархий заменяет рекурсивное WITH RECURSIVE. Старый синтаксис внешнего соединения через (+) PostgreSQL не понимает, его переписывают на LEFT JOIN. MERGE в PostgreSQL появился только в версии 15; на более старой базе его заменяет INSERT ... ON CONFLICT DO UPDATE, который покрывает обычный upsert, но не ветку WHEN NOT MATCHED BY SOURCE.

Подсказки оптимизатору /*+ ... */ обычный PostgreSQL молча пропускает: план настраивают статистикой, индексами и переписыванием запроса. Расширение pg_hint_plan возвращает привычные подсказки, и облачные провайдеры его предлагают, но брать его в план переезда не стоит: вместо переезда получится перенос привычек, а планировщик PostgreSQL с ручным управлением работает хуже, чем без него.

Объём переписывания снижает расширение orafce: оно добавляет NVL, DECODE, ADD_MONTHS, форматы TO_DATE и таблицу dual, и тысяча запросов с NVL начинает работать без правок. Ещё дальше идут совместимые сборки, Postgres Pro Enterprise и EDB Postgres Advanced Server: у них есть пакеты, часть DBMS_* и режим совместимости типов. Они честно закрывают половину списка выше, но возвращают то, от чего уезжали: платную лицензию и привязку к поставщику. Их берут, когда переписать код дороже, чем платить, и это тоже нормальный ответ.

PL/SQL и то, чему нет аналога

Синтаксис PL/pgSQL похож на PL/SQL настолько, что простые процедуры переносятся почти дословно, и на этом сходство заканчивается. Пакетов в PostgreSQL нет: PACKAGE разворачивают в схему с набором функций, а состояние пакета, переменные, живущие всю сессию, переносить некуда; их переделывают во временные таблицы или в настройки сессии через set_config. DBMS_OUTPUT.PUT_LINE становится RAISE NOTICE, пользовательские коды ошибок -20001 превращаются в RAISE EXCEPTION USING ERRCODE, а BULK COLLECT в массивы или, чаще, в один SQL-запрос вместо цикла. Управлять транзакцией изнутри хранимого кода в PostgreSQL можно только в PROCEDURE, начиная с версии 11, функции живут внутри транзакции вызывающего.

Аналога нет у одной вещи, и она обходится дороже всего: автономных транзакций. PRAGMA AUTONOMOUS_TRANSACTION в Oracle позволяет из процедуры записать строку в журнал и зафиксировать её, даже если внешняя транзакция потом откатится; на этом держится аудит и логирование ошибок в тысячах систем. В PostgreSQL коммит внутри функции невозможен, обходные пути это отдельное соединение через dblink или фоновый воркер, и оба заметно медленнее и капризнее. Честное решение переносит аудит в приложение, где вторая транзакция стоит одну строку кода.

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

Как переезжают: четыре этапа и проверка

Схему и типы конвертирует ora2pg, данные переносят выгрузкой в окно простоя или через логическую репликацию из Oracle, если простоя быть не должно. Дальше руками: хранимый код и SQL в приложении по парам из разделов выше. Проверка после переноса это отдельный этап, а не пункт в конце, и у неё три приёма.

Первый, сверка данных: число строк по каждой таблице совпадает редко достаточно, сверяют контрольные суммы. В обеих базах считают хеш от отсортированных строк таблицы, например md5(string_agg(row::text, '|' ORDER BY id)) в PostgreSQL и DBMS_SQLHASH или STANDARD_HASH в Oracle, по одинаковому представлению колонок; расхождение показывает таблицу, где потерялась точность чисел или время в датах. Второй, теневой прогон: приложение читает из PostgreSQL, а результат сравнивают с ответом Oracle на тот же запрос, и так неделю на копии трафика; именно здесь всплывают пустые строки, NULL в индексах и порядок строк без ORDER BY. Третий, план отката: пока Oracle не выключен, изменения из PostgreSQL можно возвращать логической репликацией или через outbox, и решение «переезд удался» принимают после недели без расхождений, а не в ночь переключения.

Отказоустойчивость: что покупали у Oracle

Главный аргумент «Oracle оправдан на большом масштабе» это RAC и Data Guard. RAC держит несколько узлов на одном хранилище, все пишут одновременно, и падение узла не останавливает запись. У PostgreSQL такого режима нет: пишет один узел, остальные реплики читают. Потоковая репликация со сдвигом в миллисекунды и автоматическое переключение через Patroni дают то, что даёт Data Guard: резервный узел, который становится главным за десятки секунд. Логическая репликация добавляет выборочный перенос таблиц и обновление версии без остановки. Если приложению нужно именно «писать в несколько узлов сразу», PostgreSQL этого не даст, и это редкий, но настоящий случай остаться.

Чем будет непривычно жить дальше

Одно различие не попадает в план переезда, зато встречает команду на первой неделе после него. Обе базы показывают читателю согласованный снимок, пока рядом идёт запись, но хранят старые версии строк в разных местах. Oracle складывает их в отдельную область UNDO: долгий отчёт читает оттуда, а если идёт дольше, чем живут старые версии, падает с «снимок слишком стар» (ORA-01555). PostgreSQL хранит старые версии в самой таблице рядом с живыми: отчёт ничего не уронит, но всё это время база не может выбросить ненужные версии, и таблица растёт. Уборкой занимается autovacuum, и за ним после переезда начинают следить: отстал он, и запросы, которые вчера летали, сегодня читают втрое больше страниц. Подробнее в статье про VACUUM и autovacuum, а как читать план запроса без хинтов, в статье про EXPLAIN.

Когда Oracle всё же оправдан

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

Коротко

• Мотив переезда почти всегда лицензия по ядрам; возможности баз близки, расходятся семантика и хранимый код.
• Oracle не различает '' и NULL: данные нормализуют при переносе, приложение учат писать NULL; в индексах NULL лежит по-разному.
• NUMBER без точности переводят в bigint там, где это целые; DATE в timestamp; VARCHAR2 считает байты.
• ROWNUM, NVL, DECODE, SYSDATE, CONNECT BY, (+) переписывают по одним и тем же парам; MERGE есть с PostgreSQL 15; orafce снимает половину правок.
• Пакетам, их состоянию и автономным транзакциям аналога нет; хранимую логику дешевле вынести в приложение.
• Проверка переезда: контрольные суммы таблиц, теневой прогон на копии трафика, план отката до недели без расхождений.
• RAC не заменяется ничем; Data Guard заменяет потоковая репликация с Patroni; после переезда следят за autovacuum.

Что почитать дальше

• VACUUM и autovacuum — почему таблицы растут без новых строк и как за этим следить.
• EXPLAIN и планы запросов — как настраивать план статистикой и индексами вместо хинтов.
• Миграции в PostgreSQL — как менять схему версионированными шагами.
• PostgreSQL или MongoDB — соседняя развилка: реляционная или документная модель.