Returning every row is fine until it isn't, and the point where it stops being fine arrives quietly. It's not a crash. Response time creeps up, payload size creeps up, and one day someone notices a list that used to load instantly now takes four seconds because the table underneath it grew past a few thousand rows.
The ops console's Sites panel, an internal admin tool operators use to search and filter every site in the fleet, pays that cost on purpose instead of by accident:
// app/Livewire/Admin/Ops/Infrastructure/SitesPanel.php
private function buildQuery(): LengthAwarePaginator
{
$query = Site::withTrashed()->orderByDesc('updated_at');
// ... search and filter conditions
return $query->paginate(10);
}
paginate(10): runs a COUNT(*) alongside the page query, so the page metadata (total rows, current page, last page) comes for free. That count isn't free to the database, it's a real cost you're choosing to pay in exchange for that metadata.
The page size is fixed at 10, chosen by the app, not the caller. This is an internal operator tool behind its own authorization gate, not a public endpoint, so there's no untrusted request deciding how much work each page does. That's a deliberate simplification, not an oversight: the moment a paginated list is reachable by an external client, the page size stops being the app's decision alone. If a public endpoint ever takes a client-supplied page size, it needs a ceiling, something like capping the requested size at a fixed maximum, or a client can ask for every row in one request and you've built an unpaginated endpoint with extra steps.
The page size you pick is a tradeoff between round trips and per-request cost. When you don't control who's asking, the ceiling you don't pick is a denial-of-service vector you handed them for free. Decide both on purpose.
Database Indexes And The Queries That Need Them
An index only helps the query it was built for. Add one speculatively and you've spent write overhead on every insert and update for a table scan you never asked for.
DomainBuyController checks a site's purchased-domain count before it lets a new buy request through, and it does that on every buy attempt, not just in an admin panel:
// app/Http/Controllers/DomainBuyController.php
private function boughtDomainCountForSite(Site $site): int
{
return Domain::query()
->where('site_id', $site->id)
->whereNotNull('registrar')
->whereNotIn('status', [DomainStatus::Failed->value, DomainStatus::Disconnected->value])
->count();
}
That's site_id and status in the same WHERE clause, on a hot path. The domains migration already has the index that query needs, because it was built alongside the query, not guessed at afterward:
// database/migrations/2026_07_09_194711_create_domains_table.php
Schema::create('domains', function (Blueprint $table) {
// ...
$table->index(['site_id', 'status']);
$table->index('hostname');
});
['site_id', 'status']: a composite index, not two separate ones. Column order matters: this index serves WHERE site_id = ? alone and WHERE site_id = ? AND status = ? together, because both queries use the leftmost columns of the index in order. It would not help a query that filters on status alone.
hostname: a single-column index. Every other domain lookup in this table pairs hostname with site_id, checking whether a hostname is already tracked for a site or resolving a connect attempt back to its row, so it's worth asking whether a composite (site_id, hostname) index would have served those queries better than two separate single-purpose indexes. That's the same discipline this section opened with: look at the query, don't assume the index that's already there is the one the query actually needs.
An index exists to serve a specific WHERE clause you actually write, in the column order you actually filter. Look at the query first, then design the index to match it, never the other way around. An index added because "it's a foreign key, it probably needs one" is a guess with the same problem as an un-measured cache: you don't know if it's doing anything until you check.