Just recently saw Michael Paguier's article on the recent commit of postgres_fdw so I was excited to try this out for myself. Questions we wanted to answer/experience were:
- Can I connect against different versions of PostgreSQL? Yes to test I tried connecting from my PostgreSQL 9.3 dev instance to my 9.2 instance on another box.
- Can I use a foreign type like -- you guessed it PostGIS if we have same version of PostGIS installed on both databases? Yes. I didn't try with different versions so not sure if that would work especially since the structure changed a bit between 1.5 and 2.0. I suspect 2.0 and 2.1 would work fine and might be a good way to cheat run 2.1 on 9.3 but creating derivatives of my 2.0 data with functions only available in 2.1.
- Will it use my spatial index? No or at least we couldn't figure out how and I don't see how it is possible, but would be nice if it could be
- Can I have a table defined with dynamic SQL? Nope or at least the docs don't mention it. This is a feature I really liked about the ODBC_FDW
because often times I want the table to be filtered especially if I need my filter to use an expensive index like a spatial one or utilize functions only available on the foreign server. If that were part of the definition of the foreign table, then it could in theory process that part on the foreign server. So this makes postgres_fdw not a good replacement for dblink in many cases.
So here are the tests we did to experiment with:
