Optimizing ESHOPMAN Storefront Performance: Tackling Promotion Prefilter Bottlenecks

Optimizing ESHOPMAN Storefront Performance: Tackling Promotion Prefilter Bottlenecks

At Move My Store, we're always exploring ways to enhance the performance and scalability of ESHOPMAN deployments, especially for high-volume merchants leveraging HubSpot CMS for their storefronts. A recent community discussion highlighted a critical performance bottleneck related to how ESHOPMAN processes promotions during cart operations. This insight is crucial for developers and merchants managing a large number of promotions, particularly after migrating legacy coupon systems.

The Challenge: Unexpected Database Strain from Promotion Evaluation

One ESHOPMAN user, after migrating approximately 409,000 manual code promotions (where is_automatic is set to false), observed a significant spike in database CPU usage, reaching 100% on a 2 vCPU PostgreSQL instance, particularly during cart creation and refresh operations. This was unexpected, as only a handful of their promotions were automatic.

The culprit was identified as the 'automatic promotion prefilter' query. While this query is designed to narrow down only automatic promotions for evaluation, its underlying mechanism was inadvertently scanning the rules of every single promotion – both automatic and manual – in the system. This meant that the cost of each cart refresh grew linearly with the total number of promotions, regardless of whether they were active or manual.

The core issue lies within ESHOPMAN's Node.js/TypeScript backend, specifically in the promotion module's service logic, where the computeActions method calls for automatic promotions. The SQL generated for prefiltering, particularly the anti-join subquery built by buildPromotionRuleQueryFilterFromContext, unions the rules of all promotions without restricting them to just automatic ones. This forces the database to materialize a massive dataset of rules on every call, leading to severe performance degradation.

Understanding the Technical Root Cause

The problematic query structure, simplified, looked something like this:

select "p0"."id" from "promotion" as "p0"
where "p0"."deleted_at" is null and "p0"."is_automatic" = true
and NOT EXISTS (
  SELECT 1 FROM (
    SELECT ppr.promotion_id FROM promotion_promotion_rule ppr
    JOIN promotion_rule pr ON ppr.promoti
    WHERE pr.attribute NOT IN (...)
    UNION
    SELECT am.promotion_id FROM promotion_application_method am
    JOIN application_method_target_rules amtr ON am.id = amtr.application_method_id
    JOIN promotion_rule pr ON amtr.promoti
    WHERE pr.attribute NOT IN (...)
    UNION
    ... -- additional rule types
  ) ...
)

As you can see, the subqueries within the NOT EXISTS clause do not filter by is_automatic = true, causing them to process all promotions before the outer query applies its filter. This is particularly impactful for ESHOPMAN stores with extensive coupon or referral code systems.

The ESHOPMAN Community Solution

The ESHOPMAN community identified a clear path to resolution: modify the prefilter to restrict each branch of the union within the subquery to only automatic promotions. This ensures that the database only processes relevant promotion rules from the outset, dramatically reducing the query's cost.

A proposed fix involves correlating the subquery with the outer promotion ID and explicitly joining with the promotion table to filter by is_automatic = true. This allows the database planner to use indexes more efficiently.

Here’s an example of how the subquery could be optimized:

SELECT ppr.promotion_id
FROM promotion_promotion_rule ppr
JOIN promotion p_auto ON p_auto.id = ppr.promotion_id
  AND p_auto.is_automatic = true
  AND p_auto.deleted_at IS NULL
JOIN promotion_rule pr ON ppr.promoti
WHERE ...

Applying similar logic to other rule types (target rules, buy rules) within the union ensures that the prefilter's cost scales with the number of automatic promotions, not the total count. This change yields identical results, as the outer query already filters on is_automatic = true, but with significantly improved performance.

Key Takeaways for ESHOPMAN Merchants and Developers

• Monitor Database Performance: Regularly check your ESHOPMAN deployment's database CPU and query insights, especially after large data migrations or when introducing new promotion strategies.
• Understand Promotion Impact: Be aware that even manual promotions can indirectly affect the performance of automatic promotion evaluation if the underlying queries are not optimized.
• Leverage ESHOPMAN's Headless Power: ESHOPMAN's Node.js/TypeScript architecture and Admin API provide the flexibility to implement such optimizations, ensuring your HubSpot CMS storefront remains fast and responsive.
• Community Collaboration: This issue highlights the power of the ESHOPMAN community in identifying and solving complex technical challenges, contributing to a more robust platform for everyone.

This optimization is vital for maintaining a smooth customer experience on your ESHOPMAN-powered HubSpot CMS storefront, especially as your store grows and your promotion catalog expands. Move My Store is committed to helping ESHOPMAN users achieve peak performance and scalability.