Answer to the question
History of the question
Soft-delete patterns emerged as an alternative to hard deletion for audit trails and data recovery. Early implementations used simple boolean flags or timestamp columns, but developers soon encountered performance degradation when these flags were incorporated into standard B-tree indexes. The issue became prominent with the widespread adoption of PostgreSQL partial indexes and SQL Server filtered indexes in the mid-2000s, which allowed indexing only active records. Understanding selectivity estimation—how the query planner predicts the percentage of rows matching a condition—became crucial when comparing full composite indexes versus partial indexing strategies.
The problem
When a soft-delete flag (e.g., is_deleted) is added to a composite index like (is_deleted, user_id, created_at), the database optimizer may miscalculate row selectivity for queries filtering WHERE is_deleted = false. If 90% of rows are active, the optimizer might choose a sequential scan instead of an index scan, or conversely, if the distribution is skewed, it might inappropriately favor the index. Partial indexes (WHERE is_deleted = false) store only active rows, guaranteeing high selectivity, but standard composite indexes store all rows, leading to index bloat and ambiguous cardinality estimates when statistics don't accurately reflect the soft-delete distribution.
The solution
Implement partial indexes (in PostgreSQL) or filtered indexes (in SQL Server) that exclude soft-deleted rows entirely, coupled with separate indexes for deleted data if needed. For MySQL or databases lacking partial indexing, use a composite index with the soft-delete flag as the leading column only if the active dataset is small; otherwise, partition the table by deletion status. Explicitly analyze table statistics after bulk deletions to prevent stale histograms. When querying active records, use the exact predicate from the partial index definition (WHERE is_deleted = false) to ensure the optimizer recognizes index applicability.
Code example
-- PostgreSQL: Partial index for active records only CREATE INDEX idx_active_users_email ON users(email) WHERE is_deleted = false; -- SQL Server: Filtered index equivalent CREATE INDEX IX_Active_Users_Email ON Users(Email) WHERE IsDeleted = 0; -- Query that leverages the partial index SELECT * FROM users WHERE email = 'alice@example.com' AND is_deleted = false;
Situation from life
A SaaS platform managing 10 million user records experienced severe slowdowns in their admin dashboard when filtering active users by creation date. Initially, they used a composite index (is_deleted, created_at) on PostgreSQL, assuming it would accelerate WHERE is_deleted = false ORDER BY created_at queries. However, as the dataset grew to 80% soft-deleted historical accounts, queries began taking 8-12 seconds because the planner underestimated the cost of scanning the bloated index.
Solution A: Maintain the composite index and force index usage with hints.
This approach used SET enable_seqscan = off or query plan hints to force index utilization. While it improved specific queries temporarily, it created maintenance debt and often forced suboptimal plans for other access patterns when data distribution shifted. The solution also failed to address the underlying index bloat causing increased storage and VACUUM overhead.
Solution B: Create separate partial indexes for active and deleted records.
Implementing CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false reduced the index size by 80% and allowed the planner to accurately estimate 2 million active rows versus 8 million deleted. Queries dropped to 40ms, but required refactoring all application queries to ensure the is_deleted = false predicate remained explicit and not wrapped in functions or abstracted behind views that obscured the condition.
The team chose Solution B because it provided sustainable performance without query hint maintenance. The result was a 95% reduction in query latency and elimination of periodic VACUUM bloat issues caused by the previous oversized composite index. Monitoring confirmed consistent sub-second response times for the dashboard's primary use cases.
What candidates often miss
How does the presence of NULL values in a soft-delete timestamp column (using NULL for active, timestamp for deleted) affect partial index usage versus boolean flag approaches?
When using a nullable deleted_at timestamp, partial indexes like WHERE deleted_at IS NULL face challenges with PostgreSQL's handling of NULL indexability. Unlike boolean flags where = false is explicit and sargable, IS NULL conditions require the planner to recognize index applicability, which can fail if the query uses parameterized statements where the planner cannot prove the parameter will be NULL. Additionally, updates setting deleted_at = CURRENT_TIMESTAMP cause index bloat in the partial index for active records as rows are removed from it, whereas boolean flag updates flip the bit but stay within a full composite index. The nullable approach requires more frequent ANALYZE calls and careful consideration of index fill factors to handle the high churn of deletion status changes.
Why might a covering index including soft-delete columns cause slower writes than expected even with low deletion frequency?
Covering indexes (using the INCLUDE clause in PostgreSQL 11+ or SQL Server) appending is_deleted to avoid table lookups actually degrades write performance because every soft-delete operation (an UPDATE) must modify multiple index structures. When a user is soft-deleted, the database must mark the old index entry as dead in the active partial index, insert a new entry into any deleted record indexes, and update the covering index's heap pointers. Candidates often miss that partial indexes isolate this churn—only the specific partial indexes for active or deleted states are modified—whereas covering indexes on the main table require updating the primary index structure regardless of the soft-delete status, creating write amplification that impacts transaction throughput.
When does the query optimizer ignore a partial index for soft-deleted data even when the query explicitly filters for deleted records?
If the partial index is defined as WHERE is_deleted = true for audit queries, but the application uses a prepared statement with a parameter $1 for both active and deleted queries, PostgreSQL may cache a generic plan that doesn't recognize the partial index for the specific true case. This occurs because prepared statements generate plans before parameter values are bound, and the optimizer cannot prove that $1 = true will always match the index predicate. Candidates miss that dynamic SQL or recompilation hints (OPTION (RECOMPILE) in SQL Server, executing with literal values in PostgreSQL) are required to ensure the planner sees the concrete value and matches it to the partial index predicate, rather than relying on generic plans that default to sequential scans due to the uncertainty of the parameter value.