PostgreSQL Accessing MySQL as a Data Source Using mysqsl_fdw
percona.com
percona.com
The caveats using this feature -
1/ Strict upper bound on how much data it can pull in.
2/ MySQL migrations need to be run on both Postgres and MySQL
3/ No way to gracefully migrate or version
4/ MySQL’s “loose” typing doesn’t play well with Postgres’s “strict” typing. This means data can break the fdw.
5/ Pain in the ass to debug.
6/ This is debatable, but I don’t believe that application code (SQL code in this case) should live in the database.
7/ Postgres and MySQL have different performance characteristics. This can lead to hard-to-debug performance problems if you are explicitly or implicitly using a fdw in your query.
8/ If you want to keep a local copy of MySQL data in Postgres (to solve for #7) you then have to write code to keep it up to date. This defeats the convenience of the fdw.
9/ At least when I was using fdws, they didn’t have predicate pushdown. This causes you to do structure queries in a weird way to get filters to work as you expect.
10/ You have to manage schema type mismatch between MySQL and Postgres. This isn’t fun or productive work.
My experience is colored by inheriting a disaster of interconnected fdws. But given that negative bias, I still haven’t seen enough value from fdws to justify their use outside of small scale corner cutting.
With mysql_fdw you can write the ETL code itself in PostgreSQL: you expose (a subset of) the MySQL tables through the FDW, and then you write SQL code to transform it and copy it to your analytics tables. That's exactly what I did in one project: at night the whole analytics database was recreated in 15 minutes or so (the biggest table had about a hundred million of rows).
Most of the caveats don't apply in that case:
> 1/ Strict upper bound on how much data it can pull in.
I don't know what you mean by that. As far as I know, there are no such bounds.
> 2/ MySQL migrations need to be run on both Postgres and MySQL
Since the analytics database is recreated every night, that is not the case.
> 3/ No way to gracefully migrate or version
Same as 2.
> 4/ MySQL’s “loose” typing doesn’t play well with Postgres’s “strict” typing. This means data can break the fdw.
I never ran into that, but it is possible.
> 5/ Pain in the ass to debug.
Much easier to debug than ETL scripts that talk to two different databases, in my opinion. You can interactively write the SQL code in psql. I used pgTap to unit test the SQL code.
> 6/ This is debatable, but I don’t believe that application code (SQL code in this case) should live in the database.
I don't see any problem with that.
> 7/ Postgres and MySQL have different performance characteristics. This can lead to hard-to-debug performance problems if you are explicitly or implicitly using a fdw in your query.
In my approach you just do a 'SELECT * FROM x' on the mysql side. All performance problems are easily debugged on the postgres side.
> 8/ If you want to keep a local copy of MySQL data in Postgres (to solve for #7) you then have to write code to keep it up to date. This defeats the convenience of the fdw.
If you recreate the analytics database periodically this is a non-issue.
> 9/ At least when I was using fdws, they didn’t have predicate pushdown. This causes you to do structure queries in a weird way to get filters to work as you expect.
PostgreSQL has had predicate pushdown for years now.
> 10/ You have to manage schema type mismatch between MySQL and Postgres. This isn’t fun or productive work.
You have to do that anyway, even if you're using ETL scripts.
If you use the FDW, you can implicitly create the tables on the postgres side: CREATE TABLE my_analytics_table AS SELECT foo, bar FROM mysql_fdw_table WHERE ...;
That way my_analytics_table will get the postgresql types corresponding to the mysql_table. If anything changes, it gets automatically propagated. Analytics queries against my_analytics_table might break, though. Usually, when it's a change from e.g. int8 to int16, things will continue working.
It was easier for us because it helps write maintainable logic, you can easily sync multiple databases, multiple servers, without much changes.
A few things to note though:
- we had issues with 0000-00-00 dates that are valid in mysql but not in postgres. So we had to write include a convertion in plsql into the mat view.
- We stumble upon a bug in mysql_fdw, in which the selected row is skipped if the size of a column exceed a given size. This is reaaaaally hard to catch and really bad experience as you won't get any error, notice or warning.
- we had trouble with a few type casting like json and others.
I can see this being useful in some legacy system that you have to maintain that is not changing anymore, but otherwise I don't see a good use for it for properly designed and new projects.
What's your definition of properly designed projects?
Having a process that copies data into a reporting system solves the problem that otherwise it would be brittle (changes to either system might break things) and performance (any reporting queries affect realtime ones). Also, as it's decoupled, pulling in data from a 3rd system, e.g. CRM is possible.
Granted, it's not quite as quick as this, and not suited for "one-off" reporting, but it's a lot more flexible. Put things in a nice star schema and you have a good way to do analytics on your data.