Почему мы перешли с Oracle на PostgeSQL, и как это сделать

Всем привет!
Сегодня расскажем о сравнительно новой для нас теме — про перевод приложения с Oracle на Postgres Pro (далее в тексте везде сокращу до PG). В общем смысле тема не столь уж нова — многие компании этим также занимаются или даже уже прошли этот путь. Так, например, на ежегодной конференции pgConf всегда есть несколько интересных докладов по этой теме (https://pgconf.ru/). Если говорить о формальностях, то мы реализуем инициативу согласно (Приказ Министерства связи «Об утверждении плана по импортозамещению программного обеспечения» от 01.02.2015 № 96). По факту — ещё и денег экономим, слезая с "лицензионной иглы". На эту тему можно отдельную статью написать, а в этой речь пойдёт о программной стороне вопроса. Кому интересно, добро пожаловать под кат.

Вместо введения

Есть хорошая статья, где обзорно показано текущее состояние СУБД PG и подсвечены интересные моменты, если еще не читали — обязательно посмотрите: https://habr.com/ru/company/tensor/blog/553754/

Что будем мигрировать

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

• имеет интеграционные связи;
• имеет веб-интерфейс (бэкэнд и фронтэнд части);
• модуль расчета;
• модуль генерирования отчетов;
• Данные хранятся в Oracle 12.
  Стек приложения — java/kotlin вперемежку, spring, разумеется hibernate. Часть приложений подняты на glassfish, часть автономны на spring boot. Такое вот наследие.

«Ценности» миграции

Обозначим основные «ценности» миграции. Они примерно таковы:

1. Идет постоянное развитие системы и команде линейного развития не стоит «мешать» делать ее работу, таким образом команду миграции мы решили выделить в «отдельную боевую единицу»;
2. Приложения должны (будут) уметь работать как с Oracle, так и с PG. Посчитали, что в таком виде будет проще вести параллельную работу двух команд — не выделяя отдельные версии приложений и т.д. Однако это рождает и дополнительные сложности, о которых ниже.
3. В рамках миграции не проводится никаких функциональных доработок (логических, бизнесовых), миграция решает собственно только вопрос миграции, все доработки миграции делаются для того, чтобы запуститься на новом окружении;
4. Миграция не должна ничего ухудшить (производительность, результаты). Основной критерий корректности миграции — прохождение тестов. Данные в отчетах должны быть идентичны «оракловому контуру».

Подходы к миграции

Настало время поговорить о подходах. Будем честны, опыта такой миграции в нашей команде еще не было. Зато появилась хорошая экспертиза на PG, так как последние проекты разрабатываем на нем. Поэтому для начала надо оглядеться, как люди это делают, почитать, поспрашивать. Здесь нам повезло, как раз не так давно мы были на pgConf, на котором было несколько очень качественных докладов на тему миграции. Вот они, например, от 2020 года — доступны всем желающим: https://pgconf.ru/2020/talks-and-tutorials. Если не смотрели, начните например, с Анатолия Афиногенова (РЖД) — просто, доступно и познавательно. Но там и другие очень интересные.
В процессе гугления интернета был найден очень интересный фолиант — Oracle to Azure Database for PostgreSQL Migration Guide. Пусть вас слово «Azure» не отпугнет, там все по делу и все про PostgreSQL. Очень подробно, 313 страниц на английском языке.
Технически же пойдем таким путем:

1. Организуем схожую продуктивной площадку серверов (в качестве операционки была выбрана наша «родная» RedOS 7.2 «Муром»);
2. Устанавливаем все «схожим образом». В качестве СУБД взяли самую свежую на сегодняшний момент PostgresPro Standard 13.

План работ

Все максимально просто, как казалось изначально:

1. Сервера поставили, софт тоже;
2. Создаем БД в PG, переводим объекты, перегружаем данные;
3. Дорабатываем бэкэнд приложения;
4. Фронтэнд не трогаем.
5. Все работает? К сожалению, в жизни все несколько сложнее.

Особенности мигрируемой БД

Приложение у нас, как я называю «backend-центрированное», что означает:

1. Используется ORM (hibernate);
2. В БД не так много процедурного кода (почти нет);
3. Используется не так много «изысков» SQL, как могло бы. Хотя, как выяснилось, так казалось на первый взгляд. Вообще, язык SQL очень хорошо стандартизирован и, казалось бы, должен «практически одинаково» работать на всех СУБД, которые заявляют совместимость со стандартом SQL-XX (здесь XX – это год выпуска стандарта). В реальной жизни все совсем иначе, разницу диалектов SQL можно (шутливо) проиллюстрировать следующей картинкой:
   
4. В коде вперемежку встречается «native SQL» и «Hibernate QL». Также используется QueryDSL.

Создание БД PG

Раз уж существует инструмент ora2pg, то им и попробуем воспользоваться. Написан он, похоже, аж на Perl’е, что впрочем его не портит. Были какие-то мелкие заморочки с его установкой, но в целом он вполне работает.
Было решено с его помощью преобразовать только таблицы, представления же создать руками. Пакетов и процедур — минимум, их тоже руками.
В целом инструмент отработал вполне достойно, выдав рабочий скрипт создания таблиц. Из минусов — все первичные ключи он мне сделал как numeric(22), пришлось переделывать на int8 (он же bigint, примерно он же bigserial).
И вот здесь возникает первая большая сложность. Стоит крайне пристально обратить внимание на типы полей. Лучше даже вообще составить таблицу по типам «было» — «стало» и ее придерживаться. Основные проблемы возникают с number — float — numeric. Также надо запомнить разницу в датах — в Oracle в дате есть время, в pg нет. Доступно про типы описано здесь: https://habr.com/ru/post/335716/
Важный организационный момент — всем объектом устроили ревизию, т. е. как минимум все таблицы и представления было решено снабдить комментарием, что это и зачем. Если комментария нет, то значит, что мы не знаем, что это за объект и как используется. Эта работа была сделана командой линейного развития проекта.
Таблицы создали, надо наполнять их данными. Напомню, система живая, каждый день в ней появляются данные, основных потоков три — интеграционный, расчетный и пользовательский ввод.
Здесь посчитали важным реализовать репликацию ora → pg, чтобы в каждый момент времени БД pg была максимально близка к БД ora (максимально — значит каждый день). Так родился модуль репликации.

Настройка параметров производительности СУБД

Вообще говоря, все достаточно неплохо работает и из коробки, но некоторые параметры все-таки стоит «подкрутить».
Коллеги посоветовали ссылку на такой вот симпатичный настройщик параметров:
https://pgtune.leopard.in.ua/#/ (PGTune — calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration).
Будем пробовать, экспериментировать.

Про кластер

В Oracle у нас было два сервера БД (отказоустойчивый кластер), причем в этой СУБД такой режим работы доступен «из коробки». PG в этом плане, с одной стороны встроенных таких средств не имеет, с другой – более свободен в выборе имеющихся решений, написанных для него. По настройке кластера PG мы обратились за компетенциями к одной из наших команд, подключив их к проекту. В итоге родилось следующее решение (кластер + решение по архивированию):

Модуль репликации

Итак, решили делать модуль репликации. Есть разные подходы и решения, например, SymmetricDS. Но мы им еще не пользовались, а решение хотелось проверенное и то, которое нас точно приведет к успеху. И в этот раз решили сделать его руками. Чтобы точно знать, как он работает и при необходимости дорабатывать его. В итоге он получился простым, но стабильным (также думаем опубликовать – вдруг кому-нибудь еще поможет).

Метаданные модуля репликации

Для начала о физическом соединении двух разных БД. По опыту работы мы больше «топим» за решения с использованием ETL инструментов, которых много (и многие мы знаем и умеем) — Apache Airflow, NiFi, Talend, DataStage, Informatica. Но в данном случае, так как репликация в проекте носит временный характер — решили делать максимально просто, через расширение Oracle FDW. Это расширение позволяет напрямую из БД pg соединяться с БД ora. Что ж, начнем пробовать. В стандартной поставке pg этого расширения нет, будем ставить дополнительно. Дополнительно — это «по-взрослому», компилируя из исходников, все как принято в мире linux.
Вот только досада — не поставилось. Немного поковыряв (на это ушел день) исходники расширения, установить все же получились, но как говорится «осадок остался», поэтому я взял и создал тикет в гитхабе автору расширения. Не сильно однако, веря в успех, так как это звучало как «помогите скомпилировать расширение на российской PostgresPro, на российской же операционке RedOS» (как казалось, вряд ли они все это видели), однако же разработчик отреагировал молниеносно и за день выпустил новую версию, которая устанавливалась без проблем. Я так и не понял, что это было — то ли мы побежали впереди паровоза и Oracle FDW на 13м pg еще не собирали, то ли … Но работает, и хорошо (как оказалось отлично работает).
Репликатор. Решили так — запускать каждый день ночью, реплицируем двумя принципами:

1. Интеграционный слой к нам приезжает «наборами» данных, каждый набор данных помечен своем идентификатором (shapshot_id). Т.е. мы имеем дело только со вставкой данных. Логику построили так — если на источнике появился новый снэпшот — перельем его к нам.
2. Все другие слои решили переливать полностью таблицами целиком. На больших таблицах (если встретим) хотели подумать дополнительно (как определять дельту), но в итоге не потребовалось.
   Технически репликатор — это несколько функций, которые «ходят» по таблицам «своих» метаданных и обрабатывают их. В метаданных указаны признаки, как и что реплицировать (вплоть до таблиц, шаблонов insert текстов запросов).
   Репликатор отрабатывает за 2 часа, пишет логи, проверяет количества записей «было» / «стало».
   Подождите, а как мы будем запускать репликатор? В pg (нашей редакции) нет встроенного менеджера джобов.
   На помощь приходит расширение pgCron. Функциональность полностью нас устроила, единственные проблемы в настройке были связаны с именованием хоста сервера в параметрах старта джобов (то ли localhost ему не понравилось, то ли наоборот явное указание dns-имени — но поэкспериментировав, все получилось).

Доработка приложений

Вот мы и добрались до доработки приложений. Для начала давайте разберемся, где в приложениях мира java / spring живут sql запросы.

1. В объектах repository. Как было написано выше, запросы бывают «native» и, назовем в рифму, «hibernative». Считаем, что первые надо дорабатывать, вторые скорее всего нет (оказалось, что все равно нужно);
2. Явно в коде других модулей. Обычно такого не бывает, но иногда встречается;
3. QueryDSL. Да, он у нас используется. Здесь история не про чистые SQL запросы, а про особенности и типы данных, там встречаются OracleTemplate и видимо связанные с этим особенности;
4. Модуль интеграции. Модуль интеграции имеет свой xml настроечный файл, где прописаны все запросы извлечения данных. Извлечение у нас особо не меняется, но запросы вставки надо пересмотреть.
5. Модуль отчетности. У него также есть настроечный файл yml где прописаны SQL скрипты формирования отчетов. Это, наверное самый большой блок, так как запросы здесь оказались максимально сложными для преобразования.
   В итоге приложения дорабатываем следующим образом:
   5.1. При старте приложение определяет — оно запущено под ora или под pg? Сделали это просто анализируя jdbc строку подключения. Повезло, что все настроечные и конфигурационные файлы — в философии приложения это не часть приложения, а конфигурация. И при старте приложения просто указывается, где лежат конфигурационные файлы для данного инстанса.
   5.2. Максимум запросов стараемся делать переносимыми между базами. Там где невозможно, то разводим код явно (if (ora) { ora code } else { pg code });

Проблемы
Куда без них.
Наверное, многих будет интересовать, какие проблемы придётся решать кроме тех, которые уже обозначены (чуть ранее про типы, про установку расширений).
Первой и самой очевидной стала проблема со Spring Boot 1.5. По каким то неведомым причинам он отказался подставлять имя схемы в запросы (которые сам генерирует, не native). А так как обращения у нас идут к разным схемам, это было критично. Гугл особо не помог, но одно решение рассмотрели — использовать кастомный класс NamingStrategy, в котором учесть эти нюансы. Решение бы сработало, но решили, что это костыль и решили переходить на Spring Boot 2.
Запуск приложений у нас оформлен как сервисы (systemctl), почему то под RHEL запуск приложения работает строкой «ExecStart=/path/to/spring_boot_based.jar», тогда как под RedOS заработало только если указать «ExecStart=/bin/bash /path/to/spring_boot_based.jar».
В pg есть свой «модный» преобразователь типов — двойное двоеточие, например 1.23::int. Но вот эти двоеточия не любит hibernate, который начинает думать, что это параметры запроса. Поэтому — только старый добрый cast(x as integer).
Также pg намного (очень намного) более строго относится к типам данных. И есть еще одна проблема, которая называется «null в параметрах процедур». Т.е. если у нас в Repository присутствует запрос «select * from some_function(parameters)», то при попытке ее вызова pg кроме названия проверяет перечень и типы переданных параметров (в pg функции могут быть перегруженными), после чего вызывает нужную. Null его ставит в тупик, так как он определяет его как unknown. Возможно, тут есть какой-то workaround, но сходу мы его не нашли.
Квотированные идентификаторы
SQL язык старый, раньше было «не модно» различать большие и маленькие буквы, так что он изначально родился регистронезависимым. Потом кто-то придумал, что было бы неплохо дать возможность называть колонки не техническими именами, а хоть как — хоть по-русски, хоть с пробелами. Вот так и возникли квотированные (т. е. в кавычках) идентификаторы. Сколько ж они привносят проблем…
Так вот, если идентификатор не квотирован, то он регистронезависим. Если квотирован, то должен писаться в точности.
В Oracle — по умолчанию все «воспринимается» большими буквами. В pg наоборот — маленькими. Что здесь было у нас — модуль отчетности (о нем ниже) был настроен на точное совпадение идентификаторов колонок, поэтому во всех запросах они были квотированы. Пришлось переделывать, учить модуль, убирать квотирование. Максимум проблем тут с использованием «зарезервированных» слов в названиях колонок (да, это плохо, но это было «на старте»). Например мы пишем table.some_field. Тут все хорошо. А если table.type? Тут плохо. Слово type зарезервировано и движок БД, например во view переделает это в table."TYPE" (или в table."type" ?). А это уже — квотированный идентификатор. Поэкспериментируйте, это интересно.

Модуль отчетности

Фактически, основное мерило качества интеграции — идентичность данных в отчетах. Скрипты отчетов у нас оказались сложными, этому будет посвящен весь следующий раздел. Здесь было принято следующее решение — дорабатываем максимум скриптов, которые возможно, в «универсальный вид», чтобы они запускались на обоих базах. Затем убеждаемся, что «новые запросы» выдают идентичный результат (равный старому) и на oracle и на pg. Выводим эти запросы на прод oracle — чтобы потом было легче при окончательной миграции.
А как мы будем сравнивать данные отчетов, полученных из двух баз данных? Формировать отчеты стандартным способом (Excel), после чего сравнивать данные отчетов другим каким-либо способом?… Можно, но сложновато. А если много итераций сверки — то еще более сложновато. Нашли способ проще — создали «слепки» отчетов на обоих сторонах (create table as select). Можно и не создавать, а делать представления. Перелили оракловую таблицу в PG через fdw (можно и не переливать, а делать «на ходу»). А дальше дело техники — select from таблица1 except select from таблица2. Должна получиться пустота, если множества одинаковы. Если будете этот трюк делать в Oracle — вместо except напишите minus.

Доработка особенностей SQL запросов

Вот мы и добрались до самой главной боли. Вроде бы SQL — язык стандартизированный, не должен сильно зависеть от конкретной СУБД «и все такое». Но мы взрослые люди, понимаем, что это неправда. Обращаю внимание, что нижесказанное приводится через призму того, что мы хотим сделать универсальные запросы для двух БД (т. е. работающие и там и там).
Начнем с простого.
Правило, которые надо запомнить на всю жизнь: в Oracle null + some_text = some_text. Практически во всех других базах = null. Oracle периодически за это троллят:
Так было в версии 7, заметьте фразу — "в следующей версии Oracle7":

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. It is recommended that you use VARCHAR2 rather than VARCHAR. In a future version of Oracle7, VARCHAR might be a separate datatype used for variable length character strings compared with different comparison semantics.

И, более чем 20 лет спустя, в релизе 19c:

Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.

С этим связан любопытный трюк, как нам безошибочно определить в какой базе мы находимся, изначально не зная в какой (при условии установленного в PG расширения orafce):

select coalesce(cast(null||'ora' as varchar(100)),'pg') db_type from dual

nvl — просто меняем на coalesce не думая, все работает.
decode — немного ворчим о многословности, но все же переделываем в case.
Чуть сложнее. sysdate. Помним, что у Oracle — время есть, в pg — нет. Поэтому в зависимости от контекста либо current_timestamp, либо current_date.
rownum. Очень любят приложения на spring хоть какой-нибудь ключ. Часто делают "ровнум" — так как это дешево и сердито. Чем может ответить pg?

row_number() over ()

А вот Oracle не пустит вам over с пустыми скобками, так что нужно добавлять order by, при этом выбирать правильную сортировку, которая не приведет к тормозам запроса.
А если у нас в запросах «where rownum < X». Ответ pg: limit. Но в Oracle не сработает. Компромисс для обоих баз: fetch next X rows only. Минус компромисса — не будет работать в подзапросах (rownum работает, limit скорее всего тоже нет).
Update. Апдейт «не любит указание алиса при присвоении». Т.е. update some_table t set t.some_field = … Oracle нормально, PG нет, убираем алиасы. Связано это с тем, что скорее всего поле с алисом воспринимается как вычисленное значение и его можно использовать в правой части, но при присваивании — PG настаивает на голом поле. Не забываем, что у PG есть свой синтаксис «update from join», следует всегда использовать его — иначе будут проблемы с производительностью.
Именованные подзапросы. PG не любит безымянные подзапросы, например select * from (select …). Нужно «алиасить» подзапрос. Но where x in (select …) вполне нормально, никакой алиас не нужен.
Group by 1,2,3. Это не на тему миграции, просто отмечу — это прекрасная фича PG, которой нет в Oracle. Некоторые базы тоже ее реализуют (firebird например). Это мегаудобно.
Форматирование числовых значений. Что у нас форматированием числовых значений? Оно есть, но оно разное и там, и там. Но нам по идее немного надо — вывести число с двумя знаками после запятой, причем если число целое, то вывести без знаков. Это у нас поле «пропускная способность», т. е. Мы ее хотим вывести, например «2 Мбит/с». Получилось вот так:

coalesce(to_char(round(cast(some_field as numeric(38, 8)), 2))

Разница только в том, что в Oracle разделитель — запятая, в PG — точка. Но в данном случае приемлемо (возможно, через nls это тоже можно дорешить).
Последовательности (sequence).
А вот тут нас ждало разочарование. Синтаксис различный и к единому его привести не удалось. Ладно хоть не так часто встречается явный вызов сиквенсов.
Синтаксис

Ora: select sequence.nextval from dual
Pg: select nextval(‘sequence’)

Сложные функции

Наши запросы пестрят сложными функциями работы с датами — это и извлечения номера дня в году из даты, и определение разницы между двумя датами. Изначально мы приуныли и подумали, что нужно написать просто аналоги оракловых функций, заменив все в запросах ими (так как синтаксис этих функций сильно различен в этих СУБД). А в оракле, если потребуется, сделать такие же «заглушки», вызывающие стандартные функции.
Даже начали это делать, но зашли в тупик. Посмотрите, например, как «изящно» в оракле работает функция months_between:

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Реализовывать такие функции самостоятельно — можно считать это отдельным проектом.
Вовремя попалось на глаза расширение orafce — «Oracle's compatibility functions and packages», «This module allows use a well known Oracle's functions and packages inside PostgreSQL». Можно даже сказать, что оно нас в какой то мере и спасло.
Не обошлось без ложки дегтя — 100% совместимости все равно нет, мы же помним, что pg более строг в типизации? Какие-то из этих функций в Oracle принимают на вход не только дату, а еще и timestamp, в pg такая фишка не проходит. Тем не менее это расширение стало для нас must have. Оно даже табличку dual эмулирует.
Идем дальше, любопытное — greatest и least. Вляпались, читаем у PG:

Заметьте, что функции GREATEST и LEAST не описаны в стандарте SQL, но часто реализуются в СУБД как расширения. В некоторых других СУБД они могут возвращать NULL, когда не все, а любой из аргументов равен NULL.

Очень понимаем, какие такие «некоторые» СУБД вы имели ввиду.

Великий и ужасный Number
Самый лучший тип в Oracle. Если не знаешь формат и тип числа — «лепи» всегда number. И точность сохранится и вообще все влезет.
А что нам может предложить pg? Pg может предложить numeric, в Oracle он тоже есть.
И number и numeric могут быть в трех вариантах:
numeric(x,y); numeric(x); numeric.
Почитать об этом можно в документации. Но, как пел когда-то Сергей Шнуров, есть один нюанс, он у нас в этот раз живет в PG:

без указания точности и масштаба создаёт столбец, в котором можно сохранять числовые значения любой точности и масштаба в пределах, поддерживаемых системой. В столбце этого типа входные значения не будут приводиться к какому-либо масштабу, тогда как в столбцах numeric с явно заданным масштабом значения подгоняются под этот масштаб. (Стандарт SQL утверждает, что по умолчанию должен устанавливаться масштаб 0, т. е. значения должны приводиться к целым числам. Однако мы считаем это не очень полезным. Если для вас важна переносимость, всегда указывайте точность и масштаб явно.)

Вы то, может быть, и считаете, а нам то как быть? Т.е. numeric в PG ведет себя похожим образом number’у в Oracle, но сам numeric в Oracle — ведет себя по стандарту.
Попробуйте select cast(1.2345 as numeric) from dual в разных базах, почувствуйте разницу.
Так что будем указывать явно, например numeric(38,8).
Вот еще интересно. В одном подзапросе у нас был подсчет числа строк (count), в его головном запросе был расчет:

ROUND((cast(count as numeric(38,8)) / cast(first_value(count) OVER (partition by CODE_RUS, BS ORDER BY count DESC) as numeric(38,8))) * 100, 2) AS PERCENTAGE

Изначально запрос был без приведения в numeric(38,8) (т. е. «как есть») и отрабатывал по разному: Oracle делил одно на другое как вещественные числа, PG воспринимал их как целые.
Были еще интересные моменты (так до конца и не разобрались в физике процесса), когда в одном месте Oracle округлил 87.5 к 88, а в другом уже к 87.
Засада с Boolean
Очередная проблема, которая добавила трудозатрат. Логические поля в Oracle были заведены как NUMBER(1), в Яве/Котлине/Хибернейте объявлены как Boolean. При конвертации в PG мы сделали их int2, но не тут-то было. Если Oracle спокойно «кастит» true в number(1), то PG здесь куда как более щепетилен. Особенности — вот так работает:

select count(*) from some_table where boolean_field

И так работает:

select count(*) from some_table where boolean_field = '1'

А так не работает:

select count(*) from some_table where boolean_field = 1

Вывод: меняем в PG все на явный Boolean, переделываем таблицы, связи, репликации.
Очень спасает конструкция:

ALTER TABLE table
ALTER COLUMN field1 TYPE boolean
USING CASE WHEN field1 = 0 THEN FALSE
WHEN field1 = 1 THEN TRUE
ELSE NULL
END;

Hibernate тоже добавляет странностей:

Session.createQuery("... and t.viewed = false")

Автоматически меняет false на 0. А если через параметр, то нормально:

Session.createQuery("... and t.viewed = :viewed")
q.setParameter("viewed", Boolean.FALSE);

LISTAGG
Хорошая функция, когда нужно, допустим, вывести в поле значения текста через запятую. Неприятность заключалась в том, что в pg нет возможности сделать listagg distinct, т.е. выкинуть повторяющиеся значения, пришлось делать через подзапрос.
Древовидные запросы
Да, были и они. В Oracle — синтаксис совершенно свой, ни на кого не похожий (но, на мой взгляд, очень удобный). Другие базы решают проблему через CTE (common table expression). Тем не менее, как оказалось, с использованием with recursive проблем тоже не возникло.
Самый сложный кейс
Один из запросов «в лоб» перевести не удалось. Дело в том, что у нас используется механизм версионирования записей hibernate envers. Механизм хороший, но, как мне кажется, для других задач. Чтобы получить версии на момент времени Х приходится «джойнить» все исторические таблицы с гигантской таблицей версий записей и получать ближайшую к определенной дате. Конечно, через оконные функции с rank.
Дико тормозит, даже на Oracle. Вообще, если нужна такая функциональность, она проектируется сразу и немного по-другому (вспомните, например, дату хранения итогов в 1С — это примерно на эту же тему), видимо данный отчет родился не сразу, а в процессе эволюции проекта.
Как любит PG. Вменяемой (по производительности) функциональности удалось добиться через череду временных таблиц, заполняя их последовательно, создавая индексы, обязательно запуская «analyze table».
Запрос в итоге превратился в запрос вида «select * from function()» (в Oracle это аналог pipelined функции).
Любопытный кейс
В одном из запросов была интересная просадка производительности. Вот так медленно:

With some_query as (select … from where type in (0,1))
select … from 
join  some_query as  some_query1 on … and type = 0
join  some_query as  some_query2 on … and type = 1

Если поменять на:

With 
some_query1 as (select … from where type = 0),
some_query2 as (select … from where type = 1)
select … from 
join  some_query1 on … 
join  some_query2 on … 

То нормально.
Да, пробовали и с материализацией и без (важно — обязательно прочитайте тут: https://habr.com/ru/post/440576/ )
Обработка ошибок и транзакции
Очень важная тема о нюансах обработки транзакций в Oracle и PG. Обязательно (и очень внимательно) прочитайте статью:
https://franckpachot.medium.com/postgresql-subtransactions-savepoints-and-exception-blocks-67e0fbd412af
Очень доступно и по делу.

Викторина интересных запросов, которые имели место в реальности

Двойной to_date
Это была явная ошибка, но значимого влияния не оказывала. Однако очень изящно. Попробуйте в Oracle (PG, понятно, не пропустит из-за более строгой типизации):

with dates as (
select to_date('19.02.2020', 'DD.MM.YYYY') dt from dual
union all
select to_date('21.02.2020', 'DD.MM.YYYY') dt from dual
)
select TO_DATE(dt, 'YYYY-MM-DD HH24:MI:SS') from dates

У меня результат выдал тот же день и месяц, но «нулевой» год — 0020. Хотя, вероятно, зависит от nls.

Запрос с максимумом

Пожалуй пока этот запрос в моем личном топе, я даже на собеседованиях его стал задавать.
SELECT HR.* FROM SOME_TABLE HR 
    WHERE HR.ADDRESS_ID = (SELECT AD.ID FROM SOME_TABLE2 AD 
        WHERE AD.ID = (SELECT CH.ADDRESS_B_ID FROM SOME_TABLE_3 CH
            WHERE CH.ID = 517425)) 
    AND HR.PROCESS_DATE = (SELECT MAX(HR.PROCESS_DATE) FROM SOME_TABLE)
    AND ….

Отмечу, что в pg он просто не запустился, в Oracle работает, но вопрос — что же он все-таки выдает? Предлагайте варианты в комментариях.
(для невнимательных отмечу, что в подзапросе используется max() — для поля таблицы с алиасом HR, однако в секции FROM эта таблица без алиаса, а алиас — у первоначальной, базовой таблицы).

Несколько ограничений ROWNUM
Встретился такой запрос:

select * from (
                        select ... and rownum = 1
                            union all
                        select ... and rownum = 1
                    ) ... and rownum = 1

А как я уже говорил выше — FETCH ONLY в подзарпосах не работает. Решение — а зачем здесь внутренние rownum? Разве изменится результат, если их убрать?
Ограничение по периоду
Многие массивы данных имеют месячную динамику (чаще всего счета, начисления и все что связано с ежемесячными платежами).
Выбор значений за месяц:

… trunc(bdf2.PERIOD, 'MM') = (SELECT ADD_MONTHS(trunc(SYSDATE, 'MM'), -1) FROM dual)

Лишний подзапрос к dual, отказ от использования индекса по периоду. Меняем, приводим в нужный вид (where PERIOD >= … and < ...).

Oracle всемогущий, что ж ты делаешь то?

select
        COUNT(max(some_field))
    from ...
    group by
        ....

Запрос очень изящный. Мне одному кажется, что стандарты SQL здесь нарушаются? Нет, не одному, PG обычно со мной солидарен, в нем не работает.
На самом деле Оракл посчитал просто запрос "без каунта", а сверху еще над результатом "каунт" навесил. Изящно, но надо переделать:

select count(*) from (
    select
        max(some_field)
    from ...
    group by
        ....
) q

Результат идентичен.

Вместо заключения

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