Laravel Eloquent and Database Optimization: Relationships, N+1, Upserts, and Vector Search

Laravel Eloquent optimization distinguishes an app handling 10,000 concurrent users from one struggling with 500. An unguarded relationship access inside a Blade loop triggers an extra database query per row — 200 posts become 201 queries; 5,000 rows become 5,001. This is the N+1 problem, a common performance killer in Laravel apps. This guide details how to fix it, replace slow record-by-record writes with atomic upsert() statements, run aggregate subqueries without extra round trips, process millions of rows without exhausting PHP memory, and add semantic vector search to your Laravel 13 app using PostgreSQL and pgvector — all with production-ready code examples.

For complex data-driven applications, bookmark our Laravel performance guide as a companion reference — it covers caching, queue optimization, and HTTP-level performance alongside the database work covered here.

Prerequisites and Version Requirements

Ensure your environment meets these minimum versions before proceeding. Each feature discussed here has specific version requirements.

Laravel 13 requires PHP 8.3 as the minimum — PHP 8.2 is no longer supported. Upgrade paths are sequential: Laravel 10 → 11 → 12 → 13. Version-skipping is not supported.

Understanding the N+1 Query Problem

The N+1 query problem occurs when one query fetches N records, then N additional queries fire — one per record — to load a relationship. Fetching 100 published posts and then accessing $post->author->name in a Blade loop produces 101 database queries: 1 for posts, 100 for authors.

At small scale, this is invisible. At production scale with 1,000+ rows, 1,001 queries can add 300–900 ms of latency on an indexed database and several seconds on a remote DB host. Every millisecond matters when building production APIs that serve real Mumbai businesses with real customers.

Detecting N+1 in Development

Three tools catch N+1 issues before they reach production:

• Model::preventLazyLoading() — built into Laravel, throws an exception the moment a lazy-loaded relationship is accessed outside eager loading context.
• Laravel Telescope — displays query counts, execution time, and duplicate queries in a browser UI. Restrict Telescope access to authorized users in production.
• beyondcode/laravel-query-detector — automatically detects N+1 patterns in real-time and can alert via browser console, Debugbar, or log channel. Install with composer require beyondcode/laravel-query-detector --dev.

// Prevent lazy loading globally — add to AppServiceProvider::boot()
use Illuminate\Database\Eloquent\Model;
Model::preventLazyLoading(!app()->isProduction());
// DB::enableQueryLog() for manual spot-checks
DB::enableQueryLog();
$posts = Post::with('comments')->get();
dd(DB::getQueryLog()); // inspect every fired query

Important: preventLazyLoading() does NOT prevent lazy loading in production by default — it only throws in the environments you pass true for. Ensure all with() calls are complete before deploying.

Fixing N+1 with Eager Loading

Eager loading with with() fetches all related records in a single second query — not one per parent record. Replacing Post::all() with Post::with('comments', 'author')->get() reduces 101 queries to exactly 2, regardless of how many posts exist.

// Basic eager loading — resolves N+1
$posts = Post::with('comments', 'author')->get();

// Nested eager loading (comments AND their authors)
$posts = Post::with('comments.author')->get();
// Conditional eager loading with constraints
$posts = Post::with(['comments' => function ($query) {
$query->where('approved', true)->orderBy('created_at', 'desc');
}])->get();
// Model-level default eager loading
class Post extends Model {
protected $with = ['author']; // always eager-loaded
}
// Override per-query when you don't need the relation
Post::without('author')->get();

Critical gotcha: when constraining eager-loaded columns with the colon syntax, always include the foreign key and primary key columns or the relationship join silently breaks.

// CORRECT: includes id (PK) and user_id (FK on posts table)
$posts = Post::select('id', 'title', 'user_id')
->with(['author:id,name,email'])
->get();

// BROKEN: missing id on the author relation — returns null authors
$posts = Post::with(['author:name'])->get();

Avoid defining $with on a model for relationships that most queries don't need — it adds overhead on every database call. Prefer explicit with() at the call site.

Bulk Upserts: One Atomic Statement Instead of N×2 Queries

Laravel's upsert() method — introduced in Laravel 8.10.0 (October 2020) — performs a single atomic SQL statement that inserts new records and updates existing ones on conflict. It replaces the pattern of calling updateOrCreate() in a loop, which fires two queries per record (a SELECT then an INSERT or UPDATE).

The generated SQL is INSERT ... ON DUPLICATE KEY UPDATE on MySQL and MariaDB, and INSERT ... ON CONFLICT ... DO UPDATE on PostgreSQL and SQLite. SQL Server uses a MERGE statement.

// upsert() — single atomic bulk operation
// Introduced: Laravel 8.10.0
Flight::upsert(
[
['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150],
],
uniqueBy: ['departure', 'destination'], // must have a unique index (except SQL Server)
update: ['price']                        // columns to update on conflict
);

// updateOrCreate() — two queries per record, model events fire
// Fine for single records; avoid in loops with bulk data
Flight::updateOrCreate(
['departure' => 'Oakland', 'destination' => 'San Diego'],
['price' => 99]
);

Critical Upsert Gotchas

• MariaDB and MySQL ignore $uniqueBy — they always use the table's own primary or unique indexes for conflict detection. Relying on $uniqueBy for conflict logic on these drivers silently does nothing.
• Model events do not fire — creating, created, updating, updated, and saved events are bypassed. If you have observers that clear caches or write audit logs, those side effects are silently skipped.
• Mass assignment protection is bypassed — upsert() operates at the query-builder level. Validate and whitelist input columns manually before passing arrays to it.
• Missing unique index = silent duplicate inserts — on all databases except SQL Server, you must have a primary or unique index on the $uniqueBy columns. Without one, the operation inserts duplicates instead of updating.

Aggregate Subqueries: withCount, withSum, withExists

Laravel's aggregate subquery methods — withCount(), withSum(), withAvg(), withMin(), withMax(), and withExists() — all execute as subqueries embedded in the main SELECT statement. They do not fire separate queries. The underlying method powering all of them is withAggregate().

// All run as sub-SELECTs in one main query
$posts = Post::withCount('comments')
->withSum('orders', 'total')
->withAvg('reviews', 'rating')
->withExists('publishedComments')
->get();

// Access as dynamic attributes on each model
// $post->comments_count, $post->orders_sum_total, $post->reviews_avg_rating
// $post->published_comments_exists (boolean)
// Conditional aggregate — only count approved comments
$posts = Post::withCount(['comments' => function ($q) {
$q->where('approved', true);
}])->get(); // $post->comments_count = approved count only

withExists vs withCount: Choose the Cheaper Option

withExists() generates an EXISTS subquery, which the database can satisfy as soon as it finds the first matching row. withCount() generates a COUNT(*) subquery, which must scan all matching rows. When you only need to know whether a relationship exists — not how many records it has — withExists() is measurably faster on large tables.

// withExists — boolean check, uses EXISTS subquery (cheaper)
$users = User::withExists('subscription')->get();
// $user->subscription_exists === true | false

// withWhereHas — filter + eager load in one call
$users = User::withWhereHas('posts', function ($q) {
$q->where('published', true);
})->get();

Naming collision warning: if you use selectRaw() with the same attribute name as a withCount() alias, the selectRaw result silently wins. Do not reuse attribute names between aggregate methods and raw selects.

Large Dataset Strategies: chunk, cursor, lazy

Processing large datasets without exhausting PHP memory requires choosing between three strategies: chunk(), chunkById(), cursor(), and lazy(). Each makes a different trade-off between memory consumption, query count, and support for eager loading.

// chunk() — batch processing, supports eager loading
// Loads batch_size models into memory per iteration
User::with('orders')->chunk(500, function ($users) {
foreach ($users as $user) { /* process */ }
});

// chunkById() — safe when records are modified during processing
// Paginates by primary key; records cannot be skipped due to offset drift
User::chunkById(500, function ($users) {
$users->each->update(['processed' => true]);
});
// lazy() — generator syntax over chunked results (Laravel 8+, default chunk 1000)
// Syntactic sugar over chunk(); does NOT reduce memory vs chunk()
User::lazy()->each(function ($user) { /* process one at a time */ });
// cursor() — single unbuffered query, one model hydrated at a time
// ~1.87 MB for 300,000 rows vs much higher for chunk()
foreach (User::cursor() as $user) {
// WARNING: do NOT access unloaded relationships here — causes N+1
}

Do not use chunk() when modifying the records being iterated. As rows are updated or deleted, the underlying OFFSET-based pagination skips records. Use chunkById() instead — it paginates by primary key and cannot skip rows.

Do not chain with() onto cursor(). The cursor uses a single unbuffered query that cannot be extended with relationship joins. Accessing a relationship inside a cursor() loop causes N+1 — no error is thrown, relationships are simply lazy-loaded per row.

Building a Laravel application that processes large datasets, syncs product catalogs, or handles thousands of concurrent API requests? Our team at Mumbai Web Designer architects performant Laravel backends for businesses across Mumbai and India.

Selecting Specific Columns: Why SELECT * Hurts

Calling Model::all() or Model::get() without a column list fires SELECT *, loading every column — including large TEXT, JSON, and binary fields — into PHP memory for every row. On models with content columns, token columns, or BLOB fields, this multiplies memory and network transfer costs significantly.

// Bad: loads all columns including large text/blob fields
$users = User::all();

// Good: only the columns you need
$users = User::select('id', 'name', 'email')->get();
// With eager loading — specify columns on the relation too
$posts = Post::select('id', 'title', 'user_id')
->with(['author:id,name,email']) // colon syntax for relation columns
->get();
// Always include the FK (user_id on posts) and PK (id on authors)
// Missing these breaks the relationship join silently

Column selection reduces memory, network transfer, and allows the database to satisfy queries from covering indexes. The trade-off is maintenance burden — column lists must be updated as models evolve. A missing FK or PK column in an eager-loaded relation silently breaks the join and returns null relationships without throwing an error.

Security note: SELECT * in public-facing queries can expose sensitive columns — password hashes, API tokens, and PII — in API responses. Always explicitly select needed columns for any endpoint that serializes model data to JSON.

Vector Search in Laravel 13: Semantic Similarity with pgvector

Laravel 13 (released March 17, 2026) introduced native vector search via whereVectorSimilarTo('column', $embedding), backed by PostgreSQL and the pgvector extension. This enables semantic similarity search — finding results that are conceptually related to a query even when they share no keywords. A search for "high-performance running shoes" can return "athletic footwear for marathons" without any text overlap.

Vector search requires three components working together: PostgreSQL with the pgvector extension installed, the Laravel AI SDK (a first-party package), and a vector column on your model's database table. Laravel Cloud bundles pgvector with its managed PostgreSQL, so production deployment requires no extra database configuration on that platform.

Setting Up Vector Search

// Step 1: Migration — define the vector column
Schema::table('products', function (Blueprint $table) {
// 1536 dimensions = OpenAI text-embedding-3-small output size
$table->vector('embedding', 1536);
});

// Step 2: Store embedding when creating or updating a product
$product->embedding = Ai::embeddings()->create($product->description);
$product->save();
// Step 3: Semantic similarity query
$queryEmbedding = Ai::embeddings()->create('high-performance running shoes');
$results = Product::query()
->whereVectorSimilarTo('embedding', $queryEmbedding)
->limit(10)
->get();

Vector Index: IVFFlat vs HNSW

pgvector supports two index types for approximate nearest-neighbor search at scale. Without an index, every vector similarity query performs a full-table scan — fast for small tables, prohibitive for millions of rows.

Vector Search vs LIKE / Full-Text Search

Choosing between semantic vector search and traditional text search depends on your use case, database, and budget.

Vector Search Security Considerations

Vector search introduces security risks beyond traditional SQL injection. The OWASP LLM Top 10 (2025) documents these under LLM08 — Excessive Agency:

• Embedding inversion attacks — sufficiently large embeddings can be partially reversed to recover source text. Avoid embedding raw sensitive PII (Aadhaar numbers, medical data).
• Retrieval poisoning — if user-controlled text is embedded and stored without validation, attackers can inject vectors that manipulate search results (prompt injection via RAG).
• Cross-tenant retrieval — multi-tenant vector stores without row-level access controls allow one tenant to retrieve another's data. Enforce row-level security or use separate pgvector schemas per tenant.
• Never pass raw user text to whereVectorSimilarTo() — the method expects an array of floats (the embedding output). Always generate the embedding first via Ai::embeddings()->create().

Relationship Patterns and Performance Gotchas

Eloquent relationships are convenient but carry hidden performance costs when used without care. Understanding the internals of polymorphic relationships, through-relationships, and MorphMap prevents both performance issues and data integrity problems.

Polymorphic Relationships and MorphMap

Polymorphic relationships use two columns: imageable_type (the parent PHP class name) and imageable_id (the parent record ID). Without a MorphMap, Laravel stores the full class name — App\Models\Post — in the database. Renaming or moving the class becomes a data migration problem across potentially millions of rows.

// Register MorphMap in AppServiceProvider::boot()
use Illuminate\Database\Eloquent\Relations\Relation;

Relation::morphMap([
'post'    => \App\Models\Post::class,
'video'   => \App\Models\Video::class,
'product' => \App\Models\Product::class,
]);

With MorphMap registered, the database stores 'post' instead of 'App\Models\Post'. Class renames no longer require a data migration.

hasManyThrough and the Hidden N+1

hasManyThrough does NOT automatically hydrate the intermediate model's parent on child models during loop iterations. Accessing the parent from a child inside a loop still causes N+1. Always eager-load the full chain explicitly and access results through the correctly loaded relationship chain — not through intermediate model traversals.

Index Your Foreign Keys

Eloquent relies on indexed foreign key columns for efficient JOIN and subquery operations. A missing index on a foreign key column causes full table scans on every relationship load. Always add $table->index('user_id') or $table->foreign('user_id')... in migrations for every FK column.

Architecture Decisions and Trade-offs

Every Eloquent optimization involves a trade-off between memory, query count, developer ergonomics, and code maintainability. Use this reference when making design decisions.

Common Mistakes to Avoid

These are the ten most common Eloquent mistakes that cause production performance regressions and silent data bugs in Laravel applications.

1. Calling Model::all() in a controller, then accessing a relationship in a Blade loop. This is the classic N+1. Always add with() before calling get().
2. Using with('relation') but accessing a different, non-eager-loaded relationship in the same loop. Only the specified relationships are pre-loaded. Any other access fires a lazy query.
3. Omitting the FK column when constraining eager-loaded columns. Post::with(['author:id,name']) works; Post::with(['author:name']) silently returns null authors because the primary key id is missing.
4. Using upsert() without a unique or primary index on the $uniqueBy columns on databases other than SQL Server. The operation inserts duplicates silently.
5. Assuming upsert() fires model events. It does not. Observers, cache-clearing hooks, and audit log listeners are bypassed entirely.
6. Using updateOrCreate() in a loop for bulk data sync. Each call is two queries. Use upsert() for bulk operations instead.
7. Using withCount() when only needing boolean presence. withExists() generates a cheaper EXISTS subquery.
8. Using chunk() while modifying the records being iterated. Records shift pages and some are skipped. Use chunkById() instead.
9. Using cursor() with eager loading. Chaining with() onto a cursor does not throw an error — it simply lazy-loads relationships per row, causing N+1.
10. Defining $with on a model for rarely-needed relationships. Every query on that model incurs the join overhead, even when the relationship data is not used.

Testing and Verification

Verifying Eloquent optimizations in development requires checking both query count and execution time — not just code review. Use these tools to confirm your changes actually reduce query load.

Verification Steps

1. Enable preventLazyLoading(!app()->isProduction()) in AppServiceProvider::boot(). Any lazy-loaded relationship access immediately throws an exception in development.
2. Enable DB::enableQueryLog() before the code path you are testing and dd(DB::getQueryLog()) after. Count the queries. If the count grows linearly with record count, you have an N+1.
3. Install Laravel Telescope (composer require laravel/telescope --dev) and inspect the Queries panel. Identical repeated queries are a clear N+1 signal.
4. Install beyondcode/laravel-query-detector and configure it in config/querydetector.php to alert on N+1 patterns via console or Debugbar.
5. For upsert() verification: confirm the unique index exists on $uniqueBy columns with SHOW INDEX FROM flights (MySQL) or \d flights (PostgreSQL). Then run the upsert twice with differing price values and verify the row count stays the same while the price updates.
6. For vector search: verify pgvector is installed with SELECT * FROM pg_extension WHERE extname = 'vector'. Confirm vector column exists in the migration output. Test a query with whereVectorSimilarTo() and check the results are semantically relevant to the query string.

Need a senior Laravel developer to audit your application's database layer, fix N+1 bottlenecks, or architect a vector search pipeline? We build and optimize Laravel backends for Mumbai businesses and clients across India — from custom portals to high-volume e-commerce platforms.

Frequently Asked Questions

What is the N+1 query problem in Laravel?

The N+1 query problem occurs when Laravel fires one query to fetch N parent records, then fires N additional queries — one per parent — to load a relationship. Fetching 100 blog posts and accessing $post->author->name in a loop without eager loading produces 101 queries: 1 for posts and 100 for authors. Fixing it requires using Post::with('author')->get(), which replaces those 100 individual author queries with a single second query regardless of post count. At production scale, the difference between 2 queries and 1,001 queries is the difference between a 50 ms response and a 5-second timeout.

When should I use upsert() instead of updateOrCreate() in Laravel?

Use upsert() whenever you need to insert or update multiple records in bulk — product catalog syncs, pricing feed imports, user data migrations, or any operation where you are iterating over an array of records. upsert() performs a single atomic SQL statement regardless of how many records you pass it. updateOrCreate() fires two queries per record (SELECT then INSERT or UPDATE) and is best reserved for single-record operations where you need model events to fire — for example, when an observer sends a notification or clears a cache on update. Never call updateOrCreate() in a loop for bulk data.

What is the difference between cursor(), chunk(), and lazy() in Laravel?

chunk() fetches records in batches (e.g. 500 at a time), loading each batch fully into PHP memory before processing. It supports eager loading with with() and is safe for most large-dataset operations. cursor() issues a single unbuffered database query and hydrates one model at a time, consuming roughly 1.87 MB of memory for 300,000 rows — but it cannot use eager loading, so accessing any relationship inside a cursor loop causes N+1. lazy() is syntactic sugar over chunk() with a generator interface; it consumes the same memory as chunk(), not the minimal memory of cursor(). Choose chunkById() when the records being iterated may be modified during processing to avoid skipping rows.

How does vector search work in Laravel 13?

Laravel 13 introduced native vector search via the whereVectorSimilarTo('column', $embedding) Eloquent method, which generates a pgvector cosine similarity query on PostgreSQL. To use it, you add a vector column to your migration ($table->vector('embedding', 1536)), generate embeddings for each record using Ai::embeddings()->create($text) from the first-party Laravel AI SDK, and store those float arrays in the vector column. At query time, you generate an embedding for the search query and pass it to whereVectorSimilarTo(). The database returns records ordered by semantic proximity to the query vector. This requires PHP 8.3+, PostgreSQL with the pgvector extension, and Laravel 13.x or later.

When should I use withExists() instead of withCount()?

Use withExists() any time you only need to know whether a related record exists — not how many there are. withExists() generates an SQL EXISTS subquery, which the database satisfies as soon as it finds the first matching row in the related table. withCount() generates a COUNT() subquery, which must scan all matching rows and sum them. On large tables with thousands of related records, withExists() is measurably faster. The result is a boolean attribute on each model — $user->subscription_exists is either true or false. If you later need the actual count, switch to withCount() — but never use withCount() purely to check for non-zero values.