3 ms·

I'm far more partial to the "deleted" table that simply removes the deleted entries from one table into another. Slightly more hassle on the recovery side (in

I'm far more partial to the "deleted" table that simply removes the deleted entries from one table into another. Slightly more hassle on the recovery side (in the rare event), way less risk on the read side, and covers the 90% use case where most of the time its more useful for things like debugging. Only rarely in my career have I seen soft deleted data get restored, but you don't lose that this way, you simply mitigate against accidentally showing it. All in all, its a better compromise than soft deletes EDIT: I'm pretty tempted to just convert this to dumping out to a SQLite 3 database for all soft deletes. I wonder if actual DB separation is better somehow for backup / compliance

fernandotakai 4y ago
uh, that's a really interesting idea that i've never used before -- like a graveyard for data. have you done it before? how did you implement it?
- no_wizard 4y ago
  yes, I have done it, and I've implemented it two ways: A temporary "marked for deletion" (I guess temporary soft_delete) field was used to mark rows for deletion (we had a user trash type deal) anything older than 30 days just got dump into a new table, and it recorded some simple metadata, but the data itself was dumped as JSONB. The important thing is we always recorded a way to figure out the customer / user without having to dig into the JSON blob, so if they ever actually left the platform we could still delete the data in full quite easily. This was key. It preserved the PK system we had in place for stuff like this. In another, more naive implementation, we just dumped it out into JSON blobs with special "$metadata" fields describing data types, deletion time, and other associated metadata I can't quite recall. if I recall correctly those were then encrypted and stored elsewhere (I can't recall where exactly, but likely S3 or equivalent). I don't think this was a good idea, but its what was done.
MrZander 4y ago
Our current system uses soft deletes, and I wish we had done a "deleted" table. The biggest downside to soft deletes for us is that it ruins the default indexes cause every query has a !IsDeleted where clause added to it.
- layer8 4y ago
  Wouldn’t partition indexes solve this?
  - MrZander 4y ago
    Yes, we use filtered indexes to mitigate it. It's a lot of additional boilerplate though.
- gfody 4y ago
  this works better if you split your whole schema into a physical and logical layer, which is a lot of boilerplate but seems like a pagni as well since physical concerns that shouldn't change logical semantics always eventually creep in. this entails at least a view with instead-of-delete triggers and making all indexes be filtered indexes for all tables so it's a lot of boilerplate.
wizofaus 4y ago
> I'm far more partial to the "deleted" table that simply removes the deleted entries from one table into another Or you could use a system versioned temporal table (or the equivalent in non-MSSQL databases), then the DB does the work for you, and allows recovery of UPDATEs too. Not sure exactly what the performance implications are.
- Merad 4y ago
  Temporal tables can get pretty bloated when the table sees a lot of updates since they essentially save a copy of the row every time it's modified. You could of course add a reaper system to only keep the last N versions, or only versions less than N days old, etc. Unfortunately IIRC there's no built in support to do that automatically.
  - wizofaus 4y ago
    I've actually not used in them anger, just looked into them at one point, the only reason we didn't go with them was because we didn't want to tie ourselves overly to SQL server. I'm assuming it can only work sensibly if you have an ID-generation/usage mechanism that means an ID should never be re-used even after records are deleted, which is arguably the most common scenario anyway, but I have worked with at least a few database tables in the past where this wasn't actually the case (IDs were randomly generated but with a sufficiently small range of possible values that there was a reasonable chance of generating the same ID twice within, say, 6 months of heavy usage).

fernandotakai 4y ago

uh, that's a really interesting idea that i've never used before -- like a graveyard for data. have you done it before? how did you implement it?

no_wizard 4y ago
yes, I have done it, and I've implemented it two ways: A temporary "marked for deletion" (I guess temporary soft_delete) field was used to mark rows for deletion (we had a user trash type deal) anything older than 30 days just got dump into a new table, and it recorded some simple metadata, but the data itself was dumped as JSONB. The important thing is we always recorded a way to figure out the customer / user without having to dig into the JSON blob, so if they ever actually left the platform we could still delete the data in full quite easily. This was key. It preserved the PK system we had in place for stuff like this. In another, more naive implementation, we just dumped it out into JSON blobs with special "$metadata" fields describing data types, deletion time, and other associated metadata I can't quite recall. if I recall correctly those were then encrypted and stored elsewhere (I can't recall where exactly, but likely S3 or equivalent). I don't think this was a good idea, but its what was done.

MrZander 4y ago

Our current system uses soft deletes, and I wish we had done a "deleted" table. The biggest downside to soft deletes for us is that it ruins the default indexes cause every query has a !IsDeleted where clause added to it.

layer8 4y ago
Wouldn’t partition indexes solve this?
- MrZander 4y ago
  Yes, we use filtered indexes to mitigate it. It's a lot of additional boilerplate though.
gfody 4y ago
this works better if you split your whole schema into a physical and logical layer, which is a lot of boilerplate but seems like a pagni as well since physical concerns that shouldn't change logical semantics always eventually creep in. this entails at least a view with instead-of-delete triggers and making all indexes be filtered indexes for all tables so it's a lot of boilerplate.

wizofaus 4y ago

> I'm far more partial to the "deleted" table that simply removes the deleted entries from one table into another Or you could use a system versioned temporal table (or the equivalent in non-MSSQL databases), then the DB does the work for you, and allows recovery of UPDATEs too. Not sure exactly what the performance implications are.

Merad 4y ago
Temporal tables can get pretty bloated when the table sees a lot of updates since they essentially save a copy of the row every time it's modified. You could of course add a reaper system to only keep the last N versions, or only versions less than N days old, etc. Unfortunately IIRC there's no built in support to do that automatically.
- wizofaus 4y ago
  I've actually not used in them anger, just looked into them at one point, the only reason we didn't go with them was because we didn't want to tie ourselves overly to SQL server. I'm assuming it can only work sensibly if you have an ID-generation/usage mechanism that means an ID should never be re-used even after records are deleted, which is arguably the most common scenario anyway, but I have worked with at least a few database tables in the past where this wasn't actually the case (IDs were randomly generated but with a sufficiently small range of possible values that there was a reasonable chance of generating the same ID twice within, say, 6 months of heavy usage).