Message-ID: <099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel@cybertec.at>
Subject: Re: Quesion about querying distributed databases
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: me nefcanto <sn.1361@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general@lists.postgresql.org
Date: Wed, 05 Mar 2025 12:48:23 +0100
In-Reply-To: <CAEHBEOBNoG8RkKuCcQQWkbYppMLMzA0MXq+s0kZ6wKWgD7+45Q@mail.gmail.com>
References: 
	<CAEHBEOBuoMFWuhHM3L_Zr6o1enELju-Vns6Pknt4TT+6MFQOwQ@mail.gmail.com>
	 <fd47b28c-8f6b-4bfb-a393-160c5c3de8c0@aklaver.com>
	 <CAEHBEOD969YrbPH_z9OEmThWx3-w4sMMaHLhZLOQwqCwE8Y58Q@mail.gmail.com>
	 <caf4e99941b3c83bb9eab91e33b144b826b68f79.camel@cybertec.at>
	 <CAEHBEOBXzkGTqxQSYqmEFN5hbc=zsGWFpU9h8zf7AAPv4VdOWQ@mail.gmail.com>
	 <c9699a6fd331d33864fc269060d8d961f784d827.camel@cybertec.at>
	 <CAEHBEOBNoG8RkKuCcQQWkbYppMLMzA0MXq+s0kZ6wKWgD7+45Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel%40cybertec.at>
Precedence: bulk

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