[Bug]: Major performance problems when product_sales_channels mapping table grows

[Edsparr]

Package.json file

{
  "name": "medusa-starter-default",
  "version": "0.0.1",
  "description": "A starter for Medusa projects.",
  "author": "Medusa (https://medusajs.com)",
  "license": "MIT",
  "keywords": [
    "sqlite",
    "postgres",
    "typescript",
    "ecommerce",
    "headless",
    "medusa"
  ],
  "scripts": {
    "build": "medusa build",
    "seed": "medusa exec ./src/scripts/seed.ts",
    "start": "medusa start",
    "dev": "medusa develop",
    "test:integration:http": "TEST_TYPE=integration:http NODE_OPTIONS=--experimental-vm-modules jest --silent=false --runInBand --forceExit",
    "test:integration:modules": "TEST_TYPE=integration:modules NODE_OPTIONS=--experimental-vm-modules jest --silent --runInBand --forceExit",
    "test:unit": "TEST_TYPE=unit NODE_OPTIONS=--experimental-vm-modules jest --silent --runInBand --forceExit"
  },
  "dependencies": {
    "@medusajs/admin-sdk": "latest",
    "@medusajs/cli": "latest",
    "@medusajs/framework": "latest",
    "@medusajs/medusa": "latest",
    "@mikro-orm/core": "5.9.7",
    "@mikro-orm/knex": "5.9.7",
    "@mikro-orm/migrations": "5.9.7",
    "@mikro-orm/postgresql": "5.9.7",
    "awilix": "^8.0.1",
    "pg": "^8.13.0"
  },
  "devDependencies": {
    "@medusajs/test-utils": "latest",
    "@mikro-orm/cli": "5.9.7",
    "@swc/core": "1.5.7",
    "@swc/jest": "^0.2.36",
    "@types/jest": "^29.5.13",
    "@types/node": "^20.0.0",
    "@types/react": "^18.3.2",
    "@types/react-dom": "^18.2.25",
    "jest": "^29.7.0",
    "prop-types": "^15.8.1",
    "react": "^18.2.0",
    "react-dom": "^18.2.0",
    "ts-node": "^10.9.2",
    "typescript": "^5.6.2",
    "vite": "^5.2.11"
  },
  "engines": {
    "node": ">=20"
  }
}

Node.js version

22.9.0

Database and its version

Postgres 17

Operating system name and version

Windows 11 Pro - 10.0.22631 version 22631

Browser name

Chrome

What happended?

When populating the database for a benchmark on Medusa with these parameters I found severe performance degradation:

• 50k products
• 15k categories
• 40 sales channels
• 40 mappings per product

The response times were lightning fast when not using any sales channels mapping.
Worst case scenario I could get 10+ seconds load times for a normal product get. I pinpointed it to be related to sales channels. Response times were specifiaclly related to:

• How many sales channels the published key was assigned to
• How many channel mappings existed (load times dropped signifiacntly when only using 10 mappings per product)

The gist contains my raw code. You can also just create the mappings by yourself to reproduce it.

Expected behavior

Load times should not be affected this significantly

Actual behavior

Load times was affected significantly

Link to reproduction repo

https://gist.github.com/Edsparr/7a914bfb5f50e93cc5e0608a5d1f76ef

[Edsparr]

More information:

From the zipkin tracer I can see that in the extreme case (and less extreme case too, just less visible) that the remoteQuery into sales_channels link is the performance killer.

It appears that this middleware link filter: https://github.com/medusajs/medusa/blob/develop/packages/medusa/src/api/store/products/middlewares.ts#L34

in turn call this remote query https://github.com/medusajs/medusa/blob/develop/packages/core/framework/src/http/utils/maybe-apply-link-filter.ts#L43
which produces these results

[olivermrbl]

@Edsparr, thanks for the report and for sharing the results of your load testing. It's always interesting to see such findings.

We are aware of the performance issues related to cross-module filtering, such as when you filter products by sales channels and are working on a search engine aimed at solving it. We expect to be able to share more early next year.

I'll close this issue for now, since this is a known limitation and we have a planned solution. Feel free to open this as a GitHub Discussion, if you want to open up a discussion around it.

I'll make sure to ping here when we know more about the release date of the search engine :)

[Edsparr]

@Edsparr, thanks for the report and for sharing the results of your load testing. It's always interesting to see such findings.

We are aware of the performance issues related to cross-module filtering, such as when you filter products by sales channels and are working on a search engine aimed at solving it. We expect to be able to share more early next year.

I'll close this issue for now, since this is a known limitation and we have a planned solution. Feel free to open this as a GitHub Discussion, if you want to open up a discussion around it.

I'll make sure to ping here when we know more about the release date of the search engine :)

Thank you for your detailed response Oliver!

I am curious if v2 have any other known performance bottle necks at the moment? If so, what are them and when can we expect them to be resolved?

I am currently evaluating Medusa v2 for my company (8M euro revenue for reference around size) and while I enjoy it personally I am worried about Medua V2's immaturity. From a business perspective it's hard to justify investing heavily into a completely new platform. It might have deep flaws that I won't discover until later. From a helicopter perspective, I am also worried about the linking api which I find restrictive. Are there any plans to allow for more flexibility? Would love to hear some of your thoughts around my concerns!

[BpJoshi-solveda]

Is this resolved ?

[Edsparr]

Is this resolved ?

I do not believe it is yet. Would love to hear some updates around it @olivermrbl
I know it is possible to work around but would be lovely to be able to use this feature natively.