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?

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;

Why the full index is the problem

An ordinary index on customer_id faithfully indexes all forty million rows, including the thirty-five million you never query. Seven-eighths of every page you read on the way down the tree is data destined to be discarded. The index is roughly eight times larger than it needs to be, which adds a level or two of B-tree depth, and — more importantly — it competes for shared_buffers against everything else. A 400 MB index that fits comfortably in cache behaves very differently from a 3 GB index that does not, and that difference is usually much larger than the extra tree level.

There is a write-side benefit too. Every insert and update of a soft-deleted row currently maintains index entries nobody will ever read. Under a partial index, updates to already-deleted rows touch no index at all, and the act of soft-deleting a row removes its entry rather than updating it.

The catch: the planner has to prove it can use it

This is the part that catches people out, and it is what an interviewer is usually probing for. Postgres will only use a partial index when it can prove, from the query's WHERE clause alone, that every row the query wants is present in the index. That proof is a fairly literal syntactic implication, not a runtime check.

-- uses the index: predicate matches exactly
SELECT * FROM orders WHERE customer_id = 42 AND deleted_at IS NULL;

-- does NOT use it: the planner cannot prove the parameter is null at plan time
SELECT * FROM orders WHERE customer_id = 42 AND deleted_at = $1;

-- does NOT use it: the OR admits rows outside the index
SELECT * FROM orders WHERE customer_id = 42 AND (deleted_at IS NULL OR archived);

The middle case is the practical trap, because several ORMs and query builders will happily parameterise a null comparison. The predicate must appear as a literal IS NULL in the emitted SQL. In practice this means checking what your ORM actually sends — not what the model code looks like — and pinning the scope to a view or a repository method that guarantees the literal form.

The best reason to reach for it: partial unique indexes

Soft deletes break uniqueness constraints, and the partial index is the clean fix. If email must be unique among live users, an ordinary unique index forbids ever re-registering an address that a deleted account once used:

CREATE UNIQUE INDEX idx_users_email_live
    ON users (email)
 WHERE deleted_at IS NULL;

Now three deleted rows may share an address while at most one live row holds it. This is frequently the requirement that forces the decision, and it is worth leading with in an interview because it is a correctness argument rather than a performance one — and correctness arguments are harder to defer.

What a partial index does not fix

Two things, and saying so unprompted is a strong signal.

It does not shrink the table. Thirty-five million dead rows are still in the heap, occupying pages. Any query that falls back to a sequential scan reads all of them, and VACUUM still has to visit them. If the endpoint that is slow does an aggregate over the whole table, the partial index is irrelevant to it.

It does not help queries that do not carry the predicate. Reporting jobs, admin tooling and anything that legitimately wants deleted rows will use a different index or none at all. The partial index optimises one access pattern very well and leaves the others exactly as they were.

If the table keeps growing this way, the structural answer is eventually to stop keeping dead rows in the hot table — either partition by the deleted flag or a date so the live partition is small, or move genuinely dead records to an archive table on a schedule. A partial index buys time and is often enough for years; it is worth being clear that it is a mitigation, not a change to the growth curve.

When it is not worth it

Selectivity decides. The gain is proportional to how much of the table you are excluding. At thirty-five million deleted out of forty million, the index is one-eighth the size and the argument is overwhelming. Invert the ratio — five million deleted out of forty million — and the index is seven-eighths the size, you have saved very little, and you have accepted a predicate-matching constraint on every query for it. Somewhere around "most rows are live" the trade stops paying, and a candidate who asks for the ratio before answering is doing the right thing.

Verifying it afterwards

Two checks close the loop. EXPLAIN (ANALYZE, BUFFERS) on the real query should show an index scan naming the partial index, and the buffer counts should drop sharply — that number is the cache argument made concrete. Then confirm the index is being used in production rather than in your session:

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
 WHERE relname = 'orders';

An idx_scan of zero a day after deployment means the predicate is not matching, which sends you back to what the ORM is actually emitting. Build the index with CONCURRENTLY so creating it does not take a write lock on a forty-million-row table, and remember that a concurrent build can fail and leave an invalid index behind that needs dropping.

---

© 2026 Preptima. Originally published at preptima.com.