Major ecommerce and retailers are using CockroachDB to power their inventory management and other transactional workloads.See how
“Do you have any more of these? Can you check in the back?”
This customer query has been the bane of retail employees for almost as long as shopping has existed.
But what was a simple (if annoying) question to answer in the brick-and-mortar era has become quite a challenging one in the digital age. Knowing what inventory you have and where it’s located isn’t easy when you’ve got a mix of online and offline shoppers, pulling items off both retail and digital shelves all over the world in real time.
Even online-only ecommerce shops can struggle to build inventory management systems that remain accurate and effective even under heavy “Black Friday”-style loads.
Let’s take a look at what makes inventory management so challenging, and how to architect effective, resilient systems.
One of the biggest technical challenges of inventory management is keeping data consistent in real time, or as close to it as possible. Particularly with larger operations, this is vital to avoid “overselling” scenarios that can cost the company money or damage its reputation.
For example, if an item goes out of stock during a Black Friday sale and the company’s inventory system doesn’t reflect that immediately, it may be possible for dozens, hundreds, or thousands of customers to buy items that don’t exist.
Ensuring that doesn’t happen typically requires having a single source-of-truth database for inventory that can then sync with all of the various application services that require inventory data. Building and maintaining a system that is capable of selling inventory to zero but not past zero isn’t easy, especially when it has to remain highly performant even under heavy loads. For example, DoorDash references this challenge in a recent engineering blog about using changefeeds to process real-time inventory changes.
Retail trends aren’t always predictable. But even when they are, maintaining performant infrastructure without overspending means being able to scale quickly up and down so that – for example – your system has the capacity to handle the Black Friday burst, but you’re not paying for “Black Friday” capacity on a random Tuesday in January.
To reduce latency and improve the customer experience, it makes sense to locate data relevant to the customer on a database that’s close to their geographical location. However, geographically partitioning your database can make it much harder to maintain consistency, depending on the tools you’re using (more on this later), since you now also have to maintain consistency between the various regional database partitions.
Although we’ve mostly used retail sales as an example up to this point, inventory management isn’t as simple as tracking whether an item has been sold or not. Items may move from warehouse to warehouse. Items may be lost or damaged. Some items may expire. There’s also the nebulous “item in cart” period where a particular piece of inventory isn’t quite sold, but also isn’t necessarily available. To maintain an accurate inventory, everything has to be tracked.
Solving all of the above problems is certainly possible, but many of the solutions are technically complex, requiring massive investments in engineering time, training, etc. That can create an entirely new set of problems, including delays and higher-than-expected costs.
Given the above challenges – which are really just the tip of the iceberg – what should companies be looking for when selecting tooling and architecting their own inventory management solutions?
Among other things, such a system must have:
Additionally, although they may not be strict requirements for every use case, the ideal inventory management system will also be:
Achieving these goals requires a combination of well-chosen tooling and thoughtful architecture.
So how do successful retailers accomplish inventory management? First, let’s look at a big-picture example. This anonymized diagram is based on the real architecture of an online and offline retailer with billions in yearly sales revenue:
In the diagram, the purple rectangles arranged horizontally across the center represent the actions of an online shopper from login to purchase. The cylinders represent databases, and there are four of them:
Walking through the steps of a single user purchase:
This sounds relatively straightforward, but achieving this sort of flow while maintaining consistency at scale requires choosing the right tools. In the case of the company described above, their initial solution involved a number of different Cassandra databases, but the lack of consistency at scale led to them occasionally overselling products, creating a poor customer experience.
In most contexts, the goal of selling inventory is to sell to zero but never sell beyond zero – never allow customers to buy items you don’t actually have in stock. Achieving that at scale requires a database with ironclad consistency.
By switching to CockroachDB, the company in question was able to reduce their system’s complexity and take advantage of CockroachDB’s consistency without losing out on the scalability. CockroachDB’s multi-region support also enabled them to get the performance advantages associated with locating data close to their customers without adding complexity, since every CockroachDB database can be treated as a single logical database by the application, even though it is distributed (in this case) across multiple regions.
Choosing the right database is certainly important, but inventory management isn’t just about the source-of-truth database. In all likelihood, when inventory changes, a variety of different application services will need to be notified about it, and having all of them constantly querying the inventory database for updates would quickly overload it.
For example, you may have an application service that needs real-time data to make recommendations or user behavior predictions. Or you may have an application service that requires real-time data to predict customer demand and flag “hot” items that are selling faster than expected. Having these kinds of services constantly querying the inventory and customer databases will negatively impact performance.
So how can you keep all of your application services in sync with accurate inventory information without overloading the primary databases? Below is one real-world approach to solving this problem, which comes from another CockroachDB customer that has an ecommerce operation generating billions in yearly revenue:
In the above example, an application microservice updates the CockroachDB table with an inventory change (for example, that a product has been sold). Since other services need that information too, this company takes advantage of CockroachDB’s CDC feature to generate a changefeed that sends the inventory update to Kafka, which then triggers other application services and workflows, based on the logic they’ve designed.
As a hypothetical example, imagine a product sells to zero. This information should be stored in the inventory database, but a company might also want it to trigger changes in their application such as removing the now-sold-out item from search results. This architecture makes that possible without requiring those application services to bog the database down by querying it for inventory status. Instead, the status comes to them via CockroachDB’s CDC feature piping the inventory change data to Kafka.
This kind of approach can be used to keep services in sync with any inventory database regardless of the specific technologies used. While CockroachDB’s CDC feature makes creating changefeeds simple, resilient, and scalable, it’s also possible to build this kind of service into a traditional SQL database using something like a transactional outbox, although that will require quite a bit more manual work and likely won’t scale as simply.
Ultimately, there’s no single architecture that’s going to work for all inventory management use cases and setups. However, any company looking to build a scalable, resilient, and consistent inventory management system would do well to learn from the examples in this article, as both come from massive brands with architecture that has been shaped and battle-tested over millions of millions of orders.
Is CockroachDB the right database for your inventory management or retail system? Find out how major ecommerce and retail firms are using CockroachDB.
AllSaints is a 27-year-old global fashion retail business headquartered in the UK. We have brick and mortar locations …Read more
Black Friday and Cyber Monday are like the Superbowl for retailers. Instead of point spreads, though, e-commerce …Read more