Appwrite Pagination: Choosing Limit+Offset vs Cursor for Large Collections

The Problem: Duplicate and Missing Items in Large Appwrite Collections

As an Appwrite Databases collection grows, listDocuments() calls that skip rows with offset become slower and can return the same document twice or skip it entirely after concurrent inserts. The useful takeaway: choose offset pagination only when you need exact totals on small, mostly static collections; choose cursor-based pagination for user feeds, infinite scroll, exports, or frequently written collections.

Decision and Constraints

• Dataset size: Offset scanning gets more expensive as the offset grows; cursor pagination keeps server cost roughly constant per page.
• Consistency needs: Feeds with concurrent writes need a stable anchor so new documents do not shift existing pages.
• Exact totals: Admin screens showing "X of Y items" need offset or a separate count query, because cursor responses do not provide a reliable per-page total.
• Version: Cursor helpers such as Query.cursorAfter require Appwrite server 1.0+ and a matching SDK. Confirm the listDocuments signature for your server and language before adopting cursors.

Supported Pagination Options

Strategy	Pros	Cons	Best for
Offset (Query.limit + Query.offset)	Simple to implement, supports total counts, works with any sort order	Server scans and discards skipped rows, results drift on inserts or deletes, deep pages are slow	Admin panels, static reports, collections under roughly 1,000 documents
Cursor (Query.cursorAfter or Query.cursorBefore)	Fast deep pagination, stable during concurrent writes, constant server cost per page	No built-in per-page total, requires a unique indexed sort key, slightly more client state	User feeds, infinite scroll, exports, large or frequently written collections

Trade-offs Explained

Performance: With offset, Appwrite must evaluate documents up to the offset value on every request, so page 1,000 costs far more than page 1. Cursor pagination uses the indexed sort attribute to resume after a known document, avoiding that scan.

Consistency: If a new document is inserted before the current offset, offset pages shift and a document can appear on two pages. Cursor pagination anchors to a specific document ID, so new inserts appear only once in the sequence.

Sort stability: Both strategies need a deterministic order. Use a unique indexed attribute such as $id, or combine createdAt with $id as a tiebreaker. Sorting on a non-indexed or low-cardinality attribute makes results ambiguous across pages.

Concrete Implementation: Cursor Pagination for a User Feed

This example assumes Appwrite server 1.5+ and the Node.js SDK v10+, where the entry point is databases.listDocuments. It paginates a messages collection sorted by createdAt descending, with $id as a tiebreaker.

import { Client, Databases, Query } from 'node-appwrite';

const client = new Client()
  .setEndpoint('https://cloud.appwrite.io/v1')
  .setProject('YOUR_PROJECT_ID')
  .setKey('YOUR_API_KEY');

const databases = new Databases(client);

const DATABASE_ID = 'YOUR_DATABASE_ID';
const COLLECTION_ID = 'messages';
const LIMIT = 25;

// First page
const firstPage = await databases.listDocuments(
  DATABASE_ID,
  COLLECTION_ID,
  [
    Query.orderDesc('createdAt'),
    Query.orderAsc('$id'),
    Query.limit(LIMIT)
  ]
);

const documents = firstPage.documents;
const lastDocument = documents[documents.length - 1];

// Next page resumes after the last document ID
const nextPage = await databases.listDocuments(
  DATABASE_ID,
  COLLECTION_ID,
  [
    Query.orderDesc('createdAt'),
    Query.orderAsc('$id'),
    Query.cursorAfter(lastDocument.$id),
    Query.limit(LIMIT)
  ]
);

console.log('Returned documents:', nextPage.documents.length);
console.log('Global total:', nextPage.total);

Store lastDocument.$id on the client or in a session so the next request can resume from that position. Do not treat nextPage.total as a per-page count; it is the collection-wide total.

Validation Steps for Your Environment

1. Confirm the indexed sort attribute: In the Appwrite console, open the collection attributes and verify that createdAt (or your chosen sort attribute) has an index, and that $id is unique. Without an index, cursor comparisons can be slow or unreliable.
2. Check SDK and server versions: Run npm list node-appwrite in your project directory. Compare the result against your Appwrite server changelog to confirm that Query.cursorAfter exists and whether it accepts a document ID or an opaque cursor.
3. Test with concurrent writes: Run a script that inserts one document every two seconds while paginating with both offset and cursor strategies. Record duplicate and missing document IDs across pages.
4. Inspect the response: Confirm that listDocuments returns total, documents, and that cursorAfter resumes after the expected document ID. Older SDKs may omit these fields.

Limitations and Edge Cases

• No per-page total: Cursor responses include a global total, not the number of items on the current page. Calculate locally with documents.length.
• Sort stability: Always include $id as a secondary sort when using a non-unique attribute such as createdAt. Otherwise documents with identical timestamps can appear in different orders across pages.
• Permissions and rate limits: Server-side calls with an API key still respect collection and document permissions and are subject to rate limits. Cache pages client-side where appropriate.
• Read-only operation: listDocuments does not change state, so there is no rollback procedure for this query itself.

Quick Decision Checklist

1. Do you need an exact "X of Y" display? → Use offset, or pair cursor pagination with a separate count query.
2. Is the collection large, growing quickly, or accessed by many users at once? → Use cursor pagination.
3. Do you have a unique, indexed attribute such as $id or createdAt plus $id? → Required for cursor pagination.
4. Are you on Appwrite server 1.0+ with a matching SDK? → Verify before using Query.cursorAfter or Query.cursorBefore.