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 Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ Partial Indexes in Laravel Migrations ](#partial-indexes-in-laravel-migrations) 3. [ Covering Indexes with INCLUDE ](#covering-indexes-with-include) 4. [ Reading the EXPLAIN Output ](#reading-the-explain-output) 5. [ Combining Both Techniques ](#combining-both-techniques) 6. [ Maintenance Considerations ](#maintenance-considerations) 7. [ Takeaways ](#takeaways) ![Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide](https://cdn.msaied.com/665/ced6904aad758906b6047d70ea25e267.png) #postgresql #laravel #performance #database Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide ================================================================================= 13 Sep 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) 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 in Laravel Migrations ](#partial-indexes-in-laravel-migrations) 3. [ 03 Covering Indexes with INCLUDE ](#covering-indexes-with-include) 4. [ 04 Reading the EXPLAIN Output ](#reading-the-explain-output) 5. [ 05 Combining Both Techniques ](#combining-both-techniques) 6. [ 06 Maintenance Considerations ](#maintenance-considerations) 7. [ 07 Takeaways ](#takeaways) Why Generic Indexes Leave Performance on the Table -------------------------------------------------- Most Laravel developers reach for a standard `->index()` call in migrations and move on. That works — until your `orders` table has 20 million rows and 90% of your queries filter on `status = 'pending'`. A full B-tree index on `status` stores every row, but your application only ever queries the 2% that are pending. A **partial index** fixes this by indexing only the rows that match a predicate. Similarly, a query that reads two columns — say `user_id` and `created_at` — still triggers a heap fetch after the index lookup unless those columns are bundled into a **covering index**. PostgreSQL can then satisfy the query entirely from the index (an *Index Only Scan*), skipping the heap entirely. Partial Indexes in Laravel Migrations ------------------------------------- Laravel's `Schema::create` doesn't expose partial index syntax natively, but `DB::statement` inside a migration is clean and version-controlled: ```php public function up(): void { DB::statement( 'CREATE INDEX idx_orders_pending_user ON orders (user_id, created_at DESC) WHERE status = \'pending\'' ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_orders_pending_user'); } ``` Now this Eloquent query hits the partial index directly: ```php Order::where('status', 'pending') ->where('user_id', $userId) ->orderByDesc('created_at') ->get(); ``` Run `EXPLAIN (ANALYZE, BUFFERS)` and you'll see `Index Scan using idx_orders_pending_user` with a tiny `Buffers: shared hit` count instead of a sequential scan. Covering Indexes with INCLUDE ----------------------------- PostgreSQL 11+ supports `INCLUDE` columns — columns stored in the index leaf pages but not part of the B-tree key. This enables Index Only Scans without bloating the key structure: ```php DB::statement( 'CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name, email_verified_at)' ); ``` A query like: ```php User::select('id', 'name', 'email_verified_at') ->where('email', $email) ->first(); ``` ...will now show `Index Only Scan` in EXPLAIN — zero heap pages read. ### Reading the EXPLAIN Output ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT id, name, email_verified_at FROM users WHERE email = 'alice@example.com'; ``` Look for these signals: - **Index Only Scan** — covering index working perfectly. - **Heap Fetches: 0** — visibility map is current; no heap I/O. - **Buffers: shared hit=1** — single buffer, essentially free. If you see `Heap Fetches > 0`, run `VACUUM users;` to update the visibility map. Autovacuum handles this in production, but freshly loaded tables need a manual pass. Combining Both Techniques ------------------------- For a dashboard query that lists a tenant's recent failed jobs: ```php DB::statement( 'CREATE INDEX idx_failed_jobs_tenant_recent ON failed_jobs (tenant_id, failed_at DESC) INCLUDE (uuid, payload) WHERE failed_at > NOW() - INTERVAL \'30 days\'' ); ``` This index is small (only 30 days of data), covers the selected columns, and the planner will use it for any query scoped to `tenant_id` with a recent `failed_at` filter. Maintenance Considerations -------------------------- - Partial indexes are **smaller** and faster to update than full indexes — write overhead is lower. - `INCLUDE` columns add storage to leaf pages but don't affect key comparisons. - Monitor index usage with `pg_stat_user_indexes`; drop indexes where `idx_scan = 0` after a representative period. - Partial index predicates must **exactly match** the query's WHERE clause for the planner to consider them — use `= 'pending'`, not `!= 'processed'`. Takeaways --------- - Use partial indexes when a large fraction of rows are never queried — index only what you access. - Use `INCLUDE` to build covering indexes that eliminate heap fetches for read-heavy queries. - Always verify with `EXPLAIN (ANALYZE, BUFFERS)` — assumptions about planner behaviour are often wrong. - Wrap `DB::statement` index DDL in reversible migrations to keep your schema under version control. - Run `VACUUM` after bulk loads to let Index Only Scans reach zero heap fetches. 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-1&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-1) Frequently Asked Questions ---------------------------- 3 questions Q01 Will a partial index be used if I add extra WHERE conditions beyond the index predicate? Yes, as long as the query's WHERE clause is at least as restrictive as the index predicate. If the index is defined with `WHERE status = 'pending'`, a query filtering `status = 'pending' AND user_id = 5` will still use it. A query that omits the status filter will not. Q02 Do INCLUDE columns participate in ORDER BY or range scans? No. INCLUDE columns are stored only in leaf pages and are invisible to the B-tree comparator. They satisfy SELECT projections to enable Index Only Scans, but they cannot be used for sorting or range filtering. Put columns you filter or sort on in the key; put columns you only SELECT in INCLUDE. Q03 How do I create these indexes in a zero-downtime deployment? Use `CREATE INDEX CONCURRENTLY` inside your migration. Note that `DB::statement` within a transaction will fail with CONCURRENTLY, so wrap the statement in a migration that disables transactions: set `$withinTransaction = false` on the migration class, or run the statement outside the default transaction block. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Filament v4 at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://cdn.msaied.com/664/be327447c5231a3cb27a5df9597890dd.png) filament laravel multi-panel ### Filament v4 at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning Running Filament v4 across multiple panels with distinct auth guards and thousands of rows? This guide covers... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 13 Sep 2026 3 min read Read ](https://msaied.com/articles/filament-v4-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning) [ ![Macros, Mixins, and Custom Collection Methods in Laravel](https://cdn.msaied.com/663/b8e39b17d427358aa43b5c3e8c1be908.png) laravel collections macros ### Macros, Mixins, and Custom Collection Methods in Laravel Learn how to extend Laravel's core classes with macros, mixins, and custom Collection methods — keeping your c... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 13 Sep 2026 4 min read Read ](https://msaied.com/articles/macros-mixins-and-custom-collection-methods-in-laravel-2) [ ![Modular Monolith in Laravel: Enforcing Bounded Contexts with Module Service Providers](https://cdn.msaied.com/662/7f9c800590e5d7c07197293837cf0114.png) laravel architecture modular-monolith ### Modular Monolith in Laravel: Enforcing Bounded Contexts with Module Service Providers Learn how to carve a Laravel application into cohesive bounded contexts using per-module service providers, ex... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 12 Sep 2026 3 min read Read ](https://msaied.com/articles/modular-monolith-in-laravel-enforcing-bounded-contexts-with-module-service-providers) [ ![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)