4 ms·
> 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
by konha 5y ago
> 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 5y agoYou 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.