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 1tpl2g-00Bai8-NG for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 09:27:54 +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 1tpl2f-005tPd-6A for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 09:27:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpl2e-005tPV-Pd for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 09:27:52 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpl2b-0014Nx-0p for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 09:27:52 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-2fecba90cc3so9315143a91.2 for ; Wed, 05 Mar 2025 01:27:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741166868; x=1741771668; 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=j/2x+XHjihpCt+j6pCsx0TicJ43ggPBHTD5ahGm4pIo=; b=T30e0UEkYVl/jHYOXmTgMsm66n1yJJ1Qku/+AW8u1H8N8IQVbaYYBtCwja1SPDCHBW mmxeR+uvc1mVkSD5yQVGKvstoqCbg+qnMW5fW/ivBcN/thj4/oklUa47oGMMbUSSOs9W pG9OdEK8d043mYTr3IH5kPQ2pjBQSDDO4BNwOP0oSTIDGjktVFy1pQL2YhVp9RrkCrOz iyGSFAcexFT6WLY3uaZWe5Hsnzaq1ntkhkNHWrIp/PgQpW554g989TUq95knGyMZAE6c uQwNfBjI9qxTBllN+EAqjkgu3YunbI7AAy5q4w3YsFFmJ/sPI5OS5eAid9Y3NJ2bQNLA u+KA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741166868; x=1741771668; 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=j/2x+XHjihpCt+j6pCsx0TicJ43ggPBHTD5ahGm4pIo=; b=JkFmwdRkTWY97DdaiPAvNCSl3AkAwvzztJBalZEuMMo2txyMD7713wRvCVKF7v4hTR 8bNUjU5RouCZdPRC7Mroum2VqGvuatZiHbrkQw1oOWwTpkadf2ar2abNqdjpeQ1qchAQ fpmdg9ZOyKOvsPZrTOimcqLQeUgy1ZvZ+U1yO9V3smioXA7FNbHiy0hpMwwqnXHvcjvh 3apeyc3RstWfrEEv2/G30n0iVpMMEgLocluA8cvtyFEvuJdWhG6bK2NyjSWkF3PFcESa uk4TBbfPNUXF+Tu3fWTFzAoMQeBPuOfN9H+2grtlMu3L+UJTaVMVuOwqdGQvpT9dBjDm km1A== X-Forwarded-Encrypted: i=1; AJvYcCV5S/EIUe8hKImksb0nvSgi4hWkFoIplS4j07jgm1lk4elVoV+D5qos1Ne5kPoRK6C8zfMSaEuIhedPJfgz@lists.postgresql.org X-Gm-Message-State: AOJu0YyUa+tYEEsOyXAUjQRqeCn+94hBvJ2az0i9D+QkEU7+AQz77CWo FPtLzB94wb2agAZokcAsYGSEOy+PjmqFCMjcQ5o2hCnrxqcjnAYhlgJ0IYjVMFXlAsK2cV9G2T8 F89P+yc8O3Yenl8Ix4GiMlQvruocJl0za8SQRBg== X-Gm-Gg: ASbGncun5SF/x84DPO9nk5hjyZAD7GI5thqRsrQb7u9OnWZTk/VFX+SnUv+ytiz99wn Ni/X4VdlOdTc1uqwYZwbSQFUrzYdaa87wGp5CFjd8XJSFePqKox3zq3aT++vMN8aVmPQqFmGFZi pkeWf0R64wszKiKU70RnpLjX6tqp8= X-Google-Smtp-Source: AGHT+IG5GUfCBhuIubSt8mG024eESmzLBzf24Is2B3eIG44S8f/0SQfH1bGl1CKwPjUjtOMdmtU+EyPI8lryD32xLBc= X-Received: by 2002:a17:90b:4d07:b0:2ee:df70:1ff3 with SMTP id 98e67ed59e1d1-2ff496c0412mr5398346a91.0.1741166868336; Wed, 05 Mar 2025 01:27:48 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: me nefcanto Date: Wed, 5 Mar 2025 12:57:37 +0330 X-Gm-Features: AQ5f1Jof10VZcCbG5y_AzMI2nAGyMafEsYZ6mY5Mga3WjfiBVSmyk3GtNQg0Xfg 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="000000000000ae98c1062f9500e5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ae98c1062f9500e5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Laurenz Albe, thanks for your answer. 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) Now our team has decided to migrate to Postgres. However, we realized that 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 separate servers for scalability if we have to write more code. That's the reason behind this question. But we're stuck at performance. In SQL Server and MariaDB, cross-database queries allow for neat separation of data while delivering good performance in the orchestration layer. You have separate databases, which allows for fine-grained management (different backup schedules, index recalculation, deployment, etc.) but at the same time you can write a query in your app, or in an orchestrator database (let's call it All) that is fast enough for millions of records. However, we're stuck in this in Postgres. What solutions exist for this problem? Regards Saeed On Wed, Mar 5, 2025 at 11:09=E2=80=AFAM Laurenz Albe wrote: > On Wed, 2025-03-05 at 10:12 +0330, me nefcanto wrote: > > Adrian Klaver, thank you for the link. I asked the AI to create a query > for me using FDW. > > > > The problem here is that it collects all of the product_id values from > the ItemCategories table [...] > > > > That's not scalable. Is there a workaround for this? > > Without having scrutinized the case in detail: if your data are organized > in an > entity-attribute-value design and distributed across three databases, you > cannot > expect to end up with efficient queries. > > Perhaps you can extract the data and load them into a reasonably organize= d > single database. Such an ETL process might make the task much easier. > > Yours, > Laurenz Albe > --000000000000ae98c1062f9500e5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Laurenz Albe, thanks for your answer.

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 qu= o of our application)

Now our team has decided to migrate to Postgres. H= owever, we realized that 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 separate servers for scalability if we have to write more= code. That's the reason behind this question.

But we're st= uck at performance. In SQL Server and MariaDB, cross-database queries allow= for neat separation of data while delivering good performance in the orche= stration layer. You have separate databases, which allows for fine-grained = management (different backup schedules, index recalculation, deployment, et= c.) but at the same time you can write a query in your app, or in an orches= trator database (let's call it All) that is fast enough for millions of= records.

However, we're stuck in this in Postgres. What solutions e= xist for this problem?

Regards
Saeed


On = Wed, Mar 5, 2025 at 11:09=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2025-03-05 at 10:12 = +0330, me nefcanto wrote:
> Adrian Klaver, thank you for the link. I asked the AI to create a quer= y for me using FDW.
>
> The problem here is that it collects all of the product_id values from= the ItemCategories table [...]
>
> That's not scalable. Is there a workaround for this?

Without having scrutinized the case in detail: if your data are organized i= n an
entity-attribute-value design and distributed across three databases, you c= annot
expect to end up with efficient queries.

Perhaps you can extract the data and load them into a reasonably organized<= br> single database.=C2=A0 Such an ETL process might make the task much easier.=

Yours,
Laurenz Albe
--000000000000ae98c1062f9500e5--