Интеграция PostgreSQL с MS SQL Server для тех, кто желает побыстрее и поглубже

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

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

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

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

• создание расширения (extension) в PG: офф.документация ^[3];
• создание сервера: офф.документация ^[4], пример из tds_fdw ^[5];
• создание отображения пользователей (user mapping): офф.документация ^[6], пример из tds_fdw ^[7];
• создание внешней таблицы (foreign table): офф.документация ^[8], пример из tds_fdw ^[9].

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

sudo make USE_PGXS=1 install

Спасите котиков ^[11], соберите 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'е ^[12]), хотя доподлинно не известно: у меня ли руки не оттуда растут или баг довольно хитрый и проявляется только при определённом стечении обстоятельств.

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

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

Почему же поддержка этой версии так важна? Лично для меня это важно из-за необходимости работать с MSSQL 2012. Если коротко: в Ruby on Rails для подключения к MSSQL используется библиотека activerecord-sqlserver-adapter ^[15], которая, в свою очередь, использует tiny_tds ^[16], который использует FreeTDS ^[17], который умеет общаться с 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 ^[18] и 0.91-6.1build1 ^[19] соответственно. И получить более новую версию FreeTDS можно двумя способами:

1. собрать FreeTDS из исходников ^[20];
2. воспользоваться альтернативным PPA ^[21] с версией 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, то под неё можно легко найти ^[22] 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. Почему-то описание сего механизма не востребовано в русскоязычных ресурсах (либо я не знаком с его правильным переводом, а трицепсовый жим вниз на блоке ^[23] это явно не из той оперы). Поэтому хочется коротко рассказать о нём.

В простейшем случае, когда мы в 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 ^[24] pushdown почти бесполезен, а вот для postgres_fdw ^[25] или 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 написать здесь ^[26], а детали о `debug flags` лежат вот тут ^[27].

Materialized view

Материализованное представление ^[28] (далее 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 ^[29], GIN и GiST ^[30] и др.), для них становится доступна статистика ^[31], можно увидеть детали о плане выполнения запроса и ещё много чего приятного из PG.

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

REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;

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

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

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

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

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

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

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

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

Заключение

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

Автор: Loriowar

Источник ^[38]