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:
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.
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.
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.
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.
The select queries are now using prepared statements instead of simple query protocol.
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
OPTIONS (username 'foo', password 'bar');
-- create foreign table
CREATE FOREIGN TABLE warehouse(
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
1 | UPS | 29-SEP-1423:33:46
2 | TV | 29-SEP-1423:34:25
3 | 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;
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
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.
Ahsan Hadi is Senior Director, Product Development, EnterpriseDB.
Ibrar Ahmed is a Technical Architect for EnterpriseDB and a PostgreSQL Hacker.