4 ms·

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 tre

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...

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...