👋 Hey friends,

Here’s what I’ve got for you this week:

• Creator Update: PSA - imposter warning

• Snippet from Chapter 3 of The Backend Lowdown

• Community Corner: sharing my visuals

• War Story: Coupon Code Meltdown

Let’s get into it! 👇

Creator Update: PSA - Imposter Warning

A few people brought it to my attention that someone set up another TikTok account with a lookalike handle using 2 dots instead of 1. First, I’m very grateful to those who let me know. That meant a lot to me! Second, please know that @stevencodes.swe (1 dot) is my only TikTok account and that other account is not me. I have reported them to TikTok, but apparently that’s all I can do. Please stay safe!

The Backend Lowdown: Chapter 3 Preview

Pagination Performance Cliffs

The most dangerous performance problems are the ones that work perfectly in development. Pagination is the poster child for this category: your local dataset of 100 records pages beautifully, while your production database with 10 million records is slowly dying as users browse deep into your result sets.

What Goes Wrong

OFFSET/LIMIT pagination looks deceptively simple and works exactly as you'd expect until you start dealing with many records:

-- Page 1: Lightning fast
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Execution time: 2ms
​
-- Page 500: Database destruction  
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Execution time: 800ms

The Hidden Cost of OFFSET

Here's what your database actually does with OFFSET 10000:

1. Builds the full result set from the beginning

2. Sorts all those rows according to your ORDER BY

3. Counts and discards the first 10,000 rows

4. Finally returns rows 10,001-10,020

You're forcing the database to do 99.8% wasted work to return 0.2% of the results.

Think of it like reading a book by starting from page 1 every single time, counting pages until you reach page 500. It doesn't matter that you have a bookmark, OFFSET pagination throws it away and starts counting from the beginning.

Community Corner: Sharing My Visuals

I recently started adding animations to my videos and there have been a lot of you asking about how I make them and to share them, so I figured I’d share this out here! The tools I use are:

• Excalidraw to represent some of the data structures

• ChatGPT / DALLE for a lot of the static images

• Anthropic models for the animations

I prompt the LLMs to generate the assets, but as with any LLM right now, it’s hit or miss and sometimes I have to give up or do something else. It’s also quite time consuming as they usually require a fair bit of tweaking.

If you’re curious to see some of the animations, I’ve created a GitHub repo to start sharing them out. Check it out here: https://github.com/stevencodes-swe/video-visuals

I’ll be sure to keep adding them and will continue adding those from past videos. Some of them are interactive and fun to play with!

War Story: Coupon Code Meltdown

9 AM sharp, the Black Friday promo hits. Ads are humming, traffic is surging, and our Rails checkout API is holding steady, for a little while. New Relic lights up: 500 errors on POST /checkout rocket from 0 -> 15% in under three minutes. Latency jumps from 250 ms to 4 s.

The red-herring fixes

• We doubled Puma workers and added pods - CPU usage fell, but errors didn’t.

• Bumped DB pool size - it cleared ActiveRecord queueing, but p95 was still at 4 seconds.

In our APM, every flame-graph sample funneled into one Postgres call:

SELECT "coupons".*
FROM   "coupons"
WHERE  LOWER("coupons"."code") = $1
LIMIT 1;

The real culprit: uniqueness validation

Days earlier Marketing demanded case-insensitive coupon codes, so someone added this to app/models/coupon.rb:

validates :code, uniqueness: { case_sensitive: false }

ActiveRecord enforces that by issuing LOWER(code) = ? queries whenever a coupon is created or updated. Our checkout flow increments a redemptions_count column inside a transaction, so every purchase triggered that validation-lookup. Under normal load it was fine; under Black Friday traffic, the lookup devolved into a sequential scan because we only had a plain btree (code) index. Tens of thousands of shoppers meant tens of thousands of seq scans contending for the same rows until Puma hit its request timeout and threw 500s.

The 15-minute rescue

1. Add the right index (concurrently):

CREATE UNIQUE INDEX CONCURRENTLY idx_coupons_lower_code ON coupons (LOWER(code));

2. Drop the old plain index once the new one finished.

3. Delete the Rails-level validation and let the database’s unique functional index handle truth.

4. Optional hardening (we did this later): convert code to Postgres CITEXT to make future queries naturally case-insensitive.

The moment the index went "VALID", seq scans vanished and p95 checkout latency nosedived back to normal levels.

Takeaways

• Database > model validations for uniqueness, especially on hot paths.

• If you query on a transformation, index the transformation or use citext.

• Load testing must mirror promo behavior. Our staging run never hit case-insensitive coupon redemptions at scale.

• Watch your flame graphs; they pinpointed the hotspot long before we pieced together the narrative.

That’s a wrap for this week. If something here made your day smoother, feel free to reply and tell me about it. And if you think a friend or teammate would enjoy this too, I’d be grateful if you shared it with them.

Until next time,
Steven