New OSS Tool Links Postgres and MySQL

This blog was co-written by Ibrar Ahmed.

Postgres provides a powerful feature called Foreign Data Wrappers (FDW), which enables DBAs to use the system as a single integration point to read, analyze and write to data from many remote data stores. Developers working in C can create new FDWs using the hooks exposed by the database, and many FDWs have been published in the Open Source community. FDWs essentially bridge disparate data sources, pulling data from other databases and inserting it into Postgres tables where users can work with it as if it were native Postgres data.

The implementation for Postgres FDWs is based on the SQL standard SQL/MED (SQL Management of External Data), which was introduced in 2011 in PostgreSQL 9.1. This enabled developers in the community to build FDWs with read capability. Support for the SQL/MED standard was improved upon in recent Postgres releases. Most recently, PostgreSQL 9.3 added the ability for FDWs to support write capabilities.

EnterpriseDB recently developed and released to the open source community an enhanced FDW for MySQL, building on work done with a first iteration by Dave Page, PostgreSQL core group member and EDB chief architect. Read the announcement here or find the new FDW for MySQL on EDB’s GitHub site.

Four years ago, Dave Page helped develop and release an experimental version of an FDW for MySQL. The tool quickly became production-ready. That initial version has undergone few changes, with the exception of being updated for PGXN point release and 9.2 support two years ago. But that FDW only supported reading from a table residing on the MySQL server. The user could create foreign tables by specifying a table in the remote MySQL server, or by providing a select query in the OPTIONS clause of the Create Foreign Table command. The foreign table would then be created by the select query specified by the user in the OPTIONS clause.

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.

Connection Pooling

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 

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 rows)

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.

Autor Info: 
Existing Author
Existing Author: 
New OSS Tool Links Postgres and MySQL
By Ahsan Hadi, Nov 13, 2014

This blog was co-written by Ibrar Ahmed.

Postgres provides a powerful feature called Foreign Data Wrappers (FDW), which enables DBAs to use the system as a single integration point to read, analyze and write to data from many remote data stores. Developers working in C can create new FDWs using the hooks exposed by the database, and many FDWs have been published in the Open Source community. FDWs essentially bridge disparate data sources, pulling data from other databases and inserting it into Postgres tables where users can work with it as if it were native Postgres data.

The implementation for Postgres FDWs is based on the SQL standard SQL/MED (SQL Management of External Data), which was introduced in 2011 in PostgreSQL 9.1. This enabled developers in the community to build FDWs with read capability. Support for the SQL/MED standard was improved upon in recent Postgres releases. Most recently, PostgreSQL 9.3 added the ability for FDWs to support write capabilities.

EnterpriseDB recently developed and released to the open source community an enhanced FDW for MySQL, building on work done with a first iteration by Dave Page, PostgreSQL core group member and EDB chief architect. Read the announcement here or find the new FDW for MySQL on EDB’s GitHub site.

Four years ago, Dave Page helped develop and release an experimental version of an FDW for MySQL. The tool quickly became production-ready. That initial version has undergone few changes, with the exception of being updated for PGXN point release and 9.2 support two years ago. But that FDW only supported reading from a table residing on the MySQL server. The user could create foreign tables by specifying a table in the remote MySQL server, or by providing a select query in the OPTIONS clause of the Create Foreign Table command. The foreign table would then be created by the select query specified by the user in the OPTIONS clause.

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.

Connection Pooling

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 

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 rows)

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.

Choose a Leader!

Learn why Gartner chose EnterpriseDB - an open source relational database company - to be a Leader alongside the market’s largest commercial giants.

Request report >>

Follow EnterpriseDB

Stay informed on product announcements, event information, industry news, and more!

Subscribe to the EDB blog 

Follow us on Twitter

Webcast

Postgres in Production - Enterprise Best Practices

View recommendations for deploying Postgres applications in production and scaling for growth. Learn how to resolve issues many enterprises face!

View webcast...

Webinar: Using Postgres FDWs

Learn how to use FDWs for MySQL, MondoDB and Hadoop during this live webinar on April 29th.

Learn more and register...