- What's hard delete:
Performing a delete query to delete a record permanently from the table.
- What's soft delete:
Flagging a record as deleted in a table, instead of actually deleting the record.
- What happens when we do a hard delete?
A search query is made to find in which leaf node the record exists.
The entire node is then moved into RAM.
The record is deleted from the leaf node inside RAM.
Then the leaf node has to be rebalanced inside the disk to satisfy the B+ tree rule.
This is considerably high number of operations when we are looking to save every milliseconds.
One engineering optimization is to just flag the record and return the response. So in the application layer, we can omit the records having this flag while reading.
Later when the activity is low on the server, we can perform a batch program to delete all the records having this flag set to true.
Advantages:
- Low latency ✓
- Recovery of the deleted record is easily possible ✓
Disadvantages:
- Increase complexity
- If not taken out after a threshold time, stale and unwanted records are taking up space in the database.
Everything is a tradeoff, depending on the application we have to choose. It's better to be aware that these kinds of designs and processes exist!