Optimizing Soft-Deletes in Multi-Tenant PostgreSQL with Partial Indexes

The Silent Performance Killer in Your Multi-Tenant SaaS

As a senior engineer building a multi-tenant SaaS application, you've likely encountered this scenario: a core table—projects, documents, invoices—grows to millions or tens of millions of rows. You've implemented soft-deletes (deleted_at TIMESTAMP NULL) as a standard safety and auditing measure. Your queries almost always filter by the current user's tenant_id and for non-deleted records (WHERE deleted_at IS NULL).

Initially, a standard composite index on (tenant_id, deleted_at) seems sufficient. But as the proportion of soft-deleted records climbs from 10% to 50% and beyond, you notice a creeping latency in your application's most critical endpoints. Query times that were once 5ms are now 50ms, then 150ms. The database CPU starts to spike. Your seemingly well-indexed queries are becoming a production bottleneck.

This isn't a failure of basic indexing principles. It's a failure to account for data distribution and query patterns at scale. The standard composite B-Tree index becomes bloated with entries pointing to "dead" rows that your application will almost never query. Every read operation on this index has to sift through a growing volume of irrelevant data, leading to increased I/O, larger memory footprints for cached index blocks, and ultimately, slower queries.

This article is a deep dive into solving this specific, high-impact problem using one of PostgreSQL's most powerful and underutilized features: partial indexes. We will move beyond textbook examples to dissect production-grade scenarios, analyze query plans, benchmark the trade-offs, and equip you with the patterns to build highly performant data access layers in your multi-tenant applications.

Setting the Stage: A Realistic Problem Schema

Let's model a common scenario: a project management tool. We have a tasks table that stores tasks for various tenants. Over the life of the product, millions of tasks are created and completed (soft-deleted).

Here's our table structure:

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL
);

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'in_progress', 'completed', 'archived')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

To simulate a production environment, we need a significant amount of data with a realistic distribution. We'll create 1,000 tenants and populate the tasks table with 10 million rows. Critically, we'll simulate a mature application where 70% of the tasks have been soft-deleted.

-- Generate sample data
INSERT INTO tenants (name) SELECT 'Tenant ' || n FROM generate_series(1, 1000) n;

-- Use a transaction for speed
BEGIN;
-- Disable triggers and constraints temporarily if needed for bulk loads in a real scenario
-- SET session_replication_role = 'replica';

INSERT INTO tasks (tenant_id, title, status, deleted_at)
SELECT
    t.id,
    'Task ' || (random() * 1000000)::int,
    (ARRAY['pending', 'in_progress', 'completed', 'archived'])[(random() * 4 + 1)::int],
    -- 70% of records will be soft-deleted
    CASE WHEN random() < 0.7 THEN
        now() - (random() * 365 || ' days')::interval
    ELSE
        NULL
    END
FROM
    generate_series(1, 10000000) i
-- Cross join to get a random tenant_id for each task
CROSS JOIN LATERAL (
    SELECT id FROM tenants ORDER BY random() LIMIT 1
) t;

-- Re-enable constraints if disabled
-- SET session_replication_role = 'origin';
COMMIT;

-- Ensure statistics are up to date
ANALYZE tasks;

Our dataset now mirrors a real-world problem: 10 million tasks, but only 3 million are "active" (deleted_at IS NULL). The vast majority of our data is historical.

The Common Query and The Naive Index

The most frequent query in our application will be fetching active tasks for a specific tenant:

-- The bread-and-butter query of our application
SELECT id, title, status, created_at
FROM tasks
WHERE tenant_id = 'some-tenant-uuid' -- Replace with an actual UUID from your tenants table
AND deleted_at IS NULL;

A junior developer, or even a seasoned one in a hurry, might create a simple composite index they believe covers this query:

CREATE INDEX idx_tasks_tenant_id_deleted_at ON tasks (tenant_id, deleted_at);

Let's analyze the performance. First, find a tenant UUID to test with.

-- Get a sample tenant_id
SELECT id FROM tenants LIMIT 1;
-- Let's say we get: 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'

Now, let's run EXPLAIN ANALYZE on our query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL;

The output will look something like this (your numbers will vary):

Index Scan using idx_tasks_tenant_id_deleted_at on tasks  (cost=0.56..550.48 rows=2985 width=61) (actual time=0.045..5.832 rows=3010 loops=1)
  Index Cond: ((tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d') AND (deleted_at IS NULL))
  Buffers: shared hit=3045
Planning Time: 0.150 ms
Execution Time: 5.981 ms

An execution time of ~6ms might not seem terrible. But look closer at the Buffers: shared hit=3045 line. This means PostgreSQL had to load over 3,000 8KB blocks from memory (or disk, which would be much worse) to satisfy this query. Why? Because the index idx_tasks_tenant_id_deleted_at contains an entry for every single row in the tasks table—all 10 million of them. When the planner scans the index for our specific tenant, it has to traverse all entries for that tenant, including the 7,000 deleted ones, just to find the 3,000 active ones.

This inefficiency is compounded by scale. As the table grows, or the percentage of deleted rows increases, the index becomes more bloated, and the number of buffer hits will climb, increasing latency and memory pressure.

Let's check the size of this index:

SELECT pg_size_pretty(pg_relation_size('idx_tasks_tenant_id_deleted_at'));
-- Result: ~250 MB

That's 250 MB of index that is 70% useless for our most common query path. This is the problem we need to solve.

The Solution: A Hyper-Focused Partial Index

A partial index is an index built on a subset of a table's rows, defined by a WHERE clause. This is a perfect match for our problem. We are almost exclusively interested in rows WHERE deleted_at IS NULL.

Let's create an index that contains only the active tasks.

-- First, drop the old index to compare fairly
DROP INDEX idx_tasks_tenant_id_deleted_at;

-- Create the partial index
CREATE INDEX idx_tasks_active_by_tenant ON tasks (tenant_id) WHERE deleted_at IS NULL;

This index is fundamentally different. It completely ignores the 7 million soft-deleted rows. It is a small, dense, and highly efficient map of tenant_id to active task locations.

Let's measure its size:

SELECT pg_size_pretty(pg_relation_size('idx_tasks_active_by_tenant'));
-- Result: ~75 MB

The partial index is less than a third of the size of the full composite index. This means it's more likely to fit entirely in RAM, leading to faster lookups and less pressure on the cache.

Now, for the moment of truth. Let's re-run our query analysis:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL;

This time, the query plan is dramatically better:

Index Scan using idx_tasks_active_by_tenant on tasks  (cost=0.43..165.23 rows=3010 width=61) (actual time=0.035..1.245 rows=3010 loops=1)
  Index Cond: (tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d')
  Buffers: shared hit=915
Planning Time: 0.120 ms
Execution Time: 1.398 ms

The results are staggering:

We achieved a 4.3x performance improvement on our most critical query path. The number of buffer hits dropped by over 3x. This is not a micro-optimization; this is a fundamental architectural improvement that will scale as your data grows.

The query planner is smart. It sees that the WHERE clause of our query (... AND deleted_at IS NULL) is a perfect superset of the partial index's WHERE clause. It correctly chooses our new, smaller index and performs an Index Scan using just the tenant_id condition, because every row in the index is already guaranteed to satisfy deleted_at IS NULL.

Advanced Scenario 1: Filtering by Status

Life is rarely as simple as one query. What happens when the product team wants to add tabs to the UI for "Pending" and "In Progress" tasks? Our query now becomes more specific:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL
AND status = 'pending';

With our current partial index, the plan looks like this:

Index Scan using idx_tasks_active_by_tenant on tasks  (cost=0.43..175.45 rows=752 width=45) (actual time=0.041..1.850 rows=750 loops=1)
  Index Cond: (tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d')
  Filter: ((status)::text = 'pending'::text)
  Rows Removed by Filter: 2260
  Buffers: shared hit=915
Planning Time: 0.133 ms
Execution Time: 1.995 ms

The performance is still good, but notice the Filter line. PostgreSQL uses our partial index to find all 3,010 active tasks for the tenant, then it has to visit the table heap for each of those rows to check the status. It ends up discarding 2,260 rows (Rows Removed by Filter: 2260). This is wasted work.

We can do better. If this is a hot query path, we can create an even more specific partial index.

CREATE INDEX idx_tasks_active_pending_by_tenant ON tasks (tenant_id) 
WHERE deleted_at IS NULL AND status = 'pending';

CREATE INDEX idx_tasks_active_inprogress_by_tenant ON tasks (tenant_id) 
WHERE deleted_at IS NULL AND status = 'in_progress';

Now, let's re-run the analysis for pending tasks:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL
AND status = 'pending';

Index Scan using idx_tasks_active_pending_by_tenant on tasks  (cost=0.42..45.12 rows=750 width=45) (actual time=0.031..0.512 rows=750 loops=1)
  Index Cond: (tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d')
  Buffers: shared hit=230
Planning Time: 0.180 ms
Execution Time: 0.615 ms

Another huge win. We've gone from ~2ms down to ~0.6ms, and the buffer hits dropped from 915 to 230. We are now operating at peak efficiency for this specific query.

This pattern is incredibly powerful for dashboard queries or any UI that filters on low-cardinality status or type fields. The trade-off is the overhead of maintaining multiple indexes, which we will discuss later.

Advanced Scenario 2: Handling `IN` Clauses and Multiple Statuses

What if the requirement is to show a combined view of all "workable" tasks, i.e., status IN ('pending', 'in_progress')?

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL
AND status IN ('pending', 'in_progress');

PostgreSQL is clever, but it can't magically combine our two separate partial indexes for pending and in_progress. It will likely fall back to our first partial index (idx_tasks_active_by_tenant) and apply a filter, just like before.

Index Scan using idx_tasks_active_by_tenant on tasks  (cost=0.43..185.67 rows=1505 width=61) (actual time=0.045..2.112 rows=1502 loops=1)
  Index Cond: (tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d')
  Filter: ((status)::text = ANY ('{pending,in_progress}'::text[]))
  Rows Removed by Filter: 1508
  Buffers: shared hit=915
Planning Time: 0.145 ms
Execution Time: 2.298 ms

To optimize this, we create a partial index whose WHERE clause exactly matches the query's WHERE clause.

CREATE INDEX idx_tasks_active_workable_by_tenant ON tasks (tenant_id)
WHERE deleted_at IS NULL AND status IN ('pending', 'in_progress');

Analyzing the query again:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, created_at
FROM tasks
WHERE tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d'
AND deleted_at IS NULL
AND status IN ('pending', 'in_progress');

Index Scan using idx_tasks_active_workable_by_tenant on tasks  (cost=0.42..85.34 rows=1502 width=61) (actual time=0.038..0.899 rows=1502 loops=1)
  Index Cond: (tenant_id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d')
  Buffers: shared hit=455
Planning Time: 0.211 ms
Execution Time: 1.055 ms

We've more than halved the execution time and buffer hits again. This demonstrates the core principle: for your most critical, high-frequency queries, create partial indexes that are surgically precise.

Production Considerations and Edge Cases

Partial indexes are not a silver bullet. They are a precision tool that comes with trade-offs a senior engineer must understand.

1. The Write Performance Penalty

Every index you add incurs a penalty on INSERT, UPDATE, and DELETE operations. The database must do the work of updating each index. The good news is that partial indexes have a lower penalty than full indexes, especially for updates that disqualify a row from the index.

Consider a soft-delete operation:

UPDATE tasks
SET deleted_at = now()
WHERE id = 'some-active-task-uuid';

Conversely, an INSERT of a new, active task requires an insert into our partial index, while an INSERT of a pre-deleted (e.g., archived) task would not.

Let's run a quick pgbench test to get a rough idea. We'll test the INSERT performance on our table with no indexes, with the bloated composite index, and with our set of targeted partial indexes.

Benchmark Script (simplified pgbench custom script):

-- insert_script.sql
\set tenant_id_val `(SELECT id FROM tenants ORDER BY random() LIMIT 1)`
INSERT INTO tasks (tenant_id, title, status) VALUES (:'tenant_id_val', 'pgbench task', 'pending');

Execution:

# Baseline (no indexes other than PK)
pgbench -U your_user -d your_db -f insert_script.sql -c 10 -j 2 -t 1000

# With full composite index
# CREATE INDEX idx_tasks_tenant_id_deleted_at ON tasks (tenant_id, deleted_at);
pgbench -U your_user -d your_db -f insert_script.sql -c 10 -j 2 -t 1000

# With our three partial indexes
# DROP INDEX ...; CREATE INDEX ...; (as defined above)
pgbench -U your_user -d your_db -f insert_script.sql -c 10 -j 2 -t 1000

Hypothetical Results:

These numbers illustrate that while all indexes add overhead, the cumulative size and complexity matter. Our set of smaller, targeted partial indexes provides a better balance between read and write performance than a single, bloated index.

2. Query Planner Gotchas

The PostgreSQL query planner is excellent, but it relies on statistics. If your table statistics are stale, it might make a suboptimal choice. Always run ANALYZE after large data changes.

Furthermore, a partial index might not be used if the planner determines a Sequential Scan is cheaper. This can happen if your query's WHERE clause matches a large percentage of the rows for a given tenant. For example, if a tenant has only 100 total tasks and 95 of them are active, the planner might decide it's faster to just scan the whole table for that tenant rather than doing an index scan followed by fetching each row from the heap. This is usually the correct decision and not a cause for alarm.

3. Index Maintenance and Bloat

When you UPDATE a row in a way that doesn't change its inclusion in a partial index (e.g., changing the title of an active task), the old tuple is marked for deletion and a new one is created. This can still lead to index bloat over time, though it's less severe than with the soft-delete UPDATEs on a full index. Regular VACUUM and maintenance are still essential.

The key benefit is that the UPDATE which performs the soft-delete—often a very common operation—actively removes an entry from the partial index, which is a fundamentally cleaner operation that helps mitigate bloat from that specific vector.

Conclusion: A Shift in Indexing Mindset

The performance degradation from soft-deletes in multi-tenant systems is a common but entirely solvable problem. The solution requires a shift from a "coverage" mindset of indexing (creating wide indexes to cover many query columns) to a "precision" mindset.

Key Takeaways for Senior Engineers:

By adopting this advanced indexing strategy, you can build systems that remain fast and responsive even as your data scales to hundreds of millions of rows, ensuring the performance of your application's core features doesn't degrade over time.