In this lesson, you will learn how to shrink your index footprint and accelerate write performance by indexing only a subset of your table rows using WHERE clauses.
The Concept of Partial Indexing
Normally, when you create an index, PostgreSQL builds an entry for every single row in the table. However, many production tables contain large amounts of "dead" or irrelevant data—such as soft-deleted rows, archived logs, or inactive user accounts—that you rarely query.
A Partial Index allows you to attach a WHERE clause to the CREATE INDEX statement. By doing this, you instruct the database to ignore rows that don’t meet your filter criteria. This reduces the index size (often by orders of magnitude), which keeps more of the index in RAM and drastically reduces the I/O cost during updates, as the database doesn't have to touch the index for rows that aren't included.
A Practical Example
Imagine you have a massive orders table, but you frequently query only the pending orders that need immediate fulfillment:
CREATE INDEX idx_pending_orders
ON orders (created_at)
WHERE status = 'pending';
In this case, if your table has 10 million rows but only 5,000 are in the pending state, the index will contain only 5,000 entries instead of 10 million.
When the query planner sees a query like:
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2023-01-01';
It will recognize that idx_pending_orders is perfectly suited for this lookup. Because the index is so small, it can be scanned much faster than a standard index, and the overall maintenance overhead during INSERT or UPDATE operations on "completed" orders is eliminated because those rows simply don't trigger an index update.
When to Use Them
You should consider partial indexes when:
- Data Skew: A small fraction of your data represents the majority of your lookups.
- Storage Constraints: You need to index large columns, but only for a specific status or category.
- Write Bottlenecks: Your table has high insertion rates and you want to avoid the "write penalty" of maintaining massive indexes for data you don't query.
Key Takeaway: Partial indexes allow you to trade index coverage for significantly smaller storage requirements and lower maintenance costs by only indexing the subset of data your application actually needs to find.