Boosting ESHOPMAN Storefront Performance: Unpacking Promotion Engine Query Optimization

At Move My Store, we understand that a fast and responsive e-commerce experience is paramount for merchants leveraging ESHOPMAN to power their HubSpot-managed storefronts. As the ESHOPMAN platform continues to evolve, our community actively identifies and addresses areas for optimization. Recently, a critical performance insight emerged regarding the ESHOPMAN promotion engine, particularly affecting stores managing a high volume of promotional offers.

The Challenge: Slowdowns with High Promotion Counts

Merchants utilizing ESHOPMAN's robust promotion capabilities, especially those with thousands of single-use coupons alongside automatic promotions, observed a noticeable slowdown in crucial storefront operations. Actions like adding an item to the cart, updating quantities, or proceeding to checkout were taking significantly longer. An internal ESHOPMAN function, responsible for computing promotion actions (computeActions), was identified as the bottleneck. Its processing time was growing linearly with the total number of promotions, rather than just the number of active, automatic promotions it needed to evaluate.

Consider these performance figures observed in a test environment:

  • 1,360 total promotions (360 automatic): 19 ms for computeActions
  • 10,360 total promotions (360 automatic): 98 ms for computeActions
  • 100,360 total promotions (360 automatic): 548 ms for computeActions

This linear scaling meant that as an ESHOPMAN store's promotional catalog grew, the customer experience on HubSpot CMS-deployed storefronts would degrade significantly.

The Root Cause: Database Query Inefficiency

A deep dive into the ESHOPMAN platform's Node.js/TypeScript codebase revealed the core of the issue within the promotion engine's database query generation. Specifically, in the module responsible for building promotion rule query filters (build-promotion-rule-query-filter-from-context.ts), two subqueries that join the promotion_application_method table were missing a crucial filter. These subqueries were intended to identify relevant application methods for automatic promotions.

The ESHOPMAN database schema, leveraging PostgreSQL, includes a partial index on promotion_application_method.promotion_id, specifically designed for performance: IDX_promotion_application_method_promotion_id_unique WHERE deleted_at IS NULL. This index allows PostgreSQL to quickly look up active promotion application methods by filtering out soft-deleted entries.

However, the problematic subqueries were not including AND am.deleted_at IS NULL in their WHERE clauses. Consequently, PostgreSQL couldn't utilize this optimized partial index. Instead, it was forced to scan through all application methods for every single promotion in the database — including thousands of inactive or single-use coupon application methods — to determine eligibility for automatic promotions. This full table scan was the primary driver of the performance bottleneck.

The ESHOPMAN Community Solution

The proposed and verified fix was elegant and highly effective: simply add the missing AND am.deleted_at IS NULL condition to the relevant database queries within the ESHOPMAN promotion engine. This small but critical change allows PostgreSQL to correctly utilize the existing partial index, drastically reducing the number of rows it needs to process.

Conceptually, the change ensures that the generated SQL queries for promotion application methods look for active records:

FROM promotion_application_method am
JOIN application_method_target_rules amtr ON am.id = amtr.application_method_id
-- ... other joins and conditions ...
WHERE am.promoti AND am.deleted_at IS NULL AND (...)

The Impact: Applying this fix brought the computeActions time for 100,360 promotions down from 548 ms to a mere 51 ms in the test environment. This represents a nearly 90% reduction in processing time for this critical operation, ensuring that ESHOPMAN storefronts remain fast and responsive even with extensive promotion catalogs.

Key Takeaways for ESHOPMAN Developers and Merchants

This insight highlights several important aspects for anyone working with ESHOPMAN:

  • Database Optimization is Key: Even with a powerful headless commerce platform like ESHOPMAN, understanding and optimizing database interactions is crucial for maintaining performance at scale.
  • Impact on User Experience: Backend efficiencies directly translate to frontend responsiveness. A slow promotion engine can significantly impact the customer journey on your HubSpot CMS-deployed storefront.
  • Community-Driven Improvement: The ESHOPMAN ecosystem thrives on collaboration. Identifying and addressing such issues collectively ensures the platform continuously improves for all users.

The ESHOPMAN team is committed to delivering a high-performance headless commerce experience. This specific optimization is a testament to the ongoing efforts to refine the platform's core functionalities, ensuring that merchants can manage their storefronts and promotions within HubSpot with confidence and speed.

Stay tuned to Move My Store for more ESHOPMAN community insights and best practices!

Start with the tools

Explore migration tools

See options, compare methods, and pick the path that fits your store.

Explore migration tools