👋 Hey friends,

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

• Snippet from The Backend Lowdown

• Weekly video highlight: Triage Quiz

• Book recommendation: Google SRE

Let’s get into it 👇

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.

OFFSET Pagination Solutions (Continued)

Solution 4: Optimizing OFFSET When You're Stuck With It

Sometimes you can't change the pagination method due to legacy code, API contracts, or stubborn stakeholders. Here's how to make OFFSET less painful:

1. Use covering indexes:

-- Instead of reading the full table rows
CREATE INDEX idx_products_pagination 
ON products(created_at DESC, id DESC) 
INCLUDE (name, price, status);
​
-- The database can now satisfy common queries entirely from the index
-- without touching the actual table data

2. The seek method (OFFSET hybrid):

-- Instead of: SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
​
-- Do this:
SELECT * FROM products 
WHERE created_at <= (
  SELECT created_at FROM products 
  ORDER BY created_at DESC 
  LIMIT 1 OFFSET 10000
)
ORDER BY created_at DESC
LIMIT 20;

This approach runs two queries but the subquery only fetches one column from the index, making it much faster than fetching all columns for 10,000 rows.

3. Cache common pages aggressively:

class ProductsController < ApplicationController
  def index
    page = params[:page].to_i

    if page <= 10 # Cache the first 10 pages
      @products = Rails.cache.fetch("products/page/#{page}", expires_in: 5.minutes) do
        Product.page(page).to_a # to_a forces evaluation
      end
    else
      @products = Product.page(page)
    end
  end
end

Weekly Video Highlight: Triage Quiz

A few days ago I posted yet another triage quiz! I thought it was a good one and it got a pretty good response. What happens when logs start going out of control? What do you do to fix it? See if you can determine the root cause and pick the right solution before I reveal it! Check out the video here:

@stevencodes.swe

3AM on call: 503s but CPU and memory are flat. Logs/sec are 20x, nodes in DiskPressure. Whodunnit? What’s your move? #techtok #coding #pro... See more

Note: I haven’t posted this one to Instagram yet, but I will soon!

Book Recommendation: Google SRE

Yesterday I posted a video recommending one of the best books I’ve ever read in my career: Google’s SRE book. This book really opened my eyes to some very important concepts like SLOs, SLIs, error budgets, and killing toil, among other things. It’s not a super new book, but it’s still very relevant today and is chock full of gold nuggets. If you haven’t read it yet and are looking to level up your backend mentality especially with regard to reliability and operating a system at scale, do yourself a favor and pick up this book. You won’t regret it. Best of all, it’s totally free online!

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