Build cursor pagination that survives writes

Page 4 is not a stable place in a changing collection.

If three new orders arrive before a user requests the next page, OFFSET can shift previously viewed rows forward. The next query may repeat them. A deletion can pull unseen rows backward and cause the client to skip them. A non-unique timestamp can make the boundary ambiguous even when nothing changes.

PostgreSQL's current LIMIT and OFFSET documentation warns that different subsets are inconsistent without a predictable ORDER BY and that large offsets still require the skipped rows to be computed. Its index ordering guidance explains how matching B-tree order can satisfy ORDER BY with LIMIT directly.

Cursor pagination replaces position with a boundary: after the ordered key represented by this opaque token, return the next bounded set. That is faster only when the ordering and index align, and it is correct only when ties, filters, directions, mutations, permissions, and cursor expiry are explicit.

This tutorial builds that contract around (created_at DESC, id DESC) and tests the cases that make a demo query fail in production.

Define the product ordering

Start with the order users believe they are browsing and decide whether updates are allowed to move an item after it has crossed their cursor.

I would pressure-test that decision with four questions:

• What does newest mean?
• Which field may change?
• Do pinned rows exist?
• What should happen after an edit?

The failure mode here is choosing the easiest indexed column without describing user-visible order. In PostgreSQL-backed feeds, admin tables, search results, audit logs, orders, messages, and APIs where rows are inserted, updated, or deleted while a client moves through an ordered collection, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be an ordering-semantics worksheet. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is one documented sequence across UI, API, and database. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put an ordering-semantics worksheet beside the question “What does newest mean?” before the first implementation review. The next pass would use “Which field may change?” to test the boundary, then “Do pinned rows exist?” to expose the state most likely to be missed. I would keep “What should happen after an edit?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports one documented sequence across UI, API, and database.

Make the order unique

Every sort must end in a stable unique tiebreaker so two rows can never occupy the same unresolved boundary.

The practical review starts here:

• Can timestamps tie?
• Is the identifier immutable?
• Does collation affect equality?
• How are nulls ordered?

Those questions keep using created_at alone because it looks precise to milliseconds from becoming the default. I would capture the decision in a total-order definition, then use it while the work is still cheap to change. For pagination that stays predictable while the underlying collection changes, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like deterministic row order across repeated executions. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a total-order definition part of the working surface. I would use it to answer “Can timestamps tie?” while scope is still flexible, and “Is the identifier immutable?” before code or content becomes expensive to unwind. During QA, “Does collation affect equality?” and “How are nulls ordered?” become concrete checks rather than discussion prompts. That sequence turns pagination that stays predictable while the underlying collection changes into something the team can operate and gives me a specific outcome to report: deterministic row order across repeated executions.

Build the matching index

The B-tree key order, directions, null placement, leading scope columns, and included data should support both the seek predicate and ORDER BY without a large sort.

Before implementation, I would answer:

• Which filters are always present?
• Does index direction match?
• Can an index-only scan help?
• What write cost is acceptable?

The artifact is a query-and-index pair with EXPLAIN evidence. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is adding an index on each field separately and expecting composite order; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is bounded work as the traversal grows deep. That connects a keyset-pagination contract built from a unique stable ordering, matching B-tree index, opaque scoped cursor, directional seek predicate, explicit mutation semantics, bounded page size, and adversarial concurrency tests to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “Which filters are always present?” easy to answer. The boundary should force a decision about “Does index direction match?” and “Can an index-only scan help?.” I would record both in a query-and-index pair with EXPLAIN evidence, including the part that stayed unresolved after the first pass. The final check, “What write cost is acceptable?,” is where the artifact earns its place: it either supports bounded work as the traversal grows deep, or it shows exactly why another iteration is needed.

Encode an opaque cursor

The client token should contain only the versioned boundary and scope needed to continue, then be signed or authenticated so values cannot be altered.

I would use these prompts during the working review:

• Which keys form the boundary?
• Which filter digest is required?
• How does versioning work?
• Should the token expire?

If the team slips into exposing raw database values as an editable query string, the product can still look complete while its operating rule stays ambiguous. I would make a cursor payload schema and encoder the shared reference and keep it small enough to update as evidence changes.

The standard is tamper-resistant continuation state that can evolve. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft a cursor payload schema and encoder, review it against “Which keys form the boundary?,” implement the narrowest useful path, and then return with evidence for “Which filter digest is required?.” I would use “How does versioning work?” to inspect product consequence and “Should the token expire?” to decide whether the result is stable enough to ship. This keeps exposing raw database values as an editable query string visible as a known risk and makes tamper-resistant continuation state that can evolve the release receipt rather than a hopeful conclusion.

Write the lexicographic seek

For descending (created_at,id), the next page is strictly less than the tuple; mixed directions and nulls require an expanded predicate or carefully matched tuple semantics.

I would pressure-test that decision with four questions:

• Which comparator matches direction?
• Is the boundary exclusive?
• How are mixed directions expressed?
• What do nulls mean?

The failure mode here is using less-than on the first field and losing tied rows. In PostgreSQL-backed feeds, admin tables, search results, audit logs, orders, messages, and APIs where rows are inserted, updated, or deleted while a client moves through an ordered collection, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a reviewed forward-seek SQL query. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is every eligible row appearing once within the chosen semantics. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a reviewed forward-seek SQL query beside the question “Which comparator matches direction?” before the first implementation review. The next pass would use “Is the boundary exclusive?” to test the boundary, then “How are mixed directions expressed?” to expose the state most likely to be missed. I would keep “What do nulls mean?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports every eligible row appearing once within the chosen semantics.

Return one extra row

Fetch pageSize + 1, trim the sentinel, and derive continuation from the last returned item so has-next does not require an expensive count.

The practical review starts here:

• What is the maximum page size?
• Which row becomes the cursor?
• How is an empty page represented?
• Does count have separate semantics?

Those questions keep running COUNT(*) on every interaction to display certainty users do not need from becoming the default. I would capture the decision in a page-envelope contract, then use it while the work is still cheap to change. For pagination that stays predictable while the underlying collection changes, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like cheap accurate continuation without a full collection scan. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a page-envelope contract part of the working surface. I would use it to answer “What is the maximum page size?” while scope is still flexible, and “Which row becomes the cursor?” before code or content becomes expensive to unwind. During QA, “How is an empty page represented?” and “Does count have separate semantics?” become concrete checks rather than discussion prompts. That sequence turns pagination that stays predictable while the underlying collection changes into something the team can operate and gives me a specific outcome to report: cheap accurate continuation without a full collection scan.

Bind filters and authorization

A cursor created for one tenant, viewer, search term, status, sort, or permission set must not silently continue under another scope.

Before implementation, I would answer:

• Which scope fields are signed?
• Can permissions change?
• Does search ranking stay stable?
• How does a mismatch fail?

The artifact is a cursor-scope validation table. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is replaying a valid boundary against a different tenant query; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is continuation that cannot widen data access or corrupt results. That connects a keyset-pagination contract built from a unique stable ordering, matching B-tree index, opaque scoped cursor, directional seek predicate, explicit mutation semantics, bounded page size, and adversarial concurrency tests to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “Which scope fields are signed?” easy to answer. The boundary should force a decision about “Can permissions change?” and “Does search ranking stay stable?.” I would record both in a cursor-scope validation table, including the part that stayed unresolved after the first pass. The final check, “How does a mismatch fail?,” is where the artifact earns its place: it either supports continuation that cannot widen data access or corrupt results, or it shows exactly why another iteration is needed.

Choose mutation semantics

Inserts before the boundary, deletes, and updates to sort keys have different effects; document whether traversal is live, append-oriented, best-effort, or tied to a snapshot.

I would use these prompts during the working review:

• Can rows move backward?
• Can rows reappear after updates?
• Must every initial row be seen?
• When is a snapshot required?

If the team slips into promising snapshot consistency from an ordinary live query, the product can still look complete while its operating rule stays ambiguous. I would make a concurrent-mutation behavior contract the shared reference and keep it small enough to update as evidence changes.

The standard is user expectations matched to the collection's change model. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft a concurrent-mutation behavior contract, review it against “Can rows move backward?,” implement the narrowest useful path, and then return with evidence for “Can rows reappear after updates?.” I would use “Must every initial row be seen?” to inspect product consequence and “When is a snapshot required?” to decide whether the result is stable enough to ship. This keeps promising snapshot consistency from an ordinary live query visible as a known risk and makes user expectations matched to the collection's change model the release receipt rather than a hopeful conclusion.

Support backward navigation deliberately

Previous-page cursors need inverted comparison and ordering, plus a final reversal, rather than reusing forward SQL with a different token name.

I would pressure-test that decision with four questions:

• What does before mean?
• Which index scan is used?
• How is display order restored?
• Can browser history reuse pages?

The failure mode here is returning the right rows in the wrong visible order. In PostgreSQL-backed feeds, admin tables, search results, audit logs, orders, messages, and APIs where rows are inserted, updated, or deleted while a client moves through an ordered collection, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a symmetric backward-query fixture. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is consistent next, previous, and back-button behavior. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a symmetric backward-query fixture beside the question “What does before mean?” before the first implementation review. The next pass would use “Which index scan is used?” to test the boundary, then “How is display order restored?” to expose the state most likely to be missed. I would keep “Can browser history reuse pages?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports consistent next, previous, and back-button behavior.

Test adversarial boundaries

Fixtures should tie timestamps, insert before and after the cursor, delete boundary rows, update sort keys, change permissions, tamper tokens, expire versions, traverse both ways, and inspect query plans.

The practical review starts here:

• Can any row repeat unexpectedly?
• Can an eligible row vanish?
• Does tampering fail closed?
• Does latency stay flat deep in the set?

Those questions keep testing only two static pages with sequential IDs from becoming the default. I would capture the decision in a pagination concurrency and plan test suite, then use it while the work is still cheap to change. For pagination that stays predictable while the underlying collection changes, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like correctness and bounded performance under production-shaped writes. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a pagination concurrency and plan test suite part of the working surface. I would use it to answer “Can any row repeat unexpectedly?” while scope is still flexible, and “Can an eligible row vanish?” before code or content becomes expensive to unwind. During QA, “Does tampering fail closed?” and “Does latency stay flat deep in the set?” become concrete checks rather than discussion prompts. That sequence turns pagination that stays predictable while the underlying collection changes into something the team can operate and gives me a specific outcome to report: correctness and bounded performance under production-shaped writes.

What I would show in the work

The public version needs evidence from the work itself. For this topic, the first five artifacts I would reach for are:

• an ordering-semantics worksheet
• a total-order definition
• a query-and-index pair with EXPLAIN evidence
• a cursor payload schema and encoder
• a reviewed forward-seek SQL query

I would not publish all five at equal weight. One should orient the reader, one should reveal the hardest tradeoff, and one should prove the result. The others can live in a downloadable note or appear as supporting frames. That edit matters because a keyset-pagination contract built from a unique stable ordering, matching B-tree index, opaque scoped cursor, directional seek predicate, explicit mutation semantics, bounded page size, and adversarial concurrency tests becomes harder to understand when every process detail is treated as equally important.

I would also show one rejected direction. The useful version is specific: which option looked attractive, which constraint made it wrong, and what evidence supported the narrower choice. That gives an engineering manager something real to question and keeps the case study from reading like the final answer was obvious from the beginning.

# payload
v1 / tenant t_81 / orders-open
Sort created_at desc,id desc; boundary 2026-07-19T21:04:18.331Z + 88201; page size policy 25; filter digest 64b…
# query
WHERE (created_at,id) < ($1,$2)
Tenant and authorization predicates apply first; ORDER BY matches index; LIMIT 26 detects another page without a count query.
# guard
HMAC / expires / reject scope drift
Signature prevents tampering, filter and viewer scope prevent replay elsewhere, version allows migration, and errors restart safely.

Resource path

The practical follow-up I would build is a PostgreSQL cursor-pagination starter with ordering worksheet, composite index examples, forward and backward seek queries, cursor schema and signing, filter scope, null handling, page envelope, snapshot alternatives, mutation fixtures, query-plan checks, and API compatibility tests. I am treating that as a resource backlog item, not pretending the adjacent downloads below are the same artifact. The related cards cover useful pieces of the workflow today; this specific file should only be published when its examples, fields, and instructions are complete.

The first version should stay concise: context, constraint, decision, evidence, owner, and follow-up. Its value would come from helping someone repeat this exact review, not from adding another generic PDF to the site.

Review checklist

The article-specific review questions are:

• What does newest mean?
• Can timestamps tie?
• Which filters are always present?
• Which keys form the boundary?
• Which comparator matches direction?
• What is the maximum page size?
• Which scope fields are signed?
• Can rows move backward?
• What does before mean?
• Can any row repeat unexpectedly?

I would add two editorial checks before publishing: can a recruiter find the point in the first minute, and can an engineer trace at least one claim to an implementation or production receipt? If either answer is no, the article needs another edit.

Implementation notes

For pagination that stays predictable while the underlying collection changes, I would write the implementation note before polish. It would name the changed surface, source of truth, owner, failure boundary, and verification path. Those details prevent the principle from floating above the actual code or operational workflow.

The proof signals I care about are specific to this article:

• cheap accurate continuation without a full collection scan
• continuation that cannot widen data access or corrupt results
• user expectations matched to the collection's change model
• consistent next, previous, and back-button behavior
• correctness and bounded performance under production-shaped writes

I would choose two or three of those signals for the first release rather than instrumenting everything. The strongest pair usually combines one direct behavior check with one operating check: a route and a data query, a keyboard path and a support state, a handler replay and a reconciliation result, or a migration count and a rendered screen.

The follow-up belongs in the note before shipping. It should say what remains temporary, what evidence would trigger another pass, and who owns that decision. That is how the first version stays intentionally narrow without making the boundary invisible.

Case-study packaging

I would structure the case-study version around the four visual lessons already established:

• The cursor carries an ordering boundary, not a page number.
• One traversal preserves its own boundary.
• Pagination choices promise different consistency.
• The token is versioned, scoped, and verifiable.

The opening frame explains the product pressure. The middle two show the decision moving through the system. The last frame is the receipt: what was checked, what held, and what remained unresolved. That order lets the reader move from product judgment into implementation detail without reconstructing the whole project first.

I would include one caveat tied to PostgreSQL-backed feeds, admin tables, search results, audit logs, orders, messages, and APIs where rows are inserted, updated, or deleted while a client moves through an ordered collection: a data limit, rollout boundary, unsupported state, external dependency, or result that is still directional. A precise caveat makes the evidence easier to trust because it shows where the claim stops.

The final test is whether the page creates a better conversation. If the artifact helps someone ask a sharper question about product judgment, implementation detail, or release proof in a live interview, it belongs in the story.

Interview angle

In an interview, I would explain this through a keyset-pagination contract built from a unique stable ordering, matching B-tree index, opaque scoped cursor, directional seek predicate, explicit mutation semantics, bounded page size, and adversarial concurrency tests. The story should start with the product pressure, then move into the system constraint, the artifact, and the proof. That order keeps the answer grounded. It also gives the interviewer several places to go deeper: data, frontend architecture, design systems, support, migration, accessibility, or release process.

The strongest version of the answer includes a tradeoff. I want to be able to say what I chose, what I left alone, and how I knew the work helped. That is more credible than presenting every project as a clean win.

The hiring signal

A production cursor-pagination design is a hiring signal because it shows I can connect database ordering, indexes, API state, concurrent writes, security boundaries, UX navigation, and performance evidence.

That is the level I want this site to communicate. The work should show taste, but it should also show operating judgment. It should make me look like someone who can enter a real product system, understand the messy middle, ship the useful version, and leave enough proof for the next person to trust it.