5 ms·

Could one run a separate Postgres instance for each table in a large schema, and use FDW to stitch it all back together? To what extent would this be noticeabl

Could one run a separate Postgres instance for each table in a large schema, and use FDW to stitch it all back together? To what extent would this be noticeable to applications? Could this be an interesting scaling/reliability strategy in some circumstances?

mildbyte 6y ago
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-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [1] https://www.splitgraph.com/blog/40k-sql-datasets https://www.splitgraph.com/blog/40k-sql-datasets [2] https://www.splitgraph.com/product/splitgraph/integrations https://www.splitgraph.com/product/splitgraph/integrations

mildbyte 6y ago

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-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [1] https://www.splitgraph.com/blog/40k-sql-datasets https://www.splitgraph.com/blog/40k-sql-datasets [2] https://www.splitgraph.com/product/splitgraph/integrations https://www.splitgraph.com/product/splitgraph/integrations