Eloquent Performance and Database Design: Evidence Before Eager Loading

A team dashboard feels fast during development. Each team has a few projects, every project has a handful of tasks, and the page loads almost immediately.

Six months later, one customer has thousands of projects and years of task history. The same page now spends most of its time waiting for the database. Someone suggests adding with() to the query. Someone else suggests caching the response. Either change might help, but neither tells us where the application spends its time.

Eloquent performance starts with understanding the work behind a query. How many statements run? How many rows does the database examine? How much data crosses the connection? How many models does PHP create? Are we waiting for a query to run or for another transaction to release a lock?

In this article, we will improve one team dashboard that has an N+1 problem. We will use eager loading, aggregates, subqueries, indexes, query plans, pagination, and bounded background processing. Then we will look at transaction boundaries and tests that keep those improvements in place.

The examples use Laravel 13. The query plan examples use PostgreSQL. The Eloquent concepts also apply to other supported databases, but you need to check plans and locking behavior on the engine you actually run.

Our application has teams, projects, users, and tasks. A project belongs to a team, may have an owner, and has many tasks. The dashboard displays:

• The 50 most recently created projects for the current team.
• Each project's name and owner's name.
• The number of open tasks.
• Whether the project has any overdue open tasks.
• When the most recent task was created.

We will use conventional Eloquent models and factories with these columns:

teams:    id, name
users:    id, name
projects: id, team_id, owner_id, name, status, created_at, updated_at
tasks:    id, project_id, title, status, due_at, created_at, updated_at

IDs are integer primary keys, and foreign keys reference their parent tables. The owner_id and due_at columns may be null. For our pagination order, projects.created_at must always have a value. An open task is overdue when its non-null due_at is before the current time. Tasks inherit their team through their project.

These are the relevant relationships on App\Models\Project:

use App\Models\Task;
use App\Models\User;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

public function owner(): BelongsTo
{
    return $this->belongsTo(User::class, 'owner_id');
}

public function tasks(): HasMany
{
    return $this->hasMany(Task::class);
}

The snippets focus on query behavior rather than a complete application. Team authorization happens before the dashboard query receives a team ID. Filtering by team_id scopes the data query, but it does not prove that the current user can access that team.

That distinction still matters when we focus on performance. A fast query that returns another customer's data is broken.

Let's start with an implementation that fetches a bounded list of projects and prepares some dashboard fields:

use App\Models\Project;

$projects = Project::query()
    ->where('team_id', $teamId)
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->limit(50)
    ->get();

$rows = $projects->map(fn (Project $project): array => [
    'name' => $project->name,
    'owner' => $project->owner?->name,
    'open_tasks' => $project->tasks->where('status', 'open')->count(),
]);

With normal lazy loading, 50 projects with owners can produce 101 queries. One query fetches the projects, 50 fetch the owners, and 50 fetch the task collections. This count assumes there is no automatic relationship autoloading, default eager loading, or extra queries from accessors and global scopes.

It also loads every task for those projects into PHP only to count the open ones. A project with 80,000 completed tasks makes this much more expensive, even if the dashboard shows only three open tasks.

There are two different issues here:

1. Too many round trips. The application fetches related data separately for each project.
2. Too much data. The application loads entire collections to calculate small scalar values.

Adding with(['owner', 'tasks']) fixes the first issue. For these relationships, it usually reduces the work to three queries. It does not fix the second issue because we still hydrate every task.

Query count is useful, but it does not define performance by itself.

Let's compare what crosses the database connection in each approach. This diagram assumes 50 projects with owners and the relationships above:

Application                         Database work                 PHP receives
───────────                         ─────────────                 ────────────

Lazy relationship access ─────────▶ Projects: 1 query ──────────▶ 50 projects
                         ├────────▶ Owners: 50 queries ────────▶ Owner models
                         └────────▶ Tasks: 50 queries ─────────▶ All related tasks

Eager load both relations ────────▶ Projects: 1 query ──────────▶ 50 projects
                         ├────────▶ Owners: 1 query ────────────▶ Owner models
                         └────────▶ Tasks: 1 query ─────────────▶ All related tasks

Load only what we display ────────▶ Projects + aggregates ──────▶ 50 projects + scalars
                         └────────▶ Owners: 1 query ────────────▶ Owner models

The middle approach removes repeated trips, but the full task history still crosses the connection and becomes PHP objects. The last approach keeps the count and existence checks in the database. We still need to measure how efficiently the database does that work.

Before changing the dashboard, measure it with a small team and a large team. Keep the page size, filters, database engine, and application configuration the same. Use representative data, including a few projects with much more history than the others.

At minimum, record:

• Total request duration, including a percentile such as p95 over repeated requests.
• Query count and cumulative database time.
• Repeated query shapes and the slowest statements.
• Peak PHP memory and the size of the returned response.
• Rows returned and the work needed to find them, based on execution plans.

Laravel's DB::listen() is a useful starting point. Add a listener to the boot() method of AppServiceProvider for local diagnosis:

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

public function boot(): void
{
    if (! $this->app->isLocal()) {
        return;
    }

    DB::listen(function (QueryExecuted $query): void {
        Log::debug('Database query executed', [
            'connection' => $query->connectionName,
            'sql' => $query->sql,
            'duration_ms' => $query->time,
        ]);
    });
}

Repeated statements such as select * from users where users.id = ? limit 1 make an N+1 pattern easy to spot. Seeing the same statement 50 times is often more useful than seeing one slow query.

The listener leaves bindings out of the logs on purpose. Bindings can contain personal data, tokens, or search terms. SQL with raw literals can still contain sensitive values, so placeholder SQL does not guarantee redaction.

Laravel also provides DB::whenQueryingForLongerThan() for cumulative database time. Register it separately in the provider's boot() method:

use Illuminate\Database\Connection;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

DB::whenQueryingForLongerThan(
    300,
    function (Connection $connection, QueryExecuted $query): void {
        Log::warning('Database time budget exceeded', [
            'connection' => $connection->getName(),
            'total_duration_ms' => $connection->totalQueryDuration(),
            'last_query_duration_ms' => $query->time,
        ]);
    },
);

The threshold is an example budget in milliseconds. It tracks cumulative query time on the connection instead of checking whether one statement exceeded 300 milliseconds. The callback receives the query that crossed the threshold, which may not be the slowest query.

Connect that signal to request or job tracing. In custom long running processes, verify that the lifecycle resets it. Use sampled telemetry in production instead of logging every statement indefinitely. Do not use a database backed logging destination inside a query listener.

Database timing does not include all the work PHP does after fetching rows. If SQL time falls but request duration and memory stay high, inspect model hydration, casts, accessors, serialization, and rendering. Disable verbose diagnostic logging for the final benchmark so its overhead does not skew the result.

The dashboard needs an owner model because it displays the owner's name. For tasks, it needs a count and a boolean instead of task models.

Let's express those requirements directly:

use App\Models\Project;
use Illuminate\Database\Eloquent\Builder;

$cutoff = now()->toImmutable();

$projects = Project::query()
    ->select(['id', 'team_id', 'owner_id', 'name', 'created_at'])
    ->where('team_id', $teamId)
    ->with('owner:id,name')
    ->withCount([
        'tasks as open_tasks_count' => fn (Builder $query): Builder => $query
            ->where('status', 'open'),
    ])
    ->withExists([
        'tasks as has_overdue_tasks' => fn (Builder $query): Builder => $query
            ->where('status', 'open')
            ->where('due_at', '<', $cutoff),
    ])
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->limit(50)
    ->get();

The presentation code can now read open_tasks_count and has_overdue_tasks. Eloquent adds the aggregate subqueries to the project statement and fetches owners in a separate statement. It does not hydrate task collections.

Call select() before withCount() and withExists(). Replacing the selected columns after adding those aggregates can remove the generated expressions.

The selected relationship keys also matter. Projects need owner_id, and the owner query needs the user's id so Eloquent can match the results. When loading children in a hasMany relationship, keep the child's foreign key, such as project_id, and its primary key.

withExists() asks whether a match exists, so the database can stop after a match when the plan allows it. withCount() must determine how many rows match. If the UI only needs a yes or no badge, counting every match asks the database to do more work than needed.

One detail often brings N+1 queries back:

$project->tasks()->where('status', 'open')->count();

Calling the relationship method creates a query. It does not use a loaded collection or the open_tasks_count attribute. Inside a loop, that call creates one count query per project. Read the aggregate attribute instead.

Suppose the dashboard later adds a preview of the three newest open tasks for each project. It now needs child models, but it still does not need the complete task history.

Give that partial collection a specific relationship name on Project:

public function openTasks(): HasMany
{
    return $this->hasMany(Task::class)->where('status', 'open');
}

Then add this eager load to the project builder before retrieving the page:

$query->with([
    'openTasks' => fn ($tasks) => $tasks
        ->select(['id', 'project_id', 'title', 'created_at'])
        ->orderByDesc('created_at')
        ->orderByDesc('id')
        ->limit(3),
]);

Laravel 13 supports per parent eager loading limits for this hasMany relationship. This is not one global LIMIT 3 shared by all projects. Inspect the generated SQL and its plan on your database. Limiting the returned children does not guarantee that the database examines only three rows per project.

This adds another bounded relationship query. For 50 displayed projects, the preview contains at most 150 task models. The aggregate still counts all open tasks, while openTasks contains only the preview. Do not use $project->openTasks->count() as the total.

We will leave this optional preview out of the main dashboard query. It shows how a new UI requirement changes the amount and shape of data we need to fetch.

The last dashboard field is the creation time of the newest task. Eager loading task history and sorting it in PHP would repeat the overfetching problem.

A correlated subquery can select that value for each project:

use App\Models\Task;

$query->addSelect([
    'last_task_created_at' => Task::query()
        ->select('created_at')
        ->whereColumn('project_id', 'projects.id')
        ->orderByDesc('created_at')
        ->orderByDesc('id')
        ->limit(1),
])->withCasts([
    'last_task_created_at' => 'immutable_datetime',
]);

It is correlated because it references projects.id from the outer query. A project with no tasks gets a null value. The query time cast lets presentation code use a returned timestamp as an immutable date object.

This removes extra round trips between the application and database, but a subquery still has a cost. The database must find the matching value for each outer row. An index can make that a small lookup. Without a useful access path, repeated scans can make one SQL statement very slow.

For this timestamp alone, withMax('tasks', 'created_at') is also valid. The ordered subquery is especially useful when the requirement changes to another value from the latest task, such as its title.

Joins can also help, but joining projects directly to all tasks produces several rows per project. A page limit on that result can return fewer distinct projects than requested. If a join is the right choice, preserve one row per project. For example, join a grouped subquery. Compare the plan instead of assuming that joins always beat subqueries.

We have reduced round trips and model hydration. The dashboard can still be slow for a large team if the database scans and sorts too much data.

An index is another data structure that helps the database find rows or read them in a useful order. For common B-tree indexes, column order determines which query patterns they support efficiently.

Look at the project query's shape:

SELECT id, team_id, owner_id, name, created_at
FROM projects
WHERE team_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

A candidate index is (team_id, created_at, id). The database can find a team's section of the index, read the timestamps and IDs in reverse order, and stop when it has enough rows.

Our task queries suggest two additional candidates:

• (project_id, status, due_at) for open-task counts and overdue existence checks.
• (project_id, created_at, id) for the latest-task lookup.

In a migration, the candidate additions look like this:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->index(['team_id', 'created_at', 'id'], 'projects_team_created_id');
});

Schema::table('tasks', function (Blueprint $table): void {
    $table->index(['project_id', 'status', 'due_at'], 'tasks_project_status_due');
    $table->index(['project_id', 'created_at', 'id'], 'tasks_project_created_id');
});

These are candidates to validate against the existing schema. Do not add every index shown here to every project management application.

The first task index has useful leading columns for a query that filters only by project_id and status. It does not need a due_at condition to help with an open task count. Its ordering does not directly help a query that lists all open tasks across every project by creation time.

Separate indexes on team_id and created_at are not equivalent to a composite index that matches our filter and sort. The optimizer may combine indexes for some queries, but that is a different access path.

If we enable the optional open task preview, (project_id, status, created_at, id) is another candidate to investigate. Its value depends on how often the preview runs, what its plan shows, and whether that justifies the write and storage cost.

Every index consumes storage and adds work to writes. Before adding one, inspect existing primary, unique, foreign key, and composite indexes. Before removing an index that looks redundant, check other queries and constraint requirements.

Creating an index on a large live table is an operational change. Use the concurrent or online index building procedure for your database and account for its restrictions. The portable schema snippet above does not promise a nonblocking production rollout.

EXPLAIN shows how the database plans to execute a statement. It lets us check whether an index is useful for the data and query we are testing.

Start with the main project query. Then inspect the full generated statement, including its aggregate subqueries. On PostgreSQL, use this in a local or staging environment:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, team_id, owner_id, name, created_at
FROM projects
WHERE team_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

Plain EXPLAIN reports estimates. EXPLAIN ANALYZE executes the statement and reports what happened. Use representative, safe data in an appropriate environment. It can consume real resources, acquire locks, and perform writes if you analyze a modifying statement.

Read the plan from the scans upward. For this dashboard, ask:

1. Where do the rows come from? Is it a sequential scan, index scan, or bitmap scan?
2. How much gets discarded? Are we filtering a huge number of rows to return 50 projects?
3. Is there a sort? How many rows enter it, and does it spill to disk?
4. Are estimates close to actual rows? Large differences can point to stale statistics or uneven data distributions.
5. How many times does a subplan run? A task lookup that looks cheap can dominate the work after many loops.
6. What do buffer reads and hits show? Selecting an index does not mean the query reads little data.

These two plan shapes are examples, not benchmark output:

Before:
Limit
  Sort: created_at DESC, id DESC
    Seq Scan on projects
      Filter: team_id = 42

Candidate improvement:
Limit
  Index Scan Backward using projects_team_created_id on projects
    Index Cond: team_id = 42

We want to avoid unnecessary scanning and sorting before reaching the page limit. The optimizer may choose another plan. A sequential scan can be right for a tiny table or a query that returns most of its rows.

PostgreSQL's estimated cost is not a duration in milliseconds. For repeated nodes, actual rows and timing are reported per loop. Account for the loop count. Do not add every parent and child timing together because their work overlaps in the plan tree.

Inspect the task subplans too. A count of 100,000 open tasks still counts 100,000 matching rows even when it uses an index. If exact live counts dominate the measured cost, a maintained summary may be worthwhile. That adds update ownership, consistency, and repair work. Use it only after measurements show that the live aggregate is too expensive.

Keep indexed columns usable in predicates. For a timestamp range, prefer created_at >= $start and created_at < $end instead of wrapping the column in a date conversion function when you want to use a normal timestamp index. Convert the user's timezone boundaries before building the query. Expression indexes can support other forms, but they are a deliberate database specific choice.

The dashboard currently fetches only the first 50 projects. Adding navigation means we must decide what the user needs from pagination:

• paginate() provides totals and numbered pages, normally using a count query plus the page query.
• simplePaginate() avoids the total count query, but still uses offsets.
• cursorPaginate() uses the ordered values as a position for the next query instead of skipping an increasing number of earlier rows.

Offset pagination is reasonable when numbered pages matter and the dataset is small enough. On deep pages of a large dataset, skipping earlier matching rows can become expensive.

Our dashboard needs only previous and next navigation, so we will use cursor pagination with created_at and id. The ID breaks ties between projects created at the same time. Both columns must be selected. The ordering values must be non-null and produce a unique combined order.

Let's put the complete query in a focused query class. This follows the approach from my Eloquent Query Classes article:

namespace App\Queries;

use App\Models\Project;
use App\Models\Task;
use Illuminate\Database\Eloquent\Builder;

final class TeamDashboardQuery
{
    public function forTeam(int $teamId): Builder
    {
        $cutoff = now()->toImmutable();

        return Project::query()
            ->select(['id', 'team_id', 'owner_id', 'name', 'created_at'])
            ->where('team_id', $teamId)
            ->with('owner:id,name')
            ->withCount([
                'tasks as open_tasks_count' => fn (Builder $query): Builder => $query
                    ->where('status', 'open'),
            ])
            ->withExists([
                'tasks as has_overdue_tasks' => fn (Builder $query): Builder => $query
                    ->where('status', 'open')
                    ->where('due_at', '<', $cutoff),
            ])
            ->addSelect([
                'last_task_created_at' => Task::query()
                    ->select('created_at')
                    ->whereColumn('project_id', 'projects.id')
                    ->orderByDesc('created_at')
                    ->orderByDesc('id')
                    ->limit(1),
            ])
            ->withCasts(['last_task_created_at' => 'immutable_datetime'])
            ->orderByDesc('created_at')
            ->orderByDesc('id');
    }
}

The caller can now retrieve a page after authorizing the team:

use App\Queries\TeamDashboardQuery;

$projects = app(TeamDashboardQuery::class)
    ->forTeam($teamId)
    ->cursorPaginate(50);

For a populated page with owners, the query class uses two SQL statements. One is the project query with its subqueries, and the other eager loads owners. Cursor pagination retrieves one extra parent row to detect whether another page exists. It may hydrate 51 projects even though the page displays 50. Empty results or pages without owner keys may need fewer statements.

Cursor pagination is not a snapshot of the dataset. Rows can be deleted, updated, or moved across the cursor's ordering position. Prefer stable ordering fields and keep the same team and filters while following a cursor. Do not promise snapshot consistency between page requests.

It also differs from Eloquent's cursor() method. Cursor pagination controls page navigation. cursor() controls how PHP iterates through a result set.

Our dashboard now has a bounded read path. Later, the team needs a maintenance job that archives old completed projects. Loading every project with get() is a poor fit for that job.

chunk() uses offset based batches. If the job changes a field used by its filter, processed rows can disappear from the result set and shift the next offset. That can cause the job to skip rows.

Use chunkById() when processing this changing set:

use App\Models\Project;
use Illuminate\Database\Eloquent\Collection;

$cutoff = now()->subYear()->toImmutable();

$lastId = Project::query()
    ->where('team_id', $teamId)
    ->max('id');

if ($lastId !== null) {
    Project::query()
        ->select('id')
        ->where('team_id', $teamId)
        ->where('status', 'completed')
        ->where('updated_at', '<', $cutoff)
        ->where('id', '<=', $lastId)
        ->chunkById(500, function (Collection $projects) use ($teamId, $cutoff): void {
            Project::query()
                ->where('team_id', $teamId)
                ->whereKey($projects->modelKeys())
                ->where('status', 'completed')
                ->where('updated_at', '<', $cutoff)
                ->update(['status' => 'archived']);
        });
}

Each batch advances beyond the last retrieved ID instead of skipping a number of matching rows. We keep the ID unchanged and recheck eligibility during the update. This prevents the job from blindly archiving a project that was reopened after the batch was read.

The captured maximum ID bounds this run for our increasing integer IDs. It is not a database snapshot. Concurrent changes can still affect which rows qualify. Rows that become eligible behind the current position wait for a later run. Persist progress if the operation must resume after a failure.

The set based update avoids a separate update statement for every project. It also skips individual Eloquent saving, updating, and related model events because it does not save each model. Use it only when bulk archiving matches the application's business behavior. If every project needs an audited transition, run that transition in bounded batches and accept the extra writes.

For read only work, cursor() hydrates one model at a time from one result query. It cannot eager load relationships, and driver level buffering can still retain a large amount of raw result data. Calling $project->owner inside that iteration can introduce N+1 queries again.

When an export needs owners, lazyById() provides lazy iteration through bounded batches and can eager load them:

$projects = Project::query()
    ->select(['id', 'owner_id', 'name'])
    ->where('team_id', $teamId)
    ->with('owner:id,name')
    ->lazyById(500);

foreach ($projects as $project) {
    fputcsv($stream, [$project->id, $project->name, $project->owner?->name], escape: '');
}

Here $stream is an open writable export stream owned by the caller. Keep the output streaming. Collecting every generated row into an array would remove the memory benefit. An export must also decide whether a changing dataset is acceptable or whether it needs a separate snapshot design.

A short query can be slow while it waits for a lock. Another index will not fix a transaction that holds a project row while making a remote API call.

Suppose the dashboard lets a manager archive an active project only when it has no open tasks. Reading a count and updating the project later leaves time for another request to create an open task between those operations.

A transaction gives us an atomic database operation. A row lock can coordinate competing writers, but only when every writer follows the same rule. Let's use the project row as the coordination point:

namespace App\Actions;

use App\Models\Project;
use DomainException;
use Illuminate\Support\Facades\DB;

final class ArchiveProject
{
    public function handle(int $teamId, int $projectId): void
    {
        DB::transaction(function () use ($teamId, $projectId): void {
            $project = Project::query()
                ->where('team_id', $teamId)
                ->whereKey($projectId)
                ->lockForUpdate()
                ->firstOrFail();

            if ($project->status === 'archived') {
                return;
            }

            if ($project->tasks()->where('status', 'open')->exists()) {
                throw new DomainException('A project with open tasks cannot be archived.');
            }

            $project->status = 'archived';
            $project->save();
        }, attempts: 3);
    }
}

Every operation that creates or reopens a task must lock the same project row first. It must check that the project is not archived and write the task inside its transaction. An import or another code path that ignores this protocol can violate the rule. Locking a project alone does not enforce a rule about all current and future tasks.

This example assumes PostgreSQL's usual READ COMMITTED isolation and one database connection for these models. Validate the behavior on your engine and isolation level. Transaction visibility rules differ between databases.

Here is the ordering when task creation obtains the project lock first:

Task creation                       Project row                 Archive request
─────────────                       ───────────                 ───────────────

BEGIN
Lock project ─────────────────────▶ Locked by creator
Check project is active                                           BEGIN
                                                                  Request same lock
Insert open task                                                  Wait
COMMIT ───────────────────────────▶ Lock released
                                    Locked by archiver ◀───────── Acquire lock
                                                                  Check open tasks
                                                                  Find committed task
                                                                  Reject and roll back

The second writer checks the business rule after it acquires the lock, using the state committed by the first writer. If archiving gets the lock first and succeeds, the task creator must see the new archived state and reject the insertion.

Keep the locked section short. Do not send email or make remote calls while holding the lock, even indirectly through model observers. Laravel can retry a transaction after a deadlock, so its callback may run again. Run external effects after commit and make their delivery safe to retry when needed.

When locking several projects, acquire them in a consistent order, such as ascending ID. This reduces opportunities for deadlocks. Measure lock wait time and transaction duration along with query execution time.

The dashboard read should not normally take FOR UPDATE locks. Its counts and owner names are for display, not an authoritative decision about whether a later write is allowed. Wrapping several reads in a transaction also does not give them a shared snapshot at every isolation level.

We need tests for correctness and growth. A fast count is not useful if it counts the wrong tasks or includes another team's projects.

The following Pest examples belong in a Laravel feature test file. They assume the models, factories, schema, and TeamDashboardQuery described earlier. They also assume there are no extra default eager loads or observers that query the database. Configure the file to use your application's TestCase.

First, check the meaning of the dashboard fields:

use App\Models\Project;
use App\Models\Task;
use App\Models\Team;
use App\Models\User;
use App\Queries\TeamDashboardQuery;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\DB;

uses(RefreshDatabase::class);

it('returns team-scoped counts and overdue flags', function (): void {
    $this->travelTo(now()->startOfDay());

    $team = Team::factory()->create();
    $otherTeam = Team::factory()->create();

    $project = Project::factory()->create(['team_id' => $team->id]);
    $completedProject = Project::factory()->create([
        'team_id' => $team->id,
        'owner_id' => null,
    ]);

    Project::factory()->create(['team_id' => $otherTeam->id]);

    Task::factory()->create([
        'project_id' => $project->id,
        'status' => 'open',
        'due_at' => now()->subDay(),
        'created_at' => now()->subDays(3),
    ]);

    Task::factory()->create([
        'project_id' => $project->id,
        'status' => 'open',
        'due_at' => now()->addDay(),
        'created_at' => now()->subDays(2),
    ]);

    Task::factory()->create([
        'project_id' => $completedProject->id,
        'status' => 'completed',
        'due_at' => now()->subDay(),
        'created_at' => now()->subDay(),
    ]);

    $projects = app(TeamDashboardQuery::class)
        ->forTeam($team->id)
        ->get()
        ->keyBy('id');

    expect($projects)->toHaveCount(2)
        ->and($projects[$project->id]->open_tasks_count)->toBe(2)
        ->and($projects[$project->id]->has_overdue_tasks)->toBeTrue()
        ->and($projects[$project->id]->last_task_created_at->equalTo(now()->subDays(2)))->toBeTrue()
        ->and($projects[$completedProject->id]->open_tasks_count)->toBe(0)
        ->and($projects[$completedProject->id]->has_overdue_tasks)->toBeFalse()
        ->and($projects[$completedProject->id]->owner)->toBeNull();
});

The second project has only a completed task. This checks that an overdue completed task does not turn on the badge. Also add cases for a project with no tasks, a null due date, and a task due exactly at the cutoff. The strict < comparison means the last task is not overdue yet.

Next, verify that growing the visible list does not grow the number of SQL statements:

it('keeps dashboard reads bounded as the list grows', function (int $projectCount): void {
    $team = Team::factory()->create();
    $owner = User::factory()->create();

    Project::factory()->count($projectCount)->create([
        'team_id' => $team->id,
        'owner_id' => $owner->id,
    ]);

    $connection = DB::connection();
    $connection->flushQueryLog();
    $connection->enableQueryLog();

    try {
        $page = app(TeamDashboardQuery::class)
            ->forTeam($team->id)
            ->cursorPaginate(50);

        $rows = $page->getCollection()->map(fn (Project $project): array => [
            'name' => $project->name,
            'owner' => $project->owner?->name,
            'open_tasks' => $project->open_tasks_count,
            'overdue' => $project->has_overdue_tasks,
            'last_task_at' => $project->last_task_created_at?->toIso8601String(),
        ]);

        $queries = $connection->getQueryLog();
    } finally {
        $connection->disableQueryLog();
        $connection->flushQueryLog();
    }

    expect($rows)->toHaveCount($projectCount)
        ->and($queries)->toHaveCount(2);
})->with([5, 50]);

Fixture creation happens before query recording starts. The recorded section includes reading the fields used by presentation. This exposes a regression that lazy loads owners during mapping. In the real application, test the actual resource or rendered view too. A query hidden in presentation should not escape the budget.

Enable Model::preventLazyLoading(! $this->app->isProduction()) in AppServiceProvider::boot() to catch accidental relationship property loads in development and tests. Use several persisted models in these tests. Lazy loading prevention does not detect every database call. Explicit queries such as $project->tasks()->count() still need query budget coverage.

Cursor navigation also deserves a focused test. Reuse the imports and test setup above:

it('paginates projects with identical creation times without duplicates', function (): void {
    $team = Team::factory()->create();

    $projects = Project::factory()->count(4)->create([
        'team_id' => $team->id,
        'created_at' => now()->startOfDay(),
    ]);

    $query = app(TeamDashboardQuery::class);

    $first = $query->forTeam($team->id)->cursorPaginate(2);
    $second = $query->forTeam($team->id)->cursorPaginate(
        2,
        cursor: $first->nextCursor(),
    );

    $ids = $first->getCollection()->pluck('id')
        ->concat($second->getCollection()->pluck('id'))
        ->all();

    expect($ids)->toBe($projects->pluck('id')->sortDesc()->values()->all())
        ->and($second->hasMorePages())->toBeFalse();
});

For the archive operation, test that open tasks reject the transition and leave the project unchanged. Test that an eligible project is archived and that an already archived project is a no-op. A separate concurrency test should use two database connections or processes. Let task creation acquire the project lock first, start archiving while that lock is held, commit the open task, and verify that archiving rejects it after obtaining the lock. Reverse the order and verify that task creation refuses the archived project.

Run those locking tests on the production database engine with committed fixtures. An ordinary transaction wrapped test does not prove PostgreSQL row lock behavior. Neither does SQLite's different locking model.

The query budget test does not prove that the database uses the right index. Dropping an index may leave the test green because the same two statements still run. Validate indexes with realistic staging data and execution plans. Use repeated load tests for latency and memory. Avoid brittle assertions that a database call must finish within a few milliseconds on a shared CI runner.

Improving Eloquent performance means deciding what work the application needs to do. Eager loading reduces repeated relationship queries. Aggregates avoid hydrating collections for scalar answers. Subqueries can fetch a related value directly. Indexes and query plans show whether the database can find those answers efficiently.

Pagination and batch processing limit how much data we handle at once. Transactions and locking keep write decisions correct. Tests and production measurements help prevent the same performance problems from returning.

Pick one slow screen in your application and start with its actual data needs. Capture the queries, find the unnecessary work, make one focused change, and measure again. This gives you a reason for every with(), aggregate, and index you add.

I hope that you liked this article and if you do, don't forget to share this article with your friends!!! See ya!