Interview frequency: …
Short answer
Use offset/limit for simplicity with small, stable data, and a cursor (keyset) for large or changing data, with deterministic ordering and an opaque next token.
Explanation
With offset the database must skip all the previous rows, so it slows down on deep pages, and rows may repeat or be skipped if data is added while browsing. A cursor relies on the last value you saw (such as created_at and id together), so the query stays constant in speed using an index. Make the ordering deterministic by adding a unique key, encode the token (for example signed base64) so the client does not depend on or tamper with it, and set a maximum page size.
Code example
def list_orders(conn, limit: int = 50, cursor: tuple | None = None):
limit = min(limit, 100)
if cursor is None:
rows = conn.execute(
"SELECT id, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT ?", (limit,)
).fetchall()
else:
created_at, last_id = cursor
rows = conn.execute(
"SELECT id, created_at FROM orders WHERE (created_at, id) < (?, ?) "
"ORDER BY created_at DESC, id DESC LIMIT ?", (created_at, last_id, limit)
).fetchall()
next_cursor = (rows[-1][1], rows[-1][0]) if len(rows) == limit else None
return rows, next_cursorCommon mistakes
- Ordering by a non-unique column such as created_at alone, so rows repeat or get lost between pages.
- Letting the client request an unbounded limit.