What happened
On one of our projects, a main table used soft-delete: we set deleted_at instead of removing the row. Over time more than 2M soft-deleted rows piled up, while every query in the app cared only about the live ones.
The regular index on this table covered every row, including the ones nobody ever asked for, and a query that should have been fast took around 800ms.
Why it happens
A regular index stores an entry for every row in the table, so soft-deleted rows make it larger without adding any value. A bigger index is harder to keep in memory, which means more reads from disk.
A partial index stores only the rows that match the WHERE condition given when it was created, here deleted_at IS NULL. It ends up much smaller and has a good chance of fitting entirely in memory. The catch is that the planner only uses it when the query's WHERE clause guarantees the index condition holds, so the query must include deleted_at IS NULL as well.
What we do now
We created the partial index with CREATE INDEX CONCURRENTLY so writes on the busy table were not blocked, then confirmed with EXPLAIN that the query used the new index. In our case the query time dropped from 800ms to 20ms, about 40x faster. That number depends on our data and workload and is not a guarantee for every table.
The technique suits tables where most queries touch a small slice of rows that you can describe precisely, such as rows that are not deleted or rows with an active status. When a row stops matching the condition, it also drops out of the index.
CREATE INDEX CONCURRENTLY idx_orders_customer_live
ON orders (customer_id)
WHERE deleted_at IS NULL;
-- the query must repeat the condition for the planner to use the index
SELECT * FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;Quick checklist
- 1Find the condition your main queries always filter on, such as deleted_at IS NULL.
- 2Create a partial index with CREATE INDEX CONCURRENTLY and the same WHERE condition.
- 3Check with EXPLAIN that the query uses the new index.
- 4Compare the old and new index sizes, then drop the old one if it is no longer needed.
Rule of thumb
When most queries touch a small, well-defined slice of a table, index just that slice with a partial index.