The hidden cost we battle in e-commerce isn't just DB storage/performance, it's Search Index Pollution. We treat 'availability' as a complex state machine (In Stock, Backorder, Discontinued-but-visible, Soft Deleted). Trying to map this logic directly into a Postgres query with WHERE deleted_at IS NULL works for CRUD, but it creates massive friction for discovery.
We found that strict CQRS/Decoupling is the only way to scale this. Let the operational DB keep the soft-deletes for audit/integrity (as mentioned by others), but the Search Index must be a clean, ephemeral projection of only what is currently purchasable.
Trying to filter soft-deletes at query time inside the search engine is a recipe for latency spikes.
We found that strict CQRS/Decoupling is the only way to scale this. Let the operational DB keep the soft-deletes for audit/integrity (as mentioned by others), but the Search Index must be a clean, ephemeral projection of only what is currently purchasable.
Trying to filter soft-deletes at query time inside the search engine is a recipe for latency spikes.