Implementing Stable Cursor-Based Pagination in FaunaDB

The Problem: Avoiding Offset Drift in Large Datasets

Traditional offset-based pagination (skipping X records) fails in dynamic databases because documents are frequently added or deleted. If a new record is inserted on page one while a user is navigating to page two, the last item of page one shifts to page two, causing the user to see a duplicate. In FaunaDB, the solution is cursor-based pagination using an after cursor, which tracks a specific position in an index rather than a numerical offset.

Prerequisites

• A FaunaDB database instance with an existing collection.
• An index defined on that collection that specifies the desired sort order (e.g., by a timestamp or unique ID).
• A database role with read permissions for both the index and the underlying collection.

Step 1: Defining a Stable Index

Pagination is only as stable as the index driving it. To ensure a deterministic order, your index must include the fields you wish to sort by in the values array. If you sort by a non-unique field (like category), you must include a unique field (like ref) as the final value to prevent unstable ordering of identical values.

// Example Index Definition (FQL) 
// This index sorts by 'createdAt' ascending, then by 'ref' for stability
CreateIndex({
  name: "users_by_created",
  source: Collection("users"),
  values: [
    { field: ["data", "createdAt"] },
    { field: "ref" }
  ]
})

Step 2: Executing the Paginated Query

The Paginate function is used to slice the index. It returns a Page object containing the data and a cursor. To fetch the first page, call Paginate with only the size. To fetch subsequent pages, pass the after cursor returned from the previous result.

Run this query via the Fauna Dashboard or a client SDK using a role with appropriate permissions:

// Fetching a page of 20 documents
let page = Paginate(Match(Index("users_by_created")), { size: 20 });

// To fetch the next page, use the 'after' cursor from the previous result
// let page = Paginate(Match(Index("users_by_created")), { size: 20, after: [CURSOR_VALUE] });

// Map the references to actual document data
Map(page, Lambda("ref", Get(Var("ref"))))

Step 3: Handling the Page Metadata

The Paginate function returns a Page object. Your application logic must extract two specific pieces of information to maintain the state:

• The Data: The array of references (or documents) for the current view.
• The After Cursor: An opaque string used to request the next set of results. If this value is null, you have reached the end of the dataset.

Comparison: Offset vs. Cursor Pagination

Feature	Offset (Skip)	Cursor (After)
Performance	Degrades as offset increases	Constant time regardless of depth
Consistency	Prone to duplicates/gaps on writes	Stable relative to the cursor position
Navigation	Allows jumping to page X	Sequential (Next/Previous) only

Verification and Diagnostics

To verify the implementation is functioning correctly, perform the following checks:

1. Boundary Check: Request a page size of 5. Capture the after cursor. Request the next page of 5. Ensure the first item of page 2 is the immediate successor of the last item of page 1.
2. Uniqueness Check: Compare the document ref values across three consecutive pages. There should be zero overlap.
3. Termination Check: Continue paging until the after cursor returns null, confirming the end of the index is reachable.

Operational Limitations and Recovery

Limitations: Large page sizes (e.g., >1000) can increase latency and read operation costs. If a query exceeds the database's execution time limit, reduce the size parameter.

Recovery Options:

• Transient Failures: If a query fails due to a timeout or network interruption, retry the request using the last successfully received after cursor.
• State Reset: If the client-side cursor becomes corrupted or invalid, the only recovery is to clear the cursor and restart pagination from the first page.