Pagination Queries in SQL

Introduction

Pagination divides a large ordered query result into smaller pages instead of returning every row at once. A product catalog may display twenty products per page, a support dashboard may show fifty recent tickets, and an API may return a continuation cursor for the next group of events.

SELECT order_id, customer_id, created_at
FROM orders
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 0;

Good pagination is more than adding LIMIT and OFFSET. It requires a deterministic order, a clearly defined result grain, stable page boundaries, bounded page size, appropriate indexes, authorization before limiting, and an explicit decision about how concurrent inserts, updates, and deletes should affect navigation.

Why Pagination Is Needed

Operational tables can contain millions of rows, but users normally inspect only a small portion at a time. Returning the entire result wastes database work, network bandwidth, serialization effort, application memory, and browser rendering capacity. It can also turn an ordinary endpoint into an accidental bulk export.

Pagination bounds each response and improves interaction speed. It does not automatically make the underlying query fast. The database may still filter, join, aggregate, sort, or skip a large number of rows before producing a page.

The Pagination Contract

A complete pagination contract defines the ordered dataset, page boundary, page size, continuation method, consistency model, and response metadata. If any of these is unclear, pages can overlap, omit records, expose data, or change unexpectedly.

RELIABLE PAGINATION = AUTHORIZED FILTERED DATA
                      + DEFINED RESULT GRAIN
                      + DETERMINISTIC ORDER
                      + STABLE PAGE BOUNDARY
                      + BOUNDED PAGE SIZE

Pagination Requires ORDER BY

Without ORDER BY, there is no stable definition of the first, twentieth, or next row. The database may return rows according to any valid execution plan, and that plan can change after an index, statistics update, deployment, or concurrent modification.

An unordered LIMIT with OFFSET merely skips an arbitrary set and returns another arbitrary set. Pagination intended for users or integrations should always define the sequence explicitly.

Deterministic Ordering

ORDER BY created_at DESC is incomplete when several rows can share a timestamp. Rows tied at a page boundary can move between requests. Adding a unique final key creates a total order.

ORDER BY created_at DESC, order_id DESC

The tie-breaker must be present in both the ORDER BY list and any keyset cursor. It should be immutable or sufficiently stable for the browsing session.

Two Main Pagination Strategies

StrategyBoundaryBest suited for
OFFSET paginationNumber of rows to skipPage numbers and modest datasets
Keyset paginationLast ordered key from prior pageFeeds, deep navigation, and large datasets

Neither strategy is universally superior. Product navigation, data volume, consistency expectations, and required jump behavior should drive the choice.

OFFSET Pagination

OFFSET pagination calculates how many ordered rows precede the requested page. For a one-based page number, the offset is (page number - 1) multiplied by page size.

SELECT product_id, product_name, price
FROM products
WHERE category_id = :category_id
ORDER BY price, product_id
LIMIT 20 OFFSET 40;

This returns page three when the page size is twenty. It is easy to understand, supports direct page jumps, and works well when users rarely navigate deeply.

LIMIT and OFFSET Across Databases

MySQL, PostgreSQL, and SQLite commonly support LIMIT with OFFSET. MySQL also accepts LIMIT offset, count, though the explicit OFFSET form is easier to read. SQL Server uses ORDER BY with OFFSET rows and FETCH NEXT rows only. Several databases support the SQL-standard-style FETCH syntax.

Do not assume that syntax, parameter binding, NULL ordering, or optimizer behavior is identical across products. Keep dialect-specific SQL isolated and test it against the actual production version.

SQL Server OFFSET FETCH

SELECT product_id, product_name, price
FROM products
WHERE category_id = @category_id
ORDER BY price, product_id
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

SQL Server requires ORDER BY for OFFSET FETCH. The same deterministic-ordering rule applies: price alone is not enough when products can share a price, so product_id completes the sequence.

Pagination with Filters

Every page request must apply the same tenant, authorization, search, status, date, and soft-delete filters. Changing filters between pages means navigating a different dataset, so an old cursor or offset no longer represents the same boundary.

SELECT ticket_id, priority, updated_at
FROM tickets
WHERE tenant_id = :tenant_id
  AND status = :status
  AND deleted_at IS NULL
ORDER BY updated_at DESC, ticket_id DESC
LIMIT :page_size OFFSET :offset;

Pagination and Result Grain

Result grain describes what one output row represents. Pagination might be over customers, orders, products, or grouped monthly totals. A page boundary is correct only when it is applied at the intended grain.

State the grain before writing joins. If one row should represent one customer, paginating a customer-order join can let one customer consume many slots and split that customer across pages.

Paginating Parent Rows Before Child Joins

When a page lists customers and each customer includes child data, select the customer page first and then join or separately load the required children.

WITH customer_page AS (
  SELECT customer_id, customer_name
  FROM customers
  WHERE tenant_id = :tenant_id
  ORDER BY customer_name, customer_id
  LIMIT 20 OFFSET :offset
)
SELECT p.customer_id, p.customer_name, a.city
FROM customer_page AS p
LEFT JOIN addresses AS a
  ON a.customer_id = p.customer_id
ORDER BY p.customer_name, p.customer_id;

The final result may contain more than twenty physical rows if customers have several addresses, but the page contains twenty customer entities. API serialization must group those detail rows correctly.

Pagination After GROUP BY

SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
WHERE tenant_id = :tenant_id
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
ORDER BY lifetime_value DESC, customer_id
LIMIT 25 OFFSET :offset;

The page consists of grouped customers, not individual orders. LIMIT and OFFSET apply after grouping and HAVING. Counting total pages must count groups under the same conditions rather than raw order rows.

Pagination with DISTINCT

A page of unique categories or cities must apply DISTINCT before the final order and limit. Fetching detail rows first and deduplicating in application code can create underfilled pages and miss values located later in the dataset.

SELECT DISTINCT city
FROM customers
WHERE tenant_id = :tenant_id
ORDER BY city
LIMIT 20 OFFSET :offset;

Deep OFFSET Performance

OFFSET does not mean the database jumps to a page for free. It often must locate, order, and discard every earlier row. Page 50,000 can process nearly a million rows to return twenty, and cost usually grows with page depth.

An index aligned with the filters and order helps, but scanning and discarding a large prefix remains work. Measure deep pages using production-like data rather than judging only page one.

Keyset Pagination

Keyset pagination, also called seek or cursor pagination, continues after the final ordered key from the previous page. Instead of counting how many rows to skip, it uses a comparison predicate that identifies rows following the cursor.

SELECT order_id, customer_id, created_at
FROM orders
WHERE tenant_id = :tenant_id
  AND order_id > :last_order_id
ORDER BY order_id
LIMIT 20;

This simple form works when order_id alone defines the required ascending sequence. It can seek directly into an index and remain efficient far into the dataset.

Descending Keyset Pagination

For descending order, the continuation comparison normally uses less than because later pages contain smaller keys.

SELECT event_id, event_time, event_type
FROM events
WHERE tenant_id = :tenant_id
  AND event_id < :last_event_id
ORDER BY event_id DESC
LIMIT 50;

The comparison direction and ORDER BY direction must agree. Reversing one without the other creates duplicates, skips, or an empty page.

Composite Keyset Pagination

Most feeds order by a business timestamp plus a unique tie-breaker. The cursor must carry both values, and the predicate must reproduce lexicographic continuation.

SELECT order_id, customer_id, created_at
FROM orders
WHERE tenant_id = :tenant_id
  AND (
       created_at < :last_created_at
       OR (created_at = :last_created_at
           AND order_id < :last_order_id)
  )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Comparing only created_at would skip orders sharing the cursor timestamp. Some databases support row-value comparison syntax, but the expanded predicate makes the logic portable and visible.

Mixed-Direction Keysets

A sort such as priority DESC, due_date ASC, task_id ASC requires a continuation predicate that respects each direction in sequence. Such predicates become complex because the comparison operator changes with the corresponding sort direction.

Write and test the lexicographic rule carefully or use a trusted pagination library that understands the database dialect. A cursor containing only one key cannot represent a multi-key order.

OFFSET Versus Keyset Pagination

OFFSET supports page numbers and direct jumps but becomes expensive at depth and is sensitive to row-position changes. Keyset navigation is efficient and generally more stable under leading inserts, but it naturally supports next and previous navigation rather than arbitrary page numbers.

A product catalog may reasonably use offset for shallow page-number browsing, while an activity feed should often use keyset cursors. Some systems use a hybrid: page numbers near the beginning and cursor navigation for deeper results.

Cursor Design

An API cursor represents continuation state. For a composite order, it should include every ordered key needed to resume, as well as version or direction information required by the contract. The cursor is commonly encoded as an opaque token so clients do not construct SQL concepts themselves.

Opaque does not automatically mean secure. Sensitive or trust-relevant cursor data should be signed or encrypted, and the server must validate types, version, expiration, tenant, filters, and sort options before use.

Cursor and Filter Context

A cursor created for status=OPEN and sort=newest should not be reused for status=CLOSED or sort=priority. Encode a hash or representation of the stable filter and sort context, or store cursor state server-side.

Reject incompatible cursors with a clear client error rather than silently producing a different sequence. This makes integration defects visible and prevents accidental cross-scope continuation.

Fetching One Extra Row

An API can determine whether another page exists without an exact COUNT by requesting page size plus one. For a page size of twenty, fetch twenty-one rows. Return the first twenty and set has_more according to whether the extra row exists.

This often avoids an expensive count and fits both offset and keyset pagination. Do not expose the extra record in the response or advance the cursor past it.

Total Count Tradeoffs

Exact total count can be expensive for complex filters, joins, distinct sets, and large datasets. It may cost as much as or more than retrieving the page, and under concurrent writes the count can already differ from later pages.

Ask whether the interface truly needs total pages. Alternatives include has_more, approximate counts, cached counts, bounded statements such as "more than 1,000," or asynchronous report metadata.

COUNT Window Functions

SELECT order_id,
       created_at,
       COUNT(*) OVER () AS total_rows
FROM orders
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET :offset;

COUNT(*) OVER can attach a total to every page row, but it may require processing the full qualifying set and can prevent early stopping. A separate count can be cached or run only when requested. Compare execution plans before choosing.

Concurrent Inserts with OFFSET

Suppose page one shows the newest twenty rows. If five newer rows are inserted before page two is requested, the old rows shift by five positions. OFFSET 20 can repeat five rows from page one and push five unseen rows to a later page.

This is not a SQL bug; it follows from navigating a live changing sequence by position. Product requirements must define whether this behavior is acceptable.

Concurrent Deletes and Updates

Deleting rows from earlier pages shifts later rows forward, so an offset can skip unseen records. Updating a sort key can move a row across the current boundary and cause either repetition or omission with both offset and keyset methods.

Prefer immutable or stable sort keys when possible. If rows must move as status or priority changes, treat the interface as a live feed rather than a fixed report.

Keyset Consistency Limits

Keyset pagination handles new rows inserted before the cursor better because the continuation predicate remains anchored to values already seen. It does not provide full snapshot consistency. Updates can move rows, deletes remove them, and inserts after the cursor can appear on later pages.

Keyset is a continuation mechanism, not an isolation level. Document its live-data behavior and use a snapshot strategy when the use case requires a fixed dataset.

Snapshot and Cutoff Pagination

A report can capture a cutoff such as created_at less than or equal to the first request time and apply it to every page. This prevents newer rows from entering the sequence, though updates and deletes may still affect results unless the data is append-only or versioned.

A long-lived database transaction can preserve a consistent snapshot in some systems but consumes resources and may interfere with cleanup. Saved report results, temporal tables, or report-run IDs are often safer for long navigation sessions.

Text Pagination and Collation

Pagination by name depends on collation rules for case, accents, punctuation, and language-specific ordering. A cursor must be compared under the same collation as ORDER BY, and a unique ID should normally complete the sequence.

ORDER BY customer_name COLLATE chosen_collation,
         customer_id

Changing database or collation can invalidate old cursors because the ordering contract changed. Version cursor formats when comparison semantics evolve.

Nullable Sort Keys

NULL complicates keyset predicates because ordinary less-than and greater-than comparisons with NULL are unknown. Define whether NULL appears first or last and represent that rule in both ORDER BY and the continuation predicate.

A portable design may sort first by a CASE null-rank, then by the nullable value, and finally by a unique ID. The cursor must preserve the null-rank and all following keys.

Pagination Index Design

An effective index usually begins with equality-filter columns and continues with ordered cursor columns. A recent-orders feed filtered by tenant and status and ordered by created_at DESC, order_id DESC may benefit from a composite index on those columns in that sequence.

Included projection columns can make the index covering and avoid table lookups. Coverage increases index size and write cost, so include only frequently needed values and validate plans with realistic parameter distributions.

Keyset Seeks and OFFSET Scans

With an aligned index, a keyset predicate lets the engine seek near the cursor and read the next page. OFFSET typically begins from the ordered range and walks past earlier entries even when the same index supplies order.

This difference explains why keyset latency often remains stable at depth while offset latency grows. Actual behavior still depends on filters, joins, cache state, and the number of rejected rows.

Pagination and Search Engines

Full-text search systems often expose their own continuation tokens or search-after values. Deep numeric offsets can be expensive or restricted because distributed shards must gather and discard many earlier hits.

Use the search engine's native cursor model and preserve score plus tie-breaker keys. Do not assume a relational database cursor can be applied unchanged to an external search index.

Secure Pagination

Tenant, authorization, row-level security, and soft-delete predicates must be applied before ordering and limiting. Post-filtering a global page can expose data and return fewer rows even when authorized records exist later.

Validate page size, page number, cursor length, cursor signature, allowed sort option, and filter values. Parameterize ordinary values and map public sort names to reviewed SQL fragments rather than concatenating raw input.

Pagination API Response Design

An offset response may return items, page_size, page_number, has_more, and optionally total_count. A cursor response usually returns items, next_cursor, previous_cursor when supported, and has_more. Avoid exposing raw internal SQL or sensitive keys unnecessarily.

Document whether cursors expire, whether old cursors remain valid after sort changes, and whether results are live or snapshot-based. HTTP links or structured metadata can help clients navigate without reconstructing URLs incorrectly.

Previous-Page Navigation

Backward keyset navigation reverses comparison and order to fetch rows before the current boundary, then reverses the returned list for display. A robust bidirectional cursor identifies its direction and contains the boundary keys appropriate to that direction.

This is more complex than next-only infinite scroll. If arbitrary backward navigation is essential and the dataset is modest, offset pagination may offer a simpler product and implementation.

Pagination and Caching

The first page is often the hottest and changes most frequently. A cache key must include tenant, permissions, filters, sort, page size, offset or cursor, locale, and any snapshot cutoff. Missing context can return incorrect or unauthorized data.

Cursor pages may be more cacheable when based on immutable keys, while latest first pages require careful invalidation or short expiration. Decide the tolerated staleness explicitly.

Read Replicas and Sticky Routing

Replica lag can make a cursor boundary refer to data not yet visible on another replica. Moving consecutive page requests between replicas with different replay positions can create surprising gaps or repeats.

Use sticky replica routing for a pagination session when practical, or route freshness-sensitive requests to the primary. Deterministic ORDER BY cannot compensate for different underlying snapshots.

Choosing and Enforcing Page Size

Page size is a product and capacity decision rather than a cosmetic query option. A larger page reduces the number of round trips but increases database work, response serialization, network transfer, client memory, and rendering time. A smaller page responds quickly but may create excessive requests when users scan many records.

Set a documented default and a hard maximum. Validate the requested value as an integer within that range before executing SQL. A request for zero, a negative value, a fractional value, or an extremely large number should receive a clear client error or a documented normalization rather than unpredictable behavior.

Different endpoints may justify different limits. An autocomplete list may need ten compact rows, a desktop management table may support one hundred, and a mobile feed may prefer twenty. Measure complete endpoint cost, not only database time, because related-data loading and JSON generation can dominate large pages.

Hybrid Pagination Architecture

Some products need page numbers near the beginning and efficient deep continuation later. A hybrid design can use offset for shallow, cacheable pages and issue a keyset cursor when users continue beyond a chosen depth. The transition must preserve the same filters, collation, and deterministic order.

Another approach stores periodic anchor keys. To reach a distant page, the service finds the nearest anchor and seeks forward rather than scanning from the beginning. Anchors require maintenance and are most useful for stable datasets or generated reports.

Do not present an exact arbitrary page number if the underlying live dataset cannot support that promise consistently. A feed can use newer and older navigation, while a fixed report can materialize rows with sequence numbers. Architecture should follow the user experience instead of forcing every dataset into one pagination style.

Observability for Pagination Queries

Monitor pagination by endpoint, tenant, filter, sort option, page size, and depth. Track latency, rows examined, rows returned, logical reads, CPU, sort spills, timeout rate, count-query duration, replica source, and cache hit rate. A twenty-row response can hide a query that inspected millions of rows.

For offset endpoints, record or bucket offset depth so increasing cost is visible. For cursor endpoints, record cursor version and validation failures without logging sensitive cursor content. Sudden failures after a deployment can reveal a changed sort definition, incompatible cursor format, or missing index.

Use traces to separate database execution, related-data loading, serialization, and network time. Establish performance objectives for common and worst-supported navigation paths, and test them with representative data skew. Observability turns pagination from a syntax choice into a maintainable production contract.

Cursor Versioning and Compatibility

A cursor can outlive the application deployment that created it. Include a format version so the server can decode supported tokens deliberately and reject obsolete ones cleanly. Changing an ORDER BY expression, collation, data type, tenant rule, or tie-breaker changes cursor meaning even when the public endpoint path stays the same.

During a migration, support both cursor versions for a documented period or invalidate old tokens with a specific error that tells clients to restart from the first page. Keep cursor parsing strict, bounded, and side-effect free. Compatibility tests should generate a cursor with the previous release and consume it with the new release whenever continuity is promised.

Pagination Versus Batch Processing

User pagination and batch processing share boundary concepts but have different reliability needs. A migration must process every eligible row exactly as designed, survive retries, and record progress. OFFSET batches are fragile when processed rows are deleted or updated out of the eligible set because later offsets shift.

Key-range batching with a durable last key is usually safer. Concurrency control, idempotency, transactions, and error recovery remain necessary beyond the SQL page query.

Common Pagination Mistakes

  • Paginating without ORDER BY or with a non-unique order.
  • Applying pagination before defining result grain.
  • Limiting joined child rows instead of parent entities.
  • Filtering or authorizing rows after a page is fetched.
  • Using deep OFFSET for high-volume feeds without measurement.
  • Building a cursor from only one part of a composite order.
  • Using a continuation comparison that disagrees with sort direction.
  • Changing filters, collation, or sort between page requests.
  • Trusting unsigned or unvalidated client cursors.
  • Running an expensive exact COUNT on every request unnecessarily.
  • Allowing unlimited page sizes or selecting every column.
  • Assuming pagination creates snapshot consistency.

Testing Pagination Queries

Test empty results, fewer rows than page size, exactly one full page, a full page plus one, several pages, and a final partial page. Verify exact ordered IDs across boundaries, including duplicate primary sort values and NULL.

Test malformed and expired cursors, changed filters, changed sort, negative pages, excessive sizes, tenant isolation, soft-deleted rows, and permission changes. A page-count assertion alone cannot detect duplicated or skipped records.

Concurrency Testing

Fetch page one, insert new leading rows, then fetch page two and compare offset with keyset behavior. Repeat after deleting page-one rows and after moving a page-two row by updating its sort key.

These tests reveal the actual live-data contract and help product teams decide whether a cutoff or saved snapshot is required.

Performance Testing Pagination

Use production-like volume, row width, tenant skew, selectivity, and duplicate-key distribution. Measure page one, middle pages, deep pages, forward and backward cursors, count queries, and worst-case filters.

Record duration, CPU, logical reads, rows examined, rows returned, memory, temporary spills, index use, network size, and replica behavior. A response containing twenty rows can still be backed by an expensive million-row operation.

Production Pagination Checklist

  1. Define the entity or aggregate represented by one result row.
  2. Apply tenant, authorization, soft-delete, and business filters first.
  3. Specify a deterministic order with a unique tie-breaker.
  4. Choose offset or keyset from navigation requirements.
  5. Make every cursor key match ORDER BY expressions and directions.
  6. Keep filters and sort stable across pages.
  7. Bound page size and validate page or cursor input.
  8. Define NULL, collation, and mutable-key behavior.
  9. Decide whether live changes or a fixed snapshot are expected.
  10. Avoid exact counts unless the feature needs them.
  11. Design indexes for measured high-value query patterns.
  12. Test boundaries, concurrency, security, and deep-page performance.

Practical Cursor-Paginated Feed

SELECT event_id, event_time, event_type, summary
FROM events
WHERE tenant_id = :tenant_id
  AND event_time <= :snapshot_cutoff
  AND (
       event_time < :last_event_time
       OR (event_time = :last_event_time
           AND event_id < :last_event_id)
  )
ORDER BY event_time DESC, event_id DESC
LIMIT :page_size_plus_one;

The tenant predicate enforces scope, snapshot_cutoff prevents newer events entering the session, the composite predicate resumes correctly across equal timestamps, and the extra row provides has_more without a total count.

Practical Offset-Paginated Catalog

SELECT product_id, product_name, price
FROM products
WHERE tenant_id = :tenant_id
  AND category_id = :category_id
  AND product_status = 'ACTIVE'
ORDER BY price, product_id
LIMIT :page_size OFFSET :offset;

This design supports page numbers for a filtered catalog. It is reasonable when navigation remains shallow and an index supports tenant, category, status, price, and product ID.

Interview Questions and Answers

What is pagination? It divides an ordered result into bounded pages.

Why is ORDER BY required? It defines stable row positions and page boundaries.

What is the offset formula? For one-based pages, (page number - 1) multiplied by page size.

What is keyset pagination? It continues after the previous page's final ordered key.

Why is keyset often faster at depth? An aligned index can seek to the boundary instead of discarding a large prefix.

Why add a unique tie-breaker? It prevents equal primary values from moving unpredictably across pages.

How can has_more be found without COUNT? Fetch page size plus one and retain only the requested number.

Does pagination guarantee snapshot consistency? No; that requires an explicit snapshot or cutoff strategy.

How should parent entities with children be paginated? Select the parent page first, then load or join children.

Can keyset pagination move backward? Yes, using reversed comparisons and ordering with a directional cursor.

Key Takeaway

Reliable SQL pagination begins with a deterministic, authorized, correctly grained result. OFFSET offers simple page-number navigation, while keyset cursors provide efficient continuation through large ordered datasets. Neither approach alone defines consistency under change.

GOOD PAGINATION = CORRECT RESULT GRAIN
                  + SECURITY FILTERS FIRST
                  + COMPLETE STABLE ORDER
                  + MATCHING PAGE BOUNDARY
                  + BOUNDED PAGE SIZE
                  + EXPLICIT CONSISTENCY MODEL
                  + SUPPORTING INDEX
                  + BOUNDARY AND CONCURRENCY TESTS

Choose the navigation model from product needs, make every continuation key match the order, and measure real page depths with realistic data before treating a small response as an inexpensive query.