Re: Quesion about querying distributed databases

On Wed, 2025-03-05 at 14:18 +0330, me nefcanto wrote:
> That means a solid monolith database. We lose many goodies with that.
> As a real-world example, right now we can import a single database
> from the production to the development to test and troubleshoot data.

Well, can't you import a single schema then?

> What if we host all databases on the same server and use FDW. What
> happens in that case? Does it return 100 thousand records and join
> in the memory?

It will do just the same thing.  The performance could be better
because of the reduced latency.

> Because in SQL Server, when you perform a cross-database query
> (not cross-server) the performance is extremely good, proving that
> it does not return 100 thousand ItemId from Taxonomy.ItemCategories
> to join with ProductId.
> 
> Is that the same case with Postgres too, If databases are located
> on one server?

No, you cannot perform cross-database queries without a foreign
data wrapper.  I don't see a reason why the statement shouldn't
perform as well as in SQL Server if you use schemas instead of
databases.

Yours,
Laurenz Albe