Code-Memo

Pagination, Filtering, Sorting

Offset vs cursor pagination

  1. Offset/limit is simple (?page=3&limit=50) but performs poorly on large offsets and can skip/duplicate rows if data shifts during iteration.
  2. Cursor pagination (?after=opaque) is stable under inserts/deletes when tied to an indexed sort key.
  3. Document whether sort order is total or best-effort under heavy writes.

Filtering with query params

  1. Prefer explicit filters (status=open, from=2025-01-01) over generic query languages unless you need them.
  2. Define combinator semantics (AND vs OR) and validation for unknown filters (ignore vs 400).
  3. Watch URL length limits for huge filter sets; use POST /search for complex queries.

Sorting design patterns

  1. Accept sort=field with optional direction (sort=-createdAt) and a whitelist of sortable fields.
  2. Default sort must be deterministic (tie-breaker id) to keep cursors stable.
  3. Reject unsafe sorts that force full table scans without indexes.

Performance considerations for large datasets

  1. Index the fields you filter, sort, and cursor on; explain slow queries in dev.
  2. Return partial representations (field masks) or projection params to shrink payloads.
  3. Use ETag/If-None-Match on read-heavy list endpoints when caching helps.