Paginating Phalcon ORM Queries: Offset vs. Keyset, and Where the Trust Boundaries Are

Pagination in a Phalcon application looks trivial until the table grows past a few hundred thousand rows or a client starts requesting page 9,000 with a limit of 5,000. The useful takeaway: keep offset-based pagination with Phalcon's Paginator for small, shallow datasets, validate page and limit parameters as untrusted input, and plan the switch to keyset (cursor-based) pagination before deep offsets start hurting. This note covers the requirements, the smallest design that works, and the conditions that should make you change it.

Requirements

A typical web application needs to list records — orders, log entries, products — with these constraints:

• Clients request a page number and page size; both are attacker-controlled input.
• Responses should include enough metadata (total pages, current page) for a UI to render controls.
• The query must not load entire tables into PHP memory.
• Behavior on out-of-range pages must be predictable (empty result or 404, not an exception).
• The design must have a documented escape hatch when offset performance degrades.

The smallest suitable design

Phalcon ships a Phalcon\Paginator\Adapter\QueryBuilder adapter that wraps a QueryBuilder instance and applies LIMIT/OFFSET for you. The smallest sound design has three parts: a validation step, a paginator service, and a thin controller.

First, validate input in one place. Page and limit come from the query string, so treat them as hostile:

// App\Http\PaginationParams.php — plain PHP, no framework dependency
final class PaginationParams
{
    public const MAX_LIMIT = 100;

    public function __construct(
        public readonly int $page,
        public readonly int $limit,
    ) {}

    public static function fromQuery(array $query): self
    {
        $page  = filter_var($query['page'] ?? 1, FILTER_VALIDATE_INT);
        $limit = filter_var($query['limit'] ?? 20, FILTER_VALIDATE_INT);

        if ($page === false || $page < 1) {
            $page = 1;
        }
        if ($limit === false || $limit < 1) {
            $limit = 20;
        }
        // Cap the limit: an unbounded limit is a resource-exhaustion vector.
        $limit = min($limit, self::MAX_LIMIT);

        return new self($page, $limit);
    }
}

Second, build the query with explicit columns rather than hydrating full models. Selecting only what the list view needs keeps memory flat as the table grows:

// Inside a service, e.g. App\Services\OrderLister.php
use Phalcon\Mvc\Model\Query\Builder;
use Phalcon\Paginator\Adapter\QueryBuilder as PaginatorQueryBuilder;

$builder = (new Builder())
    ->from(['o' => Orders::class])
    ->columns(['o.id', 'o.reference', 'o.total', 'o.created_at'])
    ->orderBy('o.id DESC');

$paginator = new PaginatorQueryBuilder([
    'builder' => $builder,
    'limit'   => $params->limit,
    'page'    => $params->page,
]);

$result = $paginator->paginate();

The returned page object exposes the items plus metadata such as the current page, total items, and total pages (exact property names vary by Phalcon major version — check the adapter for your installed version). The controller then just maps this to a JSON or HTML response. Keeping the paginator inside a service means the controller never sees raw query parameters.

Trust and data boundaries

There are two boundaries worth naming explicitly. The first is the HTTP boundary: page and limit arrive as strings and must never reach the QueryBuilder unvalidated. An uncapped limit lets any anonymous client force large result sets; a negative or non-numeric page can produce malformed SQL or exceptions depending on adapter behavior. The PaginationParams class above is the entire defense — small, testable, and reusable.

The second boundary is between the ORM and memory. Hydrating full model objects for a list view pulls every column and every relationship trigger into PHP. Selecting scalar columns through the QueryBuilder keeps the result as plain arrays. If you later need related data, eager-load it explicitly for the page's IDs rather than relying on lazy loading inside a loop, which produces N+1 queries.

Operational checks

After wiring this up, verify behavior rather than assuming it:

• Inspect the SQL. Enable the database profiler or your DB's query log and confirm the paginated query contains the expected LIMIT/OFFSET and only the selected columns.
• Test boundary pages. Request page 1, the last page, and one page past the end. The out-of-range request should return an empty item list (or a 404, if that is your API contract — pick one and document it), not an error.
• Test hostile input. Send ?page=-3, ?page=abc, and ?limit=100000. Each should be clamped or defaulted by PaginationParams.
• Log abnormal patterns. A client walking thousands of sequential deep pages is usually a scraper; rate-limit or log at the web-server or middleware layer.

Failure modes

Two failure modes are inherent to offset pagination and are not Phalcon-specific:

Drift between pages. If a row is inserted or deleted while a user moves from page 2 to page 3, the offsets shift and the user sees a duplicate row or misses one. For mostly-static data this is acceptable; for feeds or rapidly changing tables it is not.

Deep-offset cost. OFFSET 500000 still requires the database to scan and discard half a million rows. Response time grows roughly linearly with page depth. On a table past ~100k rows with traffic reaching deep pages, this becomes visible in query logs.

When to change the design: keyset pagination

Keyset (cursor) pagination replaces the offset with a WHERE clause on an indexed, unique, monotonic column — typically the primary key:

$builder = (new Builder())
    ->from(['o' => Orders::class])
    ->columns(['o.id', 'o.reference', 'o.total', 'o.created_at'])
    ->orderBy('o.id DESC');

if ($cursor !== null) {  // last id from the previous response
    $builder->andWhere('o.id < :cursor:', ['cursor' => (int) $cursor]);
}

$builder->limit($params->limit + 1); // fetch one extra to detect a next page

The cursor is the last id the client saw; the extra row tells you whether another page exists. This runs in constant time regardless of depth and is immune to insert/delete drift for rows already seen. The cost: no arbitrary page jumps and no cheap total count, so it suits "next page" APIs and infinite scroll, not numbered page UIs.

Switch when any of these hold: tables exceed a few hundred thousand rows with deep-page traffic, data changes frequently enough that drift confuses users, or query logs show offset scans dominating response time. Benchmark with your real data volume before and after — the crossover point depends on your schema and indexes.

Version caveat

Paginator adapter class names, constructor array keys, and the shape of the paginated result object have changed across Phalcon major versions. The examples above follow the Phalcon 5-style namespacing; confirm the adapter signature against the documentation for your installed version before copying, and run the boundary-page checks described above after any framework upgrade.