Partial & Covering Indexes in Laravel + PostgreSQL | 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 in Laravel Migrations and Query Plans 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 What You Query ](#partial-indexes-index-only-what-you-query) 3. [ Covering Indexes: Eliminate Heap Fetches ](#covering-indexes-eliminate-heap-fetches) 4. [ Reading EXPLAIN ANALYZE Output ](#reading-explain-analyze-output) 5. [ Combining Both: Partial Covering Index ](#combining-both-partial-covering-index) 6. [ Practical Takeaways ](#practical-takeaways) ![PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans](https://cdn.msaied.com/373/a6b1671af111801768bc65898128a6e4.png) #postgresql #laravel #performance #database PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans =============================================================================== 6 Jul 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Generic Indexes Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ 02 Partial Indexes: Index Only What You Query ](#partial-indexes-index-only-what-you-query) 3. [ 03 Covering Indexes: Eliminate Heap Fetches ](#covering-indexes-eliminate-heap-fetches) 4. [ 04 Reading EXPLAIN ANALYZE Output ](#reading-explain-analyze-output) 5. [ 05 Combining Both: Partial Covering Index ](#combining-both-partial-covering-index) 6. [ 06 Practical Takeaways ](#practical-takeaways) Why Generic Indexes Leave Performance on the Table -------------------------------------------------- Most Laravel applications add indexes reactively — a slow query appears, someone runs `Schema::table('orders', fn($t) => $t->index('status'))`, and the problem is patched. That works until the table grows and the planner decides a sequential scan is cheaper because the index covers too many rows. Two PostgreSQL features fix this at the schema level: **partial indexes** (index only the rows you actually query) and **covering indexes** (embed payload columns so the planner never touches the heap). Neither requires a custom database driver — just raw index definitions in your migrations. --- Partial Indexes: Index Only What You Query ------------------------------------------ A partial index carries a `WHERE` clause. If your application only ever queries `orders` where `status = 'pending'`, 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'); } ``` The index is tiny — it only contains rows where `status = 'pending'`. The planner will use it when your query includes the same predicate: ```php // Planner sees the WHERE clause matches the partial index condition Order::where('status', 'pending') ->orderByDesc('created_at') ->limit(50) ->get(); ``` If you omit `->where('status', 'pending')`, PostgreSQL will not use this index. That is intentional — the index is meaningless for other statuses. --- Covering Indexes: Eliminate Heap Fetches ---------------------------------------- A covering index uses `INCLUDE` to attach non-key columns. The planner can satisfy the entire query from the index leaf pages without a heap fetch (an **Index Only Scan**). ```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::select('id', 'name', 'created_at') ->where('email', $email) ->first(); ``` The `INCLUDE` columns are not part of the B-tree key, so they add no ordering overhead and do not bloat the index structure as aggressively as adding them to the key would. --- Reading EXPLAIN ANALYZE Output ------------------------------ Always verify with `EXPLAIN (ANALYZE, BUFFERS)`: ```php $sql = User::select('id', 'name', 'created_at') ->where('email', 'alice@example.com') ->toRawSql(); $plan = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) {$sql}"); foreach ($plan as $row) { echo $row->{'QUERY PLAN'} . "\n"; } ``` Key lines to look for: - **Index Only Scan** — the covering index is working; heap fetches are zero. - **Index Scan** — the index is used but heap rows are still fetched (add `INCLUDE` columns). - **Bitmap Heap Scan** — common for range queries; acceptable but check `Heap Blocks: exact=0` for visibility map wins. - **Seq Scan** — the planner chose a full scan; check row estimates and `work_mem`. ```yaml Index Only Scan using idx_users_email_covering on users (cost=0.43..8.45 rows=1 width=52) (actual time=0.021..0.022 rows=1 loops=1) Index Cond: (email = 'alice@example.com') Heap Fetches: 0 ``` `Heap Fetches: 0` confirms the visibility map is current and no heap I/O occurred. --- Combining Both: Partial Covering Index -------------------------------------- You can combine both techniques: ```php DB::statement( "CREATE INDEX idx_invoices_unpaid_covering ON invoices (due_date ASC) INCLUDE (id, customer_id, total_cents) WHERE paid_at IS NULL" ); ``` This index is small (only unpaid invoices), ordered for due-date queries, and carries the columns needed for a dashboard list — all without touching the heap. --- Practical Takeaways ------------------- - Use partial indexes when a query always filters on a low-cardinality column with a fixed value (`status`, `type`, boolean flags). - Use `INCLUDE` to turn an Index Scan into an Index Only Scan for read-heavy list queries. - Combine both for high-traffic, narrow-predicate queries like dashboards or queue polling. - Always confirm with `EXPLAIN (ANALYZE, BUFFERS)` — the planner can surprise you. - Run `VACUUM ANALYZE` after bulk loads; stale statistics cause the planner to ignore valid indexes. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-and-covering-indexes-in-laravel-migrations-and-query-plans&text=PostgreSQL+Partial+and+Covering+Indexes+in+Laravel+Migrations+and+Query+Plans) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-and-covering-indexes-in-laravel-migrations-and-query-plans) Frequently Asked Questions ---------------------------- 3 questions Q01 Will Laravel's Schema builder create partial or covering indexes natively? Not yet. The Blueprint API has no `partial()` or `include()` methods. You must use `DB::statement()` with raw DDL inside your migration's `up()` and `down()` methods, which is perfectly safe and version-controlled. Q02 When should I prefer a partial index over a composite index? Use a partial index when one column in your composite key is always a fixed value in queries (e.g., `status = 'pending'`). Moving that fixed predicate into the index `WHERE` clause shrinks the index significantly and can make the difference between an index scan and a sequential scan on large tables. Q03 Does INCLUDE affect write performance? Yes, but less than adding columns to the index key. INCLUDE columns are stored only in leaf pages, not in internal B-tree nodes, so the structural overhead is lower. For write-heavy tables, benchmark with realistic insert/update loads before committing. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Domain-Driven Design in Laravel: Value Objects, DTOs, and Actions Without Bloat](https://cdn.msaied.com/377/b5c2aef77aed51ee8694f377085af424.png) laravel ddd architecture ### Domain-Driven Design in Laravel: Value Objects, DTOs, and Actions Without Bloat Learn how to model domain concepts with value objects, DTOs, and single-action classes in Laravel — keeping yo... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 6 Jul 2026 4 min read Read ](https://msaied.com/articles/domain-driven-design-in-laravel-value-objects-dtos-and-actions-without-bloat) [ ![What's Missing from Your PHP Development Environment: Meet DDLess](https://cdn.msaied.com/379/baa8990d4c7b46d18498d69d68f9b6d2.png) DDLess PHP Debugging Laravel Tools ### What's Missing from Your PHP Development Environment: Meet DDLess DDLess is a PHP development workbench that brings step debugging, an in-breakpoint playground, and an interact... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 6 Jul 2026 3 min read Read ](https://msaied.com/articles/whats-missing-from-your-php-development-environment-meet-ddless) [ ![CQRS in Laravel Without a Framework: Commands, Handlers, and Query Objects](https://cdn.msaied.com/376/bec9da4b7a7ddeee26dac3df6f5d6c44.png) laravel cqrs architecture ### CQRS in Laravel Without a Framework: Commands, Handlers, and Query Objects Skip the heavy CQRS libraries. Learn how to implement commands, command handlers, and query objects in plain L... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 6 Jul 2026 3 min read Read ](https://msaied.com/articles/cqrs-in-laravel-without-a-framework-commands-handlers-and-query-objects) [ ![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)