API Pagination Qu' lIj

growing table rows Hoch wa' response Daq nob vaj development Daq Dun, production Daq customer 50,000 records ghaj jaj Qaw'. Pagination 'oH Qotlh, 'ach naive approach (LIMIT/OFFSET) correctness bug ghaj: concurrent writes Daq neH tu' - production Daq 'oH condition, tests Daq pagh.

offset pagination: simple, silently wrong

obvious approach: page number pass, offset compute, database rows skip.

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

app.get('/posts', async (req, res) => {
  const page = Number(req.query.page ?? 1);
  const pageSize = 20;
  const offset = (page - 1) * pageSize;

  const posts = await db.query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT $1 OFFSET $2',
    [pageSize, offset],
  );
  res.json(posts);
});

Qu', small mostly-static tables vaD Dun, UI page numbers (1, 2, 3 …) poQ. table tIn 'ej actively written vaj cha' real problems.

Qagh 1: offset performance degrade

OFFSET 100000 row 100,000 Daq jump pagh - database first 100,000 matching rows scan discard, next page return. page 1 nom; page 5,000 dramatically slower (same query shape).

Qagh 2: concurrent writes items shift

correctness bug (performance neH pagh). client created_at DESC results page, page 1 'ej page 2 requests Dung new row insert. rows Hoch wa' position down - page 1 last item page 2 Daq push, client cha'logh legh. row delete vaj Hoch up shift, client wa' silently skip. error pagh; client subtly wrong data.

cursor pagination: cha' fix

"N rows skip" pagh, cursor "rows after this specific one nob". cursor previous page last row sort key (row count pagh).

SELECT * FROM posts
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;

app.get('/posts', async (req, res) => {
  const pageSize = 20;
  const cursor = req.query.cursor; // ISO timestamp of the last item seen, or undefined

  const posts = await db.query(
    cursor
      ? 'SELECT * FROM posts WHERE created_at < $1 ORDER BY created_at DESC LIMIT $2'
      : 'SELECT * FROM posts ORDER BY created_at DESC LIMIT $1',
    cursor ? [cursor, pageSize] : [pageSize],
  );

  const nextCursor = posts.length === pageSize ? posts[posts.length - 1].created_at : null;

  res.json({ items: posts, nextCursor });
});

query created_at < $1 filter (row count skip pagh) - top Daq new row insert vaj pagh shift. cursor Hoch same actual row point. created_at Daq index rap fast range scan (OFFSET accumulating scan cost pagh).

cursor unique tiebreaker poQ

created_at neH Qaw' cha' rows exact same timestamp vaj - wa' skip pagh duplicate. primary key tiebreaker chel, cha' cursor Daq encode:

SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

function encodeCursor(row) {
  return Buffer.from(`${row.created_at.toISOString()}|${row.id}`).toString('base64');
}

function decodeCursor(cursor) {
  const [createdAt, id] = Buffer.from(cursor, 'base64').toString().split('|');
  return { createdAt, id };
}

Base64 cursor encode security pagh - client perspective opaque token. later choH laH (field chel, sort column choH) clients Qaw' pagh.

clients nuq legh

cursor-paginated response: total count pagh, page number pagh, "next token" neH:

{
  "items": [ { "id": 512, "title": "..." }, { "id": 511, "title": "..." } ],
  "nextCursor": "MjAyNi0wNy0xN1QwOTozMDowMFp8NTEx"
}

client nextCursor store, ?cursor=... next page vaD nob. opaque: parse pagh hand construct pagh. contract backend pagination internals choH laH (breaking API change pagh).

offset still lo'

Cursor pagination arbitrary page number jump ("page 47") laHbe' - cursor not-yet-fetched page vaD compute laHbe'. UI numbered pages direct jumps poQ (admin table page selector) vaj offset pagination Dun, small tables (performance cliff Daq qatlh pagh). table tIn, concurrent writes, client "next" "previous" neH poQ vaj cursor.

yaj

Offset pagination simple, small page-numbered UIs Dun. table tIn 'ej concurrent writes vaj OFFSET slower 'ej silently incorrect. Cursor pagination ("after this row", unique tiebreaker) cha' fix - arbitrary page jumps lose.