Partial & Covering Indexes in PostgreSQL for Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide On this page 1. [ Why Generic Indexes Often Fall Short ](#why-generic-indexes-often-fall-short) 2. [ Partial Indexes: Index Only the Rows You Query ](#partial-indexes-index-only-the-rows-you-query) 3. [ Verifying with EXPLAIN ANALYZE ](#verifying-with-explain-analyze) 4. [ Covering Indexes: Satisfy Queries Without Touching the Heap ](#covering-indexes-satisfy-queries-without-touching-the-heap) 5. [ Combining Both Techniques ](#combining-both-techniques) 6. [ Eloquent Side: Making Sure the Planner Sees Your Index ](#eloquent-side-making-sure-the-planner-sees-your-index) 7. [ Takeaways ](#takeaways) ![Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide](https://cdn.msaied.com/182/b7324e1d3db8ad8b9fd3c5d57e2e515a.png) #laravel #postgresql #performance #database Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide ================================================================================= 15 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Generic Indexes Often Fall Short ](#why-generic-indexes-often-fall-short) 2. [ 02 Partial Indexes: Index Only the Rows You Query ](#partial-indexes-index-only-the-rows-you-query) 3. [ 03 Verifying with EXPLAIN ANALYZE ](#verifying-with-explain-analyze) 4. [ 04 Covering Indexes: Satisfy Queries Without Touching the Heap ](#covering-indexes-satisfy-queries-without-touching-the-heap) 5. [ 05 Combining Both Techniques ](#combining-both-techniques) 6. [ 06 Eloquent Side: Making Sure the Planner Sees Your Index ](#eloquent-side-making-sure-the-planner-sees-your-index) 7. [ 07 Takeaways ](#takeaways) 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. ```php // 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: ```sql 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 ```php $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. ```php 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: ```sql 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: ```php 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: ```php // app/Models/Order.php public function scopePending(Builder $query): Builder { return $query->where('status', 'pending'); } ``` ```php // 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpartial-indexes-and-covering-indexes-in-postgresql-a-laravel-developers-guide&text=Partial+Indexes+and+Covering+Indexes+in+PostgreSQL%3A+A+Laravel+Developer%27s+Guide) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpartial-indexes-and-covering-indexes-in-postgresql-a-laravel-developers-guide) Frequently Asked Questions ---------------------------- 3 questions Q01 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(). Q02 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. Q03 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. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Livewire v3 Lazy Components, Islands, and Deferred Loading in Practice](https://cdn.msaied.com/184/3ea770e36ec24931e1e8571a66d65cbe.png) livewire laravel performance ### Livewire v3 Lazy Components, Islands, and Deferred Loading in Practice Livewire v3 ships three distinct async rendering strategies. Learn when to reach for lazy, islands, and deferr... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jun 2026 4 min read Read ](https://msaied.com/articles/livewire-v3-lazy-components-islands-and-deferred-loading-in-practice) [ ![Filament at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://cdn.msaied.com/183/62be0d95101cf6a9babfd701562186ed.png) filament laravel multi-tenant ### Filament at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning Running Filament across multiple panels with separate auth guards, scoped queries, and optimised table perform... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jun 2026 3 min read Read ](https://msaied.com/articles/filament-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning) [ ![Laravel Gates, Policies, and Response-Based Access Control in Depth](https://cdn.msaied.com/181/5bda736cd48ab747366fdac25d0d0d78.png) laravel authorization security ### Laravel Gates, Policies, and Response-Based Access Control in Depth Move beyond simple boolean gates. Learn how Laravel's Response objects, before hooks, policy filters, and inli... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jun 2026 4 min read Read ](https://msaied.com/articles/laravel-gates-policies-and-response-based-access-control-in-depth) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)