Intro

While working on a side project that captures and manages audit logs, I had to implement pagination to the endpoint that is supposed to retrieve the existing log entries, let’s call it /api/v1/events.

Returning all audit log entries wasn’t and neither was offset-based pagination due to the large volume of data and the rate at which said data was expected to grow.

Potentially returning gigabytes of data at once was simply not practical, and using offset-based pagination would have led the endpoint to:

• Force the database to perform full table scans in order to skip rows as the page number change:
  • Page 1: SELECT * FROM events ORDER BY occurred_at DESC LIMIT 25 OFFSET 0
  • Page 2: ... LIMIT 25 OFFSET 50
  • Page 30: ... LIMIT 25 OFFSET 750
• Return inconsistent results per page due to the high rate of writes to the events table:
  • User requests page 1 SELECT * FROM events ORDER BY occurred_at DESC LIMIT 25 OFFSET 0;
  • New 25 events are inserted before the user clicks requests page 2;
  • User requests page 2 SELECT * FROM events ORDER BY occurred_at DESC LIMIT 25 OFFSET 25;
  • Query returns the same results as those from the page 1 because 25 new events have been inserted, and the query is ordering by the column occurred_at DESC;

Cursor pagination it is

Users of the endpoint /api/v1/events do not expect to be able to jump to exact pages, and the id column from the events table stores a ULID, making the column sortable.

With those requirements in mind I just needed to express the pagination API. Good engineers copy, and Stripe’s API is one of the best there is in terms of design, so I went to take a look at theirs:

1# Go to the next page
2/v1/payment_intents?limit=25&starting_after=<id-of-the-last-payment-returned>

1# Go to the previous page
2/v1/payment_intents?limit=25&ending_before=<id-of-the-first-payment-returned>

1{
2  "data": [],
3  "has_more": true
4}

starting_after and ending_before in SQL lingo

The query parameter starting_after takes the id of the last item returned by the endpoint instruct the database to only query events that come after the given id in descending order:

1SELECT * FROM events WHERE id < ? ORDER BY occurred_at DESC LIMIT 25
2-- '?' is the placeholder for starting_after

The query parameter ending_before also takes an id but the first of one of the list from a page higher than 1 and instructs the database to only query rows that come before the given id in descending order:

1SELECT * FROM events WHERE id > ? ORDER BY occurred_at DESC LIMIT 25
2-- '?' is the placeholder for starting_after

Determining if there are more rows to be fetched

The common convention is to fetch just one more row than the limit and discard it:

1SELECT * FROM events WHERE id < ? ORDER BY occurred_at DESC LIMIT 26

 1// golang
 2package main
 3
 4func getEvents(limit int /*other params*/) ([]Row, bool) {
 5  var results []Row
 6	
 7  if len(results) > limit { 
 8    // result[:len(result)-1] splice from length 0 to the length of
 9    // 'result' minus 1 to exclude the additional row fetched.
10    return result[:len(result)-1], true
11  }
12  
13  return result, false	
14}

Do those queries perform full table scans?