Skip to content

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

Partial and covering indexes can eliminate full-table scans and index-only scans in your Laravel apps. Learn when to reach for each, how to define them in migrations, and how to verify the query planner uses them.

Published · Updated · 4 min read

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.

Frequently asked questions

Can Laravel's Schema Builder create partial or covering indexes without raw SQL?

Not natively. As of Laravel 11, the Schema Builder has no first-class support for PostgreSQL's WHERE predicate or INCLUDE clause. Use DB::statement() inside your migration's up() method and a matching DROP INDEX in down().

How do I confirm an Index Only Scan is actually heap-free in production?

Run EXPLAIN (ANALYZE, BUFFERS) on the query and look for 'Heap Fetches: 0'. If the number is non-zero, the visibility map for that table is stale — schedule a VACUUM or enable autovacuum more aggressively on that table.

Does a partial index help if the filtered column has low cardinality overall but high selectivity for one value?

Yes — that is exactly the sweet spot. If 95% of rows are 'completed' and 5% are 'pending', a partial index on the 'pending' rows is tiny and highly selective, whereas a full index on status would be nearly useless for the common case.

Mohamed Said

About the author

Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow.

About GitHub LinkedIn WhatsApp Email Address My CV

Have a technical challenge?

Tell me what you’re building. I reply within two working days.

Discuss your project