Efficient Pagination with Cloudflare D1: Optimizing SQLite for Serverless Applications

As an SRE writing a Remix application running on Cloudflare Pages with D1 (SQLite) as a weekend project, I recently implemented pagination for blog posts. The journey taught me valuable lessons about optimizing database queries in serverless environments, where every millisecond and every row read counts.

The Challenge: Pagination Without Breaking the Bank

When implementing pagination on Cloudflare D1, I initially fell into a common trap: the traditional pagination approach that relies on COUNT queries:

-- Get total count for pagination
SELECT COUNT(*) FROM posts WHERE post_type = 'blog';

-- Get paginated data
SELECT * FROM posts WHERE post_type = 'blog'
ORDER BY post_date DESC LIMIT 10 OFFSET 20;

While this works, it's inefficient for two critical reasons. First, two database queries per page view is not ideal, which doubles the database load. Second, and this is more concerning in case of Cloudflare D1, full table scans for COUNT queries is what I wanted to avoid, as Cloudflare D1 charges based on rows read, making this expensive at scale. The EXPLAIN output confirmed my fears:

┌────┬────────┬─────────┬───────────────────────────────────────┐
│ id │ parent │ notused │ detail                                │
├────┼────────┼─────────┼───────────────────────────────────────┤
│ 8  │ 0      │ 213     │ SCAN posts USING INDEX idx_posts_blog │
└────┴────────┴─────────┴───────────────────────────────────────┘

Despite having an index, the query was still scanning the entire table of matching rows.

The Solution: Prefetch Strategy

Instead of using the classic "count total records" approach, I implemented a "prefetch strategy". By prefecthing, I mean to prefetch rows from next page(s) if available. I got an inspiration from a couple of database internal optimisation techniques to prefetch data pages into memory page caches.

How does it work? The concept is simple. I first fetche one additional record beyond what's needed for display. Then that prefetched records are used to determine if there's a next page. Here's the refined query pattern:

const DEFAULT_PAGE_SIZE = 10;
const PREFETCH_COUNT = 1;

const result = await db.prepare(`
  SELECT slug, title, description, post_date as date, tags
  FROM posts
  WHERE post_type = ?
  ORDER BY post_date DESC
  LIMIT ? OFFSET ?
`).bind(postType, pageSize + PREFETCH_COUNT, offset).all();

// Determine if there's a next page
const hasNextPage = result.length > pageSize;

// Remove the prefetched item(s) before returning to the client
const posts = hasNextPage ? result.slice(0, pageSize) : result;

This approach eliminates the expensive COUNT query, reducing database operations by 50%.

Optimizing Further with Index Analysis

Even with the prefetch strategy, performance wasn't optimal. I analyzed existing indexes:

┌───────┬──────────────────────────────────┬───────────────┬──────────┬──────────────────────────────────────────────────────────────────────────┐
│ type  │ name                             │ tbl_name      │ rootpage │ sql                                                                      │
├───────┼──────────────────────────────────┼───────────────┼──────────┼──────────────────────────────────────────────────────────────────────────┤
│ index │ idx_posts_blog                   │ posts         │ 8        │ CREATE INDEX idx_posts_blog ON posts(post_date) WHERE post_type = 'blog' │
└───────┴──────────────────────────────────┴───────────────┴──────────┴──────────────────────────────────────────────────────────────────────────┘

The problem became clear: the existing idx_posts_blog index only covers post_date with a WHERE condition on post_type. While useful for filtering posts of a specific type, it doesn't optimally support our pagination query that needs to sort by date and filter by post type simultaneously.

The solution was to create a compound index that directly maps to our query pattern:

CREATE INDEX idx_posts_pagination
ON posts(post_type, post_date DESC);

This index structure allows SQLite to filter on the post_type column, and then use the pre-sorted post_date DESC order.

Conclusion

By rethinking pagination from first principles and focusing on the specific characteristics of Cloudflare D1, I was able to implement an efficient solution that:

• Reduces database queries by 50%
• Minimizes row reads for cost efficiency
• Provides responsive pagination without full table scans
• Leverages SQLite's indexing capabilities

The biggest lesson? When working with serverless databases like D1, traditional SQL patterns often need reconsideration. The "prefetch +1" pattern for pagination is just one example of how serverless environments demand different optimization strategies than traditional databases.