I have an index (products) of all our available products. These products all have unique groups of attributes (like, size, or color, etc).

I want to allow my users to search through their purchase history, finding all the products they’ve previously purchased, and allow them to utilize the product attributes to filter those results.

My product count is 20k and growing at about 10k-20k per year.

How should I structure my index to facilitate this type of search query?

Adding and index with records for every userID_productID for everything they’ve ever purchased seems unscalable, cost-wise. As my users purchase several thousand of my products, each, which scales my record count into the 10s of millions.

I think the number of buyers for any given product will exceed the allowed size of the record, if i add all the purchasing user ids to an attribute on the product.

I also think that pushing up the list of purchased products to the api for the query would be too large as well (think select * from product where <filter requrements> and product_id in (<list of all product ids user has ever purchased>).

I feel like I’m missing something here, to connect these two queries.

Any insight would be appreciated. Thanks!