Splitgraph co-founder (and post author) here. Most PostgreSQL clients don't treat foreign tables any differently than real ones, but note that things like FK constraints or triggers will have to be resolved on the remote server (your app essentially talks to an adapter that rewrites queries and forwards them to the remote database). This might not work that well as a scaling/reliability strategy (since you're still sending queries to one central database) though.

But this is actually one of the cool use cases we built Splitgraph for: you can mount a bunch of remote databases (doesn't have to be PG, can be Mongo/MySQL etc), Splitgraph images and even remote datasets like Socrata[1] into a single workspace and run e.g. JOINs between them. We optimize for the OLAP (read-only) use case and have a special FDW for querying remote Splitgraph images (we call this layered querying [0]). It downloads required regions of the table in the background, completely seamlessly to the client application. So you can spin up a lightweight Splitgraph engine at the edge and point a PostgreSQL client to it. This will let you satisfy read-only queries to huge remote datasets with a small local cache.

Re: compatibility, we have tested this setup with various analytics software and PostgreSQL clients like DBeaver/Metabase/dbt[2] and it works pretty well.

[0] https://www.splitgraph.com/docs/large-datasets/layered-query...

[1] https://www.splitgraph.com/blog/40k-sql-datasets

[2] https://www.splitgraph.com/product/splitgraph/integrations