Message-ID: <c9699a6fd331d33864fc269060d8d961f784d827.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 10:55:01 +0100
In-Reply-To: <CAEHBEOBXzkGTqxQSYqmEFN5hbc=zsGWFpU9h8zf7AAPv4VdOWQ@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>
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/c9699a6fd331d33864fc269060d8d961f784d827.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-03-05 at 12:57 +0330, me nefcanto wrote:
> Right now this data is in MariaDB, on separate databases (schema) but on =
one
> server. The solution in this situation is to have a cross-database query.
> (this is the status quo of our application)
>=20
> Now our team has decided to migrate to Postgres. However, we realized tha=
t
> Postgres does not support cross-database queries. And if we want to do so=
,
> we should use FDW. So, we thought we might as well put databases on separ=
ate
> servers for scalability if we have to write more code. That's the reason
> behind this question.

In MySQL, the terms "database" and "schema" are used for the same thing.
Not so in PostgreSQL.  I think you should migrate the data into different
schemas in a single database, pretty much like you had it in MySQL.
Then you don't need a foreign data wrapper, and I bet the query can
perform as well as it did on MySQL.

Yours,
Laurenz Albe