Course contentsShow
← Software Engineering
Lesson 876 of 1,93327. Database Design in PracticeFree lesson

Partial and Filtered Indexes

Creating indexes on subsets of data using WHERE conditions to reduce size and improve targeted query performance.

Partial and Filtered Indexes

You'll learn how to create indexes on only a subset of table rows using WHERE conditions, reducing index size and improving performance for targeted queries.


The Core Idea

A partial index (also called a filtered index) is an index that covers only rows matching a specific condition, rather than every row in the table. Instead of indexing the entire table, you define a WHERE clause when creating the index, so only relevant rows are included.

Think of it like creating a specialized phone directory that only lists businesses open after 9 PM, rather than every business in the city. If you frequently search for late-night services, this smaller, focused directory gets you answers faster and takes up less shelf space.

Why Use Partial Indexes?

When you have queries that consistently filter on the same condition, a partial index can:

  • Reduce storage: Smaller indexes consume less disk space
  • Speed up writes: Fewer rows mean faster index maintenance during INSERT, UPDATE, and DELETE operations
  • Improve query performance: The database can scan a smaller, more focused index with higher selectivity
  • Lower memory usage: Smaller indexes are easier to cache in memory

Common Use Cases

  • Indexing only active records: WHERE is_active = true
  • Indexing recent data: WHERE created_at > '2024-01-01'
  • Indexing non-null values: WHERE email IS NOT NULL
  • Indexing specific statuses: WHERE order_status = 'pending'

Example Concept

If 95% of your orders are completed and you frequently query pending orders, a partial index on WHERE order_status = 'pending' covers only the 5% that matter, dramatically reducing the index's footprint while perfectly serving your query pattern.

Key Takeaway: Partial indexes let you build smaller, faster indexes by including only the rows your queries actually need, trading generality for targeted performance gains.