The PostgreSQL Guide to Partial Indexes on Soft Deletes

If every hot query ignores deleted rows, stop paying to index them.

Soft deletes feel cheap right up until your busiest query starts with WHERE tenant_id = ? AND deleted_at IS NULL and still drags. The instinct is familiar: add one more composite index, maybe (tenant_id, deleted_at, created_at DESC), then move on. The query gets a little better while the write path gets a little worse, and a month later the table has grown again so you are back in the same meeting.

The problem is not that PostgreSQL forgot how to use an index. The problem is that your index is still carrying rows the application has already decided to ignore. A soft-delete table does not get cheaper with age. It gets more expensive to pretend archived rows still belong on the hot path.

PostgreSQL gives you a cleaner option: index only the rows your real workload still cares about, which is what a partial index does. The interesting part is not the syntax but the contract, because partial indexes help only when your query shape keeps making the same promise as the index predicate. Miss that point and the index becomes a very elegant ornament.

The Slowdown Usually Starts With Tombstones, Not With Traffic

Soft deletes are operationally convenient. They preserve history, make accidental recovery possible and keep foreign-key relationships from turning into cleanup projects. Laravel makes them even easier because the framework adds a global scope that filters out deleted rows for normal Eloquent queries.

That convenience creates a quiet cost. Your application keeps acting as if deleted rows are gone while PostgreSQL still stores them, vacuums them and updates indexes that include them. If the table holds invoices, sessions, audit events or any other row that gets archived far more often than it gets restored, the active slice of the data shrinks while the index keeps growing anyway.

You see the symptoms in a predictable order:

The tenant dashboard query starts reading more index pages than it used to.
ORDER BY created_at DESC LIMIT 50 no longer feels instant.
Soft deletes themselves get more expensive because every index entry still needs maintenance.

Nothing about that looks dramatic in code review. That is why it survives so long.

What A Partial Index Actually Changes

PostgreSQL defines a partial index as an index built over a subset of a table, chosen by a predicate. Only rows that satisfy that predicate get index entries. That matters for soft-delete tables because your hot-path predicate is usually embarrassingly consistent: deleted_at IS NULL.

Here is the version I reach for when the common read path is “latest active records for one tenant”:

CREATE INDEX invoices_active_tenant_created_at_idx
ON invoices (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;

This does three useful things at once.

It shrinks the index

Archived rows never enter it.
Fewer pages means less memory pressure and less work during scans.
Soft deletes stop updating this index after the row leaves the active set.

It keeps the read path aligned with the real query

tenant_id narrows the scan first.
created_at DESC lets PostgreSQL satisfy the sort order from the B-tree itself.
The predicate removes rows your application already treats as invisible.

It changes write cost in the right direction

Inserts still touch the index for active rows.
Updates that mark a row deleted remove one index entry instead of maintaining a forever-growing archive footprint.
Restores add the row back, but rarely enough that I still want the index optimized for active rows.

That last detail matters more than people admit. Indexes are not free lookup candy. PostgreSQL documentation is blunt about it: indexes speed reads, but they add overhead to the system as a whole. A partial index works when you can prove the overhead is only worth paying for a subset of rows.

A Partial Index Is A Promise, Not A Shortcut

This is the part people miss.

PostgreSQL will use a partial index only when it can prove that the query’s WHERE clause implies the predicate of the index. The planner does not reward vibes. It does not try to guess that your expression is "basically the same" as deleted_at IS NULL. It matches what it can recognize at planning time.

That means this query shape is a strong fit:

SELECT id, total_cents, created_at
FROM invoices
WHERE tenant_id = $1
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

The tenant value is parameterized. That is fine. The partial-index predicate is still explicit and still stable.

What breaks the contract is hiding or rewriting the predicate:

an admin report that uses withTrashed() and expects the same index
a query that wraps deleted_at in an expression instead of keeping IS NULL
a generic repository method that toggles between active and deleted rows with a runtime condition

PostgreSQL’s own docs call out the sharp edge here: matching happens at planning time, not run time. If the planner cannot prove the predicate, it will not use the index. That is why partial indexes feel magical in one endpoint and absent in another that “looks close enough.”

Close enough is not a database concept.

Laravel Makes This Easy To Miss

Laravel helps and hurts here.

It helps because soft deletes already push your domain model toward a consistent predicate. The framework’s global scope only retrieves non-deleted models by default, so ordinary Eloquent reads naturally align with deleted_at IS NULL.

It hurts because that consistency is not universal. Teams drift into three query styles:

standard Eloquent reads, where the scope is active
query builder or reporting queries, where deleted_at must be stated manually
maintenance or admin flows, where withTrashed() or onlyTrashed() changes the access pattern completely

The index only helps the first two, and only if the second style keeps the predicate visible.

This is the query-builder shape I want for a dashboard or list endpoint that should benefit from the partial index:

<?php

use Illuminate\Support\Facades\DB;
$invoices = DB::table('invoices')
    ->where('tenant_id', $tenantId)
    ->whereNull('deleted_at')
    ->orderBy('created_at', 'desc')
    ->limit(50)
    ->get(['id', 'total_cents', 'created_at']);

The nice thing about writing it this plainly is that your SQL intent stays obvious. The planner sees the same promise your index was built for. So does the next engineer who has to debug it.

When I see teams struggle with partial indexes in Laravel, it is usually not because PostgreSQL is tricky. It is because the application stopped being honest about which rows belong on the hot path.

The Real Trade-Off Is Not Partial Versus Full

The trade-off is narrower than that. You are deciding whether your hot path is stable enough to deserve its own index contract.

Here is the comparison I actually use.

Regular composite index

Works for active and deleted lookups.
Keeps growing with every row, useful or not.
Charges every write for rows the application may never read again.

Partial index

Ignores archived rows completely.
Stays smaller when active rows remain a minority.
Fails fast when the query stops matching the predicate.

The failure mode is important. A regular index degrades gradually and expensively, while a partial index degrades abruptly because the planner walks away once the query shape changes. I prefer the second failure mode because it is easier to see and much easier to fix.

The Quiet Bonus: Active-Only Uniqueness

Partial indexes are not only about reads. PostgreSQL also lets you enforce uniqueness over a subset of rows, which is perfect for soft-delete tables with business keys that must be unique only while the record is active.

That looks like this:

CREATE UNIQUE INDEX customers_active_email_unique
ON customers (tenant_id, email)
WHERE deleted_at IS NULL;

This is useful when an archived customer, project slug or external reference should not block a new active row forever. Two active rows still cannot share the same value. Deleted rows stop participating in the rule.

There is a catch, and it is a healthy one: your restore flow now needs a decision. If someone restores an old row whose email or slug has been claimed by a newer active record, PostgreSQL will reject the restore until the conflict is resolved. Good. That is a business rule collision, not a database inconvenience.

When Partial Indexes Are The Wrong Fix

They are not a default. PostgreSQL documentation explicitly warns that a partial index can be counterproductive and should not be used as a substitute for partitioning.

I skip partial indexes in four common cases.

Most rows are still active.
If deleted_at IS NULL matches most of the table, the index is not selective enough to justify the extra complexity. Build a regular multicolumn index or rethink the access path.
Deleted rows are queried almost as often as active rows.
Admin search, audit review and support tooling often need both sets. That is not one access pattern. It is two. Pretending otherwise usually produces a mediocre index for both.
The real bottleneck is data shape, not index shape.
If one tenant owns a huge slice of the table or time-based retention keeps piling onto a single access path, partitioning or archival strategy may matter more than predicate-based indexing.
The query cannot keep a stable predicate.
If the application genuinely needs runtime-switchable semantics around deleted rows, the partial-index contract is too brittle for the workload.

This is less elegant than it sounds, but it saves time: I would rather keep one boring index than carry a clever partial index nobody remembers how to query.

The Decision Framework I Use

This is the section I come back to later, and the one I wish more teams wrote down before opening EXPLAIN.

Use a partial index on a soft-delete table when all of these are true:

The hot query path always excludes deleted rows.
Active rows are a meaningfully smaller slice than total rows.
The query needs a stable filter plus a stable sort, such as tenant_id plus created_at DESC.
The application can keep the predicate visible in SQL instead of hiding it behind runtime branching.

Use a regular composite index when one of these is true:

Active rows still dominate the table.
Read paths frequently mix active and deleted rows.
You need broader reuse across several query shapes.

Skip both and rethink the data layout when one of these is true:

Tenant size is wildly uneven and one tenant now dictates the plan.
Historical rows belong in colder storage or a partitioned layout.
The query is slow because it returns too much data, not because PostgreSQL cannot find the first rows quickly.

That framework is more valuable than the exact SQL. Index syntax is easy to copy. Knowing when not to copy it is the whole job.

A Good Partial Index Should Feel Boring In Production

Once the contract is right, the index stops being something you think about every week. Your dashboard query reads from a smaller structure. Your soft deletes stop paying index rent forever. Your reporting queries either match the contract or they do not, which is much healthier than guessing.

The bigger lesson is not “partial indexes are fast.” The bigger lesson is that PostgreSQL rewards honesty. If your application says deleted rows are cold data, your index should stop pretending otherwise.