Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide

Why Generic Indexes Leave Performance on the Table

Most Laravel developers reach for a standard ->index() call in migrations and move on. That works — until your orders table has 20 million rows and 90% of your queries filter on status = 'pending'. A full B-tree index on status stores every row, but your application only ever queries the 2% that are pending. A partial index fixes this by indexing only the rows that match a predicate.

Similarly, a query that reads two columns — say user_id and created_at — still triggers a heap fetch after the index lookup unless those columns are bundled into a covering index. PostgreSQL can then satisfy the query entirely from the index (an Index Only Scan), skipping the heap entirely.

Partial Indexes in Laravel Migrations

Laravel's Schema::create doesn't expose partial index syntax natively, but DB::statement inside a migration is clean and version-controlled:

public function up(): void
{
    DB::statement(
        'CREATE INDEX idx_orders_pending_user
         ON orders (user_id, created_at DESC)
         WHERE status = \'pending\''
    );
}

public function down(): void
{
    DB::statement('DROP INDEX IF EXISTS idx_orders_pending_user');
}

Now this Eloquent query hits the partial index directly:

Order::where('status', 'pending')
    ->where('user_id', $userId)
    ->orderByDesc('created_at')
    ->get();

Run EXPLAIN (ANALYZE, BUFFERS) and you'll see Index Scan using idx_orders_pending_user with a tiny Buffers: shared hit count instead of a sequential scan.

Covering Indexes with INCLUDE

PostgreSQL 11+ supports INCLUDE columns — columns stored in the index leaf pages but not part of the B-tree key. This enables Index Only Scans without bloating the key structure:

DB::statement(
    'CREATE INDEX idx_users_email_covering
     ON users (email)
     INCLUDE (id, name, email_verified_at)'
);

A query like:

User::select('id', 'name', 'email_verified_at')
    ->where('email', $email)
    ->first();

...will now show Index Only Scan in EXPLAIN — zero heap pages read.

Reading the EXPLAIN Output

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, name, email_verified_at FROM users WHERE email = 'alice@example.com';

Look for these signals:

• Index Only Scan — covering index working perfectly.
• Heap Fetches: 0 — visibility map is current; no heap I/O.
• Buffers: shared hit=1 — single buffer, essentially free.

If you see Heap Fetches > 0, run VACUUM users; to update the visibility map. Autovacuum handles this in production, but freshly loaded tables need a manual pass.

Combining Both Techniques

For a dashboard query that lists a tenant's recent failed jobs:

DB::statement(
    'CREATE INDEX idx_failed_jobs_tenant_recent
     ON failed_jobs (tenant_id, failed_at DESC)
     INCLUDE (uuid, payload)
     WHERE failed_at > NOW() - INTERVAL \'30 days\''
);

This index is small (only 30 days of data), covers the selected columns, and the planner will use it for any query scoped to tenant_id with a recent failed_at filter.

Maintenance Considerations

• Partial indexes are smaller and faster to update than full indexes — write overhead is lower.
• INCLUDE columns add storage to leaf pages but don't affect key comparisons.
• Monitor index usage with pg_stat_user_indexes; drop indexes where idx_scan = 0 after a representative period.
• Partial index predicates must exactly match the query's WHERE clause for the planner to consider them — use = 'pending', not != 'processed'.

Takeaways

• Use partial indexes when a large fraction of rows are never queried — index only what you access.
• Use INCLUDE to build covering indexes that eliminate heap fetches for read-heavy queries.
• Always verify with EXPLAIN (ANALYZE, BUFFERS) — assumptions about planner behaviour are often wrong.
• Wrap DB::statement index DDL in reversible migrations to keep your schema under version control.
• Run VACUUM after bulk loads to let Index Only Scans reach zero heap fetches.