consider packaging functions as extension for FDW use

[smnorris]

As multiple projects rely on FWA data, it would be convenient to store the FWA data in a single db and refer to it from project specific databases via foreign data wrappers rather than loading FWA data to each project specific db (yes, different schemas for different projects works ok, but managing naming conflicts becomes awkward).

Adding a fdw works fine, but calling the fwa functions does not - they can be installed on the client db, but queries are not pushed down to the foreign server for optimization. If the functions are packaged as an extension, this push down should work:

https://www.ongres.com/blog/boost-query-performance-using-fdw-with-minimal-changes/

[smnorris]

A sample fdw setup. Note that inclusion of postgis extension in options is critical for performant display of data in QGIS via the fdw.

create extension if not exists postgis;
create extension if not exists ltree;
create extension if not exists intarray;
create extension if not exists postgres_fdw;
create schema if not exists whse_basemapping;

drop server if exists fwapg cascade;
create server fwapg
foreign data wrapper postgres_fdw
options (host 'localhost', dbname 'fwapg', extensions 'ltree, postgis, intarray');

create user mapping for postgres
server fwapg
options (user 'postgres', password 'postgres');

import foreign schema whse_basemapping from server fwapg into whse_basemapping;

[smnorris]

Packaging as an extension does seem to be necessary. A test run of the bcfishobs queries against FWA data held in a FDW is too slow to be practical. Manipulating the slow query to materialize some CTEs makes it complete (~8min) but still much slower than in the local db (~5min). Disk space saved does not seem worth the investment of packaging an extension and/or having to optimize existing queries in bcfishobs/bcfishpass - closing for now.

[smnorris]

If revisiting extension packaging, maybe consider also moving all fwapg data (FWA plus usgs/hydrosheds) from whse_basemapping, hydrosheds, usgs schemas into a single fwa schema.