Symfony Doctrine pagination: why deep pages slow down and what to check

The problem: page 1 is fast, page 2000 is not

You are building a blog listing in Symfony. The first page of posts loads instantly, but as visitors click deeper into the archives, response times climb. The database is not returning more rows; it is doing more work to skip the rows that came before.

This usually happens when Doctrine’s QueryBuilder paginates using setFirstResult and setMaxResults. Those methods translate to SQL LIMIT and OFFSET. OFFSET tells the database how many rows to discard before it starts returning results.

How Doctrine turns pagination into LIMIT and OFFSET

The Doctrine ORM is Symfony’s object-relational mapper. Its QueryBuilder lets you write queries in PHP instead of raw SQL. A typical repository method looks like this:

namespace App\Repository;

use App\Entity\Post;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class PostRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Post::class);
    }

    public function findPaginated(int $page, int $limit = 20): array
    {
        $offset = ($page - 1) * $limit;

        return $this->createQueryBuilder('p')
            ->orderBy('p.publishedAt', 'DESC')
            ->setFirstResult($offset)
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

The controller reads the page number from the request and Twig renders the list. This is a read-only operation, so no database rollback is needed.

public function index(Request $request, PostRepository $posts): Response
{
    $page = $request->query->getInt('page', 1);
    $posts = $posts->findPaginated($page);

    return $this->render('blog/index.html.twig', [
        'posts' => $posts,
        'page' => $page,
    ];
}

Verify what the database actually does

Run EXPLAIN in your database client, or through Symfony’s doctrine:query:sql console command if it is installed. Run from the project root. The database user needs SELECT privileges on the table. Replace <post_table> with your actual table name.

# Example only; adapt the table name and credentials for your environment.
php bin/console doctrine:query:sql "EXPLAIN SELECT * FROM <post_table> ORDER BY published_at DESC LIMIT 20 OFFSET 19980"

Expected check: the query plan should show a limit or offset operation. As the offset grows, many databases scan and discard more rows before applying the limit. Risk: do not run EXPLAIN ANALYZE on a very large production table during peak traffic; it executes the statement and can add load. Plain EXPLAIN only plans the query.

The deep-page limitation and a keyset alternative

OFFSET becomes inefficient on large tables because the database must read through skipped rows even though it does not return them. For a blog with tens of thousands of posts, page 1000 can scan tens of thousands of rows just to return twenty.

Keyset pagination, sometimes called the seek method, avoids skipping. Instead of OFFSET, you remember the last value from the previous page and ask for rows after it:

public function findAfter(\DateTimeInterface $after, int $limit = 20): array
{
    return $this->createQueryBuilder('p')
        ->where('p.publishedAt < :after')
        ->setParameter('after', $after)
        ->orderBy('p.publishedAt', 'DESC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();
}

Trade-off: keyset pagination works well for “next page” flows, but it does not naturally support jumping directly to page 42 or rendering “page 3 of 500” without extra counting queries. Confirm that your Doctrine ORM version and database driver handle the parameter binding and cursor column as expected before adopting it in production.

Practical check before you deploy

Run the repository method with page=1 and page=1000 in a non-production environment. Compare the generated SQL and execution time. Use EXPLAIN to confirm that LIMIT and OFFSET appear, and observe whether the number of scanned rows increases as the offset grows. If deep-page response time is unacceptable, switch to keyset pagination for “next” links or add an indexed cursor column.

Because this is a read-only query change, there is no database state to roll back. Verify in staging first, then deploy the repository and controller changes together.