PostgreSQL integration with MS SQL Server for those who want faster and deeper

Recently, a description of the integration of PostgreSQL and MSSQL has already been published on the hub But, details were categorically lacking. Therefore, the objectives of this publication are as follows:

• expand and deepen publicly available information on FDW for MSSQL called tds_fdw : talk about the difference in major versions and describe basic compatibility problems;
• talk about the possibilities of optimizing queries using external tables;
• touch on caching external data in materialized representations;
• say a few words about exotic approaches to the integration of PostgreSQL and MSSQL.

Install and configure TDS FDW

The guys from PostgresPro have already said enough about this process, I won’t repeat myself. I will leave only a few links to the official PostgreSQL documentation and examples from tds_fdw:

• creation of an extension (extension) in PG: off.documentation ;
• server creation: off.documentation , example from tds_fdw ;
• creation of mapping of users (user mapping): off-documentation , example from tds_fdw ;
• creation of an external table (foreign table): off.documentation , an example from tds_fdw .

And one more thing: please do not do as indicated in the installation instructions for tds_fdw

sudo make USE_PGXS=1 install

Save the cats , collect the deb package and enjoy life:

sudo USE_PGXS=1 checkinstall

Differences between major versions of TDS FDW

Currently, there are two current versions of FDW's: stable 1.0.7 and 2.0.0-alpha, which, in essence, is a master branch and in which all the fun happens. Here is a short list of their differences:

• 2.0.0 finally introduced pushdown support for conditions in the WHERE clause that relate directly to the external table; however, it still does not work well when using the query option inside a foreign_table declaration;
• Support for tds 7.4 has appeared (I will describe below why this is necessary and important);
• There are some problems with the work of DISTINCT on an external table ( here is the issue on GitHub ), although it is not known for certain whether my hands are growing from there or is the bug quite tricky and appears only under a certain set of circumstances.

Pitfalls of compatibility

Until recently, tds_fdw did not work with tds version above 7.3. But during the writing of this article, support for version 7.4 had to be found . So now, starting with the 3a803c commit , tds_fdw supports all current versions of tds.

Why is support for this version so important? For me personally, this is important because of the need to work with MSSQL 2012. In short: in Ruby on Rails, the activerecord-sqlserver-adapter library is used to connect to MSSQL , which, in turn, uses tiny_tds , which uses FreeTDSthat can communicate with MSSQL. The trouble is that for RoR 3 and the corresponding major versions of libraries, the use of the tds 7.1 version is nailed and you can change it through the config only in the 4+ version. At the same time, version 7.1 works great with MSSQL 2008, but when communicating with MSSQL 2012, the following errors appear:

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

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

TinyTds::Error: Adaptive Server connection failed

And the like.

I wanted to avoid them by switching to using FDW, since updating RoR is categorically longer and more expensive. But tds_fdw did not support the desired version and had to do something about it.

As for the mistakes, they all appear randomly and grow due to the same place; make some "variety" in the application, forcing it to fall off in random places at random time. All this outrage is treated only using the correct version of tds. For MSSQL 2012, this is tds 7.4.

Here is the first ambush: support for tds 7.4 is implemented in FreeTDS starting from version 0.95. But out of the box in Ubuntu 14.04 and 16.04 go versions 0.91-5 and 0.91-6.1build1respectively. And you can get a newer version of FreeTDS in two ways:

1. FreeTDS collect from source ;
2. Use an alternative PPA with FreeTDS 1.00.

In the second case, there is one caveat: in the specified repository there is a package only for Ubuntu 14.04 (which is trusty). For 16.04 (which is xenial), there is nothing there. But, in general, nothing fatal, and if you fix /etc/apt/sources.list.d/jamiewillis-freetds-trusty.list in 16.04 to something like this:

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

You can install the package in the latest Ubuntu (and yes, it works without problems).

If you have CentOS, then under it you can easily find FreeTDS up to version 0.95 inclusive. Everything older will have to be collected from source.

Workaround for compatibility issue

If the error number 20017 and its derivatives are very annoying, and there is no possibility to use the required version of tds, then you can handle the exception thrown by PostgreSQL and restart the block / method / etc that accesses MSSQL via FDW. In my case for a RoR application, it looked like this:

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

At first, it saves, but it is categorically not suitable for a long-term solution.

A bit about pushdown and how FDW works “on the fingers”

Before moving on to questions of query optimization to an external database, I would like to say a few words about pushdown. For some reason, the description of this mechanism is not in demand in Russian-language resources (or I am not familiar with its correct translation, and the triceps bench press on the block is clearly not from that opera). Therefore, I want to briefly talk about it.

In the simplest case, when we in PG execute a query of the form:

SELECT column_name FROM foreign_table WHERE column_id = 42;

In fact, the following occurs in the database:

1. from the table associated with foreign_table (or not a table) located on a third-party server, all content in postgres is extracted;
2. then, the received data is filtered based on the conditions from WHERE.

Not a very efficient scheme, especially if you want to get only one from a table with several million rows. And here comes pushdown. This mechanism allows us to reduce the number of lines that we receive from a remote server. This is done by constructing a query to an external database taking into account what we want on the PG side, that is, taking into account what is indicated in WHERE, JOIN, ORDER, etc. In other words, FDW parses the original query in PotsgreSQL, choose from it, that the remote data warehouse can understand and assemble a new request, according to these conditions. The obvious consequence follows: pushdown is not applicable for all FDWs (for example, pushdown is almost useless for file_fdw , but quite the opposite for postgres_fdw or tds_fdw ).

Bottom line: pushdown is cool, it allows you to use external data storage mechanisms, reduces the amount of data circulating between PG and external storage, thereby speeding up query execution, but at the same time, it is a separate mechanism, so it needs to be implemented, supported, and this quite a non-trivial task.

Request Acceleration

We figured out the installation, configuration and hardware. Now, let's begin to describe how to quickly extract data from MSSQL.

Pushdown

This approach is useful in the case of simple queries that are not burdened with various JOINs and other SQL tricks. The latest version of tds_fdw (currently 2.0.0-alpha) introduced support for the simple pushdown for WHERE.

For example, consider the simple_table table from the MSSQL database. There are two fields in this table: id and data. The definition of the external table for it will be as follows:

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');

In this case, the first column has the same name in PostgreSQL and in MSSQL: id. The second column has different names in PG and in MSSQL, so the column_name option is needed here . This parameter explicitly sets the mapping of columns from PostgreSQL to columns in MSSQL. Also, at the end, the match_column_name parameter is specified , which is responsible for implicit mapping of column names by names, that is, thanks to it, the id column is mapped.

That's it, now if you execute the request:

SELECT custom_data FROM mssql_table WHERE id = 42;

FDW should process the condition specified in WHERE and collect the correct query in MSSQL. For example, this:

SELECT data FROM simple_table WHERE id = 42;

In the case of tds_fdw version 1.0.7 and below, the query in MSSQL will be different:

SELECT id, data FROM simple_table;

I repeat once again: pushdown, at the moment, works only for WHERE; for JOIN, ORDER and other functions like MAX, LOWER, etc. it will not take off.

And one more thing: how do you know which actually query was executed on the MSSQL side? When using FDW for, for example, MySQL, the following line appears in explain:

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

And it’s convenient. In tds_fdw this is not yet and you need to go a longer way through the FreeTDS logs. By default, logs are disabled in FreeTDS, but this can be easily fixed by digging into /etc/freetds/freetds.conf . Here you can find these lines:

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

Which need to remove the semicolon at the beginning.

Now for any query in MSSQL from PG, FreeTDS will log everything it can. This will slow down the execution of all external requests and can spawn a bunch of logs (in my case, the usual SELECT made a log at ~ 300MB, and JOIN barely narrowed at ~ 1.5GB). But then in the logs it will be seen what actually performed in MSSQL. In addition, the volume of logs can be reduced by playing with the `debug flags`. Write more about logging in FreeTDS here , and details about `debug flags` are here .

Materialized view

A materialized view (hereinafter MV) is a regular view + a data table. This approach will help in case of complex queries with joins of external and internal tables, with functions, preference and courtesans.

The profit from MV is the following: it is a “native” object for PG, that is, MV interacts wonderfully with the rest of PostgreSQL and it can be indexed and analyzed regardless of the data source that populated it. Cons too: MV needs to be updated. You can update by internal triggers, by external events, you can completely recreate, and so on. But, in any case, MV causes PG to lag behind the original data source.

For the above external table MV can be created as follows:

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

Now all the data from MSSQL is in PostgreSQL, which means that you can index it as you like ( B-tree , GIN and GiST , etc.), statistics are available for them , you can see details about the query execution plan and much more pleasant things from PG.

You can update MV through the standard INCERT / UPDATE / DELETE commands, or simply recreate all the contents using

REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;

The CONCURRENTLY option allows you to update MVs without blocking competing read requests, but it requires more time and resources. Also, in order to be able to use CONCURRENTLY, the target MV must satisfy some requirements. They can be found on the corresponding documentation page .

Exotic approach

Honestly, the devil knows whether this approach can take off, maybe the respected public will tell something interesting on this subject. In any case, I think what needs to be said about it, since on the profile resources most of the questions on the integration of the two databases are answered “use FDW” and no variety is expected, even if you want something strange.

So, when it may be needed: in case all of the above options did not help due to various limitations. For instance:

• indecently large amount of external database and the impossibility of its cloning in PG;
• strict performance requirements and the availability of an optimal query to an external database;
• the desire to execute a parameterized query, that is, an analogue of the query option for FDW, only with a dynamic parameter, for example, you want to use full-text search on the MSSQL side through the CONTAINS function ;
• something else unusual.

What to use: dbi-link or dblink-tds . These are analogs of dblink, but with support for several DBMSs: PostgreSQL, MySQL, MSSQL Server and Oracle in the case of dbi-link and just TDS in the case of dblink-tds.

As the mechanics of work are seen: as a kind of highly specialized analogue of FDW in the form of a PG function that collects the necessary query inside itself based on the arguments passed, executes it in an external database through the above tools, receives data, processes it and returns it to PG as a pipeline function . That is, hypothetically, it is possible to fulfill exactly the request that you want and present its result in a form digestible for subsequent processing in PG.

All of the above are pure theoretical considerations. If you have real experience using these or similar tools, please share your knowledge with the world.

Conclusion

At the moment, there is the only simple and working solution for joining PostgreSQL and MSSQL. This is tds_fdw . It has many shortcomings, but the project is developing, bugs are fixed, features are rolled out and it's great. Therefore, tds_fdw can solve most of the problems associated with retrieving data from MSSQL via PG. For those who want to quickly, optimally and with courtesans, PostgreSQL and its rich arsenal of optimization tools will help. And those who want something very strange and want to do everything inside the database with a minimum of external services will have to be tight. The toolkit is ancient, there is no documentation, there is no support, it is populated by robots, and apart from reading the source, nothing will help.