Performance trade-offs between offset and cursor pagination in FeathersJS

Direct answer

Moving from FeathersJS’ built‑in offset pagination ($skip/$limit) to a custom cursor‑based approach trades simplicity for predictable performance: offset pagination degrades linearly with the skip value, while cursor pagination using an indexed, monotonic field yields near‑constant lookup time but requires you to remove the default pagination hook, add custom before/after hooks, and manually enforce the service‑level paginate.max.

Likely explanation

The performance gap appears because offset queries force the database to scan all preceding rows even when an index exists on the sort field. A cursor query rewrites the request as a range filter (e.g., createdAt > :cursor) that lets the engine seek directly to the start point, so latency stays flat as you paginate deeper.

Confirmed facts (from FeathersJS behavior)

• Feathers’ paginate object automatically adds $limit and $skip and populates total, limit, skip in the response.
• When you disable the built‑in pagination hook (app.configure(paginate()) or set paginate: false on the service), Feathers no longer adds $skip or $limit unless you do it yourself.
• The service‑level paginate.max is still read by the default pagination hook; if you bypass that hook you must apply the limit yourself (either by adding $limit: Math.min(requestLimit, paginate.max) in a hook or by slicing the result array).

Steps to implement cursor pagination while respecting paginate.max

1. Disable the default pagination hook for the service (set paginate: false or remove the hook).
2. Choose a monotonic, indexed field (e.g., createdAt) and ensure it is unique or combine it with _id as a tie‑breaker.
3. In a before hook:
   • Read the cursor value from params.cursor (or a custom query param like $gt).
   • Build a query object: { [sortField]: { $gt: cursorValue } } (or $gte for inclusive).
   • If you need a tie‑breaker, add _id: { $gt: cursorId } when the sort field equals the cursor value.
   • Apply the limit: const limit = Math.min(params.$limit ?? paginate.default, paginate.max); then set params.$limit = limit;
   • Keep any existing $sort on the chosen field (ascending).
4. In an after hook:
   • If the service would normally return pagination metadata, compute it manually: context.result = { data: context.result, limit, cursor: lastItem[sortField] (and _id if used), total: undefined };
   • Optionally strip total if the client does not need it.
5. Return the cursor (or an opaque token) to the client so it can be sent back for the next page.

Missing diagnostic detail

If you cannot guarantee an indexed, unique (or near‑unique) monotonic field, the cursor approach may still scan many rows and lose its advantage. In that case, confirm whether you have such a field (e.g., createdAt with an index) or whether you can add a composite index on (sortField, _id). The answer to this determines whether switching to cursor pagination is recommended.