DEV Community: Elastic Path

Improving paging performance with large data exports.

James Luterek — Thu, 24 Aug 2023 15:27:50 +0000

My work with Elastic Path involves helping companies make amazing ecommerce experiences, but many of these companies are huge enterprises with massive amounts of data. The challenge of handling huge record sets is common and not specific to the ecommerce world. For ecommerce it is often a large customer base or ever increasing list of historical orders, but other fields deal with the same problem of data just in different way. While databases today have become much better at handling this level of data requirements, the need to export or synchronize these records with other systems can still create problems. Elastic Path is API-first, so it’s easy to say, just build an API, but sending all order data in a single request is impossible. Paging is required to make things work, but not all paging is created equally.

The initial approach to building pagination is typically to leverage the systems already built into the database of choice. For MongoDB that means using offset paging with skip() and limit().

This allows each query to skip a predetermined number of records to reach a particular page and then limit the results to that specific page size.

Here is an example of retrieving page 3, with a page size of 10 records:

mongodb // MongoDB query example for offset-based paging db.records.find().skip(20).limit(10);

MongoDB has a default limit of 10,000 records when using skip. This is not an intrinsic limitation of MongoDB, but is a practical limit to ensure good performance and to ensure memory and processing power is not monopolized by paging. Having this limit ensures that paging does not negatively affect the systems overall performance.

When you use offset-based pagination, MongoDB needs to skip a certain number of records to retrieve the desired page of results. However, as the offset value increases, the database needs to perform more work to skip over the specified number of records. This can lead to slower query performance and potential resource exhaustion, especially with large offset values.

Queries become progressively slower as the offset increases, leading to performance degradation.

This is not limited to MongoDB or document databases, PostgreSQL, MySQL, and other databases will also see performance degradation with very large values passed to LIMIT.

While this approach works well for small numbers of records or in cases where you will only show the first few pages of results it proves inefficient for large databases and data exports.

Solution: Cursor-Based Pagination

Cursor-based paging takes a different approach to splitting a dataset into pieces. This takes a bit more setup but can offer better performance when at higher values. Cursor-based paging leverages a unique identifier, the cursor, to navigate through the dataset. Unlike offset paging, where the skip value can lead to deteriorating performance over time, cursor-based pagination relies on a cursor that points directly to a specific record. This ensures consistent and predictable performance.

Cursor-Based Pagination Steps:

Identify your cursor, choose a field that is unique and sortable.
For the first page of results, simply sort by this cursor and limit the number of records.
Grab the last item from this page and make note of the cursor value.
For the next page, include a filter that records must have a cursor greater (or less depending on sort) than the value from step 3.

This approach leverages the database indexes to improve performance so it’s important that the database is configured in a way to optimize the query pattern.

Index the Cursor Field: The field used for the cursor, often an increasing or unique identifier, should be indexed. This helps in efficient retrieval of records based on the cursor value.
Avoid Complex Queries: Keep cursor-based queries simple. Complex queries can hinder the database's ability to optimize and use indexes effectively.
Use Compound Indexes: If your cursor is based on multiple fields, consider using compound indexes, while not ideal it’s better than no index at all.
Regularly Monitor Index Usage: Keep an eye on the indexes' usage and performance. Unused or redundant indexes can impact overall database performance.
Balance Read and Write Operations: Be mindful of the trade-off between read and write performance when adding indexes. Over-indexing can slow down write operations.

Here is an example MongoDB query using this pattern:

mongodb // MongoDB query example for cursor-based pagination db.records.find({ _id: { $gt: lastProcessedId } }).limit(10);

Benefits

The advantages of cursor-based pagination over traditional offset methods are multifaceted. By ensuring consistent and predictable performance, this approach proves highly effective in managing large datasets. It circumvents challenges related to data gaps and duplicate entries that can plague offset paging. Moreover, cursor-based pagination's elimination of record skipping streamlines the processing of new data, enhancing both efficiency and reliability.

If you want to see how this can be put in practice with an API-First solution you can see the Elastic Path documentation.

Learn the foundations of

Composable Architecture

Free Course

Syncing Inventory: Incrementing vs. Overwriting

James Luterek — Wed, 05 Jul 2023 13:29:57 +0000

Tracking inventory in the world of digital commerce is crucial for both customers who expect their items delivered and business who need to ensure items do not sit in the warehouse unavailable for purchase. While tracking a simple count appears to be a basic programming task, the reality involves multiple systems from an e-commerce system to an ERP (Enterprise Resource Planning), each reading and writing values at high volumes, it is essential to employ the right approach to ensure data integrity. This type of high-throughput system requires additional care, the correct programming approach, and back-up solutions like safety stock values to avoid major pitfalls such as race conditions.

Scenario: Let's consider a scenario where an ERP is the source of truth for inventory data, handling receiving and fulfillment. In addition to the ERP there are multiple customer touch-points including traditional ecommerce, a mobile application, and in-store functionality which can sell the items in inventory. The ERP can be slow and handles many functions, so to remove the burden of constant inventory checks, the inventory levels are synced to an API-based online inventory system that can serve the customer touchpoints. When an order is placed, the online inventory is decreased and the order is sent to the ERP, processing the order in the ERP decrements the master inventory level. In this dynamic environment, simultaneous updates to inventory quantities can occur, and it becomes crucial to handle these updates in a manner that maintains data accuracy and prevents conflicts.

The first approach may be to simply copy the inventory values from the ERP into the online inventory system. In order to keep things up to date, the values may be copied in a CRON job running every 5 minutes. On first launch this appears to work well and in low-volume systems may continue to perform as required. However, as volumes increase values will quickly become inaccurate leaving gremlins in the code, bugs that are highly situational and almost impossible to reproduce. The problem is that syncing data is never instantaneous and with so many events firing it becomes likely for changes to take place after the inventory value is retrieved, but before it is fully synchronized.

Incrementing or Decrementing Values for Syncing: Instead of overwriting the inventory count with a new number during syncing, a more reliable approach involves incrementing or decrementing the existing value. This method ensures that each inventory update considers the changes made by other processes, reducing the risk of data inconsistencies and race conditions. So, a well-designed inventory API will accept an increment or decrement integer which will be applied to the existing quantity rather than taking the quantity as a direct value. You can see an example API on the Elastic Path docs website.

Incrementing or decrementing values offers several advantages:

Data Integrity: By incrementing or decrementing values, existing data remains intact, reducing the risk of data loss or discrepancies during inventory updates.
Concurrent Updates: In a dynamic e-commerce environment, multiple systems or users may simultaneously update inventory counts. Incrementing or decrementing values allows for sequential application of updates, preventing conflicts and maintaining the accuracy of stock levels.
Audit Trail: Incrementing or decrementing values provides an audit trail of inventory changes. Each transaction or adjustment made to the inventory count can be tracked, enabling better accountability, error analysis, and identification of discrepancies.
Granularity: Incrementing or decrementing values allows for granular tracking of individual item changes. This detailed record of added or removed items facilitates better traceability and analysis of inventory fluctuations.
Historical Reporting: Incrementing or decrementing values preserves historical data, enabling the generation of accurate reports based on past inventory levels. This information is invaluable for analyzing trends, forecasting demand, and making informed business decisions.

Even taking this approach there is still the potential for syncing delays. Inventory may leave the warehouse, but also be sold before the sync is completed, causing an over-sold situation. To combat this issue, it may be beneficial to access the master inventory record during checkout to verify the inventory levels. However, since this process is slow it should only be done when absolutely necessary, this is the role of Safety Stock Values.

We can track a minimum safe quantity in our online system, if the quantity shown is below this value during checkout the system should verify with the ERP. The easy way to handle this requirement is to set a single value for the entire system. A more robust approach is to store the safety stock value within each inventory entry and calculate the necessary value based on recent sell-through of the specific SKU. This is possible with an API system that allows for custom fields, but can be more difficult with a more rigid ecommerce platform.

While this example is tied directly to ecommerce, the approach can be applied to any distributed system where multiple processes need to read, update, and cache a single value. Choosing an increment/decrement approach allows for better syncing by tracking changes instead of point-in-time values and additional business logic can be created to mitigate issues from syncing delays.

Learn the foundations of

Composable Architecture

Free Course

Creating "Buy-Again" Purchase Flows

James Luterek — Wed, 28 Jun 2023 16:03:18 +0000

Learn the foundations of

Composable Commerce Architecture

Free Course

In this tutorial, we will walk you through the process of enabling "Buy-Again" purchase flows streamlining the ordering process for customers who have large high-value carts and make standard, repeat orders. The purpose of this functionality is to streamline the purchasing process for your customers, increasing the likelihood of repeat business and building customer loyalty.

By leveraging the power of Elastic Path Commerce Cloud, an API-first commerce solution, adding this type functionality is straight forward and easy to create. The flexibility of APIs allows companies to build unique features that feel seamless to end-users. Enabling custom features like "Buy-Again" can be added without disrupting the user interface, requiring extensive plugin coding, or destabilizing and slowing down the overall system by adding custom code to the core. Instead the functionality can be added directly to the front-end without the use of iframes or other hacks, and the logic can be executed with a small amount of JavaScript calling the APIs.

Step 1: Place an Order

Before a customer can buy again, they must create and place a new order. The customer creates their first cart and completes the checkout process, this order and all details are then stored in their order history and accessible via API. This information can then be retrieved later when the customer needs to "Buy-Again”.

Step 2: Identify order to Repeat

The next step is for the customer to select the order they want to repeat, this step is traditionally taken from the order history section in an account profile, but the freedom of an API-First approach means it can happen from any location, even a link from the past order confirmation email or a reminder text. The import aspect is that the customer has an easy wait to find and identify the order they want to repeat.

sequenceDiagram
  Customer->>Profile: View Profile
  Profile->>Order History: Retrieve Order History
  Customer->>Order History: Selects Order to Repeat

Step 3: Order->Cart

Create a Function to Query the Selected Order
While the normal process is for a cart to be converted to an order, the similar schema mean the opposite is also possible and easy to accomplish. At this point, code will be called that retrieves the order and leverages the details to populate either a new or existing cart. Since pricing and inventory may have changed, rather than copy all details, instead we can simply extract the line-items from the order and add them to the user’s existing shopping cart, if no cart exists, create a new one.
Here's some example code:

const MoltinGateway = require('@moltin/sdk').gateway;

const Moltin = MoltinGateway({
  client_id: 'YOUR_CLIENT_ID',
  client_secret: 'YOUR_CLIENT_SECRET',
});

async
function repeatOrder(orderId) {
  // Get the order details
  const orderDetails = await Moltin.Orders.Get(orderId);

  // Extract line items
  const lineItems = orderDetails.data.line_items;

  // Get or create the user's cart
  const cart = await getOrCreateCart();

  // Add line items to the cart
  for (const item of lineItems) {
    await Moltin.Cart(cart.id).AddProduct(item.product_id, item.quantity);
  }
}

Step 4: Customer Reviews the Cart and Makes Changes (Optional)

Once the items from the previous order have been added to the cart, the customer can review their cart and make any necessary changes.

sequenceDiagram
  Customer->>Cart: Review Cart
  Customer->>Cart: Make Changes (Optional)

Step 5: Checkout

Finally, the customer proceeds to the standard checkout flow, completing their "Buy-Again" purchase.

sequenceDiagram
  Customer->>Checkout: Proceed to Checkout
  Checkout->>Order Confirmation: Complete Purchase

Summary

Making it easy for customers to quickly repeat their orders can increase sales and increase customer loyalty by reducing friction and improving the user experience. Unique features like this "Buy-Again" purchase flow can boost a company’s competitive advantage..

As demonstrated, the flexibility of Elastic Path Commerce Cloud’s API-first approach allowed us to implement this feature with only small and seamless front-end modifications. There was no need for complex plugins, extensions, or learning new coding techniques as it was all standard JavaScript.

Here's a diagram of the entire process:

flowchart TB
  A[Customer Makes Original Purchase] --> B[Customer Selects the Order to Repeat]
  B --> C1[[Query the Order JSON]]
  C1 --> C2{Cart Exists?}
  C2 -->|NO| C3[Create Cart]
  C2 -->|YES| C4
  C3 --> C4[[Add Line-Items to Cart]]
  C4 --> D[Customer Reviews the Cart and Makes Changes]
  D --> E[Customer Goes Through the Standard Checkout Flow]

Learn the foundations of

Composable Commerce Architecture

Free Course