Choosing Pagination Strategy in Doctrine ORM: Offset vs Cursor for Large Datasets

Decision and Constraints

When a Doctrine ORM application needs to page through millions of rows, the pagination method determines query latency, memory consumption, and cache correctness. The two native approaches are:

• Offset‑based – setFirstResult() / setMaxResults() (SQL LIMIT … OFFSET …).
• Cursor‑based – a WHERE id > :lastId ORDER BY id LIMIT :pageSize clause that seeks from the last seen primary key.

Constraints that shape the choice:

• Primary key is monotonic (auto‑increment integer or UUID v1/v7).
• Result set must be stable for the duration of a user session (no concurrent inserts that would shift offsets).
• Deep pages (page > 10 000) are accessed regularly.
• Entity manager’s identity map must stay consistent with the returned objects.

Supported Options at a Glance

Aspect	Offset (LIMIT/OFFSET)	Cursor (Seek)
SQL generated	SELECT … LIMIT 20 OFFSET 40000	SELECT … WHERE id > 40000 ORDER BY id LIMIT 20
Index usage	Full scan up to offset; may ignore covering index	Index seek on PK; constant‑time start
Deep‑page latency	Grows linearly with offset	Flat, independent of page number
Stability with inserts	Rows shift → duplicate or missing items	New rows after cursor are invisible until next request (acceptable for most feeds)
Doctrine API	QueryBuilder::setFirstResult() / setMaxResults()	Custom DQL/QueryBuilder with parameter binding
Third‑party support	Pagerfanta, KnpPaginatorBundle (offset mode)	Pagerfanta cursor adapter, KnpPaginatorBundle cursor mode
Cache friendliness	Identity map works out‑of‑the‑box	May bypass identity map; need explicit refresh() or clear()

Trade‑offs

Performance

Offset pagination forces the engine to read and discard OFFSET rows before returning the page. On a table with a clustered primary key, the optimizer still walks the B‑tree up to the offset, causing I/O proportional to page depth. Cursor pagination turns the query into a range scan that starts at the last known key, so the cost stays constant.

Result Consistency

If new rows are inserted before the current offset, offset pagination will return duplicate rows on the next page or skip rows entirely. Cursor pagination naturally excludes rows inserted after the cursor, which is usually the desired behavior for infinite‑scroll feeds.

Complexity

Offset is a one‑liner with Doctrine’s built‑in methods. Cursor requires a tiny amount of extra code (binding the last ID) and careful handling of the first page (no cursor). Third‑party bundles hide most of this complexity.

Entity Cache

Doctrine’s identity map caches entities by primary key. Offset queries load entities through the normal hydration path, so the cache stays coherent. Cursor queries that use a raw WHERE id > :lastId still hydrate via the same path, but if you bypass the QueryBuilder (e.g., native SQL) you must call $em->clear() or $em->refresh($entity) to avoid stale data.

Concrete Implementation – Offset Pagination

// src/Repository/ArticleRepository.php
public function findPaginated(int $page, int $pageSize): array
{
    $qb = $this->createQueryBuilder('a')
        ->orderBy('a.id', 'ASC');
    $qb->setFirstResult(($page - 1) * $pageSize)
       ->setMaxResults($pageSize);
    return $qb->getQuery()->getResult();
}

Usage:

$articles = $articleRepo->findPaginated(3, 20); // page 3, 20 items

Run EXPLAIN on the generated SQL to confirm a full index scan up to the offset:

EXPLAIN SELECT a.id, a.title FROM article a ORDER BY a.id ASC LIMIT 20 OFFSET 40;

Look for type=index and a large rows estimate – a sign the offset is expensive.

Concrete Implementation – Cursor Pagination

// src/Repository/ArticleRepository.php
public function findAfterId(int $lastId, int $pageSize): array
{
    $qb = $this->createQueryBuilder('a')
        ->where('a.id > :lastId')
        ->setParameter('lastId', $lastId)
        ->orderBy('a.id', 'ASC')
        ->setMaxResults($pageSize);
    return $qb->getQuery()->getResult();
}

First page (no cursor):

$firstPage = $articleRepo->findAfterId(0, 20); // assumes IDs start at 1

Subsequent pages:

$lastId = end($firstPage)->getId();
$secondPage = $articleRepo->findAfterId($lastId, 20);

Validate index usage:

EXPLAIN SELECT a.id, a.title FROM article a WHERE a.id > 1000 ORDER BY a.id ASC LIMIT 20;

Expect type=range with key=PRIMARY and a low rows estimate (≈ pageSize).

Validation Checklist

1. Row count match – Run the offset query for page 1 and page 2; sum of returned rows equals total rows for the filter.
2. EXPLAIN plan – Verify cursor query uses a range scan on the primary key; offset query shows increasing rows with deeper offsets.
3. Memory profiling – Use Xdebug or Blackfire to compare peak memory for a deep offset page vs. a cursor page of the same size.
4. Cache sanity – After a cursor fetch, call $em->contains($entity) on a known ID; if false, run $em->refresh($entity) or $em->clear() before reuse.

Limitations & When to Re‑evaluate

• Cursor pagination assumes a strictly increasing primary key. Composite keys or non‑monotonic UUIDs (v4) break the simple id > :lastId predicate.
• If the UI requires random‑access page numbers (e.g., “jump to page 42”), offset is unavoidable unless you store a mapping of page → cursor.
• Heavy joins or complex ORDER BY clauses may prevent the optimizer from turning the cursor predicate into a pure index seek; test with EXPLAIN each release.
• Third‑party bundles add abstraction but also a dependency surface; evaluate bundle maintenance status before adopting.

Quick Decision Flow

• Deep pages + monotonic PK → Cursor.
• Shallow pages, need random page jumps → Offset (or hybrid with cached cursors).
• Existing Pagerfanta integration → use its cursor adapter for zero‑code switch.

By measuring the EXPLAIN plan and memory profile on a staging dataset that mirrors production volume, you can confirm the theoretical advantages before committing to a strategy.