Appwrite Database Pagination: Architecture Decisions for Reliable Data Retrieval

The Pagination Problem in Distributed Databases

When building applications on Appwrite, developers quickly encounter a fundamental constraint: the database API enforces a maximum page size of 100 documents per request. This isn't an arbitrary limit—it's a deliberate design choice that protects both the database cluster and your application from unbounded memory consumption and timeout cascades. Understanding how this constraint shapes your data access patterns is essential for building reliable pagination.

Requirements Driving the Design

Appwrite's pagination mechanism addresses three core requirements:

• Bounded resource consumption: Each query must complete within predictable memory and CPU limits
• Consistent ordering: Results must return in a deterministic sequence so clients can page through data without duplicates or gaps
• Stateless server operation: The database maintains no per-client cursor state, enabling horizontal scaling

The resulting design uses offset-based pagination with limit and offset parameters, default ordering by $createdAt descending, and client-managed pagination state.

Smallest Suitable Design: Offset-Based Pagination

The minimal viable pagination implementation requires only two parameters:

const databases = new Databases(client);

async function fetchPage(collectionId, pageSize = 25, pageNumber = 0) {
  const offset = pageNumber * pageSize;
  const response = await databases.listDocuments(
    DATABASE_ID,
    collectionId,
    [
      Query.limit(pageSize),
      Query.offset(offset),
      Query.orderDesc('$createdAt') // explicit for clarity
    ]
  );
  return response;
}

This design assumes the client tracks pageNumber or computes offset from a stored position. The server validates that limit ≤ 100 and returns an error if exceeded—no silent truncation occurs.

Trust and Data Boundaries

The pagination contract creates clear boundaries between client and server responsibilities:

Boundary	Server Enforces	Client Manages
Page size	Hard limit of 100	Chooses value ≤ 100
Ordering	Defaults to $createdAt desc	Can override with orderBy
Position	Stateless offset application	Tracks offset/cursor
Total count	Returns in response	Uses for UI/page calc

Critically, the server does not validate that offset corresponds to a previously returned position. A malicious or buggy client could request arbitrary offsets, forcing full collection scans. This is a deliberate trade-off: stateless servers cannot authenticate pagination position without storing per-session state.

Operational Checks and Verification

Before deploying pagination logic to production, verify these behaviors in a test environment:

1. Create a test collection with a known document count (e.g., 50 documents)
2. Request page 1 with limit=10, offset=0 — confirm 10 documents returned
3. Request page 2 with limit=10, offset=10 — confirm next 10 documents, no overlap
4. Request beyond total with limit=10, offset=50 — confirm empty documents array, total=50
5. Exceed limit with limit=101 — confirm HTTP 400 error with descriptive message

Run these checks against both your development and staging Appwrite instances. Self-hosted deployments may have different MAX_PAGE_SIZE configuration values than Appwrite Cloud.

Failure Modes and Mitigations

Deep Paging Performance Degradation

Offset-based pagination requires the database to scan and discard offset documents before returning the requested page. At offset=10000 with limit=25, the query processes 10,025 documents to return 25. This manifests as linearly increasing latency:

// Latency profile (illustrative)
offset=0     → 15ms
offset=1000  → 120ms
offset=10000 → 850ms
offset=50000 → 4200ms

Mitigation: For deep paging scenarios (admin dashboards, data exports), implement cursor-based pagination using a unique, indexed field:

async function fetchNextPage(collectionId, pageSize, lastCursor = null) {
  const queries = [
    Query.limit(pageSize),
    Query.orderAsc('$id') // or any unique indexed field
  ];
  
  if (lastCursor) {
    queries.push(Query.greaterThan('$id', lastCursor));
  }
  
  return databases.listDocuments(DATABASE_ID, collectionId, queries);
}

This shifts the query plan from "scan and discard" to "index seek from cursor," maintaining constant latency regardless of page depth.

Concurrent Modification Gaps and Duplicates

With offset pagination, documents inserted or deleted between page requests cause items to shift across page boundaries:

• Insertion before current offset: Next page skips a document (gap)
• Deletion before current offset: Next page returns a duplicate

Cursor-based pagination using a stable, immutable field (like $id or $createdAt with tiebreaker) eliminates this class of anomaly.

Client State Loss

Since the server stores no pagination state, a page refresh or navigation loss resets the user to page 1. Persist the current offset or cursor in URL query parameters (?page=3 or ?cursor=abc123) or client-side storage to survive navigation.

Conditions That Would Change This Design

Consider migrating from offset to cursor pagination when:

• Users regularly access pages beyond offset=5000 (measured via analytics)
• Collection size exceeds 100,000 documents with frequent deep paging
• Real-time collaboration features require consistent view across concurrent users
• Export jobs need to iterate entire collections without timeout risk

Appwrite's current API does not provide native cursor tokens (opaque server-generated pagination handles). The Query.greaterThan approach above is a client-side cursor implementation that requires an indexed, unique field with stable ordering.

Practical Verification Checklist

After implementing pagination, validate in staging:

• ☐ Page size never exceeds 100 (unit test the limit clamp)
• ☐ Empty results return documents: [] not null
• ☐ total field matches expected count for filtered queries
• ☐ Cursor pagination produces zero duplicates across 1000+ page iterations (stress test)
• ☐ Network failure mid-pagination leaves UI in recoverable state (retry with same offset/cursor)

Limitations Summary

• Maximum page size: 100 documents (verify per deployment)
• No server cursors: Client must implement cursor logic using indexed fields
• Offset scan cost: Linear with offset value; unsuitable for deep paging
• No transactional snapshot: Concurrent writes affect pagination consistency
• Ordering dependency: Custom orderBy must use indexed fields for performance

The offset-based model works well for typical user-facing pagination (first 10-20 pages). For administrative interfaces, exports, or infinite-scroll feeds with deep history, invest in the cursor-based pattern using $id or a dedicated sort key.