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 1tpiSd-00Ar3X-Ro for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 06:42:31 +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 1tpiSb-002e8S-Ts for pgsql-general@arkaria.postgresql.org; Wed, 05 Mar 2025 06:42:29 +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 1tpiSb-002e6n-F4 for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 06:42:29 +0000 Received: from mail-pj1-x102f.google.com ([2607:f8b0:4864:20::102f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpiSY-0013cw-2N for pgsql-general@lists.postgresql.org; Wed, 05 Mar 2025 06:42:28 +0000 Received: by mail-pj1-x102f.google.com with SMTP id 98e67ed59e1d1-2fea8d8c322so12052215a91.2 for ; Tue, 04 Mar 2025 22:42:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741156946; x=1741761746; 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=6pUlGKwi0cF/iABfAubfkst5RFW/xiopSrvEdul+Ga8=; b=julIddTS7ntZQtd4Y2J5S4rqMnccQ83lvElOkISBXJ07ALKo0d0LVi9ibOE8DEb+DT ojoueVv0sA/+qmps7VtT+Ac6jWkhyInHSCQBgvYK2MMjMPgnV1/XCb81H1Cqc1lB4jMt RXapW+nDYZR9YPjW76DyX+/GzfOvh1GLUK6HKFDkLzC5bNxOCNbvCqIAHOho57DeisDL E6YazB/VtiIDDLGelDrRCUCa6m1y7+yQ11l9+3nrzV6tmLjKDf/eGOLEGvcJil23XxVX 15eUW1v8OmQp4W3Fa99Dj0wfKZgrxTZ+hD7M/l/TCnjIYYyJbag4zUJAJhLUmq1YxEVK 4sBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741156946; x=1741761746; 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=6pUlGKwi0cF/iABfAubfkst5RFW/xiopSrvEdul+Ga8=; b=MQN12EBZnZLJ8eKTGWZ/4JUPEM9LhW8kgVGXB0ILNpFfKtxQZpu8SsmkVKjb6Tyuqb H8kkt4hF2fyAzz6C6VVwdFnGEu9+/cE+URfmsM8jUDkVBMGoJqLloanExQNDzeWZ1doN Fhb1kCT1mhIIXXTQALQrk/p1g9ePNiw8btCk3JHLy+K+xtC+p2mI0R9JAZddzbXguqBB heVay4s1OmJKNgcZ5B1FU9Z2irPNH9SWLVX9kg0bBnHXcHtS/jPOaQrfPb6f83Ed39Ij 7G91zuZI4fBu/c0jAZbgVMc/wVn4RRgDU97mdeihsQiI7b3FPJE8jgpqIFYL0t0dDRqi yf2Q== X-Gm-Message-State: AOJu0YwNbd4dA2xK85VjoiR6mbz5EnyvGMhvp9oQQtM6jNhbSZDhB2Nn dBnMvA/scsGD9dA4WQecO9NS8pemmHF+w0ex6HE9Jyra8CPQiXxNZJwVN2svh6jkC92itCKfZwX mSd2UFq0nILo2uotDiE+zIKdScY5LO6gvydKGrQti X-Gm-Gg: ASbGncvKO3pyZ+33uOAMMWDAS8K377nD2hanCZ1sHem533rDi3YviJyXK64FRQagpTx KXG8iCiIaZha2vTK6XDZR3LqsGh7xEFCOMKHxTo5vJBWCaBv5BuL6M6uLPXPdVv1lVjaAOkowzK kT7yXfK1Wxi0dfa5Akl2CpsFFKX54= X-Google-Smtp-Source: AGHT+IEpEtFXDAN02HH36ROiSEfWOfrz59vs1SxNcgUhcNZ3Ykdqp5f/yWPYv6eAXFXVIjCEhdDDy1CN7PLxqeZZ1hM= X-Received: by 2002:a17:90a:d648:b0:2fa:137f:5c61 with SMTP id 98e67ed59e1d1-2ff497a94f6mr4569747a91.12.1741156945927; Tue, 04 Mar 2025 22:42:25 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: me nefcanto Date: Wed, 5 Mar 2025 10:12:13 +0330 X-Gm-Features: AQ5f1Jpn0HlSj9ykXZV6W1mlgaoqbCf1q6lJrbKD8SuYp4RMh3E1cVbolmYiWt4 Message-ID: Subject: Re: Quesion about querying distributed databases To: Adrian Klaver Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000042a67c062f92b1d1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000042a67c062f92b1d1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Adrian Klaver, thank you for the link. I asked the AI to create a query for me using FDW. This is the sample query: with filtered_products as ( select p.product_id from products.product p where p.title ilike '%search_term%' ), category_filtered as ( select ic.product_id from taxonomy.item_categories ic where ic.category_id =3D any(array['category_id_1', 'category_id_2']) ), attribute_filtered as ( select ia.product_id from attributes.item_attributes ia where ia.attribute_id =3D any(array['attribute_id_1', 'attribute_id_2']= ) ), final_products as ( select f.product_id from filtered_products f join category_filtered c on f.product_id =3D c.product_id join attribute_filtered a on f.product_id =3D a.product_id order by f.product_id -- replace with relevant sorting column limit 50 offset 0 ) select p.* from products.product p join final_products fp on p.product_id =3D fp.product_id; The problem here is that it collects all of the product_id values from the ItemCategories table. Let's say each product is put in one category only. This means that we have 100 thousand records in the ItemCategories table. Thus, to show a list of 20 products on the website, this query first fetches 100 thousand product_id values from the remote server. That's not scalable. Is there a workaround for this? Thank you Saeed On Wed, Mar 5, 2025 at 8:12=E2=80=AFAM Adrian Klaver wrote: > On 3/4/25 20:40, me nefcanto wrote: > > Hello > > > > Consider this scenario: > > > > * 3 servers, 3 databases, each on a separate server: > > o *Products database*: Contains the *Products* table (with over > > 100,000 records). > > o *Taxonomy database*: Contains the *Categories* and > > *ItemCategories (EAV)* tables. > > o *Attributes database*: Contains the *Attributes* and > > *ItemAttributes (EAV)* tables. > > > > How do you find products based on the following criteria? > > https://www.postgresql.org/docs/current/postgres-fdw.html > > > > > 1. A search in the title (e.g., "awesome shirts"). > > 2. Selected categories (e.g., "casual" and "sports"). > > 3. Selected attributes (e.g., "color: blue" and "size: large") > > > > > > Regards > > Saeed > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000042a67c062f92b1d1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Adrian Klaver, thank you for the link. I asked the AI to create = a query for me using FDW.

This is the sample query:

with filtered_pr= oducts as (
=C2=A0 =C2=A0 select p.product_id
=C2=A0 =C2=A0 from prod= ucts.product p
=C2=A0 =C2=A0 where p.title ilike '%search_term%'=
), category_filtered as (
=C2=A0 =C2=A0 select ic.product_id
=C2= =A0 =C2=A0 from taxonomy.item_categories ic
=C2=A0 =C2=A0 where ic.categ= ory_id =3D any(array['category_id_1', 'category_id_2'])
= ), attribute_filtered as (
=C2=A0 =C2=A0 select ia.product_id
=C2=A0 = =C2=A0 from attributes.item_attributes ia
=C2=A0 =C2=A0 where ia.attribu= te_id =3D any(array['attribute_id_1', 'attribute_id_2'])), final_products as (
=C2=A0 =C2=A0 select f.product_id
=C2=A0 =C2= =A0 from filtered_products f
=C2=A0 =C2=A0 join category_filtered c on f= .product_id =3D c.product_id
=C2=A0 =C2=A0 join attribute_filtered a on = f.product_id =3D a.product_id
=C2=A0 =C2=A0 order by f.product_id -- rep= lace with relevant sorting column
=C2=A0 =C2=A0 limit 50 offset 0
)select p.*
from products.product p
join final_products fp on p.prod= uct_id =3D fp.product_id;

The problem here is that it collects all= of the product_id values from the ItemCategories table. Let's say each= product is put in one category only. This means that we have 100 thousand = records in the ItemCategories table. Thus, to show a list of 20 products on= the website, this query first fetches 100 thousand product_id values from = the remote server.

That's not scalable. Is there a workaround for th= is?

Thank you
Saeed

On Wed, Mar 5, 2025 at 8:1= 2=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/4/25 20:40, me nefcanto wrote:
> Hello
>
> Consider this scenario:
>
>=C2=A0 =C2=A0* 3 servers, 3 databases, each on a separate server:
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o *Products database*: Contains the *Product= s* table (with over
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0100,000 records).
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o *Taxonomy database*: Contains the *Categor= ies* and
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*ItemCategories (EAV)* tables.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o *Attributes database*: Contains the *Attri= butes* and
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*ItemAttributes (EAV)* tables.
>
> How do you find products based on the following criteria?

https://www.postgresql.org/docs/current/p= ostgres-fdw.html

>
>=C2=A0 1. A search in the title (e.g., "awesome shirts").
>=C2=A0 2. Selected categories (e.g., "casual" and "sport= s").
>=C2=A0 3. Selected attributes (e.g., "color: blue" and "= size: large")
>
>
> Regards
> Saeed

--
Adrian Klaver
adrian.klave= r@aklaver.com

--00000000000042a67c062f92b1d1--