GLOSSARY

What is Cursor Pagination?

Cursor pagination walks a result set by passing back an opaque pointer to the last row you received, instead of a numeric offset, so rows inserted or deleted while you are paging cannot shift the window and make you skip or repeat records.

Try Pinlyx freeRead the Data API docs

Free forever plan · No credit card required · Cancel anytime

Quick definition

Cursor pagination walks a result set by passing back an opaque pointer to the last row you received, instead of a numeric offset, so rows inserted or deleted while you are paging cannot shift the window and make you skip or repeat records.

In a single sentence: "give me the 50 rows after this one" beats "give me rows 400 to 450" on both correctness and cost.

What it means

Cursor pagination, also called keyset pagination or the seek method, replaces the page number with a pointer. The server sends you a batch of rows and a token that encodes where that batch ended. You send the token back to get the next batch. You never tell the server "page 9"; you tell it "continue from here".

Almost every API starts with offset pagination instead, because ?page=3 is obvious and LIMIT 50 OFFSET 100 is one line of SQL. It works fine until the table is large or the data moves, at which point it fails in two ways that are both invisible from the client side.

Why offset breaks: the correctness argument

This is the failure that matters, and it has nothing to do with speed. Take a list sorted newest first, 50 rows per page.

  1. You fetch page 1 and get rows 1 to 50, where row 1 is the newest.
  2. While you are processing them, three new rows are inserted at the top.
  3. You fetch page 2 with offset=50. What was row 48 is now row 51, so your second page starts by handing you three rows you already have.

Deletions do the same thing in the opposite direction. Delete three rows near the top and offset 50 now points past three rows you never saw. Nothing errors. Nothing logs. Your import job just silently missed three records, and the only way you find out is a reconciliation weeks later.

A cursor is immune to both because it is anchored to a row rather than to a count. "Everything after the row whose sort key is X" does not care how many rows were inserted above X, because none of them are after X.

Why offset breaks: the cost argument

OFFSET 40000 does not skip 40,000 rows. The database reads them, in order, and throws them away, and only then starts collecting the ones you asked for. The cost of page N grows linearly with N, so the deepest pages of a big list are the slowest, which is exactly backwards from what a user expects.

Keyset pagination turns that into an index seek:

  • Offset: ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 40000 reads 40,050 rows to return 50.
  • Keyset: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 50 seeks straight into the index and reads 50 rows. Page 800 costs what page 1 costs.

The row-comparison syntax matters. Writing it as created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id) is logically the same but frequently prevents the planner from using the composite index as a single seek. The tuple form is both shorter and faster.

The tiebreaker is not optional

A cursor is a position, and a position has to be unique. Sort by a timestamp alone and any group of rows sharing that timestamp becomes ambiguous: the page boundary lands somewhere inside the group, and the server has no way to know which members of it you already received. On a high-write table this is not a rare edge case, it is a nightly occurrence.

Always pair the sort column with something unique, normally the primary key, and compare the pair. If your ids are sequential you can often sort by the id alone. If they are UUIDv4 you cannot, because random UUIDs have no meaningful order; UUIDv7 and ULID were designed to fix exactly that by putting a timestamp in the high bits.

Opaque and signed

In the Pinlyx Data API, list endpoints take ?limit= (default 50, maximum 500) and ?cursor=, and every list response carries a meta.page block. The documented rule is short: follow meta.page.next_cursor until it is null, and treat the cursors as opaque and signed.

Those two adjectives are doing real work. A real next_cursor from the watch delivery listing is eyJrIjoiMTczNDU2IiwiZCI6ImEifQ, and yes, that base64-decodes to {"k":"173456","d":"a"}: a sort key and a direction. Decoding it is easy. Depending on it is a mistake, because the encoding is an implementation detail that can change without a version bump, and because the value is signed. A hand-edited cursor does not return adjacent rows, it gets rejected. That signature is what stops a cursor from becoming an accidental query parameter into somebody else's data.

Reading the page block

The meta.page object has four fields and each one has a trap attached:

  • limit: rows requested. Default 50, maximum 500. Ask for 501 and you get a 422, not a clamp.
  • next_cursor: pass it as ?cursor= next time. Null is the only reliable stop condition.
  • count: rows in this page. A short page does not mean the last page.
  • total: optional. Present only when counting is cheap. Code that assumes it is there will crash on the endpoints where it is not.

Some endpoints add a depth cap on top of pagination. The research findings feed stops at 500 rows per status, which is a product decision about how deep a feed is useful, not a bug you can page around.

Writing the loop correctly

The naive loop is four lines and has one dangerous failure mode: if a server bug or a filter interaction returns the same cursor twice, the loop never terminates and quietly burns your daily quota. Every production paging loop should carry three guards: a page cap, a check that the cursor actually changed, and a limit on the total rows collected. See the snippets below.

The other operational note is that paging costs requests, and requests are what the API meters. Walking 25,000 rows at limit=50 is 500 calls; at limit=500 it is 50. On the free tier, where the ceiling is 1,000 requests a day, that difference is the whole budget. Metering counts HTTP calls rather than rows, so page as wide as the endpoint allows.

Related concepts

  • Rate limit: paging is a request multiplier, and the limit is per key rather than per loop.
  • API scope: the permission that decides whether a list endpoint answers you at all.
  • Exponential backoff: what to do when a page fails halfway through a walk.
  • Webhook: the push alternative that removes the need to page for changes at all.

How Pinlyx handles it

Every list endpoint across the API's 127 operations uses the same two parameters and the same meta.page block, so a paging helper written once works everywhere. Cursors are opaque and signed, the maximum page is 500 rows, and next_cursor is a required field so there is always a stop condition to read rather than infer.

Cheat sheet · one page, one cursor

A real paged response, trimmed to the parts that matter.

The data array is cut for width. Everything in meta.page is exactly what the API returns.

GET /data-api/v2/watch/deliveries?limit=50 HTTP/1.1
Host: pinlyx.com
Authorization: Bearer psk_live_...

HTTP/1.1 200 OK
Content-Type: application/json
X-Request-Id: req_9f2c41a8b3d5
X-RateLimit-Limit: 600
X-RateLimit-Remaining: 597

{
  "data": {
    "deliveries": [
      {
        "id": "c0ffee00-1111-2222-3333-444455556666",
        "event_type": "account.audience_changed",
        "platform": "x",
        "handle": "elonmusk",
        "day": "2026-08-30",
        "status": "delivered",
        "attempts": 1,
        "max_attempts": 8,
        "last_status_code": 200
      }
    ]
  },
  "meta": {
    "request_id": "req_9f2c41a8b3d5",
    "generated_at": "2026-08-23T09:14:02.317Z",
    "took_ms": 42,
    "page": {
      "limit": 50,
      "next_cursor": "eyJrIjoiMTczNDU2IiwiZCI6ImEifQ",
      "count": 50
    }
  }
}
And the failure: limit out of range
GET /data-api/v2/watch/deliveries?limit=1000

HTTP/1.1 422 Unprocessable Content

{
  "error": {
    "code": "invalid_request",
    "message": "One or more parameters are invalid.",
    "details": { "limit": "Must be between 1 and 500." },
    "request_id": "req_9f2c41a8b3d5"
  }
}

Note that the limit is refused rather than clamped. A silent clamp would mean your loop's arithmetic and the server's disagree, which is how a "we only imported 40% of the rows" bug is born.

Cheat sheet · a paging loop with guards

TypeScript and Python, both with the three guards.

async function* walk(path: string, limit = 500) {
  let cursor: string | null = null;
  let seen = new Set<string>();
  let pages = 0;

  while (true) {
    const url = new URL("https://pinlyx.com" + path);
    url.searchParams.set("limit", String(limit));
    if (cursor) url.searchParams.set("cursor", cursor);

    const res = await fetch(url, {
      headers: { Authorization: "Bearer " + process.env.CRMS_KEY },
    });
    if (!res.ok) throw new Error("page failed: " + res.status);
    const body = await res.json();

    yield body.data;

    const next = body.meta.page?.next_cursor ?? null;
    if (next === null) return;                 // the only real stop condition
    if (seen.has(next)) throw new Error("cursor did not advance");
    if (++pages > 1000) throw new Error("page cap reached");
    seen.add(next);
    cursor = next;
  }
}
import httpx

def walk(client, path, limit=500, max_pages=1000):
    cursor, pages, seen = None, 0, set()
    while True:
        params = {"limit": limit}
        if cursor:
            params["cursor"] = cursor
        body = client.get(path, params=params).raise_for_status().json()
        yield body["data"]

        nxt = (body["meta"].get("page") or {}).get("next_cursor")
        if nxt is None:
            return
        if nxt in seen:
            raise RuntimeError("cursor did not advance")
        pages += 1
        if pages > max_pages:
            raise RuntimeError("page cap reached")
        seen.add(nxt)
        cursor = nxt
Offset versus cursor, honestly

Cursor wins four of five. The one it loses is real.

AxisOffset / page numberCursor / keyset
Cost of page NThe database reads and discards every row before the offset. Page 800 of 50 costs 40,050 row reads.An index seek to the cursor position plus the page. Page 800 costs the same as page 1.
Correctness under writesAn insert above the window repeats a row; a delete skips one. Both are silent.The window is anchored to a row, not a count. Inserts and deletes elsewhere do not move it.
Random accessJump straight to page 47. This is the only thing offset does better.Next and previous only. There is no page number to jump to.
Total row countUsually paired with a COUNT, which is its own expensive query.Often omitted on purpose. In our envelope meta.page.total appears only when counting is cheap.
Stability of the tokenAn integer, valid forever, meaningless if the sort changes.Opaque and signed. Tied to one sort order; tampering is rejected rather than misread.
Watch out for

Five ways a paging loop goes wrong.

  • Stopping when a page comes back short. A short page is not the last page; a null cursor is.
  • Parsing the cursor. It decodes today and changes tomorrow, and a tampered cursor is rejected by the signature anyway.
  • Sorting by a non-unique column with no tiebreaker, so rows sharing a timestamp straddle the boundary and vanish.
  • Assuming meta.page.total exists. It is optional, and present only where counting is cheap.
  • Paging at limit=50 out of habit. On the free tier that turns a 25,000-row walk into 500 of your 1,000 daily requests.

Cursor pagination: FAQ

What developers ask on their first walk through a large result set.

Usually the sort key of the last row plus a tiebreaker and a direction, encoded and signed. A real next_cursor from the Pinlyx Data API delivery listing is eyJrIjoiMTczNDU2IiwiZCI6ImEifQ, which base64-decodes to {"k":"173456","d":"a"}: a key and a direction. That it decodes does not make it yours to parse. Opaque means the server may change the encoding without a version bump, and signed means a hand-edited cursor is rejected rather than quietly returning rows you were not meant to see.
Stop when next_cursor is null, and only then. Do not stop when a page returns fewer rows than the limit: a filtered or sharded page can legitimately come back short and still have more behind it. Do not stop when you have collected meta.page.total rows either, because total is optional in our envelope and is present only when counting is cheap. next_cursor is the single reliable stop condition, which is why it is documented as required rather than optional.
Because a cursor is a position, and a position needs to be unique. If you sort by created_at alone and forty rows share the same millisecond, the boundary between two pages falls somewhere inside that group and the server cannot tell which of those forty rows you have already seen. Sort by (created_at, id) and compare the pair, and the position is exact even when the timestamps collide.
Not honestly. Cursor pagination gives next and previous, not random access, because there is no cheap way to know how many rows sit before an arbitrary cursor. The usual answers are an infinite-scroll or load-more UI, or a separate cheap count when the filter is narrow enough. Faking page numbers by walking cursors in a loop reintroduces exactly the cost that cursor pagination removed.
You get 422 with error.code invalid_request and a details object naming the field: {"error":{"code":"invalid_request","message":"One or more parameters are invalid.","details":{"limit":"Must be between 1 and 500."},"request_id":"req_9f2c41a8b3d5"}}. The limit defaults to 50 and caps at 500 across the API. Some endpoints also cap the total depth you can walk: the research findings feed stops at 500 rows per status, which is a deliberate limit rather than an error.

Keep reading

Rate limit

Paging multiplies requests, and the ceiling is per key.

API scope

The permission a list endpoint checks before it answers.

Exponential backoff

Resuming a walk that failed halfway through.

Webhook

The push model that removes the need to page for changes.

Data API reference

Every list endpoint, its parameters and its page block.

Data API

One envelope across seven platforms and 127 endpoints.

Ready to ship

Page a million rows. Skip none of them.

Signed, opaque cursors and a documented stop condition on every list endpoint in the Pinlyx Data API.

Get an API keyRead the docs

Free forever plan · GDPR-ready · No credit card required

We value your privacy

We use cookies to improve our site, analyze traffic, and personalize ads. You can accept all, reject non-essential, or customize your choices. Read our Cookie Policy.