Mastering PostgreSQL Indexes in Production Laravel

Introduction

PostgreSQL is an extraordinarily robust relational engine, but unindexed queries will quickly degrade response times as tables scale beyond millions of rows.

In this guide, we explore how to design targeted composite and partial indexes in Laravel migrations.

The Cost of Sequential Scans

When PostgreSQL has no suitable index, it falls back to a sequential table scan, loading every single page from disk into memory.

EXPLAIN ANALYZE 
SELECT * FROM lessons 
WHERE course_id = 42 AND is_free_preview = true;

If lessons contains 500,000 rows, this query may take 120ms or more.

Creating a Composite Index in Laravel

In your migration, create a compound index matching the query's filter order:

Schema::table('lessons', function (Blueprint $table) {
    $table->index(['course_id', 'is_free_preview'], 'lessons_course_free_idx');
});

Leveraging Partial Indexes

Partial indexes only index rows that satisfy a specific WHERE clause. This drastically reduces index size and write overhead:

use Illuminate\Support\Facades\DB;

DB::statement('CREATE INDEX lessons_published_active_idx ON lessons (published_at) WHERE status = \'published\';');

Summary

• Use EXPLAIN (ANALYZE, BUFFERS) to inspect query cost.
• Add composite indexes based on actual filter and sorting cardinality.
• Partial indexes are ideal for sparse boolean states or published content.