Connect MS SQL Server and PostgreSQL using foreign data wrapper

Question

I used tds_fdw between MS SQL Server and PostgreSQL.

I created a foreign server and foreign tables in PostgreSql. When I select data from foreign table it also run Mssql? Or data store in PostgreSQL?

Answer 1

when you select from a foreign table the query will execute in the SQL server. but based on this issue: https://github.com/tds-fdw/tds_fdw/issues/154 you must set the row_estimate_method to 'showplan_all' in foreign table option. otherwise, the query will run twice in the SQL server.

example: I want to use postgres foreign table to create table in the SQL server:

DROP FOREIGN TABLE IF EXISTS ft_create_table;
CREATE FOREIGN TABLE ft_create_table()
SERVER sql_server
OPTIONS (row_estimate_method 'showplan_all' ,query 'CREATE TABLE [test](
    [Id] [bigint] NOT NULL);';

SELECT * FROM ft_create_table;

Answer 2

A foreign data wrapper is only a different way to query remote data. If you create a foreign table, any SELECT that accesses that table is forwarded to the foreign server (in your case SQL Server).

So the data is only stored in SQL Server.

When you select from the foreign table, the foreign data wrapper will send the SELECT statement to SQL Server. Then SQL Server will process the query and will send the data back to the Postgres server. The foreign data wrapper then hands the data back to your application (that is connected to your Postgres server). Some operations can be "pushed" down to the foreign server by the FDW so that Postgres does not need to handle them. I don't know the details of the tds_fdw implementation, but things like a WHERE clause are usually pushed to the remote server so that only the relevant data is processed on the Postgres side.

From the view point of MS SQL Server, the FDW is just another client sending SQL queries.

While MS SQL Server is processing the request, Postgres (or more correctly: the backend that is created for your Postgres connection) is idle (does nothing) and waits until MS SQL Server has finished processing.