Partial Indexes: Less is More
One of the most powerful yet underused features in Postgres is the Partial Index. Most databases index every single row in a table. But if you only query a fraction of your data, why pay the cost of indexing the rest?
A partial index includes a WHERE clause in its definition. It only contains entries for rows that satisfy the condition.
1. Why Use Partial Indexes?
1. Reduced Storage Size
If you have a table with 100 million rows, but only 1 million are “active” (e.g., uncompleted orders), a partial index on the active rows will be 100x smaller than a full index. This means:
- It fits entirely in RAM.
- It is faster to scan.
- It is faster to back up.
2. Faster Writes
When you insert a row that doesn’t satisfy the index condition, Postgres does not need to update the partial index. This reduces the overhead of INSERT and UPDATE operations significantly.
3. Unique Constraints on Subsets
You can enforce uniqueness only on a subset of rows. For example, allowing multiple “cancelled” reservations for a room, but only one “active” reservation.
2. Common Use Cases
Scenario A: Soft Deletes
Many applications use a deleted_at column to “soft delete” records. Queries almost always filter for deleted_at IS NULL.
Bad Approach:
Index email fully. The index contains millions of deleted users.
Good Approach:
CREATE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
This index is smaller and faster for the queries that actually matter.
Scenario B: Queue Tables
In a job queue table, processed jobs pile up. You only care about jobs with status = 'queued'.
CREATE INDEX idx_jobs_priority
ON jobs (priority)
WHERE status = 'queued';
This keeps the index tiny even if the table grows to billions of historical rows.
Scenario C: Uniqueness with Conditions
Imagine a reservations table. A room can have many cancelled bookings, but only one confirmed booking per date.
CREATE UNIQUE INDEX idx_unique_booking
ON reservations (room_id, date)
WHERE status = 'confirmed';
This allows:
- Row 1: Room 101, Jan 1, Cancelled (OK)
- Row 2: Room 101, Jan 1, Cancelled (OK)
- Row 3: Room 101, Jan 1, Confirmed (OK)
- Row 4: Room 101, Jan 1, Confirmed (FAIL - Duplicate!)
3. Important Caveats
Query Planner Matching
For the query planner to use a partial index, the query’s WHERE clause must mathematically imply the index’s WHERE clause.
Index: WHERE status = 'active'
- Query 1:
SELECT * FROM users WHERE status = 'active' AND email = '...'(✅ Matches) - Query 2:
SELECT * FROM users WHERE email = '...'(❌ No match - might include inactive users) - Query 3:
SELECT * FROM users WHERE status = 'active'(✅ Matches)
Parameterized Queries
Be careful with ORMs. If you use a parameter for the status:
SELECT * FROM users WHERE status = ?
The planner might not use the partial index because it doesn’t know at plan time that the parameter will be 'active'.
[!TIP] Pro Tip: Partial Index Statistics Postgres maintains separate statistics for the partial index predicate. This helps the query planner estimate row counts accurately for queries that match the
WHEREclause, even if the column distribution is skewed in the overall table.
4. Partial Indexes: Less is More
[!NOTE] This module explores the core principles of Partial Indexes, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.