11st Intern's Catalog Review API Improvement Story

As an intern task on the 11st PDP Development Team, the catalog review API was improved. This API has a catalog 1:N products 1:N reviews structure overlapping into a 1-to-N^2 form, which structurally causes heavy queries and led to DB load and Read Timeout issues.

Initially, only a local cache based on Caffeine was used, but since the cache differed per server, there were situations where some WAS instances had the cache and others didn't. As a result, even identical requests would hit the heavy query again depending on the instance, causing an intermittent issue where catalog reviews wouldn't appear on refresh.

To resolve this, a global cache was added. The cache key was set as CatalogReviewListParam and the value as CatalogDetailReviews, creating a 2-layer cache structure using both local and global caches together. The request flow was then changed to check the local cache first, search the global cache on a miss, and if still not found, query the DB and load the result into both caches.

In the next step, Redis Sorted Set was utilized to pre-update frequently called catalogs. The idea was to accumulate call frequency as a score to identify popular catalogs, but there was an issue where the score only increased on local cache misses, making the data not meaningful. So the structure was changed to place the global cache ahead of the local cache, and a switch was also added to the controller to allow immediate rollback if problems occurred.

Since the network cost of the global cache still remained, tasks not directly related to the return value were switched to asynchronous processing. Comparison tests were conducted with 3 structures.

• Existing local cache structure
• Synchronous structure that hits the global cache first
• Structure that hits the global cache first but processes some tasks asynchronously

According to the measurement results, for cases where both local and global caches missed, the synchronous structure's response time of 153~175ms was reduced to 50~75ms, a 61% reduction. For cases where both caches hit, it was reduced from 30~37ms to 16~21ms, a 44% reduction.

In the end, catalog reviews are now delivered faster, the issue of missing data on refresh has decreased, and a foundation was laid to potentially increase the review count limit, which had been capped at 150. Thanks to the introduction of the global cache and automatic pre-updating based on call frequency, the frequency of heavy query execution decreased proportionally to the number of WAS instances, and DB load was reduced accordingly.