Doctrine Paginator versus Keyset Pagination for High-Offset Performance

The Performance Verdict

For datasets growing indefinitely, keyset pagination is the only sustainable approach. While the Doctrine\ORM\Tools\Pagination\Paginator is convenient for small-to-medium datasets, its reliance on the SQL OFFSET clause creates a linear performance degradation that eventually makes the application unresponsive as page depth increases.

Performance Thresholds

There is no universal row count where the Paginator "breaks," as performance depends on your database engine, indexing, and hardware. However, empirical patterns suggest the following thresholds for review:

• Low Impact: Offsets under 1,000 rows typically show negligible latency.
• Noticeable Degradation: Once offsets exceed 10,000 rows or represent more than 10% of the total table size, query execution time often climbs noticeably.
• Untenable: At millions of records, requesting a page at a high offset (e.g., page 50,000) can lead to seconds of execution time and high I/O overhead because the database must scan and discard all preceding rows.

Implementation Comparison

The following table outlines the operational differences between the two strategies:

Feature	Doctrine Paginator (Offset)	Keyset (Cursor)
Complexity	Low (Built-in)	Moderate (Manual DQL/SQL)
Time Complexity	O(N) — Linear scan	O(log N) — Index seek
Navigation	Random access (Page 1, 5, 10)	Sequential (Next/Previous)
Stability	Rows shift if records are deleted	Consistent anchor point

Moving to Keyset Pagination

To implement keyset pagination in Doctrine, avoid the Paginator tool and write a custom repository method. Assume you are ordering by a primary key id:

// Example: Fetching the next page after ID 5000
$query = $this->createQueryBuilder('e')
    ->where('e.id > :lastId')
    ->setParameter('lastId', 5000)
    ->orderBy('e.id', 'ASC')
    ->setMaxResults(20)
    ->getQuery();

Verification and Assumptions

This recommendation assumes that your result set is ordered by a unique, immutable, and indexed column. If you order by a non-unique column (e.g., created_at), you must include the primary key as a secondary sort tie-breaker to prevent skipping records.

To verify the performance gap on your specific environment, run EXPLAIN ANALYZE on your SQL queries. Compare the rows removed by filter or discarded rows count in an OFFSET query versus the Index Scan efficiency of a WHERE id > X query.

Diagnostic Detail Required: Are you requiring "Jump to Page X" functionality? If random access is a hard business requirement, keyset pagination cannot be used alone, and you may need to implement a hybrid approach or a search index (e.g., Elasticsearch).