5 ms·

what are the downsides to foreign data wrappers?

touisteur 6y ago
Same as extensions I guess? You're including a binary that clings to the innards of your operational prod pg instance? I mean I love FDWs, especially how easy it is to write one... But this is an issue I've run into.
steve_adams_86 6y ago
It’s added complexity, it means changing the foreign data’s shape could break the wrapper, performance won’t be as good. There is probably more, but those come to mind. For a lot of people that’s probably not a net negative.
- rattray 6y ago
  How does the wrapper break when the foreign data's shape changes? What is the nature of the errors surfaced?
  - steve-chavez 6y ago
    Could be a type error for example. You specify an `int` column(say you expect "20") in the fdw but one row has it as `text`(you get "twenty"). Something like this has happened to me when reading from a csv file. In this case pg will show an error and not read any rows from the fdw.
bdcravens 6y ago
If you don't own the install and are using a managed service, you aren't able to install new ones (RDS for example supports a lot of common extensions, but few fdws) so depending on them may place a hard dependency on the ops side.
mumblemumble 6y ago
The biggest one I've seen is that the data source you're connecting in via the FDW may provide operational capabilities that are quite a bit different from what's built into PostgreSQL. This can add quite a lot of complexity to things like your backup, upgrade, and disaster recovery plans.
mildbyte 6y ago
Splitgraph co-founder (and post author!) here. By far the biggest problem with foreign data wrappers is that you're still forced into PostgreSQL's format of treating and returning each tuple separately. There's some research being done in PostgreSQL [0] with pluggable storage formats as an alternative to using FDWs for querying. Also, Citus had a prototype that would hook into the query planner to vectorize cstore_fdw aggregations [1]: this is also promising for getting around some FDW restrictions. FDWs aren't really that layperson-friendly: to set one up, you need to run a lot of SQL boilerplate like CREATE FOREIGN SERVER, CREATE USER MAPPING and CREATE FOREIGN TABLE. We wanted to make them more accessible through our sgr mount [2] command and snapshottable via Splitfiles (e.g. [3]). The point about data types changing is also a good one. Where the foreign data wrapper doesn't implement IMPORT FOREIGN SCHEMA [4] through introspection on the foreign database side, you have to enumerate all columns and types in CREATE FOREIGN TABLE. If a column on the remote table goes away, the FDW might still query it and cause runtime errors. [0] https://wiki.postgresql.org/wiki/Future_of_storage https://wiki.postgresql.org/wiki/Future_of_storage [1] https://github.com/citusdata/postgres_vectorization_test https://github.com/citusdata/postgres_vectorization_test [2] https://www.splitgraph.com/docs/sgr/data-import-export/mount https://www.splitgraph.com/docs/sgr/data-import-export/mount [3] https://www.splitgraph.com/docs/ingesting-data/socrata#splitfile https://www.splitgraph.com/docs/ingesting-data/socrata#split... [4] https://www.postgresql.org/docs/current/sql-importforeignschema.html https://www.postgresql.org/docs/current/sql-importforeignsch...
- asah 6y ago
  how's performance?
  - mildbyte 6y ago
    You always have the latency/bandwidth overhead from moving queries/data between instances, but FDW performance can be surprisingly fast. There's a performance-FDW complexity spectrum and you can choose a point on it that's applicable to your use case. At its simplest, the FDW can just return all tuples from the remote database without filtering them (letting the local DB run filtering). But more advanced FDWs like postgres_fdw[0] can push down qualifiers and joins to the remote database. postgres_fdw even runs EXPLAIN on the remote instance and parses its output -- essentially letting the local and the foreign query planners collaborate on execution. [0] https://www.postgresql.org/docs/current/postgres-fdw.html#id-1.11.7.42.13 https://www.postgresql.org/docs/current/postgres-fdw.html#id...
deleted 6y ago
[deleted]

Same as extensions I guess? You're including a binary that clings to the innards of your operational prod pg instance? I mean I love FDWs, especially how easy it is to write one... But this is an issue I've run into.

steve_adams_86 6y ago

It’s added complexity, it means changing the foreign data’s shape could break the wrapper, performance won’t be as good. There is probably more, but those come to mind. For a lot of people that’s probably not a net negative.

rattray 6y ago
How does the wrapper break when the foreign data's shape changes? What is the nature of the errors surfaced?
- steve-chavez 6y ago
  Could be a type error for example. You specify an `int` column(say you expect "20") in the fdw but one row has it as `text`(you get "twenty"). Something like this has happened to me when reading from a csv file. In this case pg will show an error and not read any rows from the fdw.

bdcravens 6y ago

If you don't own the install and are using a managed service, you aren't able to install new ones (RDS for example supports a lot of common extensions, but few fdws) so depending on them may place a hard dependency on the ops side.

mumblemumble 6y ago

The biggest one I've seen is that the data source you're connecting in via the FDW may provide operational capabilities that are quite a bit different from what's built into PostgreSQL. This can add quite a lot of complexity to things like your backup, upgrade, and disaster recovery plans.

mildbyte 6y ago

Splitgraph co-founder (and post author!) here. By far the biggest problem with foreign data wrappers is that you're still forced into PostgreSQL's format of treating and returning each tuple separately. There's some research being done in PostgreSQL [0] with pluggable storage formats as an alternative to using FDWs for querying. Also, Citus had a prototype that would hook into the query planner to vectorize cstore_fdw aggregations [1]: this is also promising for getting around some FDW restrictions. FDWs aren't really that layperson-friendly: to set one up, you need to run a lot of SQL boilerplate like CREATE FOREIGN SERVER, CREATE USER MAPPING and CREATE FOREIGN TABLE. We wanted to make them more accessible through our sgr mount [2] command and snapshottable via Splitfiles (e.g. [3]). The point about data types changing is also a good one. Where the foreign data wrapper doesn't implement IMPORT FOREIGN SCHEMA [4] through introspection on the foreign database side, you have to enumerate all columns and types in CREATE FOREIGN TABLE. If a column on the remote table goes away, the FDW might still query it and cause runtime errors. [0] https://wiki.postgresql.org/wiki/Future_of_storage https://wiki.postgresql.org/wiki/Future_of_storage [1] https://github.com/citusdata/postgres_vectorization_test https://github.com/citusdata/postgres_vectorization_test [2] https://www.splitgraph.com/docs/sgr/data-import-export/mount https://www.splitgraph.com/docs/sgr/data-import-export/mount [3] https://www.splitgraph.com/docs/ingesting-data/socrata#splitfile https://www.splitgraph.com/docs/ingesting-data/socrata#split... [4] https://www.postgresql.org/docs/current/sql-importforeignschema.html https://www.postgresql.org/docs/current/sql-importforeignsch...

asah 6y ago
how's performance?
- mildbyte 6y ago
  You always have the latency/bandwidth overhead from moving queries/data between instances, but FDW performance can be surprisingly fast. There's a performance-FDW complexity spectrum and you can choose a point on it that's applicable to your use case. At its simplest, the FDW can just return all tuples from the remote database without filtering them (letting the local DB run filtering). But more advanced FDWs like postgres_fdw[0] can push down qualifiers and joins to the remote database. postgres_fdw even runs EXPLAIN on the remote instance and parses its output -- essentially letting the local and the foreign query planners collaborate on execution. [0] https://www.postgresql.org/docs/current/postgres-fdw.html#id-1.11.7.42.13 https://www.postgresql.org/docs/current/postgres-fdw.html#id...

deleted 6y ago

[deleted]