Working across multiple PostgreSQL database servers

Question

So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but we are evaluating different possibilities.

One option I'm pondering is basing our app on PostgreSQL and use its facilities for accessing external PostgreSQL instances.

What's the status of this?

PG's documentation contains http://www.postgresql.org/docs/9.1/static/sql-createserver.html and http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html , which allow you to reference tables in an external server. What's their status? Are queries performant (i.e. sends WHERE to the other side)? Can you reasonably join between local and foreign tables?

There's also pgsql_fdw ( http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html ), which seems more featureful. Does it offer an improvement on the above? Anyone using it?

Thanks,

Alex

Answer 1

Foreign data wrappers are currently read-only. This will change in the future, but that's the way it is. You can also use dblink to access other databases. As I understand it a big issue will be commit handling. I would recommend against this approach unless you are trying to aggregate lots of data that is really external. It is a huge complexity cost and the benefits have to be worth it.

Edit: Note that foreign tables and foreign servers are how you use foreign data wrappers to map in foreign data to your application. You can't choose between them. It's all three or nothing.