mysql_fdw turns MySQL into a writable foreign table in PostgreSQL

The previous release was read only, and that changed everything

The feature list in the documentation leads with the difference that matters most. The previous version of mysql_fdw was only read-only; the latest version provides write capability, so a user can issue insert, update and delete statements against foreign tables. Underneath, it uses the PostgreSQL type casting mechanism to provide opposite type casting between MySQL and PG data types.

That last point is where the practical difficulty lives. A write across two engines means the data has to be converted twice, once in each direction, and the two systems do not agree on types. The extension handles that with the PostgreSQL casting machinery rather than leaving it to the user, but it does not erase the semantic differences. It also means the remote side of the transaction is not covered by PostgreSQL's transaction semantics in the way a local table is, which is a property of the architecture rather than of this implementation.

The extension is a PostgreSQL module in C, built against PostgreSQL's extension interface, and it talks to MySQL using MySQL's own C client library. The Makefile at the top of the repository shows the object list: connection.o, option.o, deparse.o, mysql_query.o, mysql_fdw.o and mysql_pushability.o, with the corresponding C files in the repository root. The deparse and pushability modules are the ones that rewrite a PostgreSQL query into MySQL syntax, which is where most of the interesting behaviour and most of the limitations live.

Pushdown is the feature, and its boundaries are documented

Most of the documentation's length is spent on pushdown, which is the mechanism that decides how much work happens on the MySQL server rather than in PostgreSQL.

WHERE clause pushdown sends the foreign table's where condition to the foreign server, so fewer rows cross the network. Column pushdown fetches only the columns that appear in the select target list rather than all columns of the remote table. Both are described as performance features, and both are the minimum you would expect from a mature FDW.

JOIN pushdown pushes joins between two foreign tables on the same remote MySQL server to that server instead of fetching both sides and joining locally. The documentation is careful about the scope: joins involving only relational and arithmetic operators in the join clause are pushed down, to avoid a potential join failure, and only INNER and LEFT/RIGHT OUTER joins are supported, with FULL OUTER, SEMI and ANTI joins not supported.

Aggregate pushdown sends aggregates to the remote server, but the pushdown is limited to min, max, sum, avg and count, specifically to avoid pushing functions the MySQL server does not have. Aggregate filters and orders are not pushed down.

ORDER BY pushdown has a subtle wrinkle that is worth knowing before you trust a result. NULLs behave in the opposite way on the MySQL server, so to get an equivalent result the extension adds an expression IS NULL clause at the beginning of each ORDER BY expression and skips it when the column is marked NOT NULL on the PostgreSQL side. If a column is marked NOT NULL on the MySQL side but not on the PostgreSQL side, the documentation warns that the result may differ.

LIMIT OFFSET pushdown performs the limiting on the remote server where possible. The ALL and NULL options are not supported on the MySQL server and so are not pushed down, and OFFSET without LIMIT is not supported on the MySQL server, so queries with that construct are not pushed either.

CREATE SERVER decides how the remote connection behaves

The connection behaviour is configured through the options that CREATE SERVER accepts, and the list is long enough to be worth reading before you assume defaults.

The basics are host as an optional string with a default of 127.0.0.1, and port as an optional integer with a default of 3306. secure_auth is an optional boolean defaulting to true and toggles secure authentication. init_command is an optional string with no default and is the SQL statement to execute when connecting to the MySQL server, which is where you would set a session mode or a time zone.

Two options are about planning rather than transport. use_remote_estimate is an optional boolean defaulting to false and controls whether mysql_fdw issues remote EXPLAIN commands to obtain cost estimates. Leaving it off means the planner works from local statistics and never queries the remote server for a plan, which is faster to plan and less accurate about remote table sizes.

reconnect is an optional boolean defaulting to false and enables automatic reconnection to the MySQL server if the existing connection is found to have been lost. That default is a deliberate choice worth understanding: with reconnection off, a dropped connection surfaces as an error to the query rather than being silently retried.

sql_mode is an optional string defaulting to ANSI_QUOTES and sets the MySQL sql_mode for the established connection. The SSL options are ssl_key, ssl_cert and ssl_ca, all optional strings with no defaults, giving the path names of the client private key file, the client public key certificate file and the Certificate Authority certificate file.

Building it needs pg_config, mysql_config and the MySQL C client

This is a source install, and the prerequisites are specific. To compile the foreign data wrapper, MySQL's C client library is needed, and the documentation says it can be downloaded from the official MySQL website.

The build expects two executables on your path. pg_config is typically in your PostgreSQL installation's bin directory, and the documentation shows the pattern:

    $ export PATH=/usr/local/pgsql/bin/:$PATH

The mysql_config binary must also be on the path, and it resides in the MySQL bin directory:

    $ export PATH=/usr/local/mysql/bin/:$PATH

Compilation, installation and the regression suite are then three make invocations, all with USE_PGXS set:

    $ make USE_PGXS=1

    $ make USE_PGXS=1 install

    $ make USE_PGXS=1 installcheck

The regression run needs the MYSQL_HOST, MYSQL_PORT, MYSQL_USER_NAME and MYSQL_PWD environment variables set correctly, and the documentation points at the mysql_init.sh script in the repository for the default settings. The Makefile lists the regression test names, which double as a map of what is covered: server_options, connection_validation, dml, select, pushdown, join_pushdown, aggregate_pushdown, limit_offset_pushdown and misc.

One build detail is worth noting for Debian based distributions. The Makefile checks whether the client library reported by mysql_config contains mariadbclient, and if so links against mariadbclient, otherwise against mysqlclient. It also picks the shared library suffix based on the operating system, using dylib on Darwin and so elsewhere.

Connection pooling and prepared statements changed the wire behaviour

Two changes sit underneath the pushdown features and explain why this version behaves differently from the one it replaces.

The first is connection pooling. The documentation says the latest version comes with a connection pooler that uses the same MySQL database connection for all the queries in the same session, where the previous version opened a new MySQL database connection for every query. That is a large change in behaviour for a workload that opens many short queries, since connection setup to a remote server is not free.

The second is prepared statements. The select queries now use prepared statements instead of the simple query protocol. The documentation describes this as a refactoring for select queries, which suggests the work was done for correctness or for parameter handling rather than as a headline feature, but it changes how the remote server sees your traffic.

Both changes point in the same direction as the pushdown work: less data crossing the network, fewer round trips, and fewer connections. That is what makes the extension viable as a live query path rather than only as a migration aid.

The repository layout supports the same picture. Alongside the C sources there are the SQL upgrade scripts, from mysql_fdw--1.0.sql through mysql_fdw--1.2.sql with the intermediate upgrade files, a mysql_fdw_pushdown.config file, an expected directory holding the regression test expectations, and a sql directory. The version naming in the SQL scripts stops at 1.2 while the release tags use the REL-2_9_x form, so the two numbering schemes are separate.

Supported PostgreSQL versions are checked at compile time

The documentation states that this version of mysql_fdw works with PostgreSQL, EDB Postgres Advanced Server, and EDB Postgres Extended Server 14, 15, 16, 17, 18 and 19.

That list is enforced rather than aspirational. In the Makefile, when the major version derived from the PostgreSQL version is not one of 14 15 16 17 18 19, the build stops with an error stating that PostgreSQL 14, 15, 16, 17, 18 or 19 is required to compile this extension. So a PostgreSQL 13 or 20 deployment is a build problem, not a runtime surprise.

On platforms, the documentation says mysql_fdw was developed on Linux and should run on any reasonably POSIX-compliant system, with a pointer to separate mysql_fdw documentation for the platform matrix.

The release history in this repository follows the REL naming that PostgreSQL itself uses for major version branches. REL-2_9_3 was tagged on 2025-09-30, REL-2_9_2 on 2024-07-11 and REL-2_9_1 on 2023-07-14. The last push was on 2026-09-28, so the source tree has been touched within the last month, and the branch naming indicates the extension is maintained per PostgreSQL major version rather than on a single trunk.

The licensing position deserves a note rather than an opinion. The project's licence is recorded as a custom one that GitHub cannot classify, so the actual terms are in the LICENSE file in the repository root and should be read directly. The Makefile header shows portions copyright the PostgreSQL Global Development Group from 2012 to 2014 and EnterpriseDB Corporation from 2004 to 2026, which is consistent with a project that began as a PostgreSQL contrib module and is now maintained by a vendor.

Where the foreign table approach is the wrong tool

Three situations rule it out.

The first is a one time migration. If the goal is to move MySQL data into PostgreSQL and then decommission the MySQL server, a live foreign table leaves your query path dependent on MySQL forever. A dump and restore, or logical replication, moves the data and removes the dependency. The FDW is for the case where both databases genuinely have to stay.

The second is a workload where the remote side cannot answer the query efficiently. Pushdown only helps when the predicate, the join or the aggregate is something MySQL can evaluate. A query whose real work is a transformation that stays in PostgreSQL still pulls the relevant rows across the network, and the type casting between the two engines adds a step the planner does not model. The limitation list in the documentation is precise about the functions it will not push, so it is worth checking whether your query falls inside that list before assuming the rows stay remote.

The third is a requirement for strong transactional guarantees or for performance characteristics that the remote hop cannot give you. A write to a foreign table is a write to another system, and the two engines do not share a transaction manager.

A reasonable alternative for many of these cases is to keep the MySQL data in PostgreSQL entirely, either by migrating it or by replicating it, and treat the FDW as a transitional or read mostly tool rather than as a permanent architecture. The pushdown list and the write support make it considerably more capable than an earlier read only FDW, but it remains a bridge between two databases rather than a replacement for one of them.

Editorial conclusion

mysql_fdw fits a PostgreSQL and MySQL estate that has to keep both databases running, where the PostgreSQL side needs to query or even update MySQL tables without a second connection layer in the application, and where the pushdown behaviour matters because it decides how many rows cross the network. It is the wrong choice for a one time migration, where a dump and restore or a logical replication tool does the job without leaving a live dependency on MySQL in your query path. Before adopting it, check three things: that your PostgreSQL version is one of 14 to 19, since the Makefile refuses to compile against anything else, that the MySQL C client library is installed and its mysql_config is on your path, and that the specific pushdown you depend on is one the documentation lists, since aggregate pushdown is limited to min, max, sum, avg and count, and FULL OUTER, SEMI and ANTI joins are not pushed down at all.

Frequently asked questions

Official sources

1. EnterpriseDB/mysql_fdw on GitHub
2. Issues
3. README
4. Releases