Недавно на хабре уже было опубликовано описание интеграции PostgreSQL и MSSQL. Но, деталей там категорически не хватало. Посему, цели сей публикации следующие:

• расширить и углубить общедоступную информацию о FDW для MSSQL называемом tds_fdw: рассказать о разнице в мажорных версиях и описать базовые проблемы совместимости;
• рассказать о возможностях оптимизации запросов, использующих внешние таблицы;
• затронуть тему кэширования внешних данных в материализованных представлениях;
• сказать пару слов об экзотических подходах к интеграции PostgreSQL и MSSQL.

Установка и настройка TDS FDW

Ребята из PostgresPro уже достаточно сказали об этом процессе, повторяться не буду. Оставлю лишь несколько ссылок на официальную документацию PostgreSQL и на примеры из tds_fdw:

• создание расширения (extension) в PG: офф.документация;
• создание сервера: офф.документация, пример из tds_fdw;
• создание отображения пользователей (user mapping): офф.документация, пример из tds_fdw;
• создание внешней таблицы (foreign table): офф.документация, пример из tds_fdw.

И ещё один момент: пожалуйста, не делайте как указано в инструкции по установке tds_fdw

sudo make USE_PGXS=1 install

Спасите котиков, соберите deb-пакет и радуйтесь жизни:

sudo USE_PGXS=1 checkinstall

Различия между мажорными версиями TDS FDW

На текущий момент существует две актуальные версии FDW'шки: стабильная 1.0.7 и 2.0.0-alpha, которая, по сути, является master-веткой и в которой происходит всё самое интересное. Вот небольшой список их различий:

• в 2.0.0 наконец-то появилась поддержка pushdown для условий в блоке WHERE, относящихся непосредственно к внешней таблице; однако он пока плохо работает при использовании опции query внутри объявления foreign_table;
• появилась поддержка версии tds 7.4 (ниже опишу почему это нужно и важно);
• есть некоторые проблемы с работой DISTINCT по внешней таблице (вот issue на GitHub'е), хотя доподлинно не известно: у меня ли руки не оттуда растут или баг довольно хитрый и проявляется только при определённом стечении обстоятельств.

Подводные камни совместимости

До недавнего времени, tds_fdw не работал с версией tds выше 7.3. Но в ходе написания сей статьи поддержку версии 7.4 пришлось изыскать. Поэтому теперь, начиная с коммита 3a803c, tds_fdw поддерживает все актуальные версии tds.

Почему же поддержка этой версии так важна? Лично для меня это важно из-за необходимости работать с MSSQL 2012. Если коротко: в Ruby on Rails для подключения к MSSQL используется библиотека activerecord-sqlserver-adapter, которая, в свою очередь, использует tiny_tds, который использует FreeTDS, который умеет общаться с MSSQL. Вся беда в том, что для RoR 3 и соответствующих мажорных версий библиотек, использование версии tds 7.1 прибито гвоздями и изменять её через конфиг можно только в 4+ версии. При этом версия 7.1 замечательно работает с MSSQL 2008, но при общении с MSSQL 2012 появляются следующие ошибки:

DB-Library error: DB #: 20017, DB Msg: Unexpected EOF from the server

ActiveRecord::LostConnection: TinyTds::Error: closed connection: ...

TinyTds::Error: Adaptive Server connection failed

И им подобные.

Их хотелось избежать перейдя на использование FDW, так как обновить RoR — это категорически дольше и дороже. Но tds_fdw не поддерживал нужной версии и пришлось с этим что-то делать.

Что же касается ошибок, то все они появляются рандомно и произрастают из-за одного и того же места; вносят некоторое «разнообразие» в приложение, заставляя его отваливаться в случайных местах в случайное время. Лечится же всё это безобразие только использованием правильной версии tds. Для MSSQL 2012 это tds 7.4.

Здесь же первая засада: поддержка версии tds 7.4 реализована во FreeTDS начиная с версии 0.95. Но из коробки в Ubuntu 14.04 и 16.04 идут версии 0.91-5 и 0.91-6.1build1 соответственно. И получить более новую версию FreeTDS можно двумя способами:

1. собрать FreeTDS из исходников;
2. воспользоваться альтернативным PPA с версией FreeTDS 1.00.

Во втором случае есть один нюанс: в указанном репозитории есть пакет только для Ubuntu 14.04 (которая trusty). Для 16.04 (которая xenial), там ничего нет. Но, в целом, ничего фатального и если в 16.04 поправить /etc/apt/sources.list.d/jamiewillis-freetds-trusty.list на что-нибудь вот такое:

deb http://ppa.launchpad.net/jamiewillis/freetds/ubuntu trusty main

То можно будет ставить пакет и в последней Ubuntu (и таки да, он работает без проблем).

Если же у вас CentOS, то под неё можно легко найти FreeTDS до версии 0.95 включительно. Всё что старше придётся собирать из исходников.

Временное решение проблемы совместимости

Если ошибка под номером 20017 и её производные очень сильно докучают, а возможности заиспользовать необходимую версию tds нет, то можно обработать исключение, выбрасываемое PostgreSQL и перезапустить блок/метод/etc, обращающийся к MSSQL через FDW. В моём случае для RoR приложения это выглядело так:

def retry_mssql_operation(tries = 5)
  begin
    yield
  rescue ActiveRecord::StatementInvalid => e
    if e.message =~ /^PG::FdwUnableToCreateExecution/ && tries > 0
      tries -= 1
      retry
    else
      raise
    end
  end
end

На первое время спасает, но для долговременного решения категорически не подходит.

Немного о pushdown и о том, как работает FDW «на пальцах»

Прежде чем перейти к вопросам оптимизации запросов к внешней БД хотелось бы сказать несколько слов о pushdown. Почему-то описание сего механизма не востребовано в русскоязычных ресурсах (либо я не знаком с его правильным переводом, а трицепсовый жим вниз на блоке это явно не из той оперы). Поэтому хочется коротко рассказать о нём.

В простейшем случае, когда мы в PG выполняем запрос вида:

SELECT column_name FROM foreign_table WHERE column_id = 42;

Фактически в БД происходин следующее:

1. из ассоциированой с foreign_table таблицы (или не таблицы), находящейся на стороннем сервере, извлекается всё содержимое в postgres;
2. затем, полученные данные фильтруются на основании условий из WHERE.

Не шибко эффективная схема, особенно если из таблицы с несколькими миллионами строк хочется получить всего лишь одну. И вот здесь появляется pushdown. Сей механизм позволяет уменьшить количество строк, которые мы получаем от удалённого сервера. Делается это посредством конструирования запроса к внешней БД с учётом того, что мы хотим на стороне PG, то есть с учётом того что указано в WHERE, JOIN, ORDER и др. Иначе говоря, FDW разбирает исходный запрос в PotsgreSQL, выбрать из него то, что может понять удалённое хранилище данных и собрать новый запрос, сообразно этим условиям. Отсюда вытекает очевидное следствие: pushdown применим не для всех FDW (например, для file_fdw pushdown почти бесполезен, а вот для postgres_fdw или tds_fdw — совсем наоборот).

Итого: pushdown — это круто, он позволяет использовать механизмы внешнего хранилища данных, уменьшает объём данных, циркулирующих между PG и внешним хранилищем, тем самым ускоряя выполнение запросов, но, при этом, он является отдельным механизмом, поэтому его нужно реализовывать, поддерживать и это довольно нетривиальная задача.

Ускорение запросов

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

Pushdown

Пригодится такой подход в случае простых запросов, не обременённых различными JOIN и прочими SQL-ухищрениями. В последней версии tds_fdw (на текущий момент это 2.0.0-alpha) появилась поддержка простейшего pushdown для WHERE.

Для примера рассмотрим таблицу simple_table из БД MSSQL. В этой таблице есть два поля: id и data. Определение внешней таблицы для неё будет следующим:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    custom_data varchar OPTIONS (column_name 'data'))
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo',
             table_name 'simple_table',
             row_estimate_method 'showplan_all',
             match_column_names '1');

В данном случае, первый столбец имеет одинаковое название в PostgreSQL и в MSSQL: id. У второго столбца различные имена в PG и в MSSQL, поэтому здесь нужна опция column_name. Сей параметр явно задаёт отображение столбцов из PostgreSQL на столбцы в MSSQL. Так же, в конце указан параметр match_column_name, который отвечает за неявный мапинг названий колонок по именам, то есть, благодаря ему, мапается столбец id.

Всё, теперь если выполнить запрос:

SELECT custom_data FROM mssql_table WHERE id = 42;

FDW должен обработать условие, указанное в WHERE и собрать правильный запрос в MSSQL. Например такой:

SELECT data FROM simple_table WHERE id = 42;

В случае tds_fdw версии 1.0.7 и ниже запрос в MSSQL будет другим:

SELECT id, data FROM simple_table;

Ещё раз повторюсь: pushdown, на текущий момент, работает только для WHERE; для JOIN, ORDER и прочих функций типа MAX, LOWER и др. он не взлетит.

И ещё одно: как же узнать, какой фактически запрос выполнился на стороне MSSQL? При использовании FDW для, например, MySQL, в explain появляется вот такая строка:

Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`

И это удобно. В tds_fdw такого пока нет и нужно идти более длинным путём через логи FreeTDS. По умолчанию, во FreeTDS логи отключены, но это легко исправить покопавшись в /etc/freetds/freetds.conf. Там можно найти вот такие строки:

;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

У которых нужно убрать точку с запятой в начале.

Теперь для любого запроса в MSSQL из PG, FreeTDS будет логировать всё что сможет. Это замедлит выполнение все внешних запросов и может наплодить кучу логов (в моём случае обычный SELECT сделал лог в ~300Мб, а JOIN еле ужодился в ~1.5Гб). Но зато в логих будет видно что фактически выполнилось в MSSQL. К тому же, объём логов можно уменьшить, поигравшись с `debug flags`. Подробнее о логировании во FreeTDS написать здесь, а детали о `debug flags` лежат вот тут.

Materialized view

Материализованное представление (далее MV) — это обычное представление + таблица с данными. Сей подход поможет в случае сложных запросов с джойнами внешних и внутренних таблиц, с функциями, преферансом и куртизанками.

Профит от MV следующий: оно являются «родным» объектом для PG, то есть MV замечательно взаимодействует с остальными частями PostgreSQL и оно может быть проиндексировано и проанализировано независимо от источника данных, который его наполнил. Минусы тоже есть: MV нужно обновлять. Обновлять можно по внутренним триггерам, по внешним событиям, можно полностью пересоздавать и тд. Но, в любом случае, MV порождает отставание PG от первоисточника данных.

Для вышеописанной внешней таблицы MV можно создать следующим образом:

CREATE MATERIALIZED VIEW materialized_mssql_table AS
    SELECT id, custom_data
    FROM mssql_table;

Теперь все данные из MSSQL есть в PostgreSQL, а значит их можно индексировать как вздумается (B-tree, GIN и GiST и др.), для них становится доступна статистика, можно увидеть детали о плане выполнения запроса и ещё много чего приятного из PG.

Обновить MV можно через стандартные INCERT/UPDATE/DELETE команды, либо просто пересоздать всё содержимое при помощи

REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;

Опция CONCURRENTLY позволяет обновить MV не блокируя конкурирующие запросы на чтение, но требует больше времени и ресурсов. Так же, для возможности использования CONCURRENTLY целевое MV должно удовлетворять некоторым требованиям. Их можно найти на соответствующей странице документации.

Экзотический подход

Честно говоря, чёрт знает, может ли взлететь этот подход, быть может уважаемая публика расскажет что-нибудь интересное на этот счёт. В любом случае, считаю что нужно о нём сказать, так как на профильных ресурсах на большинство вопросов по интеграции двух БД отвечают «используйте FDW» и никакого разнообразия не предвидится, даже если вы желаете странного.

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

• неприлично большой объём внешней БД и невозможность её клонирования в PG;
• строгие требования к быстродействию и наличие оптимального запроса во внешнюю БД;
• желание выполнять параметризованный запрос, то есть аналог опции query для FDW, только с динамическим параметром, например хочется использовать полнотекстовый поиск на стороне MSSQL через функцию CONTAINS;
• ещё что-нибудь необычное.

Чем пользоваться: dbi-link или dblink-tds. Сие есть аналоги dblink'а но с поддержкой нескольких СУБД: PostgreSQL, MySQL, MSSQL Server и Oracle в случае dbi-link и просто TDS'а в случае dblink-tds.

Как видится механика работы: как некий узкоспециализированный аналог FDW в виде функции PG, которая собирает внутри себя нужный запрос исходя из переданных аргументов, выполняет его во внешней БД через вышеуказанные инструменты, получает данные, обрабатывает их и возвращает их в PG как pipeline-функция. То есть, гипотетически, можно выполнить именно тот запрос, который хочется и представить его результат в виде, удобоваримом для последующей обработки в PG.

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

Заключение

На текущий момент есть единственное простое и рабочее решение для стыковки PostgreSQL и MSSQL. Это tds_fdw. У него есть много недоработок, но проект развивается, баги чинятся, фичи выкатываются и это здорово. Поэтому tds_fdw может решить бо?льшую часть проблем, связанных с извлечением данных из MSSQL через PG. Тем же, кто хочется побыстрее, пооптимальнее и с куртизанками поможет PostgreSQL и его богатый арсенал инструментов по оптимизации. А те, кто желает очень странного и хочет делать всё внутри БД с минимумом внешних сервисов придётся туго. Инструментарий древний, документации нет, поддержки нет, населена роботами и кроме чтения исходников ничего не поможет.