Currently Available: Need a skilled Software Developer for your next project?
Categories
APIs

Designing High-Volume Pagination for Backend Coding Challenges

In a backend coding challenge, a request to fetch a user’s records needs a way to return a large collection in manageable pieces. The prompt fragment “fetch a user’s …” does not name the resource, so these examples use posts. The same design applies to messages, orders, or events.

For a small collection, numbered pages can work. For a large collection that clients browse from newest to oldest, use keyset pagination: return a batch of rows, then have the client request the next batch using the last row’s sort values. This keeps the database from skipping all earlier rows on every request. The query still needs a consistent order, an index that matches that order, a maximum page size, and a clear explanation of what happens when records change between requests.

Choose pagination for the way clients browse

Offset pagination asks the database to skip a number of rows, then return the next batch. It supports numbered pages and jumps to a chosen page, which can suit small collections or interfaces that need that feature. But deep offsets can cost more because the database still computes the skipped rows. PostgreSQL also warns that LIMIT and OFFSET need a predictable order; without one, different requests can return inconsistent subsets (PostgreSQL’s LIMIT and OFFSET documentation).

Keyset pagination asks for rows after the last row the client received. It suits feeds and other large collections that clients browse forward or backward. It does not naturally support jumping to an arbitrary page number, so keep offset pagination when that capability is a real product requirement. Zendesk’s comparison of pagination methods and Zalando’s API guidelines describe these trade-offs.

For a feed of a user’s newest posts, keyset pagination is a good default. The endpoint can look like GET /users/me/posts?limit=...&after=..., where after is a cursor token that tells the server where to continue.

Make the order unique

A database does not guarantee row order unless the query sets one. When two posts share a timestamp, sorting only by created_at does not determine which comes first. Add a unique tie-breaker, such as the post ID, so each row has its own position.

For PostgreSQL, a query for the first page can use:

SELECT id, created_at, body
FROM posts
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT $2;

For a later page, add a condition that selects rows with a timestamp and ID lower than those of the last post, following the descending order:

SELECT id, created_at, body
FROM posts
WHERE user_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;

The client should not choose the user_id used to authorize the request. The server should take that ID from the authenticated user and include it in the query. The cursor values should come from the final post returned on the previous page, and the cursor should preserve every sort key used in the query.

This tuple comparison fits the example because both sort keys use descending order and are non-null. For nullable keys or mixed ascending and descending order, define the comparison and null ordering explicitly; the same condition is not a universal substitute.

Match the index to the query

Create an index that starts with the user filter and continues with the ordered columns:

CREATE INDEX posts_user_created_id_idx
ON posts (user_id, created_at DESC, id DESC);

The first column, user_id, lets PostgreSQL narrow the search to one user’s posts. The other columns match the sort order. PostgreSQL explains that multicolumn B-tree indexes, which store values from several columns in sorted order, work best when a query filters on the first columns. A matching index can also return rows in order for LIMIT queries (multicolumn index guidance).

An index adds storage and write overhead, so check the query plan and measure the endpoint against realistic data before treating this exact index as the answer for every schema. PostgreSQL also recommends using indexes sensibly because they speed retrieval while adding system overhead (PostgreSQL’s index overview). A cursor alone does not make a query fast; the database still needs an efficient way to find the next rows.

Bound requests and define the cursor

Check the requested page size and set a maximum. The limit keeps a request from asking the server to return an unbounded collection. Choose the cap based on response size and workload; each API sets its own limits. For example, Stripe documents a limit range for its report-runs endpoint, but that range is not a general standard (Stripe’s report-runs API).

The server can request one more row than the client’s page size. It returns no more than the requested number and checks the extra row to see whether more results exist. This avoids calculating an exact total when the client only needs to know whether to offer a next page.

Keep the cursor opaque, meaning the client treats it as a token and does not inspect or construct its contents. The cursor can encode the last row’s sort values and the query context, such as active filters and sort order. The server should reject a cursor that does not match the current query contract, or document exactly how it interprets that cursor. Signing the token is one implementation option for detecting tampering; the API still needs to validate its contents.

Return a clear end condition. For example, the response can return the records with a nextCursor and a hasMore field to show whether another page exists. When no further page exists, document whether the cursor is null or whether the API uses another convention. Do not calculate an exact total unless the client needs it and the database can support that cost.

Explain what changes between requests

A cursor keeps page boundaries from shifting just because someone inserts or deletes earlier rows. With offset pagination, deleting a row before the next offset shifts later rows and can cause the client to miss one. Zalando’s guidelines also explain that changes between requests can cause offset-based pagination to return missing or duplicate entries.

Keyset pagination does not create a fixed snapshot across separate HTTP requests. If a post’s sort key changes, or the set of posts matching a filter changes, later pages reflect the newer database state. If a challenge requires a consistent export, state that requirement separately and design for a snapshot or export job rather than promising that an ordinary cursor provides one.

Demonstrate the design in an interview

A strong solution states its assumptions and explains how each choice fits the query. Say that the example fetches the authenticated user’s posts, orders them by timestamp and unique ID, sets a page-size limit, and uses a cursor to move forward through the results. Then explain why the index follows the filter and sort columns.

Test repeated timestamps to confirm the tie-breaker works. Also test an empty result and a final partial page. Check invalid or oversized limits and malformed cursors. Also test that changing a request parameter cannot expose another user’s records. Finally, insert or delete a post between page requests and describe the consistency behavior the endpoint promises.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *