Phalcon paginator limits: QueryBuilder deep offsets versus NativeArray memory on large tables

Adapter Performance Trade-offs

The Phalcon\Paginator adapters handle data retrieval in fundamentally different ways. For tables scaling into the millions of rows, the QueryBuilder adapter is the only viable built-in option, though it does not solve the inherent database costs of deep pagination.

QueryBuilder and Deep Offsets

The QueryBuilder adapter translates pagination requests directly into SQL LIMIT and OFFSET clauses. It does not implement any internal logic to mitigate deep-offset degradation. Because the database engine must scan and discard all rows preceding the offset, performance degrades linearly as the page number increases.

NativeArray Memory Constraints

The NativeArray adapter requires the entire result set to be loaded into a PHP array before the paginator slices the requested page. Memory consumption is tied to the total size of the dataset, not the page size. In practice, NativeArray becomes a liability once the dataset exceeds a few thousand records, as it will likely trigger a PHP memory_limit exhaustion long before the pagination logic executes.

Implementation Guidance

Given your requirement for numbered navigation on a million-row table, follow these constraints:

• Use QueryBuilder: Avoid NativeArray and Model adapters for large tables.
• Database Optimization: Ensure the columns used for ORDER BY are indexed to minimize the cost of the scan during the offset process.
• Hybrid Keyset Approach: Phalcon's paginator does not natively support keyset pagination (the "seek method") while providing total-page metadata. To achieve this, you must manually execute a COUNT(*) query to get the total pages, then use a custom QueryBuilder filter (e.g., WHERE id > :last_id:) to fetch the actual page data.

Verification Steps

To quantify the performance impact on your specific environment, use these scoped tests:

1. Offset Latency: Request page 1 and page 10,000 using the QueryBuilder adapter. Compare the execution time in your database slow query log to measure the offset penalty.
2. Memory Profiling: Wrap a NativeArray initialization in memory_get_peak_usage() with a dataset of 10,000 rows to observe the memory footprint relative to your production php.ini limits.

Diagnostic Requirement: Please specify your Phalcon version (v3, v4, or v5), as namespace paths for the Paginator adapters changed between major versions.