We’ve all done it. Imagine you’re browsing around the AllSaints online shop, dreaming about refreshing your fall wardrobe. You find a couple items and add them to your cart. You continue browsing but then, for some reason, you don’t check out. Instead, you close the browser tab and move on. In the e-commerce world, this is what’s known as shopping cart abandonment.
All e-commerce sites suffer from some amount of cart abandonment. According to a 2023 Baymard Institute report, the average documented rate is 70.19%. Items in abandoned carts can result in skewed inventory management data, which could limit availability and visibility to other customers, or make it difficult to determine which items are in high demand and which advertising campaigns have been successful.
There are many reasons why a user might abandon their cart. The top reason among 58.6% of US online shoppers during the time of the report was, “I was just browsing / not ready to buy.” Although many of the reasons could likely be resolved through user experience and design changes, this one could have a simple solution: clear the cart. Why not let each cart item expire after a set period of time? What if I told you CockroachDB users could add this functionality with a single SQL statement right now using row-level TTL?
Row-level TTL is a mechanism in which rows from a table are considered “expired” and will be automatically deleted once those rows have been stored longer than a specified expiration time. You might remember this from our No time to live: James Bond explains row-level time to live post following its preview release as part of CockroachDB 22.1. Row-level TTL became generally available with the CockroachDB 22.2 release and there have been some improvements.
To demonstrate the usefulness of row-level TTL for fixing the abandoned cart problem, I’ve built RoachMart. Go ahead and create a new user to try it out.
How row-level TTL solves the abandoned cart problem
RoachMart is a fictitious Cockroach Labs swag store built using Remix, CockroachDB, Prisma, and Tailwind CSS, and deployed to Vercel. It demonstrates row-level TTL by allowing a user to add products to their shopping cart and see a countdown showing when the item will expire. Once expired, the item is removed from the cart.
Feel free to take a look at the code in the GitHub repo.
At a high level, when a product is added to the cart, a new row is created in a cart items table. That table has row-level TTL applied to it. Let’s take a look at the Prisma schema for the table:
model CartItem {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
productId String @map("product_id") @db.Uuid
userId String @map("user_id") @db.Uuid
createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
expiredAt DateTime? @default(dbgenerated("NOW() + INTERVAL '15 minutes'")) @map("expired_at") @db.Timestamptz
product Product @relation(fields: [productId], references: [id], onDelete: Cascade, onUpdate: NoAction)
user User @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: NoAction)
@@map("cart_items")
}
For each CartItem row, we store a reference to the product and the user who added it. The important field when it comes to row-level TTL is the expiredAt field. First, we ensure that it’s a DateTime field of type TIMESTAMPTZ.
RELATED
Time, TIMETZ, Timestamp, and TimestampTZ in PostgreSQL
Next, you’ll notice something special for the default value. We tell Prisma to let the database handle setting the default value based on an expression that calculates the expiration date as a specified interval after the date it was added. In this case, we’re saying that items should expire 15 minutes after being added to the cart. (Yes, that’s a short time, but this is a demo and I’m impatient.) Now, changing the expiration interval for future items only requires updating the default value of the expiredAt field in the Prisma schema or executing the following SQL statement:
ALTER TABLE cart_items ALTER COLUMN expired_at SET DEFAULT NOW() + INTERVAL ‘7 days’;
You can learn more about the INTERVAL data type in the CockroachDB Docs.
Now that we have a field defining when an item should expire, we can configure row-level TTL to use it. Because Prisma doesn’t have native support for row-level TTL, we’ll configure it after the database has been created by executing the following SQL statement:
ALTER TABLE IF EXISTS cart_items SET (ttl_expiration_expression = 'expired_at');
It’s important to note that items are only eligible to be deleted once the expiration date has passed and may not be removed until the background job executes. By default, this job runs hourly, but can be figured by updating the ttl_job_cron storage parameters. Check out the TTL storage parameters docs for more ways to configure row-level TTL. Because of this, you’ll want to filter out any expired items. Here’s an example using Prisma:
const items = await prisma.cartItem.findMany({
orderBy: { createdAt: "asc" },
select: {
expiredAt: true,
id: true,
product: {
select: { imgUrl: true, name: true, price: true }
},
productId: true
},
where: { expiredAt: { gt: new Date() }, userId }
});
The database does the res
And that’s it! The database will take care of the rest. Now, is this the only way to do it? Nope. For this example, we wanted to show a countdown so it was important to store the actual expiration date. Row-level TTL only needs a date field and you can use one that already exists. The ttl_expiration_expression parameter will accept more than just a field name — it will take, well, an expression. Say you’d like rows to be removed 30 days after they were created and your table already has a created_at field of data type TIMESTAMPTZ. You can use the following SQL statement to set the ttl_expiration_expression parameter:
ALTER TABLE IF EXISTS some_table SET (ttl_expiration_expression = '((end_date AT TIME ZONE ''UTC'') + INTERVAL ''30 days'') AT TIME ZONE ''UTC''');
Sometimes the simplest answer is the best answer. If you have a problem with abandoned cart items, delete them. If you’re using CockroachDB, you can do this painlessly with a few SQL statements. The best part is that no code changes are required.
This is just one of the many use cases for row-level TTL. How could it help improve your application?
Did you enjoy the demo? Want to watch me create more? Follow me on Twitch and get notified when I go live every Thursday at 3 PM ET.
