PostgreSQL

PostgreSQL Partial Indexes vs Full Indexes for Soft Deleted Rows

4 min read by DebuggedIt

Quick answer

For tables that use soft deletes, a partial index that covers only rows where deleted_at IS NULL is usually smaller and faster than a full index, because most...

For tables that use soft deletes, a partial index that covers only rows where deleted_at IS NULL is usually smaller and faster than a full index, because most queries only care about live rows. A full index is still the better choice when you frequently query deleted rows too. The key is matching the index predicate to the filter your queries actually use.

Why Do Soft Deleted Rows Hurt Index Efficiency?

A soft delete marks a row as removed, typically by setting a deleted_at timestamp, instead of physically deleting it. Over time the table accumulates dead weight: rows that are invisible to the application but still occupy space in every index. A full index on email or tenant_id keeps entries for all of them.

That has three costs. The index is larger than necessary, so fewer pages fit in memory. Writes touch more index data. And unique constraints become awkward, because a deleted user's email can block a new signup with the same email unless the constraint ignores deleted rows.

How Does a Partial Index Work?

A partial index includes only rows that satisfy a WHERE condition declared at creation time. PostgreSQL maintains it only for matching rows, so it is smaller and cheaper to update.

CREATE INDEX idx_orders_customer_live
  ON orders (customer_id, created_at)
  WHERE deleted_at IS NULL;

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

The second example solves the unique email problem. Uniqueness is enforced only among live rows, so a soft deleted account does not block re-registration. This is often the most compelling reason to use a partial index with soft deletes, even before performance enters the picture.

When Will the Planner Use the Partial Index?

The planner can use a partial index only when it can prove that the query's WHERE clause implies the index predicate. A query with WHERE deleted_at IS NULL matches the predicate above. A query that omits the condition cannot use it, because it might need rows the index does not contain.

  • Keep the predicate identical in form to what your queries use, such as deleted_at IS NULL.
  • Be careful with parameterized queries. A predicate that depends on a parameter value may not be provable at plan time, so confirm with EXPLAIN.
  • Views and ORMs can hide the condition, so verify the generated SQL.

Always check the plan with EXPLAIN (ANALYZE, BUFFERS) on realistic data volumes. Behavior with generic and custom plans for prepared statements can differ between versions, so check the PostgreSQL documentation for the version you run.

Partial Index or Full Index: How to Decide

Choose a partial index when the large majority of reads target live rows and deleted rows are a meaningful share of the table. Choose a full index when you regularly query deleted rows, such as in an admin restore screen or audit report, or when the deleted fraction is tiny and the savings would be negligible.

You can also combine them. Keep a small partial index for the hot path and a separate full index only if a less frequent query truly needs it. Every extra index adds write cost, so add the second one only when measurements justify it.

Another option for very large tables is partitioning or archiving deleted rows to a separate table. That removes the problem at its source, at the cost of more application logic.

Frequently Asked Questions

Is a partial index faster than a full index?

For queries that match its predicate, usually yes, because it is smaller and fits better in cache. For queries that do not match the predicate, it cannot be used at all.

Can a partial index enforce uniqueness?

Yes. A unique partial index enforces uniqueness only among rows that satisfy the predicate, which is the standard way to allow reuse of values after a soft delete.

Why is PostgreSQL ignoring my partial index?

The query probably does not include a condition that implies the index predicate, or the planner estimates a sequential scan is cheaper. Compare the query filter with the index definition and inspect EXPLAIN output.

Should I index the deleted_at column itself?

Rarely. A boolean-like filter has low selectivity. Use it as the partial index predicate instead of as an indexed column.

Summary

For soft deleted tables, partial indexes shrink index size, reduce write overhead, and make unique constraints behave sensibly. Match the predicate to your query filters, verify with EXPLAIN, and keep a full index only when you genuinely query deleted rows. Check the official documentation for version-specific planner behavior.

You might also like