Btukfyl

I'm seeking for assistance on the decision of the best database, be that relational one or not, as well as the best schema for the given task.

The idea is the following: there is an offline store network with multiple stores (~10). Each store has multiple products (~100000), that are shared between them, but the availability and the prices/discounts differ. The products are structured into hierarchical categories (~1000). Some categories can be discount-categories. Some aren't. Some products may be adult-only. Products have various attributes.

The required queries:

• Get full categories tree (limited by a nesting level) for a given store with products currently available (filtered by adult flag).


• Get some subtree by given category ID for a given store with products currently available.


• Get a tree for discounted-only categories for a given store with products currently available and discounted.


• Get the paginated list of currently available products for a given store in a given category including subcategories, filtered by adult.


• Get the single product details with full attributes list.


• Filter products by attributes.

Current solution is built on top of Oracle Database with the following schema:

Tables: stores, products, categories (hierarchy via MPTT), products_categories (references products and categories), productprices (references stores and products), attributes (references stores), productattributes (references attributes and products), attributevalues (references productattributes).

It sort of works, but starts to get slow. For example the query to get the categories with available products sometimes executes for less than a second, and sometimes it takes more than 30, depending on the data currently imported in the database.

The query in question:

SELECT "categories".* FROM "categories"

WHERE (

  NOT ("categories"."external_id" = '_reserved' AND "categories"."external_id" IS NOT NULL)

  AND "categories"."is_promo" = 0

  AND "categories"."begins_on" <= to_timestamp('2018-11-27', 'YYYY-MM-DD')

  AND "categories"."ends_on" >= to_timestamp('2018-11-27', 'YYYY-MM-DD')

  AND (EXISTS(

    SELECT U0."id" FROM "productprices" U0

    INNER JOIN "products" U1 ON (U0."product_id" = U1."id")

    INNER JOIN "products_categories" U2 ON (U1."id" = U2."product_id")

    WHERE (

      U2."category_id" = ("categories"."id")

      AND U0."updated_at" >= to_timestamp('2018-11-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

      AND U0."store_id" = 42

      AND U0."begins_on" <= to_timestamp('2018-11-27 09:00:00', 'YYYY-MM-DD HH24:MI:SS')

      AND U0."ends_on" >= to_timestamp('2018-11-27 09:00:00', 'YYYY-MM-DD HH24:MI:SS')

      AND (U0."discount" IS NOT NULL OR U1."has_special_offer" = 1)

      AND U1."is_adult" = 0))

  )

)

ORDER BY "categories"."tree_id" ASC, "categories"."lft" ASC

We're currently about to start migrating away from Oracle Database. The primary app database is going to be Postgres, but for a catalogue part of application I'm willing to take a look at the different storage. Or maybe I should just optimize the schema/queries?

Currently there are 15 stores, with ~4 stores added yearly.
There should never be more than 200 000 products, as far as I know. With 25 stores it limits the prices table by 5 000 000 rows for a foreseeable future.

From what I know, several million rows is OK for Postgres. I would not bother migrating to some NoSQL solution. Instead, focus on optimizing (proper model, indexes, etc) and sort out the imports you mentioned (prevent long locks, large transactions, etc). The query you sent should be easy to index (use partial indexes on Postgres). That's what I would do.