Re: Quesion about querying distributed databases

Dear Laurenz, the point is that I think if we put all databases into one
database, then we have blocked our growth in the future.
A monolith database can be scaled only vertically. We have had huge
headaches in the past with SQL Server on Windows and a single database.
But when you divide bounded contexts into different databases, then you
have the chance to deploy each database on a separate physical machine.
That means a lot in terms of performance. Please correct me if I am wrong.

Let's put this physical restriction on ourselves that we have different
databases. What options do we have? One option that comes to my mind, is to
store the ID of the categories in the Products table. This means that I
don't need FDW anymore. And databases can be on separate machines. I first
query the categories database first, get the category IDs, and then add a
where clause to limit the product search. That could be an option. Array
data type in Postgres is something that I think other RDBMSs do not have.
Will that work? And how about attributes? Because attributes are more than
a single ID. I should store the attribute key, alongside its value. It's a
key-value pair. What can I do for that?

Thank you for sharing your time. I really appreciate it.
Saeed

On Wed, Mar 5, 2025 at 3:18 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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
>