[konha]

> On the flip side, soft deletes require every single read query to exclude deleted records.

You can use partial indexes to only index non-deleted rows. If you are worried about having to remember to exclude deleted rows from queries: Use a view to abstract away the implementation detail from your analytics queries.

[ako]

You can also use the index to cluster database blocks of the table based on the index (postgres => cluster command). This means all active records will be written to the same database blocks, and all deleted records will be kept in separate database blocks. This can speed up queries that need to access a lot of active records.

This is a good alternative to moving deleted records from an active table to a deleted table.