Les Labs / Backend

BackendWorking~7h

Cursor-Based Pagination Under Live Writes

A paginated API endpoint over a growing table that uses an opaque, encoded cursor (not raw offset/limit) so results stay stable as rows are inserted or deleted mid-scroll. Includes a seeding script that inserts rows concurrently with pagination requests to prove correctness under real churn.

Démarrer ce projet

Ce que ça prouve

La phrase que vous pouvez défendre en entretien.

Ligne de CV

Implemented cursor-based pagination with opaque tie-breaker cursors, replacing offset pagination to eliminate duplicate and skipped rows under concurrent inserts, verified with a concurrent-write test harness.

route
map
prove

Le brief

Ce que vous construisez, étape par étape.

01GET /items?cursor=&limit= orders results by (created_at, id) with id as a tie-breaker for rows sharing a timestamp
02Cursor is an opaque, base64-encoded token containing the last-seen (created_at, id) pair; clients never construct or parse it themselves
03Query uses a keyset WHERE clause (created_at, id) > (cursor.created_at, cursor.id) rather than OFFSET, so page cost stays O(limit) regardless of position
04Response includes next_cursor (null on the last page) and does not leak total count or page number as an implicit contract
05A load-test script inserts new rows between paginated requests and walks the full result set to verify no row is skipped or duplicated
06An index on (created_at, id) is created and its use is confirmed via EXPLAIN so pagination doesn't degrade to a sequential scan

La preuve

C’est terminé quand ces critères sont validés.

Walking all pages while inserting new rows between requests yields zero duplicate IDs and zero skipped existing IDs

automated test

Tampering with the decoded cursor payload (e.g. injecting a future timestamp) is rejected with 400, not a SQL error

automated test

EXPLAIN ANALYZE on a paginated query shows an index scan, not a sequential scan, at page 500

checked output

Offset-based pagination on the same dataset is shown (in a comparison test) to skip or duplicate rows under identical concurrent inserts

automated test

Stack

Node.jsExpressPostgreSQLbase64/JSON cursor encodingVitest

Sage Method

route → map → prove

Vous conservez

Paginated API + concurrent-write test harness + EXPLAIN output proving index usage

← Retour à tous les projets

Cursor-Based Pagination Under Live Writes

Ce que vous construisez, étape par étape.

01GET /items?cursor=&limit= orders results by (created_at, id) with id as a tie-breaker for rows sharing a timestamp

02Cursor is an opaque, base64-encoded token containing the last-seen (created_at, id) pair; clients never construct or parse it themselves

03Query uses a keyset WHERE clause (created_at, id) > (cursor.created_at, cursor.id) rather than OFFSET, so page cost stays O(limit) regardless of position

04Response includes next_cursor (null on the last page) and does not leak total count or page number as an implicit contract

05A load-test script inserts new rows between paginated requests and walks the full result set to verify no row is skipped or duplicated

06An index on (created_at, id) is created and its use is confirmed via EXPLAIN so pagination doesn't degrade to a sequential scan

C’est terminé quand ces critères sont validés.

Walking all pages while inserting new rows between requests yields zero duplicate IDs and zero skipped existing IDs

automated test

Tampering with the decoded cursor payload (e.g. injecting a future timestamp) is rejected with 400, not a SQL error

automated test

EXPLAIN ANALYZE on a paginated query shows an index scan, not a sequential scan, at page 500

checked output

Offset-based pagination on the same dataset is shown (in a comparison test) to skip or duplicate rows under identical concurrent inserts

automated test