3 ms·

Why deleted_at? We have soft_deleted as boolean which excludes data from all queries and last_updated which a particular query can use if it needs to. If over

by nottorp 9mo ago

Why deleted_at? We have soft_deleted as boolean which excludes data from all queries and last_updated which a particular query can use if it needs to. If over 50% of your data is soft deleted then it's more like historical data for archiving purposes and yes, you need to move it somewhere else. But then maybe you shouldn't use soft delete for it but a separate "archive" procedure?

hapidjus 9mo ago
Are you asking why we wouldn’t use 'last_updated' to store when the record was deleted? One reason is that you might want to know when it was last updated before it was deleted.
- nottorp 9mo ago
  No, more like why you'd use a more expensive filter to hide soft deleted data, instead of just a flag.
  - masklinn 9mo ago
    Checking whether `deleted_at is null` should be extremely cheap, and it avoids the duplication and desynchronisation of having both “deleted” and “deleted_at”.
    - nottorp 9mo ago
      Yes, if your database has null. I know this is about postgres, but a lot of stuff is nosql now.
      - indigo945 9mo ago
        Even in MongoDB, you can can index `null` values, so I don't understand in what database system this would be a problem.