Paginating Large Result Sets in Phalcon Without Loading Everything Into Memory

Your admin panel worked fine with 200 rows. Now the table has 400,000, the page takes twelve seconds to load, and PHP is eating half a gigabyte of memory per request. The cause is almost always the same: someone called Model::find() with no limit and looped over the entire result set to render a table that shows twenty rows.

The fix is not exotic. Phalcon ships pagination as first-class adapters under Phalcon\Paginator\Adapter, and using them correctly pushes the slicing work down to the database, where it belongs. The decision that actually matters is which adapter to use — Model or QueryBuilder — and knowing where the built-in approach stops scaling.

What the paginator actually gives you

Both adapters take a configuration array with limit and page keys, and getPaginate() returns a plain object exposing items, current, next, last, total_items and total_pages. That shape maps directly onto a typical list view: render items, then build next/previous links from current, next and last.

Because Phalcon runs as a compiled C extension, the adapter logic itself is cheap. But that is not where the win comes from. The win is that the adapter emits SQL with LIMIT and OFFSET, so the database returns twenty rows instead of four hundred thousand, and PHP only hydrates twenty model objects.

Model vs QueryBuilder: the real decision

The Model adapter paginates the result of a find() call. It is the quickest to write and fine for modest tables where you genuinely need every column of every row on the page.

The QueryBuilder adapter paginates a Phalcon\Mvc\Model\Query\Builder query. This is the better default for large tables, for two reasons. First, you can select only the columns the view actually renders, which cuts both transfer size and hydration cost. Second, you can add joins and conditions without contorting find() parameters.

A rule of thumb that holds up in practice: if the table is small and the query is trivial, Model is fine. If the table is large, the query has joins, or the view only needs a handful of columns, reach for QueryBuilder.

A worked example

The following controller action reads the requested page from the query string, clamps it to a sane value, and paginates a filtered query. This targets Phalcon 4/5-style namespaces — check your installed major version, because paginator namespaces and constructor signatures changed across 3.x, 4.x and 5.x. Run php -r 'echo Phalcon\Version::get();' on the server (with the extension loaded) to confirm what you have before adapting the sample.

use Phalcon\Mvc\Controller;
use Phalcon\Paginator\Adapter\QueryBuilder as PaginatorQueryBuilder;

class RobotsController extends Controller
{
    public function listAction()
    {
        // Clamp user input: never trust ?page directly.
        $page = (int) $this->request->getQuery('page', 'int', 1);
        if ($page < 1) {
            $page = 1;
        }

        $builder = $this->modelsManager->createBuilder()
            ->columns('id, name, type, year')   // only what the view needs
            ->from(Robots::class)
            ->where('type = :type:', ['type' => 'industrial'])
            ->orderBy('id');                    // stable ordering matters

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

        $this->view->page = $paginator->getPaginate();
    }
}

In the view, iterate page.items for the rows and use page.current, page.next and page.last to render navigation links. Two details are easy to miss: the orderBy('id') is not optional decoration — without a deterministic order, the database is free to return rows in any sequence, and the same row can appear on two pages. And the clamp on $page matters because an unsanitized page number produces confusing empty pages or invalid offsets.

The trade-off: deep pages get slow

OFFSET-based pagination has a structural cost: to serve page 500 at 20 rows per page, the database must locate and discard 9,980 rows before returning your 20. Page 1 is fast; page 5,000 is not. This is a property of the database and its indexes, not of Phalcon — the C extension cannot fix it for you.

The standard answer is keyset (cursor) pagination: instead of OFFSET 9980, you query WHERE id > :last_seen_id ORDER BY id LIMIT 20, which uses the index directly and costs the same on page 5,000 as on page 1. Phalcon's built-in adapters do not provide this out of the box, so for high-traffic deep pagination you would implement it manually with a query builder and pass the cursor as a query parameter. For most admin panels and content lists, where users rarely go past the first few pages, OFFSET pagination via the built-in adapters is entirely adequate — just know where the cliff is.

Verify it actually did what you think

Do not assume the LIMIT made it into the SQL. Attach a database listener (or enable your database server's query log) and confirm the emitted query contains the expected LIMIT and OFFSET for the page you requested. Then seed a few thousand rows in a test environment, request page 1 and a deep page like 500, and compare execution time and memory — you will see the OFFSET cost directly, and you will know whether it matters for your traffic pattern. A unit test asserting total_pages and the items count against a known fixture dataset catches regressions in the clamping and limit logic.

The actionable summary: paginate at the database, not in PHP; prefer QueryBuilder and narrow column lists on large tables; always clamp the page parameter and order deterministically; and if users routinely browse thousands of pages deep, plan for keyset pagination because the built-in adapters will not save you there.