Pagination
Return a page plus a way to ask for the next, and why a cursor survives rows being inserted mid-read.
GET /notes?limit=20&cursor=<opaque> -> {"items":[...],"next_cursor":"..."}A list endpoint must never promise to return everything. Ten million rows is a slow query, a huge response and an out-of-memory client. Pagination means returning one page of rows plus a way to ask for the next. Two schemes dominate, and they fail differently.
Offset
The client sends ?limit=20&offset=40 and the database skips 40 rows before returning 20. It is simple and lets a client jump straight to page seven. The cost is that the database still has to walk and discard every skipped row, so deep pages get slower in proportion to how deep they are.
Cursor, also called keyset
Instead of "skip 40 rows", the client says "start after this row". The cursor is the sort key of the last row it received. The query becomes WHERE id > $1 ORDER BY id LIMIT $2, which an index answers directly, so page one thousand costs the same as page one. It also survives writes. A row inserted earlier in the list does not shift the window, so no row is repeated and none is skipped.
What trips people up
The ordering behind a cursor must be stable and unique. Sorting by a timestamp alone breaks when two rows share it, so add the id as a tiebreaker and put both in the cursor. Encode the cursor as one opaque string so clients cannot hand-build one, and always clamp limit to a maximum you chose.
Example
package main
import (
"fmt"
"strconv"
)
type Note struct {
ID int
Text string
}
// Kept in ID order. A stable order is what makes a cursor possible at all.
var notes = []Note{{ID: 1, Text: "a"}, {ID: 2, Text: "b"}, {ID: 3, Text: "c"}, {ID: 4, Text: "d"}}
// page returns up to limit notes whose ID is above after, plus the cursor for
// the next page. after == 0 asks for the first page.
func page(after, limit int) ([]Note, string) {
var out []Note
for _, n := range notes {
if n.ID > after && len(out) < limit {
out = append(out, n)
}
}
if len(out) < limit {
// Fewer rows than asked for: this was the last page.
return out, ""
}
return out, strconv.Itoa(out[len(out)-1].ID)
}
func main() {
first, cursor := page(0, 2)
fmt.Println(first, "cursor:", cursor)
// A note arrives before the client asks for page two. Offset paging would
// now hand back note 2 again, because every row shifted by one.
notes = append([]Note{{ID: 0, Text: "new"}}, notes...)
after, err := strconv.Atoi(cursor)
if err != nil {
fmt.Println(err)
return
}
second, next := page(after, 2)
fmt.Println(second, "cursor:", next)
}
// Output:
// [{1 a} {2 b}] cursor: 2
// [{3 c} {4 d}] cursor: 4When to use it
- Capping a /notes list so one client cannot pull the whole table in one request.
- Letting a sync job read every row in order while new rows keep arriving.
- Fixing a feed where users see the same item twice after someone posts above it.
More examples
Reading limit and cursor from the query string
Every list handler needs this, and the clamp is the part people skip. A bad limit is not worth a 400: silently falling back to the default keeps old clients working. strconv.Atoi(s) and strconv.ParseInt(s, 10, 0) are the same operation, the second letting you ask for a different base or bit size.
func pageParams(r *http.Request) (cursor string, limit int) {
q := r.URL.Query()
// An empty cursor means the first page.
cursor = q.Get("cursor")
// A default the client never has to think about.
limit = 20
if raw := q.Get("limit"); raw != "" {
// Atoi is documented as ParseInt(raw, 10, 0) converted to int.
n, err := strconv.Atoi(raw)
if err == nil && n > 0 {
limit = n
}
}
// A client asking for ten million does not get ten million.
if limit > 100 {
limit = 100
}
return cursor, limit
}An opaque cursor
RawURLEncoding uses - and _ instead of + and / and adds no = padding, so the result needs no escaping in a URL. Keeping the cursor opaque means you can later put a timestamp and an id in it without breaking clients, because nobody was parsing it. A malformed cursor is a 400, not a 500.
// encodeCursor hides the sort key behind one URL-safe string, so clients
// treat it as a token to pass back rather than a number to do maths on.
func encodeCursor(id int64) string {
return base64.RawURLEncoding.EncodeToString([]byte(strconv.FormatInt(id, 10)))
}
func decodeCursor(s string) (int64, error) {
raw, err := base64.RawURLEncoding.DecodeString(s)
if err != nil {
return 0, fmt.Errorf("bad cursor: %w", err)
}
return strconv.ParseInt(string(raw), 10, 64)
}
// encodeCursor(2) == "Mg" -> GET /notes?cursor=Mg&limit=2The two queries your store will send
The Go code above is the same either way; only the query changes. Keep offset for a small admin table where a user wants to click page 5. Use keyset for any feed, log or export that grows, and for anything a client reads in a loop until it is empty.
-- Offset: the database walks 10000 rows and throws them away first.
SELECT id, text FROM notes
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;
-- Keyset: resumes immediately after the last row the client saw. The pair
-- (created_at, id) is compared as one value, which is why the tiebreaker
-- belongs in the cursor. With an index on (created_at DESC, id DESC) the
-- cost does not grow with how deep the client has read.
SELECT id, text FROM notes
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;
Discussion