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

Why Generic Indexes Often Fall Short

Most Laravel developers reach for $table->index(['status', 'created_at']) and call it done. That works — until your orders table has 20 million rows and 18 million of them share status = 'completed'. The planner may ignore your index entirely because the selectivity is too low. Two PostgreSQL index features fix this cleanly: partial indexes and covering indexes.

Partial Indexes: Index Only the Rows You Query

A partial index stores entries only for rows matching a WHERE predicate. If your application almost exclusively queries pending orders, index only those rows.

// database/migrations/2024_06_01_000001_add_partial_index_to_orders.php
public function up(): void
{
    DB::statement(
        "CREATE INDEX idx_orders_pending_created
         ON orders (created_at DESC)
         WHERE status = 'pending'"
    );
}

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

This index is tiny — it only contains the fraction of rows where status = 'pending'. The planner will use it for:

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

But not for status = 'completed' queries, which is exactly what you want.

Verifying with EXPLAIN ANALYZE

$plan = DB::select(
    "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
     SELECT id, user_id, total
     FROM orders
     WHERE status = 'pending'
     ORDER BY created_at DESC
     LIMIT 50"
);

foreach ($plan as $row) {
    echo $row->{'QUERY PLAN'} . "\n";
}

Look for Index Scan using idx_orders_pending_created in the output. If you see Seq Scan, the planner decided the index wasn't selective enough — re-examine your data distribution.

Covering Indexes: Satisfy Queries Without Touching the Heap

A covering index stores extra columns alongside the indexed key using PostgreSQL's INCLUDE clause. When every column in a SELECT list is present in the index, PostgreSQL performs an Index Only Scan — it never touches the main table heap at all.

DB::statement(
    "CREATE INDEX idx_orders_user_status_covering
     ON orders (user_id, status)
     INCLUDE (id, total, created_at)"
);

Now this query is heap-free:

SELECT id, total, created_at
FROM orders
WHERE user_id = 42
  AND status = 'pending';

The EXPLAIN output will read Index Only Scan with Heap Fetches: 0 once the visibility map is up to date (run VACUUM if you see non-zero heap fetches in development).

Combining Both Techniques

You can combine partial and covering in a single index:

DB::statement(
    "CREATE INDEX idx_orders_pending_user_covering
     ON orders (user_id, created_at DESC)
     INCLUDE (id, total)
     WHERE status = 'pending'"
);

This is a small, fast, heap-free index that serves your most common dashboard query perfectly.

Eloquent Side: Making Sure the Planner Sees Your Index

The planner uses your index only when the query matches the index predicate exactly. Eloquent scopes help enforce this:

// app/Models/Order.php
public function scopePending(Builder $query): Builder
{
    return $query->where('status', 'pending');
}

// Controller or action
$orders = Order::pending()
    ->where('user_id', $userId)
    ->orderByDesc('created_at')
    ->limit(50)
    ->get(['id', 'total', 'created_at']);

The explicit column list in get() is important — SELECT * forces a heap fetch even with a covering index.

Takeaways

• Partial indexes shrink index size and improve selectivity by indexing only rows matching a predicate — ideal for status-filtered queries.
• Covering indexes (INCLUDE) enable Index Only Scans, eliminating heap access entirely for read-heavy paths.
• Always verify with EXPLAIN (ANALYZE, BUFFERS) — never assume the planner picks your index.
• Explicit column lists in Eloquent (->get(['col1', 'col2'])) are required for Index Only Scans to work.
• Run VACUUM regularly so the visibility map stays current; stale maps cause unexpected heap fetches.
• Define both index types in raw DB::statement() migrations — Laravel's schema builder doesn't expose INCLUDE or partial predicates natively.