Chapter 05 · Intermediate
Ask the Database for Less
Fix the catalogue’s N+1 with Eloquent, fetch narrower rows and compare offset and cursor pagination under the same workload.

Overview
Section titled “Overview”A catalogue response can look small while the server makes 22 database calls to build it. Start by making fewer calls. Then see how much data those calls pull into PHP. Finally, compare how much work PostgreSQL does to reach a deep page.
Each experiment keeps the product IDs, ordering, prices and category labels the same. The shop’s default endpoint now eager loads categories and selects the fields it needs. It keeps offset pagination for existing callers, and the lab keeps the original lazy path for comparison.
Prerequisites
Section titled “Prerequisites”Use the Compose shop from Set a Baseline for a Laravel App and the request trace from Profile a Slow Laravel Request. Run the commands from code/bang-for-buck. Allow about 30 minutes.
Quick Start
Section titled “Quick Start”Rebuild the application, run the additive migration and seed a separate category for this comparison. Category 21 has 25,000 products and a 4,096-byte description per product. That gives you wide rows and enough depth to test page 1,000. The seeder can be rerun and leaves the original 20 categories available for the earlier labs.
docker compose build phpdocker compose run --rm php php artisan migrate --forcedocker compose run --rm php php artisan db:seed --class=CatalogueBenchmarkSeeder --forcedocker compose exec -T db psql -X -U shop -d shop -c 'ANALYZE products'docker compose up -d --wait --force-recreate php web worker shippingcurl -fsS --retry 5 --retry-all-errors --retry-delay 1 "http://127.0.0.1:${SHOP_PORT:-8088}/api/lab"Look for 25,000 products under catalogue_benchmark. Keep the same PHP-FPM, OPcache and database settings across the runs.
Step 1 – Load the categories together
Section titled “Step 1 – Load the categories together”bash bench/trace.sh catalogue lazybash bench/trace.sh catalogue eagerThe lazy trace shows 22 queries. One validates the category, one loads the product page, and the loop asks for the category again for each of the 20 products. Eloquent’s with('category') fetches the related category IDs in one query and attaches the loaded records to the products. The eager trace shows three queries, including validation, and the JSON body is identical. Laravel eager loading
$products = Product::query() ->where('category_id', $categoryId) ->orderBy('id') ->with('category') ->simplePaginate(20);If you add a relationship to a response later, trace it again. Loading the first relationship does not automatically load other relationships the serializer may start reading.
Step 2 – Select only the fields the response uses
Section titled “Step 2 – Select only the fields the response uses”The published JSON needs the product ID, title, price and category name. Select those alongside the keys Eloquent needs to match the models.
$products = Product::query() ->where('category_id', $categoryId) ->orderBy('id') ->select('id', 'category_id', 'title', 'price_cents') ->with('category:id,name') ->simplePaginate(20);Keep category_id on the product and id on the category so Eloquent can attach the category. Keep the product’s id for its identity and ordering. The eager variant selects full product rows, while projected selects these four columns. Both variants return the same JSON and run three queries. Laravel eager loading specific columns
Check how much description text was in the original first-page query. simplePaginate(20) reads a 21st row to determine whether more items exist.
docker compose exec -T db psql -X -U shop -d shop <<'SQL'SELECT count(*) AS rows_read, sum(octet_length(description)) AS description_bytesFROM ( SELECT description FROM products WHERE category_id = 21 ORDER BY id LIMIT 21) AS first_page;SQLExpect 21 rows and 86,016 bytes of description values before database protocol overhead. The projection leaves those values in PostgreSQL. The HTTP body size stays the same because both versions already omit the description when serialising the products.
Step 3 – Seek from the last product
Section titled “Step 3 – Seek from the last product”The original pagination query uses OFFSET to skip earlier products. The cursor query uses the last product’s ID as its starting boundary and asks for IDs above it. It can seek to that boundary using an index. The order must be unique and non-null, so this shop sorts by the primary key. Use the cursor with the same category and sort order. Laravel cursor pagination · PostgreSQL LIMIT and OFFSET
catalogue="http://127.0.0.1:${SHOP_PORT:-8088}/api/catalogue?category=21&variant=cursor"next=$(curl -fsS "$catalogue" | docker compose exec -T php php -r ' $page = json_decode(stream_get_contents(STDIN), true, flags: JSON_THROW_ON_ERROR); echo $page["next_cursor"];')curl -fsS --get --data-urlencode "cursor=$next" "$catalogue"The first response covers products 5001 through 5020. The second covers 5021 through 5040. Follow the returned token until next_cursor is null. Use offset pagination on screens that need numbered-page jumps. The benchmark alone constructs a cursor for page 1,000 from the known seed, so it can time the same window without walking the first 999 pages.
At page 1,000, compare the two queries directly. Both fetch the narrow columns and the same 20 products plus a lookahead row.
docker compose exec -T db psql -X -U shop -d shop <<'SQL'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT id, category_id, title, price_centsFROM productsWHERE category_id = 21ORDER BY id LIMIT 21 OFFSET 19980;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT id, category_id, title, price_centsFROM productsWHERE category_id = 21 AND id > 24980ORDER BY id LIMIT 21;SQLIn the local plans, the index scan produced 20,001 rows for the offset query and 21 rows for the cursor query. The offset scan also filtered out 5,000 rows from other categories. Both plans used the primary-key index on this seed. EXPLAIN ANALYZE executes the statement, so the example uses read-only queries against the lab. PostgreSQL EXPLAIN
Step 4 – Measure the same pages under load
Section titled “Step 4 – Measure the same pages under load”The catalogue workload uses only the opt-in fixture. It checks the ordered items, prices and category on each response, and checks the expected query count using the Chapter 3 trace. Run each case at the same offered rate.
for entry in lazy:1 eager:1 projected:1 cursor:1 projected:1000 cursor:1000; do variant=${entry%:*} page=${entry#*:} WORKLOAD=catalogue CATALOGUE_VARIANT="$variant" CATALOGUE_PAGE="$page" \ RATE=30 DURATION_SECONDS=6 bash bench/run.sh steady || breakdoneCompare shop_catalogue_sql_ms for the database time in the request and shop_catalogue_ms for client-observed HTTP latency. The harness also records PHP process CPU when available and JSON body bytes. PHP CPU includes driver and framework work. Use that combined signal alongside the projection and query evidence. The original mixed workload keeps its lazy variant so readers can reproduce the earlier chapters’ measurements.
Local measurements
Section titled “Local measurements”Each row is the median of three six-second runs at an offered rate of 30 requests per second. The local lab ran PHP-FPM with OPcache on an Apple M3 Pro. The retained runs completed 180 or 181 requests each with zero failures or drops. One response failed the harness checks in the first deep-page comparison, so both sides of that comparison were rerun. The original runs are marked in the sample data. Measurements by trial
| Variant | Page | Queries | SQL p50 | HTTP p95 median and range | JSON body |
|---|---|---|---|---|---|
| Lazy | 1 | 22 | 8.88 ms | 15.60 ms (14.89–29.69) | 1,607 B |
| Eager | 1 | 3 | 6.18 ms | 16.59 ms (13.39–16.67) | 1,607 B |
| Projected offset | 1 | 3 | 6.09 ms | 16.10 ms (16.09–17.17) | 1,607 B |
| Projected cursor | 1 | 3 | 6.16 ms | 13.35 ms (13.33–13.67) | 1,674 B |
| Projected offset | 1,000 | 3 | 7.54 ms | 15.00 ms (13.78–18.19) | 1,647 B |
| Projected cursor | 1,000 | 3 | 5.97 ms | 12.31 ms (11.95–14.32) | 1,715 B |
Eager loading removed 19 queries per page. The narrower query removed about 84 KiB of description values from the first-page database result. Its HTTP body and query count stayed the same, and its measured SQL time was close to the full-row eager variant.
At page 1,000, both narrow variants still used three queries. The cursor saved work inside the product query, which is visible in the plan’s row counts and the lower SQL median. The HTTP p95 ranges still overlap. The cursor adds a small token to the JSON so the client can request the next page.
Exercise
Section titled “Exercise”Choose the projected and cursor variants and repeat them at pages 1 and 1,000. Check that the rows match. Compare the query counts, SQL medians and HTTP p95 before deciding whether changing the pagination interface is worth it for this endpoint.
Continue with Make the Remaining SQL Cheaper.