Subject: Re: Quesion about querying distributed databases

From: Adrian Klaver <[email-masked]>
Date: Tue, 4 Mar 2025 20:42:08 -0800

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
[email-masked]

---

From: me nefcanto <[email-masked]>
Date: Wed, 5 Mar 2025 10:12:13 +0330

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 = any(array['category_id_1', 'category_id_2'])
), attribute_filtered as (
    select ia.product_id
    from attributes.item_attributes ia
    where ia.attribute_id = 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 = c.product_id
    join attribute_filtered a on f.product_id = 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 = 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