Implementing Consistent Pagination in FaunaDB using FQL Cursors

The Problem with Offset Pagination in Distributed Databases

Traditional SQL databases often use OFFSET and LIMIT for pagination. However, in a distributed environment with high write concurrency, offset pagination leads to “drifting” results: if a record is inserted or deleted on page one while a user is navigating to page two, the user may see the same record twice or skip a record entirely.

FaunaDB solves this by using opaque cursors. Instead of calculating a numeric position, Fauna provides a pointer to the exact location in the index where the last result was found. This ensures that the next page starts exactly where the previous one ended, regardless of concurrent modifications to the collection.

How Cursor-Based Pagination Works in FQL

In Fauna Query Language (FQL), pagination is handled by wrapping a set (such as the result of a Match) in the Paginate function. The Paginate function accepts a configuration object where you define the size of the page and the starting point using either after or before.

Implementation Example

Assume you have a collection of Posts indexed by created_at. To retrieve the first 10 posts and then fetch the subsequent 10, you would use the following sequence.

Step 1: Initial Request
Run this query in the Fauna Shell or via a driver to get the first page:

Paginate(
  Match(Index("posts_by_date")), 
  { size: 10 }
)

The response will return a data array containing the first 10 references and an after field containing a cursor string (e.g., S29vS...). This string is an opaque identifier; your application should treat it as a black box and store it to pass back in the next request.

Step 2: Requesting the Next Page
To fetch the next 10 results, pass the after cursor from the previous response back into the Paginate options:

Paginate(
  Match(Index("posts_by_date")), 
  { size: 10, after: ["S29vS..."] }
)

Comparison: Cursor vs. Offset

Feature	Offset Pagination (Traditional)	Cursor Pagination (FaunaDB)
Performance	Degrades as offset increases (must scan skipped rows)	Constant time regardless of depth
Consistency	Prone to duplicates/skips during writes	Stable; points to a specific record position
Navigation	Supports jumping to a specific page (e.g., Page 50)	Sequential only (Next/Previous)

Constraints and Common Mistakes

• No Numeric Skipping: FQL does not support an offset parameter. You cannot request “the 100th record” without having the cursor for the 99th.
• Cursor Manipulation: Never attempt to parse, decode, or modify the cursor string. Doing so will invalidate the request and result in a system error.
• Page Size Limits: While you can set a large size, extremely large pages increase latency and risk timeouts. It is recommended to keep page sizes reasonable (e.g., 10–100) and rely on the cursor for traversal.
• Index Dependency: Pagination is only as effective as the index being matched. Ensure your index is sorted correctly to make the after and before logic intuitive for the end user.

Verification and Testing

To verify your implementation is working correctly, follow these diagnostic steps:

1. Execute a Paginate query with a size of 5.
2. Note the last document ID in the result set and the after cursor.
3. Insert a new document into the collection that would logically appear on the first page.
4. Execute the second query using the saved after cursor.
5. Expected Result: The second page should still begin with the document immediately following the one noted in step 2, proving that the new insertion did not shift the result set and cause a duplicate.

Rollback and State Changes

Since Paginate is a read‑only operation, it does not change the state of the database. No rollback is required for the queries themselves. If you are storing cursors in a client‑side session or a cache, simply clear that cache to reset the user’s pagination state to the first page.