Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpneo-00CJyT-1N for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 12:15:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tpnel-009TXy-EI for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 12:15:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpnek-009TXq-Qu for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 12:15:22 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpnei-0016K6-2H for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 12:15:21 +0000 Received: by mail-pj1-x102e.google.com with SMTP id 98e67ed59e1d1-2feb9078888so11332836a91.3 for ; Wed, 05 Mar 2025 04:15:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741176920; x=1741781720; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Lk4aM0Td30S26Y526oE2qPnKoO2Pmy3XuyL1aVsyYDk=; b=nOcIGLgkKhydEBHCUTX1s9Ml/skrwFjjiBvMD1wPpHhzM32Jzj1bbnhxNMUqsWQlws UfwxNkXFpygpQ++OFnkIyBjaxgmKY3eJuREGvr09udOFMk9IviS7WKGo9NocjfG1Jy/w T1XJGWdUJ48mP/oYPbG5ShQNx+JLg3hSK+uyZZuHrdXGndudfhysnZduRYI5xaFHEh58 f0XD1HaydIrZuecsRXtcfGq7CJvR5jHBZsWqqHSipxW2mo6kDq0X+N2AIm3YB6Rt6X8J gDKy3W/oLujxkonp380uS4VsHfWx+yllu9QYglFVkOqrlDLiUgBPLbjeLSUfCs2am9hp JYAQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741176920; x=1741781720; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Lk4aM0Td30S26Y526oE2qPnKoO2Pmy3XuyL1aVsyYDk=; b=CYRZ5/FjZ7Apnkf5LCHHQAJnULZVMTwrh30B7nDwIqr3FgADTeZrNk9IerPNpP27wB gEjJgt14blYc7QE8Q7cGhzDkw03S+6rIQxk+paxU8f7ffDSwLF6Z0KruiNGU0zp89Lo8 nAHedxVNVPxYEvaFo6/tXD4gt0SEqy0GtoZ/tGuXovlp1KJ3Fltm/LcN3OPm3wa3iRWa ktW9RGo9pMbnjSJVsehzwcP7pz/Shoie+028N+SP+QmFs6UFWG31vG7pdGL78fu9VIzE lAJpG/jj0y4mYT4W+JNNtf681Ink5DwspEgFv+wE3Tr0YnVN7XeYS5lLElR4HB6aKSzC MjYg== X-Forwarded-Encrypted: i=1; AJvYcCWIU/4FIJ+s0lNY7B25Mah1YwSeUqZxPggxh9j/1Yo8EuyJbgeQLO502Z7Ii8Ehl8ERrt7ZfLkyI1qjHgOy@lists.postgresql.org X-Gm-Message-State: AOJu0YxtDq32D4RXjOXxM7sHNuBznHn63R5g+wo3NkC9z4+C7tOfmegM oROtYNo17v856rwDE932sj9eZR1nnhcupF2SbH36ehUkjrQIbNpuzZcfs9XLzCWDzHHp5yznpaU 8mfb20xpn5bdV8NeTkuzDZUOsLwc= X-Gm-Gg: ASbGncu2bJRdR7MaW+j/bhLTy0T8EXwBWCJA2duGLLlYpyIPTKPoruzvJji+weZjzf3 F/6F04lp70Qqxk1l6d9JCAnjHGCX0j70N4HJwwtHTdTXqqFTiA/lLQHXDnaiNMabRpjpeaZ5Kl6 vX/WHVow3jc4BLIg411FxgyH8aQXU= X-Google-Smtp-Source: AGHT+IFMoZcBUsu51FB+yIZFnzJlO3KjUXhoLfYwGH88rkKmlFwjXB1RUMkgYpZk4+awW060dh6n38t19oHVagdCU5A= X-Received: by 2002:a17:90b:3ecd:b0:2fe:afbc:cd60 with SMTP id 98e67ed59e1d1-2ff49813918mr5647212a91.24.1741176919605; Wed, 05 Mar 2025 04:15:19 -0800 (PST) MIME-Version: 1.0 References: <099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel@cybertec.at> In-Reply-To: <099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel@cybertec.at> From: me nefcanto Date: Wed, 5 Mar 2025 15:45:08 +0330 X-Gm-Features: AQ5f1JocXc6Y4-SKoLfhhe84c4M52HfzZTYhVedw2mxzmWP1-5afk1qhbPaCqt0 Message-ID: Subject: Re: Quesion about querying distributed databases To: Laurenz Albe Cc: Adrian Klaver , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c8caf7062f9757b6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c8caf7062f9757b6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Laurenz Albe 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 > --000000000000c8caf7062f9757b6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 b= ounded contexts into different databases, then you have the chance to deplo= y each database on a separate physical machine. That means a lot in terms o= f performance. Please correct me if I am wrong.

Let's put this physi= cal 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 ca= tegories in the Products table. This means that I don't need FDW anymor= e. 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 a= bout attributes? Because attributes are more than a single ID. I should sto= re 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 appreci= ate it.
Saeed





On Wed, Mar 5, = 2025 at 3:18=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2025-03-05 at 14:18 +0330, me nef= canto wrote:
> That means a solid monolith database. We lose many goodies with that.<= br> > As a real-world example, right now we can import a single database
> from the production to the development to test and troubleshoot data.<= br>
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.=C2=A0 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.=C2=A0 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
--000000000000c8caf7062f9757b6--