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 ================================================================================ Learn how partial and covering indexes eliminate wasted index space and redundant heap fetches in Laravel apps, with real migration examples and EXPLAIN output that proves the gains. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 13 Sep 2026 · Updated 13 Sep 2026 · 4 min read ShareCopy linkCopied ![Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide](https://cdn.msaied.com/665/ced6904aad758906b6047d70ea25e267.png) 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) 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. - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [laravel](https://www.msaied.com/public/articles?search=laravel) - [performance](https://www.msaied.com/public/articles?search=performance) - [database](https://www.msaied.com/public/articles?search=database) Frequently asked questions --------------------------- 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. 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. 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. ![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 articleFilament v4 at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://www.msaied.com/public/articles/filament-v4-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning) [Next articleJob Batching, Chaining, and Catch Callbacks: Reliable Async Workflows in Laravel](https://www.msaied.com/public/articles/job-batching-chaining-and-catch-callbacks-reliable-async-workflows-in-laravel) 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) ### 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)