Pagination Architectures: Navigating Data Without Losing Your Mind (or Users) 😜🚀

Scalable Strategies for High-Traffic Systems

Hey there, fellow coders! 👋 Ever tried to eat a pizza in one bite? 🍕 No? Then why are you forcing your users to swallow 10,000 records in one gulp? 😱 That’s where pagination swoops in like a superhero with a napkin. But not all heroes wear capes — some use LIMIT and OFFSET. Let’s break down the types of pagination, their spicy pros, stinky cons, and when to use them. Buckle up! 🚗💨

1. Offset Pagination: The Old Reliable (But Sometimes Slothful) 🐢📜

What is it?

The classic “page 1, 2, 3…” approach. It’s like telling your SQL, “Hey, skip the first 1000 pizzas and gimme the next 20!” 🍕➡️

Real-Life Problem

Imagine scrolling through an e-commerce site for “World’s Okayest T-Shirts” 👕. Page 1? Fast. Page 500? Your grandkids will finish scrolling before the page loads. 😭

Code Magic (Node.js + MySQL)

app.get('/products', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = 20;
  const offset = (page - 1) * limit;
  const [products] = await connection.query(`
    SELECT * FROM products
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
  `, [limit, offset]);
  res.json(products);
});

Pros

• Simplicity: Easy to implement. Even your cat 🐈 could write this query.
• Stateless: No need to track where users left off. They can jump to page 42 directly! 🎯

Cons

• Performance Nightmare: OFFSET 100000 makes your database cry. 😭 It literally counts all skipped rows.
• Skipped/Repeated Data: If new data arrives while paginating, you might see duplicates or missing items. “Wait, where’d that T-rex T-shirt go?!” 🦖

When to Use

• ✅ Required random page access (e.g., support dashboards)
• ✅ Small datasets (<100K rows)
• ❌ Avoid for large tables without composite indexes on sorted columns.

2. Cursor Pagination: The Speedy Gonzales of Pagination 🎯🚀

What is it?

Use a pointer (like the last item’s ID) to fetch the next set. Perfect for infinite scroll! Think of it as SQL saying, “Gimme 20 items after this T-rex mug.” ☕

Real-Life Problem

Scrolling through a social media feed 🕶️. You don’t want to see the same “I’m baby” meme 5 times because new posts shifted the offset. 😤

Code Magic

app.get('/posts', async (req, res) => {
  const cursor = parseInt(req.query.cursor) || 0;
  const limit = 20;
  const [posts] = await connection.query(`
    SELECT * FROM posts
    WHERE id > ?
    ORDER BY id ASC
    LIMIT ?
  `, [cursor, limit]);
  const nextCursor = posts.length ? posts[posts.length - 1].id : null;
  res.json({ posts, nextCursor });
});

Pros

• Blazing Fast: No OFFSET, so your DB doesn’t waste time counting skips. 🏎️
• Stable Order: New data won’t mess up your pagination. Bye-bye, duplicate memes! 👋

Cons

• No Random Jumps: Users can’t leap to page 69. It’s like a “You Must Be This Tall” ride — forward only! 🎢
• Single Column Limitation: Needs a unique, sequential column (like id or created_at).

When to Use

• ✅ High-velocity append-only data (logs, feeds)
• ✅ Eliminates duplicates in real-time streams
• ❌ Not for multi-column sorting.

3. Keyset Pagination: When Cursor Gets Fancy 🔑🎩

What is it?

Cursor’s hipster cousin. Uses multiple columns for sorting. Perfect for “Sort by price, then by rating, then by alpaca name” scenarios. 🦙

Real-Life Problem

Building a leaderboard 🏆 where users are ranked by score and registration date. Offset pagination would collapse faster than a house of cards. 🃏

Code Magic

app.get('/users', async (req, res) => {
  const { lastScore, lastId } = req.query;
  const limit = 20;
  const [users] = await connection.query(`
    SELECT * FROM users
    WHERE (score, id) < (?, ?)
    ORDER BY score DESC, id DESC
    LIMIT ?
  `, [lastScore, lastId, limit]);
  res.json(users);
});

Pros

• Efficient Sorting: Handles complex ordering like a champ. 🥇
• No Offset: Still fast for large datasets.

Cons

• Complex Queries: Writing WHERE (a, b) > (?, ?) feels like solving a Rubik’s cube. 🧩
• Client-Side Hassle: Clients must track multiple values. “What do you mean I need to remember the score AND the ID?!” 😫

When to Use

• ✅ Multi-dimensional sorting (e.g., e-commerce filters)
• ✅ Avoids offset performance penalties
• ❌ Requires deterministic sort order (no NULLs).

4. Time-Based Pagination: For the Chronically Organized ⏳📆

What is it?

Paginate using timestamps. Great for time-series data like logs or orders. “Show me all errors after 3 AM… when the devs were asleep.” 😴

Real-Life Problem

Checking your UberEats history 🍔 to find that midnight snack you regret. Scrolling day-by-day is easier than guessing page numbers.

Code Magic

app.get('/logs', async (req, res) => {
  const after = req.query.after || new Date(0).toISOString(); // 1970, baby! 👶
  const limit = 20;
const [logs] = await connection.query(`
    SELECT * FROM logs
    WHERE created_at > ?
    ORDER BY created_at ASC
    LIMIT ?
  `, [after, limit]);
  const nextCursor = logs.length ? logs[logs.length - 1].created_at : null;
  res.json({ logs, nextCursor });
});

Pros

• Intuitive for Time Data: Feels natural for logs or activity feeds.
• No Offset Issues: Smooth sailing for new data.

Cons

• Duplicates Possible: If two events have the same timestamp, chaos ensues. 😵
• Not For Everything: Terrible for non-time-sorted data. “Why is this pizza order sorted by time?!” 🍕

When to Use

• ✅ Time-range scans (logs, IoT sensor data)
• ✅ Partition pruning minimizes I/O
• ❌ Poor fit for non-temporal queries.

Conclusion: Choose Wisely, Padawan! 🧙♂️✨

• Offset Pagination: Your go-to for small datasets with random access. Just don’t use it for the Encyclopedia Galactica. 📚
• Cursor/Keyset: Infinite scroll kings 👑. Use them for social feeds, leaderboards, or anything where speed > random jumps.
• Time-Based: When you’re tracking the when more than the what. ⏰

Now go forth and paginate like a pro! And remember: if your users are happy, you can finally eat that pizza slice. 🍕😌 (Just don’t use OFFSET 1000 to find it.)