Designing APIs for Massive Datasets (Cursor Pagination, Sparse Fields, Caching)

The scenario: Your API returns a list of 2 million products. A mobile app needs to display them. How do you design this so the app doesn't crash and the server doesn't melt?

The naive answer — return everything in one response — fails immediately at scale: 2 million records at even a modest ~400 bytes each is ~800MB of JSON, which will crash a memory-constrained mobile client and saturate a slow connection long before it renders anything. The real answer is a stack of five complementary techniques, each solving a different bottleneck.

---

1. Cursor-Based Pagination (not offset-based)

The problem with offset pagination

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 999980;

This is what ?page=50000&size=20 compiles down to. The database still has to scan and discard the first 999,980 matching rows before it can return the 20 you actually asked for — even though an index exists on id/ORDER BY column, OFFSET forces the DB to walk that many index entries and skip them. The deeper the page, the slower the query — page 1 is instant, page 50,000 can take seconds, and the cost grows roughly linearly with offset depth.

The fix: cursor (a.k.a. keyset) pagination

SELECT * FROM products WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Instead of asking for "page 50,000," the client says "give me 20 records after this specific ID (or timestamp, or composite key)." The database uses the index to seek directly to :last_seen_id and reads forward — O(log n) seek + O(page size) read, regardless of how deep into the dataset you are. Page 1 and page 100,000 cost the same.

API shape:

GET /products?limit=20&cursor=eyJpZCI6MTAwMjB9   // base64-encoded cursor, e.g. {"id": 10020}

Response:
{
  "items": [...],
  "next_cursor": "eyJpZCI6MTAwNDB9"
}

Trade-offs worth stating explicitly:

• ❌ Cursor pagination can't jump to an arbitrary page number ("go to page 500") — only forward/backward from a known cursor. If a UI truly needs numbered page jumps (rare for infinite scroll, common for admin tables), offset pagination (or a hybrid) may still be needed for that specific view.
• ✅ Cursors should encode a unique, stable sort key (or tuple, e.g., (created_at, id) to break ties) — using a non-unique column alone can cause skipped or duplicated rows if two rows share the same value.
• ✅ Works cleanly with infinite-scroll UIs (the dominant mobile pattern), which never need to jump to an arbitrary page anyway.

---

2. Sparse Field Selection (Response Shaping)

A product with 30 fields (full description, specs, reviews, inventory count, etc.) might be ~4KB. The list screen only renders name, price, and thumbnail URL — maybe 200 bytes. Returning full objects for a list view is pure waste: 20x more data transferred than what's rendered.

• Return a lean DTO/projection for list endpoints — not the same serialization used for the detail endpoint.
• Detail fields (description, specs, reviews) load on-demand when the user taps into a specific item (GET /products/{id} — a separate, richer response).
• Optionally support a fields= query param (GraphQL-style field selection, or a simple comma-separated whitelist) so different client screens can request exactly what they need without needing a new endpoint per view.

Rule of thumb: design list/detail as two different response shapes from day one — don't let the detail DTO become the "default" that list endpoints lazily reuse.

---

3. Database-Level Filtering (push filters down, never up)

If a user searches "bluetooth speaker" against 2 million products:

• ❌ Worst: fetch all 2M rows, filter in the mobile app.
• ❌ Bad: fetch all 2M rows, filter in the application/server layer.
• ✅ Correct: push the filter into the database query (WHERE name ILIKE '%bluetooth%' backed by a proper index, or a dedicated search engine like Elasticsearch for full-text search at this scale) so the database does the heavy lifting using indexes, and the server only ever receives and forwards the ~340 matching rows.

Why this matters beyond just this example: any filtering logic that runs after data leaves the database has already paid the full cost of reading/transferring the unfiltered dataset — the filtering step itself becomes irrelevant to performance at that point. The entire goal is to narrow the result set as early as possible in the pipeline.

---

4. Aggressive Caching of Early Pages

Real usage data almost always follows a power law: ~80% of users never scroll past page 3. This makes the first few pages of the most common queries (default sort, popular filters/search terms) extremely cache-friendly.

• Cache the first N pages (e.g., first 5) of common queries in Redis, keyed by (query_params, cursor).
• Most user sessions are served entirely from cache and never touch the database.
• The database only handles cache misses and deep-pagination requests — a small, predictable fraction of total traffic.
• (See Caching Strategies.md for cache-aside/TTL/invalidation patterns to apply here — this is a textbook cache-aside candidate with a short TTL, since product catalogs change but not on every request.)

---

5. Response Compression

A trivial but high-leverage win: enable gzip (or brotli) compression on API responses.

• A ~50KB JSON payload commonly compresses down to ~8KB over the wire — JSON's repetitive key names and text-heavy structure compress extremely well.
• On slow/high-latency mobile networks (3G, poor signal), this is the difference between a multi-second load and a near-instant one — compression reduces transfer time, which dominates total latency on constrained networks far more than server-side processing time does.
• Usually a single configuration line (e.g., gzip on; in Nginx, or a compression middleware in the app framework) — no application code changes required.

---

The Full Architecture, Stacked

Client (mobile app)
   │  GET /products?limit=20&cursor=... &q=bluetooth+speaker
   ▼
API Gateway / Load Balancer  ── gzip compression on response ──►
   ▼
App Server
   │  1. Parses cursor + filter params
   │  2. Checks Redis cache for (query, cursor) — HIT for ~80% of requests (early pages)
   ▼
   on cache MISS:
   │  3. Pushes filter + keyset condition to DB:
   │       SELECT id, name, price, thumbnail_url        ← sparse fields
   │       FROM products
   │       WHERE name @@ 'bluetooth speaker'             ← DB-level filter (indexed / search engine)
   │         AND id > :cursor
   │       ORDER BY id LIMIT 20                          ← cursor pagination, index seek
   │  4. Writes result back into Redis (short TTL)
   ▼
Response: { items: [ {id, name, price, thumbnail_url}, ... ], next_cursor: "..." }

Each layer removes an order of magnitude of unnecessary work from the layer above it — by the time data reaches the client, it's a tiny, precisely-shaped, compressed payload instead of the naive 800MB firehose.

---

Quick Comparison Table

Technique	Solves	Cost if skipped
Cursor pagination	Deep-page query slowness	O(offset) scan cost that grows with dataset size
Sparse fields	Oversized payloads per item	~20x more bytes transferred than needed
DB-level filtering	Wasted compute/transfer on irrelevant rows	Full table scan/transfer before any filtering happens
Caching early pages	Redundant DB load for the most common requests	Every request (even identical ones) hits the DB
Compression	Wire transfer time on slow networks	5-6x larger payload size over the wire

---

Meta-Lesson

None of these five techniques are individually complex — each is a well-known, "simple" optimization. The real insight isn't knowing any single technique; it's recognizing that they compose, and each targets a different bottleneck in the pipeline (query cost, payload size, filtering cost, repeated-request cost, transfer cost). A shallow answer stops at "add pagination." A deeper answer explains why offset pagination specifically breaks down at scale, and layers the remaining four techniques on top with a clear rationale for each.