Reading at Scale: Pagination, Projections, Chunking

Reading at Scale is the chapter for the route that was fine when the table held ten thousand rows and is now the slowest thing you own. Nothing about the code changed. The cost of the same statement grew with the data underneath it, which is a property of the statement rather than a regression.

Key takeaways

• Laravel's pagination documentation states that the offset clause scans through all previously matched data, so a deep page reads and discards everything before it.
• The paginate method counts the total matched records before retrieving any, and simplePaginate skips that count, which is why deep pagination costs two expensive statements rather than one.
• Cursor pagination surrenders four documented things: page-number links, non-unique or nullable ordering columns, unaliased order expressions, and order expressions with parameters.
• Laravel documents chunkById as required, not preferred, when you filter on a column you are also updating, because plain chunk there gives unexpected and inconsistent results.
• The cursor method runs one query and holds a single model in memory, and for that reason cannot eager load relationships at all; lazy is the documented alternative when you need them.

Read this after Chapter 3, which handled the number of queries, because this chapter handles the size of one. Chapter 5 covers what happens when a long read holds a snapshot that a write is waiting on, and Chapter 9 covers what happens to the bytes once you have selected them.

An export endpoint works for a year, then starts timing out. The code has not been touched.

Someone raises the memory limit, and it works again for a month. Then they raise it again. Then the worker gets killed by the kernel and the export produces a partial file with no error anywhere.

The endpoint was hydrating every matching row into a model. It always was. The data grew.

The offset clause reads everything you skipped

Laravel's pagination documentation puts the two statements side by side, and the comparison is the whole mechanism. Offset pagination emits a select with limit 15 offset 15. Cursor pagination emits a select with where id > 15 and the same limit.

The documentation then explains why the second scales and the first does not: the offset clause scans through all previously matched data. The database cannot jump to row sixty thousand, because rows have no addresses. It walks the ordered set and discards what it walked past.

So the cost of page N is proportional to N. Page one is cheap forever, and the last page of a large table is the most expensive statement in your application, served to whichever crawler happened to follow the pagination links to the end.

That is why deep pagination is usually discovered from a crawler in the access log rather than from a user complaint. Users stop at page three. Crawlers do not.

The count query you did not ask for

There is a second statement hiding in ordinary pagination, and it is often the more expensive of the two.

Laravel documents that paginate counts the total number of records matched by the query before retrieving the records, in order to compute the number of pages. On a filtered query over a large table, that count has to resolve the whole filtered set, which no limit clause can shorten. The user sees fifteen rows. The database resolved every matching row twice.

simplePaginate performs a single query without the count, and gives you next and previous links instead of numbered pages. That is the trade in one sentence: you lose the total, and you stop paying to compute it on every request.

Ask what the total is for. In an admin table that supports jumping to page forty, it is load-bearing. On a public listing it is decoration, and it is decoration with a query attached.

There is a middle option worth knowing about, because it keeps the interface and drops the cost. Compute the total on a cheaper cadence and cache it, then serve pages from a single statement. A count that is a few minutes stale is honest on a listing that changes continuously, and Chapter 6 is about writing down exactly which numbers are allowed to be stale like that.

Cursor pagination costs you exactly four things

Cursor pagination is not free, and Laravel's documentation is unusually explicit about the bill. Reading the limits before adopting it saves an awkward revert.

It can only render next and previous links, with no page numbers. It requires ordering on at least one unique column, or a combination of columns that is unique, and columns with null values are not supported. Order-by expressions are supported only when they are aliased and added to the select clause. And order-by expressions with parameters are not supported at all.

The nullable-column limit is the one that catches teams, because it interacts with a common ordering choice. Sorting a feed by a nullable published date and hoping is how you get rows that appear on two pages, or on none.

The workable pattern is ordering on a unique tiebreaker alongside the column you care about. Sort by the business column, then by the primary key, and make both non-nullable.

A worked reading, composited and labelled

The details here are composited from ordinary production shapes rather than one system, and the shape is exact.

A paginated listing is reported slow, intermittently. The first reading captures the plan for the same statement twice: once at page one, once at the deepest page a crawler reached. Rows emitted are identical in both. Rows examined are not, and the difference grows with page depth. PostgreSQL's documentation is the reason this reading is legible, since it warns that the plan's row figure is what the node emitted rather than what it scanned.

The second reading captures the plan for the count statement alone, separated from the page fetch. Teams are routinely surprised here, because the count node is doing the work they attributed to the fetch.

Two readings, two decisions. If depth dominates, move to a cursor. If the count dominates, drop to simplePaginate or cache the total. If both dominate, you have a listing that should never have offered numbered pages.

Batch reads have four modes, and they differ in memory shape

The other half of reading at scale is not a page but a job: reprocess every row, rebuild every search document, recalculate every balance. Laravel documents several tools and they are not interchangeable.

The row that surprises people is the last one. Laravel documents that because cursor only ever holds a single Eloquent model in memory at a time, it cannot eager load relationships, and points at lazy when you need them. So the most memory-efficient option is also the one that reintroduces the N+1 from Chapter 3 the moment your loop touches a relationship.

Pick by what the loop body does. A loop that only reads columns wants cursor. A loop that touches relationships wants lazyById.

There is a second axis the table does not show, which is what happens when the job dies halfway. Chunked and keyed modes leave you a resumable position, because the last id processed is a value you can store. A single-statement cursor does not, since the position lives inside a result set that dies with the process. For a job that takes an hour, resumability is worth more than memory.

Batch reads also compete with live traffic for the same buffer pool, which is a Chapter 2 point wearing different clothes. A reprocessing job that walks a whole table evicts the pages your web requests were relying on, so the visible symptom is a slow website during a job nobody associated with the website. Schedule the walk, or limit its rate, or both.

chunkById is a correctness rule, not a tuning choice

This is the paragraph in the chapter with real teeth, because getting it wrong corrupts data rather than slowing it down.

Laravel's documentation warns that when you filter results based on a column you will also update, chunk can lead to unexpected and inconsistent results, and chunkById should be used instead. The mechanism is offset arithmetic. Plain chunk pages with offsets, so updating the filtered column shifts the set out from under the pagination and rows slide past the window unprocessed.

chunkById avoids it by keying each batch on an id greater than the last one seen, which is the same insight cursor pagination uses on the read path. Laravel also documents lazyById and lazyByIdDesc for the stream-shaped version.

The rule is short enough to remember. If the job writes to a column the job filters on, key on the id. Never on the offset.

Narrow the select before you widen the machine

Hydration is the cost that never appears in a query log, because it happens after the database has finished. Every column you selected becomes a property, every row becomes an object, and every object costs allocation and a pass through the model's casting.

Two levers exist and both are cheap. Select fewer columns, so the payload and the hydration both shrink. And skip hydration entirely where you only need values, which is what the query builder gives you without Eloquent on top.

Laravel 13 adds a relevant piece for APIs specifically. Its release notes, for the release dated 17 March 2026, name first-party JSON:API resources handling serialisation, relationships, sparse fieldsets and compliant response headers. Sparse fieldsets are a protocol-level way for the client to ask for fewer columns, which turns projection into a contract instead of a guess.

The decision rule I use: no endpoint selects a column no consumer reads. Write it in the review checklist, because it is invisible in profiling.

The objection: drop the ORM and write raw SQL

Every chapter like this attracts the same response, which is that Eloquent is the overhead and raw SQL is the answer. It is worth taking seriously, because sometimes it is correct.

Where it is correct is a specific and narrow place: a reporting query with several joins and aggregations, run rarely, where the result is rows rather than entities. Writing that as SQL is clearer than expressing it through a query builder, and nothing is lost because nothing needed a model.

Where it is wrong is everywhere the cost was hydration or round trips rather than SQL generation. Rewriting an N+1 in raw SQL gives you an N+1 in raw SQL. The framework did not add the loop.

There is a maintenance cost too, and it lands later. A hand-written statement stops tracking schema changes, so a column rename that Eloquent would have surfaced in a test becomes a runtime error in a report nobody runs until quarter end. Pay that cost deliberately, for queries that earn it.

Chapter summary

Offset pagination scales with page depth because, as Laravel's pagination documentation states, the offset clause scans through all previously matched data, and rows have no addresses for the database to jump to. Ordinary paginate adds a second statement, counting every matched record before fetching any, so a filtered listing resolves its whole result set twice, while simplePaginate drops the count along with numbered pages. Cursor pagination replaces the offset with a where comparison and charges exactly four documented prices: no page numbers, ordering only on unique non-nullable columns, order expressions only when aliased into the select, and no parameterised order expressions, which is why ordering on a business column plus the primary key is the pattern that survives. For batch work the four modes differ in memory shape rather than in speed, and the sharp edge is that cursor runs a single query holding one model and therefore cannot eager load relationships at all, with lazy documented as the alternative when you need them. chunkById is a correctness requirement rather than a tuning preference, since Laravel warns that filtering on a column you also update gives unexpected and inconsistent results under plain offset-based chunk, and lazyById and lazyByIdDesc are the stream-shaped equivalents. Hydration is the cost no query log shows, so select fewer columns and skip Eloquent where you only need values, with Laravel 13's first-party JSON:API resources making sparse fieldsets a client-driven contract. And dropping to raw SQL helps only where the cost was SQL generation, which it almost never was.

Reads can be made cheap without coordinating with anybody. Writes cannot, because two of them want the same row. Chapter 5 is Writing at Scale, and it is about lock scope, deadlocks and the one hot row that serialises everything behind it.