hardConceptScenarioDesignMidSeniorStaff
A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?
A partial index only contains rows matching its predicate, so indexing WHERE deleted_at IS NULL stores five million entries instead of forty million - smaller, shallower, and far likelier to stay cached. The catch is the planner only uses it when it can prove the query predicate implies the index predicate, and it does nothing about dead rows still in the heap.
5 min readUpdated 2026-08-16Target archetype: Big Tech, Enterprise Captive, Fintech
Answer
Short answer
Yes, and this is close to the textbook case for one. A partial index stores only the rows satisfying its predicate, so an index defined WHERE deleted_at IS NULL holds five million entries rather than forty million. It is smaller on disk, shallower to descend, cheaper to maintain on write, and — the part that usually dominates — far more likely to stay resident in cache.
CREATE INDEX CONCURRENTLY idx_orders_active_customer
ON orders (customer_id)
WHERE deleted_at IS NULL;
Was this answer helpful?
Likely follow-ups
- Your ORM emits WHERE deleted_at = $1 with null bound as the parameter. Does the partial index get used?
- You need email to be unique, but only among non-deleted users. How do you enforce that?
- The table is still slow on queries that do not use the index at all. What is going on?
- At what ratio of live to deleted rows does a partial index stop being worth it?
- When would you argue for partitioning or a genuine archive table instead?
Related questions
- A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.hardAlso on indexing and query-optimisation3 min
- Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?hardAlso on indexing and postgresql6 min
- This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?hardAlso on indexing and postgresql6 min
- Walk me through how you read a PostgreSQL execution plan.mediumAlso on query-optimisation and postgresql5 min