Partial & Covering Indexes in PostgreSQL for Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide 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. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 15 Jun 2026 · Updated 15 Jun 2026 · 4 min read ShareCopy linkCopied ![Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide](https://cdn.msaied.com/182/b7324e1d3db8ad8b9fd3c5d57e2e515a.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [performance](https://www.msaied.com/public/articles?search=performance) - [database](https://www.msaied.com/public/articles?search=database) 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](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)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](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleLaravel Gates, Policies, and Response-Based Access Control in Depth](https://www.msaied.com/public/articles/laravel-gates-policies-and-response-based-access-control-in-depth) [Next articleFilament at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://www.msaied.com/public/articles/filament-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)