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) PostgreSQL Partial and Covering Indexes for Laravel Query Performance On this page 1. [ Why Generic Indexes Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ Partial Indexes: Index Only the Rows You Query ](#partial-indexes-index-only-the-rows-you-query) 3. [ Migration syntax ](#migration-syntax) 4. [ When to reach for a partial index ](#when-to-reach-for-a-partial-index) 5. [ Covering Indexes: Satisfy Queries Without Touching the Heap ](#covering-indexes-satisfy-queries-without-touching-the-heap) 6. [ Combining both techniques ](#combining-both-techniques) 7. [ Reading the EXPLAIN Output ](#reading-the-explain-output) 8. [ Practical Checklist ](#practical-checklist) 9. [ Takeaways ](#takeaways) ![PostgreSQL Partial and Covering Indexes for Laravel Query Performance](https://cdn.msaied.com/558/40ed2a1b013ac76dc4d08f1a245c5747.png) #postgresql #laravel #performance #indexing PostgreSQL Partial and Covering Indexes for Laravel Query Performance ======================================================================= 17 Aug 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 9 sections 1. [ 01 Why Generic Indexes Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ 02 Partial Indexes: Index Only the Rows You Query ](#partial-indexes-index-only-the-rows-you-query) 3. [ 03 Migration syntax ](#migration-syntax) 4. [ 04 When to reach for a partial index ](#when-to-reach-for-a-partial-index) 5. [ 05 Covering Indexes: Satisfy Queries Without Touching the Heap ](#covering-indexes-satisfy-queries-without-touching-the-heap) 6. [ 06 Combining both techniques ](#combining-both-techniques) 7. [ 07 Reading the EXPLAIN Output ](#reading-the-explain-output) 8. [ 08 Practical Checklist ](#practical-checklist) 9. [ 09 Takeaways ](#takeaways) 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 ```php // 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: ```php 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. ```php DB::statement( 'CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name, created_at)' ); ``` Now this query never touches the `users` heap: ```php 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 ```sql 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 -------------------------- ```sql 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-and-covering-indexes-for-laravel-query-performance&text=PostgreSQL+Partial+and+Covering+Indexes+for+Laravel+Query+Performance) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-and-covering-indexes-for-laravel-query-performance) Frequently Asked Questions ---------------------------- 3 questions Q01 Will Laravel's schema builder ever support partial or covering indexes natively? As of Laravel 11, the Blueprint API does not expose a WHERE clause or INCLUDE syntax for indexes. You must use DB::statement() with raw SQL in your migrations. There are open community PRs, but nothing merged yet. Q02 Do partial indexes work with Eloquent global scopes like SoftDeletes? Yes, as long as the SQL predicate in the index matches the WHERE clause Eloquent generates. For SoftDeletes, create the index with WHERE deleted_at IS NULL and Eloquent's automatic whereNull('deleted_at') scope will trigger it. Q03 When does an Index Only Scan fall back to a heap fetch? PostgreSQL uses the visibility map to determine whether a heap fetch is needed. Rows modified after the last VACUUM may not be marked all-visible, forcing a heap fetch. Running VACUUM ANALYZE after bulk loads keeps Heap Fetches near zero. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Filament at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://cdn.msaied.com/557/7c7cc76acf702e58f5175e1308414ec8.png) filament laravel multi-tenant ### Filament at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning Running Filament across multiple panels with distinct auth guards and tuning table queries for large datasets... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 16 Aug 2026 3 min read Read ](https://msaied.com/articles/filament-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning-4) [ ![Contextual Macros and Mixins: Extending Laravel Collections Without Bloat](https://cdn.msaied.com/556/0c5a2892229d005cb3b747c868df5bb6.png) laravel collections macros ### Contextual Macros and Mixins: Extending Laravel Collections Without Bloat Learn how to add domain-specific behaviour to Laravel's Collection class using macros, mixins, and higher-orde... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 16 Aug 2026 3 min read Read ](https://msaied.com/articles/contextual-macros-and-mixins-extending-laravel-collections-without-bloat) [ ![Modular Monolith in Laravel: Enforcing Bounded Contexts Without a Microservice Tax](https://cdn.msaied.com/555/c194fc79e9397fef3bcd3a896eb558fd.png) laravel architecture ddd ### Modular Monolith in Laravel: Enforcing Bounded Contexts Without a Microservice Tax Learn how to carve a Laravel application into cohesive bounded contexts using modules, internal contracts, and... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 16 Aug 2026 3 min read Read ](https://msaied.com/articles/modular-monolith-in-laravel-enforcing-bounded-contexts-without-a-microservice-tax) [ ![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)