Stably Paginate Large FaunaDB Collections with FQL Indexes

Desired Outcome

Return a deterministic, offset‑free list of documents from a large FaunaDB collection, ensuring that concurrent writes do not cause duplicates or missing items between pages. The solution must use a properly defined index and FQL’s paginate() function.

Prerequisites

• Active FaunaDB account and a project with a database.
• FaunaDB client library installed (Node.js, Go, Python, etc.).
• Basic knowledge of FQL syntax and index concepts.
• Understanding that FaunaDB’s hosted service is being sunsetted in 2025; verify current service status before production use.

Step‑by‑Step Procedure

1. Create a Schema‑Aware Collection (Optional)

Although FaunaDB collections are schemaless, defining a logical shape helps with index design. For example, a users collection:

CREATE COLLECTION "users"

2. Define the Index

The index must expose the fields you’ll filter on (terms) and the fields you’ll sort or project (values). A typical index for paginating by creation time:

CREATE INDEX "users_by_created_at" {
  collection: "users",
  terms: [ { field: ["data", "created_at"] } ],
  values: [ { field: ["data", "created_at"] }, { field: ["ref"] } ],
  plan: "ALL",
  unique: false
}

Why this matters: Without an index, FaunaDB would perform a full collection scan on every query, incurring higher read cost and latency.

3. Insert Sample Documents

Populate the collection so pagination has data to return. Example (Node.js driver):

const db = require("faunadb").Client({ secret: "YOUR_FAUNA_SECRET" });
const q = require("faunadb").query;

async function seed() {
  for (let i = 0; i < 1000; i++) {
    await db.query(
      q.Create(q.Collection("users"), {
        data: { name: `User ${i}`, created_at: new Date().toISOString() }
      })
    );
  }
}
seed();

4. Fetch the First Page

Use paginate() with a page size that balances round‑trip cost and latency. The default page size is 10.

const pageSize = 50;
const result = await db.query(
  q.Paginate(
    q.Match(q.Index("users_by_created_at")),
    { size: pageSize }
  )
);
// result contains: { data: [...], after: <cursor>, before: <cursor> }

Verify that result.data contains pageSize documents and that result.after is not null (unless at end).

5. Fetch Subsequent Pages

Pass the after cursor back into paginate() to continue:

const nextPage = await db.query(
  q.Paginate(
    q.Match(q.Index("users_by_created_at")),
    { size: pageSize, after: result.after }
  )
);

Repeat until nextPage.after is null. Because the cursor references a stable point in the index, concurrent inserts or deletes between page fetches won’t shift the boundaries.

6. Handle Cursor Expiration or Index Changes

Cursors are opaque and may become invalid if the index is dropped or its definition changes. Persist them only for short‑term navigation, not long‑term storage.

Expected Checks & Validation

1. Index Usage: Run EXPLAIN on the query to confirm the index is used instead of a collection scan.

   EXPLAIN q.Paginate(q.Match(q.Index("users_by_created_at")))
   

2. Deterministic Boundaries: Insert a new user between page fetches and verify that earlier pages remain unchanged.

   // After page 1, insert a new user
   await db.query(q.Create(q.Collection("users"), { data: { name: "New User", created_at: new Date().toISOString() } }));
   // Fetch page 2 again using the same cursor – no duplication or omission should occur.
   

3. Page Size Impact: Measure latency and memory usage for different size values to find the sweet spot for your workload.

Recovery & Rollback Options

• If a cursor is invalidated, restart pagination from the beginning or from a known safe page boundary.
• To reduce write overhead, avoid indexing fields that rarely change or are not used in queries.
• For strong consistency (e.g., read‑your‑writes), add consistent: true to the query plan, but be aware of increased latency and cost.

Limitations & Practical Checks

• FaunaDB’s hosted service is being sunsetted; verify whether your project is migrated or if a self‑hosted community edition is available.
• Index maintenance costs grow with the number of writes; monitor write‑cost metrics in the Fauna console.
• Cursors are opaque and may change if the index is rebuilt; avoid persisting them beyond a single user session.
• Fauna’s eventual consistency model applies to reads unless consistent: true is set; plan for acceptable staleness in pagination flows.

Practical Verification Checklist

• Run EXPLAIN on your paginate query to ensure index usage.
• Confirm that after cursors are non‑null until the last page.
• Insert a document between page fetches and re‑run the second page query to verify stability.
• Check Fauna’s billing console for read‑operation counts per page size.
• Review Fauna’s status page or migration guide to confirm service availability.

Conclusion

By defining an explicit index and leveraging FQL’s cursor‑based paginate(), you can retrieve large result sets deterministically, avoid offset drift, and keep read costs predictable. Carefully balance page size, write overhead, and consistency requirements to match your application’s performance goals.