Skip to content
Bang for Buck: Scaling PHP Apps

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.

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.

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.

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.

Terminal window
docker compose build php
docker compose run --rm php php artisan migrate --force
docker compose run --rm php php artisan db:seed --class=CatalogueBenchmarkSeeder --force
docker compose exec -T db psql -X -U shop -d shop -c 'ANALYZE products'
docker compose up -d --wait --force-recreate php web worker shipping
curl -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.

Terminal window
bash bench/trace.sh catalogue lazy
bash bench/trace.sh catalogue eager

The 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.

Terminal window
docker compose exec -T db psql -X -U shop -d shop <<'SQL'
SELECT count(*) AS rows_read, sum(octet_length(description)) AS description_bytes
FROM (
SELECT description
FROM products
WHERE category_id = 21
ORDER BY id
LIMIT 21
) AS first_page;
SQL

Expect 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.

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

Terminal window
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.

Terminal window
docker compose exec -T db psql -X -U shop -d shop <<'SQL'
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, category_id, title, price_cents
FROM products
WHERE category_id = 21
ORDER BY id LIMIT 21 OFFSET 19980;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, category_id, title, price_cents
FROM products
WHERE category_id = 21 AND id > 24980
ORDER BY id LIMIT 21;
SQL

In 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.

Terminal window
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 || break
done

Compare 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.

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

VariantPageQueriesSQL p50HTTP p95 median and rangeJSON body
Lazy1228.88 ms15.60 ms (14.89–29.69)1,607 B
Eager136.18 ms16.59 ms (13.39–16.67)1,607 B
Projected offset136.09 ms16.10 ms (16.09–17.17)1,607 B
Projected cursor136.16 ms13.35 ms (13.33–13.67)1,674 B
Projected offset1,00037.54 ms15.00 ms (13.78–18.19)1,647 B
Projected cursor1,00035.97 ms12.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.

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.

Report a problem with this example