PostgreSQL Partial and Covering Indexes for Laravel Query Performance

Why Generic Indexes Leave Performance on the Table

Most Laravel developers reach for $table->index('status') and move on. That single-column index works, but it indexes every row — including the 95 % of orders rows where status = 'completed' that your background worker never touches. PostgreSQL has two index features that fix this precisely: partial indexes (filter which rows are indexed) and covering indexes (embed extra columns so the engine never touches the heap).

Partial Indexes: Index Only the Rows You Query

A partial index carries a WHERE clause. Only rows satisfying that predicate are stored in the B-tree, making the index smaller, faster to update, and more cache-friendly.

Migration syntax

// database/migrations/2024_11_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');
}

Eloquent will use this index automatically when your query predicate matches:

Order::where('status', 'pending')
    ->orderByDesc('created_at')
    ->limit(50)
    ->get();

Run EXPLAIN (ANALYZE, BUFFERS) and you will see Index Scan using idx_orders_pending_created instead of a sequential scan — even on a table with millions of completed orders.

When to reach for a partial index

• Soft-delete patterns: index only WHERE deleted_at IS NULL
• Queue-style tables: index only WHERE processed_at IS NULL
• Feature flags: index only WHERE is_active = true

Covering Indexes: Satisfy Queries Without Touching the Heap

PostgreSQL 11+ supports INCLUDE columns on B-tree indexes. The planner can then perform an Index Only Scan, reading all needed columns directly from the index pages and skipping the heap entirely.

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

Now this query never touches the users heap:

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

EXPLAIN output will show Index Only Scan with Heap Fetches: 0 once the visibility map is up to date (run VACUUM after bulk loads).

Combining both techniques

CREATE INDEX idx_subscriptions_active_billing
ON subscriptions (next_billing_date ASC)
INCLUDE (user_id, plan_id)
WHERE status = 'active';

This index is tiny (only active subscriptions), sorted for range scans, and carries the two columns your billing job selects — a triple win.

Reading the EXPLAIN Output

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, plan_id
FROM subscriptions
WHERE status = 'active'
  AND next_billing_date < NOW();

Key lines to check:

| Line | Good sign | |---|---| | Index Only Scan | Heap not touched | | Heap Fetches: 0 | Visibility map current | | Buffers: shared hit=N | Data served from cache | | Rows Removed by Filter: 0 | Predicate matches index exactly |

If you still see Rows Removed by Filter > 0, your WHERE clause does not match the partial index predicate — check for type mismatches or expression differences.

Practical Checklist

• Audit high-traffic queries with pg_stat_statements before adding any index.
• Partial indexes pay off when a small fraction of rows is queried repeatedly.
• INCLUDE columns are worth it when SELECT columns are stable and the heap is large.
• Never add both a full index and a partial index on the same column set — the planner will pick one and the other wastes write overhead.
• Run VACUUM ANALYZE after bulk inserts to keep visibility maps current for Index Only Scans.
• Drop unused indexes: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0.

Takeaways

• Partial indexes shrink index size and improve cache hit rates by excluding irrelevant rows.
• Covering indexes with INCLUDE enable Index Only Scans, eliminating heap I/O entirely.
• Both features are invisible to Eloquent — define them in raw DB::statement migrations.
• Always validate with EXPLAIN (ANALYZE, BUFFERS) before and after; never trust assumptions.
• Combine partial + covering on the same index for maximum effect on queue and billing patterns.