The EDB Blog
November 13, 2014

このブログはIbrar Ahmed氏によって共同執筆されました。

Postgresは、DBAが単一の統合ポイントとしてシステムを使用し、多くのリモートデータストアからのデータを読み込み、分析、書き込みを可能にする、Foreign Data Wrappers(FDW)という名の強力な機能を提供します。C言語で作業する開発者は、データベースによって公開されたフックを使用して新しいFDWを作成できます。そして、多くのFDWがOpen Sourceコミュニティに公開されています。FDWは本質的に異種のデータソースを橋渡しし、他のデータベースからデータを取り出してPostgresテーブルに挿入します。そこでは、ユーザーは異種のデータをPostgresデータのように扱うことができます。

Postgres FDWの実装は、2011年にPostgreSQL 9.1で導入されたSQL標準SQL/MED(SQLの外部データ管理)に基づいています。これにより、コミュニティの開発者は、読み取り能力を備えたFDWを構築できました。最近のPostgresのリリースでは、SQL/MED標準のサポートが改善されました。さらに、最も最近の更新では、PostgreSQL 9.3にFDWへの書き込みをサポートする機能が追加されました。

EnterpriseDBは最近、PostgreSQLのコアグループメンバーであるDave Page氏が、EDBのチーフアーキテクトによる最初のループ作業を基に、MySQL用の拡張FDWをopen sourceコミュニティ用に開発、リリースしました。ここにある発表を読むか、EDBのGitHubサイトでMySQL用の新しいFDWを見つけてください。

4年前、Dave Page氏は、MySQL用のFDWの実験版を開発し、リリースしました。このツールは、すぐに生産準備まで漕ぎつけました。その初期バージョンは、2年前にPGXNポイント・リリースと9.2サポートのためにアップデートされていることを除いて、ほとんど変更されていません。しかし、FDWは、MySQLサーバー上のテーブルからの読み取りのみをサポートしていました。ユーザーは、リモートMySQLサーバーにテーブルを指定するか、またはCreate Foreign TableコマンドのOPTIONS句で選択クエリーを指定することによって、外部テーブルを作成できます。外部テーブルは、その後、OPTIONS句でユーザーが指定した選択クエリーによって作成されます。

Given the expanded potential for more robust FDWs, EDB’s Ibrar Ahmad and Ahsan Hadi helped develop and release a new version of the FDW for MySQL. The new version of the FDW for MySQ works with PostgreSQL 9.3 and later releases; the older version of MySQL FDW works with PostgreSQL 9.2 and prior releases.

The new version of FDW for MySQL is essentially a rewrite of the previous FDW with a number of new enhancements, which are listed below:

Writability

The previous version was only read-only; the latest version also provides the write capability. The user can now issue insert/update and delete statements for the foreign tables using the FDW for MySQL. It uses the Postgres typecasting mechanism to provide opposite type casting between MySQL and Postgres data types.

接続プール

The latest version comes with a connection pooler that utilizes the same MySQL database connection for all the queries in the same session, as opposed to the previous version, which would open a new MySQL database connection for every query. This is a performance enhancement.

Push-Down

The push-down feature means pushing down a part of the foreign table query to the foreign server. This improves the performance of the query because some of the heavy processing can be done on the foreign server, and the results will be sent back to the originating server. Right now, Postgres only provides the framework for pushing down the where condition and table columns. There are more clauses of the query that can be pushed down to the foreign server for improving performance. These include query constructs like aggregates, functions, expression, joins, order-by clause, etc.

Where clause

The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign table will be executed on the foreign server, hence there will be fewer rows to bring across to Postgres. This is a performance feature.

Column push-down

The previous version was fetching all the columns from the target foreign table. The latest version does the column push-down and only brings back the columns that are part of the select target list. This is a performance feature.

Prepared Statement

The select queries are now using prepared statements instead of simple query protocol.

Options clause

The options clause of the CREATE FOREIGN table has been adjusted to be more similar to other FDW’s. As mentioned previously, the first version of the FDW for MySQL only allowed the table specified in the options clause. We have changed the options clause in the new FDW for MySQL wrapper; as you can see in the example below, it now accepts both database name and table name. This is compatible with other FDWs, like those for Postgres, Oracle and MongoDB.

MySQL_FDW in Action

-- load extension first time after install

CREATE EXTENSION mysql_fdw;

-- create server object

CREATE SERVER mysql_server

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

-- ユーザーマッピングを作成する 

CREATE USER MAPPING FOR postgres

SERVER mysql_server

OPTIONS (username 'foo', password 'bar');

-- create foreign table

CREATE FOREIGN TABLE warehouse(

warehouse_id int,

warehouse_name text,

warehouse_created datetime)

SERVER mysql_server

OPTIONS (dbname 'db', table_name 'warehouse');

-- insert new rows in table

INSERT INTO warehouse values (1, 'UPS', sysdate());

INSERT INTO warehouse values (2, 'TV', sysdate());

INSERT INTO warehouse values (3, 'Table', sysdate());

-- select from table

SELECT * FROM warehouse;

warehouse_id | warehouse_name | warehouse_created

--------------+----------------+--------------------

| UPS            | 29-SEP-1423:33:46

| TV             | 29-SEP-1423:34:25

| Table          | 29-SEP-1423:33:49

-- delete row from table

DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table

UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table

EXPLAIN SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

QUERY PLAN

-----------------------------------------------------------------------------------

Limit  (cost=10.00..11.00 rows=1 width=36)

->  Foreign Scan on warehouse 

(cost=10.00..13.00 rows=3 width=36)

Local server startup cost: 10

Remote query: SELECT warehouse_id,

warehouse_name FROM db.warehouse WHERE ((warehouse_name like 'TV'))

Planning time: 0.564 ms

(5 )

The development and release to the open source community of this updated FDW for MySQL is part of EDB’s investment in the foreign data wrapper technology as a mechanism to support database federation. FDWs give Postgres the ability to collect data from multiple disparate database types and allow it to be used collectively for business intelligence, data analysis and other purposes. A few months back, EDB released an enhanced version of the FDW for MongoDB and EDB announced plans to develop and release an FDW for Hadoop FDW.

Please visit our site or contact us to learn more about the tools and software enhancements EDB has developed for Postgres. 

Ahsan Hadi is Senior Director, Product Development, EnterpriseDB.

Ibrar Ahmed is a Technical Architect for EnterpriseDB and a PostgreSQL Hacker.