PostgreSQL Partial Indexes for Soft-Delete in Multi-Tenant Systems

The Silent Performance Killer in Multi-Tenant Architectures

In any mature multi-tenant SaaS application, two patterns are nearly ubiquitous: data isolation via a tenant_id column and data retention via soft-deletion (e.g., a deleted_at timestamp). While fundamental, their combination creates a significant performance challenge at scale. The vast majority of application queries operate on a small, specific subset of the data: records belonging to the current tenant that are not deleted.

A standard composite B-tree index, such as (tenant_id, created_at), is the conventional first step. However, as a table like invoices grows to hundreds of millions of rows, this index becomes bloated with entries for deleted records. Since soft-deleted records are rarely, if ever, queried by the application's primary workflows, every byte they occupy in an active-use index is wasted space. This bloat increases index height, consumes valuable memory (buffer cache), and slows down DML operations (INSERT, UPDATE).

Let's model a realistic scenario. Consider an invoices table for a large SaaS product:

CREATE TABLE invoices (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    customer_id INT NOT NULL,
    status TEXT NOT NULL, -- e.g., 'draft', 'pending', 'paid', 'void'
    amount_cents INT NOT NULL,
    invoice_date DATE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Simulate a large dataset: 10 million invoices across 5,000 tenants
INSERT INTO invoices (tenant_id, customer_id, status, amount_cents, invoice_date, deleted_at)
SELECT
    (random() * 4999) + 1, -- tenant_id
    (random() * 100000) + 1, -- customer_id
    (ARRAY['draft', 'pending', 'paid', 'void'])[(random() * 3 + 1)::int],
    (random() * 50000 + 100)::int, -- amount_cents
    NOW() - (random() * 365)::int * '1 day'::interval, -- invoice_date
    CASE WHEN random() > 0.9 THEN NOW() - (random() * 30)::int * '1 day'::interval ELSE NULL END; -- 10% soft-deleted

A common query is to fetch all active, pending invoices for a specific tenant:

EXPLAIN ANALYZE
SELECT id, amount_cents, invoice_date
FROM invoices
WHERE tenant_id = 1234
  AND status = 'pending'
  AND deleted_at IS NULL;

Without any index, this is a disastrous parallel sequential scan. Let's add the standard composite index:

CREATE INDEX idx_invoices_tenant_status ON invoices (tenant_id, status);

Now, the query plan improves:

-- Execution plan with standard composite index
Bitmap Heap Scan on invoices  (cost=51.53..4384.45 rows=125 width=20) (actual time=0.215..0.450 rows=105 loops=1)
  Recheck Cond: (tenant_id = 1234 AND status = 'pending'::text)
  Filter: (deleted_at IS NULL)
  Heap Blocks: exact=102
  ->  Bitmap Index Scan on idx_invoices_tenant_status  (cost=0.00..51.50 rows=250 width=0) (actual time=0.198..0.199 rows=117 loops=1)
        Index Cond: (tenant_id = 1234 AND status = 'pending'::text)
Planning Time: 0.158 ms
Execution Time: 0.482 ms

This is much better, but notice the Filter: (deleted_at IS NULL) line. The database uses the index to find all invoices for tenant_id = 1234 with status = 'pending', including the soft-deleted ones. It then fetches these rows from the table heap and applies a secondary filter to discard the deleted records. When 90-95% of your records are not deleted, this is acceptable. But as the proportion of deleted records grows, or for queries that don't include status, the inefficiency becomes pronounced. The index itself is larger than it needs to be, consuming memory and disk I/O for data that is almost never needed.

The Surgical Solution: The Partial Index

A partial index is an index built over a subset of a table's rows, defined by a WHERE clause. This is precisely what we need. We can create an index that only contains entries for active records.

Pattern 1: The Primary Active-Record Index

Let's create an index specifically for the most common query pattern: active records for a given tenant, ordered by date.

-- Drop the old index
DROP INDEX idx_invoices_tenant_status;

-- Create a partial index for active records
CREATE INDEX idx_invoices_active_tenant_date ON invoices (tenant_id, invoice_date DESC)
WHERE deleted_at IS NULL;

This index is a game-changer. Let's analyze its properties:

    -- Compare index sizes
    SELECT pg_size_pretty(pg_relation_size('idx_invoices_tenant_status')); -- Hypothetical full index
    SELECT pg_size_pretty(pg_relation_size('idx_invoices_active_tenant_date')); -- Our partial index

Let's re-run a query that fits this index perfectly:

EXPLAIN ANALYZE
SELECT id, amount_cents, invoice_date
FROM invoices
WHERE tenant_id = 1234
  AND deleted_at IS NULL
ORDER BY invoice_date DESC
LIMIT 100;

-- Execution plan with the partial index
Limit  (cost=0.43..13.88 rows=100 width=20) (actual time=0.035..0.152 rows=100 loops=1)
  ->  Index Scan using idx_invoices_active_tenant_date on invoices  (cost=0.43..2495.32 rows=18524 width=20) (actual time=0.034..0.138 rows=100 loops=1)
        Index Cond: (tenant_id = 1234)
Planning Time: 0.189 ms
Execution Time: 0.175 ms

Look at the difference. There is no Filter step. It's a pure Index Scan. The planner goes directly to the small, dense index of active records for tenant_id = 1234 and pulls the first 100 rows in the correct order. This is maximally efficient.

Advanced Scenarios and Production Patterns

The simple active-record index is powerful, but real-world applications have more complex query needs. This is where combining partial indexes becomes a strategic imperative.

Pattern 2: Handling Multiple Statuses with High Cardinality

Our first partial index on (tenant_id, invoice_date) is great for fetching all active invoices. But what about our original query for a specific status?

EXPLAIN ANALYZE
SELECT id, amount_cents, invoice_date
FROM invoices
WHERE tenant_id = 1234
  AND status = 'pending'
  AND deleted_at IS NULL;

With only idx_invoices_active_tenant_date, the plan would look like this:

Bitmap Heap Scan on invoices  (cost=389.34..2884.66 rows=463 width=20) (actual time=0.850..2.150 rows=455 loops=1)
  Recheck Cond: (tenant_id = 1234)
  Filter: (deleted_at IS NULL AND status = 'pending'::text)
  Heap Blocks: exact=445
  ->  Bitmap Index Scan on idx_invoices_active_tenant_date  (cost=0.00..389.23 rows=18524 width=0) (actual time=0.650..0.650 rows=1800 loops=1)
        Index Cond: (tenant_id = 1234)
Planning Time: 0.120 ms
Execution Time: 2.201 ms

It uses the partial index to find all 1800 active invoices for the tenant, then visits the table to filter them down to the 455 that have status = 'pending'. This is inefficient. We are forcing a table heap scan for the status filter.

Solution A: Multiple, Hyper-Specific Partial Indexes

If your application has a few, very high-frequency queries for specific statuses (e.g., a dashboard that only shows 'pending' and 'overdue' invoices), you can create indexes for each.

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

CREATE INDEX idx_invoices_active_paid_tenant ON invoices (tenant_id)
WHERE deleted_at IS NULL AND status = 'paid';

Now, the query for pending invoices is perfectly optimized:

EXPLAIN ANALYZE
SELECT id, amount_cents
FROM invoices
WHERE tenant_id = 1234
  AND status = 'pending'
  AND deleted_at IS NULL;

-- Plan using the hyper-specific partial index
Index Scan using idx_invoices_active_pending_tenant on invoices (cost=0.42..18.59 rows=463 width=16) (actual time=0.025..0.150 rows=455 loops=1)
  Index Cond: (tenant_id = 1234)
Planning Time: 0.210 ms
Execution Time: 0.180 ms

* Pros: Maximum performance for specific, known query patterns. The indexes are incredibly small and fast.

* Cons: Proliferation of indexes. This doesn't scale if you have many statuses or need to query for combinations. Each new index adds maintenance overhead.

Solution B: Partial Index with an IN Clause

If you frequently query a small set of statuses together, you can embed this logic in the index itself.

-- For a dashboard showing actionable invoices
CREATE INDEX idx_invoices_active_actionable_tenant ON invoices (tenant_id, status)
WHERE deleted_at IS NULL AND status IN ('pending', 'draft');

This single index can now efficiently serve queries for either 'pending', 'draft', or both for active records.

EXPLAIN ANALYZE
SELECT id
FROM invoices
WHERE tenant_id = 1234
  AND status IN ('pending', 'draft')
  AND deleted_at IS NULL;

This approach provides a balance between the specificity of multiple indexes and the generality of a single, broader index.

Pattern 3: The Complementary Archive Index

Partial indexes for deleted_at IS NULL are fantastic for the application's hot path. But what about the cold path? An admin, support engineer, or data analyst might need to find a deleted record.

-- This query is now an orphan, with no suitable index
SELECT id, deleted_at
FROM invoices
WHERE id = 5432101 AND tenant_id = 1234;

If invoice 5432101 is deleted, none of our deleted_at IS NULL indexes will contain it. The database will likely fall back to a scan on the primary key, but queries on other columns for deleted data will be painfully slow.

The solution is a complementary partial index for the archived data.

CREATE INDEX idx_invoices_deleted_tenant_id ON invoices (tenant_id, id)
WHERE deleted_at IS NOT NULL;

This index is the mirror image of our active indexes. It's typically very small (e.g., 10% of the table size in our example) but provides lightning-fast access to the entire history of soft-deleted records for a tenant.

Now, an internal tool or support dashboard can efficiently query for historical data without impacting the performance of the main application.

Edge Cases and Production Gotchas

While powerful, partial indexes require careful implementation. Here are critical considerations for senior engineers.

Gotcha 1: Prepared Statements and Parameter Type Mismatch

This is the most common and frustrating issue when using partial indexes from an application. Most database drivers use prepared statements for security and performance. The query planner, however, can be conservative and may fail to use a partial index if it cannot prove that the query's WHERE clause unconditionally matches the index's WHERE clause.

Consider our index:

... WHERE status = 'pending'

And an application query in Node.js using node-postgres:

// In your application code
const status = 'pending';
const tenantId = 1234;

// The driver creates a prepared statement like:
// EXECUTE ... ($1, $2) WHERE tenant_id = $1 AND status = $2 AND deleted_at IS NULL
const result = await pool.query(
  'SELECT id FROM invoices WHERE tenant_id = $1 AND status = $2 AND deleted_at IS NULL',
  [tenantId, status]
);

PostgreSQL's planner might not choose the idx_invoices_active_pending_tenant index. Why? Because the planner sees status = $2. It doesn't know for certain that $2 will always be 'pending'. It has to generate a generic plan that works for any value of $2. This generic plan will likely use a broader index and filter, defeating the purpose of our hyper-specific index.

Solution: You must write the query so the value is part of the query string, not a parameter, if and only if that value corresponds to the partial index definition. This sounds like an anti-pattern (and it can be, beware of SQL injection), but it's necessary in this specific case to give the planner the hint it needs.

// Corrected application code
const tenantId = 1234;

// The status is hardcoded to match the index definition.
// The tenant_id remains a parameter to prevent SQL injection.
const sql = `SELECT id FROM invoices WHERE tenant_id = $1 AND status = 'pending' AND deleted_at IS NULL`;

const result = await pool.query(sql, [tenantId]);

Now, the planner sees the literal 'pending' and can perfectly match the query to the partial index. Always parameterize user input (tenantId), but use literals for values that define which partial index you are targeting.

Gotcha 2: Planner Statistics and `ANALYZE`

The PostgreSQL query planner relies on statistical data about your tables to make intelligent decisions. For a partial index, it needs to know not just about the table as a whole, but about the subset of data defined by the index's WHERE clause.

After creating a partial index or after a significant change in the data distribution (e.g., a mass deletion event), the statistics can become stale. A query that should be using your new index might not, because the planner incorrectly estimates that a sequential scan would be cheaper.

Solution: Always run ANALYZE your_table_name; after creating new indexes or after large data modifications. For our invoices table, ANALYZE invoices; would update the necessary statistics, allowing the planner to correctly evaluate the cost of using the partial index.

Gotcha 3: NULLs in the Index Predicate

Standard B-tree indexes in PostgreSQL do not store entries where all indexed columns are NULL. However, our primary use case is WHERE deleted_at IS NULL. This is a special case that is handled correctly. The IS NULL predicate works perfectly with partial indexes.

The complexity arises when you have nullable columns in the index key itself, not just the predicate. For example, CREATE INDEX ... ON invoices (tenant_id, customer_id) WHERE deleted_at IS NULL. If customer_id can be NULL, queries like WHERE tenant_id = 123 AND customer_id IS NULL AND deleted_at IS NULL will be able to use this index effectively.

Performance Benchmark: Tying It All Together

Let's quantify the impact. We'll use our 10-million-row invoices table and test a common dashboard query: fetching the 20 most recent paid invoices for a tenant.

Setup:

The Query:

-- Run with EXPLAIN (ANALYZE, BUFFERS)
SELECT id, invoice_date, amount_cents
FROM invoices
WHERE tenant_id = 2500
  AND status = 'paid'
  AND deleted_at IS NULL
ORDER BY invoice_date DESC
LIMIT 20;

Results:

Analysis of EXPLAIN Output:

* Generic Index Plan:

    Limit ... (actual time=3.821..3.852 rows=20 ...)
      Buffers: shared hit=245
      ->  Bitmap Heap Scan on invoices ... (actual time=3.819..3.845 rows=20 ...)
            Filter: ((deleted_at IS NULL) AND (status = 'paid'::text))
            Rows Removed by Filter: 58
            Heap Blocks: exact=75
            Buffers: shared hit=245
            ->  Bitmap Index Scan on idx_generic ... (actual time=3.712..3.712 rows=490 ...)
                  Index Cond: (tenant_id = 2500)
                  Buffers: shared hit=170

The plan had to scan the generic index for the tenant, retrieve all 490 matching rows from the table (Heap Blocks: exact=75), and then filter out 58 of them that weren't paid and active. This involved 245 buffer reads.

* Partial Index Plan:

    Limit ... (actual time=0.068..0.082 rows=20 ...)
      Buffers: shared hit=5
      ->  Index Scan using idx_partial_paid on invoices ... (actual time=0.066..0.077 rows=20 ...)
            Index Cond: (tenant_id = 2500)
            Buffers: shared hit=5

This is a perfect plan. A pure Index Scan that went directly to the correct, tiny index and read the first 20 entries. It only needed to read 5 memory blocks. The database did almost no unnecessary work.

Conclusion

For senior engineers working on large-scale systems, moving beyond standard indexing patterns is not just an optimization—it's a necessity for maintaining performance and scalability. Partial indexes, when applied with a deep understanding of application query patterns, are one of the most effective tools in the PostgreSQL arsenal.

By strategically creating indexes on the hot subsets of your data (deleted_at IS NULL) and complementary indexes for colder data (deleted_at IS NOT NULL), you can achieve significant performance gains, reduce memory pressure, and lower storage costs. Always remember to validate your assumptions with EXPLAIN ANALYZE, be mindful of the planner's interaction with prepared statements, and keep your table statistics up to date. This surgical approach to indexing separates a system that merely functions from one that performs exceptionally under pressure.