Limit-Offset Parameters

Limit and offset parameters let an API return a bounded slice of a larger result set. `limit` controls the number of records returned, while `offset` indicates how many records to skip before collecting results.

In a paginated API, `limit` represents the requested page size. For example, `limit=20` asks the server for at most 20 records. The actual number can be smaller when fewer records remain or when the server enforces a maximum page size.

`offset` represents the starting position within the ordered result set. With `offset=0`, the API starts at the first record; with `offset=20` and `limit=20`, it skips the first 20 records and requests the next 20.

A common SQL implementation is `LIMIT ? OFFSET ?`. The API should also define a stable ordering, such as `ORDER BY created_at DESC, id DESC`, because pagination without deterministic ordering can produce inconsistent pages.

Offset pagination is easy to understand and implement, but large offsets can become inefficient because the database may need to scan or discard many preceding rows. It can also be affected by inserts or deletes between requests, causing records to appear twice or be skipped.

In a Java backend, these parameters are commonly represented by request parameters such as `?limit=20&offset=40`, validated before being passed to the data-access layer. A production API should usually impose sensible bounds on `limit` and reject negative `offset` values.

Key points

• `limit` controls the maximum number of records requested.
• `offset` specifies how many records to skip before returning records.
• Use deterministic ordering when paginating a changing dataset.
• Validate pagination parameters and enforce a maximum `limit`.
• Large offsets can become inefficient and may be unstable when data changes.

Interview questions

What do `limit` and `offset` mean in API pagination?

`limit` specifies how many records the API should return at most. `offset` specifies how many records should be skipped before collecting the requested records. For example, `limit=10&offset=20` requests records starting after the first 20, with at most 10 returned. The server should validate both values before querying the database.

How would you implement limit-offset pagination in a Java backend?

I would accept pagination parameters at the controller boundary and validate that `limit` is positive and within a configured maximum, while `offset` is non-negative. The service would pass validated values to the repository or database query. The query would apply a deterministic ordering together with the limit and offset. The response could also include pagination metadata when the API contract requires it.

What problems can occur when using offset pagination on frequently changing data?

If records are inserted or deleted between two requests, the positions of later records can shift. A newly inserted record can cause an item to appear on two pages, while a deletion can cause an item to be skipped. This happens because the offset describes a position rather than a durable record boundary. A stable ordering helps make results deterministic, but it does not completely remove this shifting-position problem.

Why can a large OFFSET be inefficient in a database?

An offset identifies rows by their position in the ordered result rather than by a direct boundary value. Depending on the database, obtaining a later page can require processing or traversing many preceding rows before the requested rows can be returned. As the offset grows, that work can become expensive. Cursor or keyset pagination can avoid much of this by continuing from a known indexed ordering value.