Недавно на хабре уже было опубликовано описание интеграции 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 serverActiveRecord::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 можно двумя способами:
- собрать FreeTDS из исходников;
- воспользоваться альтернативным 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;Фактически в БД происходин следующее:
- из ассоциированой с foreign_table таблицы (или не таблицы), находящейся на стороннем сервере, извлекается всё содержимое в postgres;
- затем, полученные данные фильтруются на основании условий из 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 и его богатый арсенал инструментов по оптимизации. А те, кто желает очень странного и хочет делать всё внутри БД с минимумом внешних сервисов придётся туго. Инструментарий древний, документации нет, поддержки нет,
