Optimizing ShoppingCart: Purging Strategies for Improved Database Performance

Introduction:

OneShop, a thriving e-commerce platform operational in 10 countries, faces a significant challenge with its ShoppingCart system. Initially designed for efficiency with soft delete mechanisms, the sheer growth of data over time has led to slow query performance and a bottleneck in the system, especially in countries where the database size exceeds 25 GB. The solution to this issue requires careful consideration, given the intentional absence of cascading delete behavior in the database structure.

Problem Overview:

The ShoppingCart system was conceived to avoid hard deletes from the database. Instead, carts are marked as invalid, allowing for soft deletes. While effective during the platform’s infancy with lower traffic and fewer database entries, the current scenario demands a more optimized approach. The challenge lies in efficiently deleting unused carts without compromising application performance during live traffic, all while ensuring zero downtime. For the first-time deletion, the count of data is much bigger than expected, because we need to delete all the carts that are older than 60 days, now the data is there from the past 6 years for a few of the countries with row counts more than 10 million records. The major challenge is to delete this data first and then run a scheduler that will automatically delete all the records that are 60 days older.

Database Structure and Decision-Making:

The intentional design choice of implementing “ON DELETE NO ACTION” for all foreign keys, rather than “ON DELETE CASCADE,” was made with several factors in mind:

1. Data Integrity: Using “ON DELETE CASCADE” could automatically delete all corresponding rows in child tables when a referenced row in the parent table is deleted. This poses a risk of data loss if not handled meticulously.
2. Unintended Deletions: The fear of unintentional loss of related child records due to accidental deletion of a parent record was a significant consideration. “ON DELETE CASCADE” might trigger unintended deletions, necessitating caution in its usage.
3. Performance Concerns: Cascading deletes have the potential to impact system performance, especially in large databases. The sheer volume of deletions triggered by deleting a row in the parent table could lead to performance issues.
4. Application Logic: The dependence on specific actions or logic during data deletion in the application prompted a cautious approach. Implementing “ON DELETE CASCADE” might bypass such logic, creating potential issues.
5. Complex Relationships/Foreign Key Constraints: In databases with intricate relationships, cascading deletes might yield unexpected results. Ensuring correct foreign key relationships and avoiding circular dependencies became crucial considerations.

Proposed Approach:

1. Scheduled Off-Peak Deletions: Identify low-traffic periods and schedule cart deletions during these times to minimize the impact on live traffic.
2. Incremental Deletions: Implement a phased deletion approach, gradually removing records in smaller batches to distribute the load and prevent sudden spikes in resource usage.
3. Database Sharding: Distribute the database into smaller, more manageable units (shards) and delete carts on a shard-by-shard basis, reducing the overall impact on the system.
4. Temporary Data Storage: Consider temporarily storing data slated for deletion in a separate table before permanently removing it, allowing for easy rollback in case of unexpected issues.

We proceeded with Approach 1 & Approach 2.

Streamlining Implementation:

The implementation of our solution is divided into two key components:

Part 1: Conductor/Scheduler

Conductor (Link: Conductor) is a powerful tool that simplifies complex applications. It manages the execution flow of tasks, ensuring they are carried out in the correct order, handles retries when necessary, gracefully manages failures, and produces the final results. The beauty lies in its ability to build workflows that orchestrate task execution reliably and at scale.

Scheduled Conductor Workflows (Link: Scheduled Conductor Workflows) further enhances Conductor’s capabilities, providing a community-driven solution for scheduling workflows.

To incorporate Scheduled Conductor Workflows into your project, add the following dependency: (https://jas34.github.io/scheduledwf/)

<dependency>
    <groupId>io.github.jas34</groupId>
    <artifactId>scheduledwf-module</artifactId>
    <version>$v2.0.1</version>
</dependency>

Key Concepts:

• Workflows: Defined by a blueprint encompassing the flow of business logic, workflows consist of task configurations that dictate the order of execution and data/state transitions between tasks. Workflow Definitions also contain metadata governing runtime behavior, such as input/output parameters and timeout settings. More on Workflows
• Tasks: These are the building blocks of Conductor workflows. Each Workflow Definition must include at least one configured task. Tasks execute in sequence until the workflow is completed or terminated. More on Tasks

Sample Workflow Definition:

{
    "name": "delete-shopping-cart",
    "description": "Deletes Invalid Shopping Cart Which Are Older Than 28 days",
    "version": 1,
    "tasks": [
        {
            "name": "delete-cart-cz",
            "taskReferenceName": "delete-cart-cz",
            "type": "SIMPLE",
            "inputParameters": {
                "tenant": "cz"
            },
            "ownerEmail": "vipul.gupta@telekom-digital.com"
        }
    ],
    "schemaVersion": 2,
    "restartable": true,
    "ownerEmail": "vipul.gupta@telekom-digital.com",
    "workflowStatusListenerEnabled": true
}

Sample Task Definition:

[
    {
        "name": "delete-cart-cz",
        "description": "This task will delete inactive shopping carts of cz",
        "timeoutPolicy": "RETRY",
        "retryLogic": "FIXED",
        "retryDelaySeconds": 2,
        "retryCount": 2,
        "timeoutSeconds": 3600,
        "concurrentExecLimit": 10,
        "ownerEmail": "vipul.gupta@telekom-digital.com",
        "type": "SIMPLE",
        "taskReferenceName": "delete-cart-cz",
        "inputParameters": {
            "tenant": "${task.input.tenant}"
        }
    }
]

Sample Scheduler Definition:

{
    "wfName": "delete-shopping-cart",
    "wfVersion": 1,
    "status": "RUN",
    "cronExpression": "0 0/1 * * * ?",
    "wfInput": {
    }
}

Part 2 : ShoppingCart Query:

When it comes to ShoppingCart we have 54 tables in the ShoppingCart database where the main tables are Cart and CartItem tables.

Cart: The CART acts as a main domain that contains all items and supporting details needed for successful Product Order creation.

Cart_Item: CART_ITEM is a subdomain of ShoppingCart and it contains actions related to one Product Offering or Product (details of items added in cart).

other tables are for PRODUCT_OFFERING, PRICING, CHARACTERISTICS, and other information related to a product.

The challenge is to delete entries with foreign key relationships from all the tables. This complex web of dependencies must be understood before writing any query.

Using the specified query, we identified that the CART table has relationships with 11 other tables, and CART_ITEM has relationships with 8 different tables. These tables also have further relationships, creating an extensive network of interconnected tables.

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'db_name'
  AND REFERENCED_TABLE_NAME = 'table_name';

We wrote SQL queries considering all parent-child relationships, initially using subqueries. This method worked for tables with fewer relationships but failed with timeouts on complex relationships. We then switched to JOINs, which performed better and worked for smaller datasets. However, it failed on larger datasets, like Germany, with extensive relationships and data volume.

Exhausted, we shifted to an optimized queries approach: selecting and then deleting records from child to parent tables.

Approach :

1. We have identified all the tables and their relationship tables and plan to delete records in CART_ITEM table and to maintain referential integrity, we must consider the tables that have foreign key dependencies on the CART_ITEM table. We have used Query(1) to check the table details and identified all the tables have foreign key references to the CART_ITEM table, after the identification we have used The namedParameterJdbcTemplate.queryForList(query, paramValue) Code (2) method in Spring's NamedParameterJdbcTemplate that executes a SQL query and returns the results as a list of maps. Each map in the list represents a row in the result set, where the keys are the column names, and the values are the corresponding column values. So that we can identify all the ID’s and delete the records fromCART_ITEM (child table) then with the Parent table.

1. show create table CART_ITEM;

2. List<Map<String, Object>> result = namedParameterJdbcTemplate.queryForList(query, paramValue);

2. We have identified all the tables which are not having any relationship defined with other tables but have records present for some logic present in code like the relationship between cart items (isChildOf, isParentOf,dependsOn, Includes) which we define for addOns, tariff, and device relationship. As currently defined, without explicit foreign key constraints, deleting a record from CART_ITEM will not automatically impact the CART_ITEM_RELATIONSHIP table. The CART_ITEM_RELATIONSHIP the table will still contain references to the deleted CART_ITEM entries, leading to orphaned records and potential data integrity issues.

Given the high number of records (nearly 1 million) that need to be deleted, processing the list of IDs in batches is essential to optimize the deletion process.

So we designed a Java method to batch delete or update records in a database table using Spring’s NamedParameterJdbcTemplate. The method processes a list of IDs in chunks (or batches) of a specified size (idsSize), performing the delete/update operation in each batch until all IDs are processed. The method logs the start and end time of each batch's query execution and handles any exceptions.

Key Points:

• Batch Processing: Handles large lists of IDs in manageable chunks to avoid overwhelming the database.
• Logging: Provides detailed logs for query execution times, which can be useful for monitoring and debugging.
• Error Handling: Catches and logs exceptions, and rethrows them for non-corrupt queries.
• Parameterization: Uses MapSqlParameterSource to safely pass parameters to the SQL query.

Benefits of Batching

• Improved Performance: Smaller batches reduce the load on the database and network, often resulting in faster execution.
• Reduced Memory Usage: Handling smaller sets of data at a time is more memory-efficient.
• Better Error Handling: Errors can be more easily isolated to specific batches, making debugging easier.
• Transactional Control: Smaller transactions are less likely to cause contention and are easier to manage.

public void batchDeleteOrUpdate(List<String> listOfIds, SC_Dao_Query_Enum scDaoQueryEnum) {
    int idsSize = 300;
    int fromIndex = 0;
    int toIndex = idsSize;

    while (!CollectionUtils.isEmpty(listOfIds) && fromIndex < listOfIds.size()) {
        if (toIndex >= listOfIds.size()) {
            toIndex = listOfIds.size();
        }
        List<String> idsToDelete = listOfIds.subList(fromIndex, Math.min(toIndex, listOfIds.size()));
        MapSqlParameterSource paramMap = new MapSqlParameterSource();
        paramMap.addValue("ids", idsToDelete);

        try {
            int update = 1;
            while (update != 0) {
                Instant startTime = Instant.now();
                log.info("query execution started for batchSize: {} at: {} for query: {}",
                        idsToDelete.size(), startTime, scDaoQueryEnum.getQuery());
                update = namedParameterJdbcTemplate.update(scDaoQueryEnum.getQuery(), paramMap);
                Instant endTime = Instant.now();

                long elapsedTimeInMillis = endTime.toEpochMilli() - startTime.toEpochMilli();

                log.info("query execution ended for batchSize: {} in Milis: {} for query: {}",
                        idsToDelete.size(), elapsedTimeInMillis, scDaoQueryEnum.getQuery());
            }
        } catch (Exception e) {
            log.info("Error with Corrupt query {}", e);
            if (!scDaoQueryEnum.name().contains("CORRUPT")) {
                throw new DTTechnicalException("Shopping Cart PURGE issue {}", e);
            }
        }

        fromIndex = toIndex;
        toIndex += idsSize;
    }
}

Conclusion:

OneShop’s challenge of optimizing the ShoppingCart system involves a careful balance between database performance, application logic, and user experience. By strategically implementing a suitable deletion strategy, the platform can achieve the desired results without compromising the efficiency of its live operations.

Special thanks to the Ankit Banka at OneShop for helping me in designing and implementing these optimization strategies. His expertise and commitment have been instrumental in overcoming the challenges faced by the ShoppingCart system, ensuring a smoother and more efficient experience for our users.

Stats:

These are the Carts deleted from DB, if we talk about number of records deleted then the count is more than 100,00,000+

AT: 30109+ |HR: 119813+ | PLRB: 34389+ | HU: 41684+ | Sk: 1300000+ PL:1100000+ | PLH: 4393623+ | CZ: 243638+ | DE: 96912+ | MK: 46000+

Conductor Workflow live on production scheduled 5:40 am

Delete-Shopping-Cart workflow on production with each Task Definition

CPU Utilizatin during the process (DE)

DB connections, while scheduler is running (DE)

Implementing effective purging strategies not only optimizes database performance but also enhances the overall user experience, making your ShoppingCart system more robust and efficient.

Thanks for reading!