Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Easy, alternative soft deletion: `deleted_record_insert`

brandur.org

Post reply on HN
Print view View original

81–90 of 109 posts

Re: Easy, alternative soft deletion: `deleted_record_insert`

#81

Post by bastawhiz »

paulryanrogers wrote: post #71
bastawhiz wrote: post #70

Earlier quoted context omitted.

Excluding the rows still doesn't solve problems with foreign keys (you can't DELETE CASCADE and instead have to iterate all relationships manually). It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.

> It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes. Considering soft deleted for uniqueness can be a feature, especially if one has a restore feature. Though I agree it can be easily overlooked if you want to exclude them, such as forgetting to use COALESCE with some reserved value. (Otherwise null will make the whole constraint always unique.)

In my experience, you only want machine-generated values to be reserved sheet soft delete. Operationally, too many users want to delete stuff so that they can free up a user-generated value. In my service, users frequently delete their sites so that they can replace the URL/subdomain with another that they want to replace it with.

Especially when you consider foreign keys, restoration of deleted data is essentially never just setting the deleted field to null. You need a more robust system to fill in missing details or rectifying the deleted data with new data.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#82

Post by ttfkam »

nickjj wrote: post #62
no_wizard wrote: post #9

Earlier quoted context omitted.

To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…

I think there's still a spot for deleted_at or deleted_at'like functionality. It's around historical data, especially in a work scenario. For example a worker might create a thread and then 38 other workers reply to it. There could be a lot of great information in this thread. It could also be referenced in 5 other threads and external sources (docs, etc.). If the worker leaves the company, should you really delete t…

Wouldn't this be better handled by marking the user entry as inactive and then reading that value from the join rather than setting every conversation thread as is_deleted/is_deactivated?

I feel like you are solving a different problem than the one presented in the article.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#83

Post by berkle4455 »

LudwigNagasena wrote: post #80

If you want to delete something, delete it. If you want to restore something, get it from a backup. If you want to delete something, but you fear that it will ruin something in your db because the architecture is a mess and you are not really sure what references what and what will break, then soft-delete it. But what is the point of this?

soft deletes clutter up the table in question. They lead to bugs in code when people forget to exclude the soft deletes WHERE clause. It also can complicates patterns: user deletes account, a month later the same user wants to create a new account but the soft delete row prevents creation due to duplicate email address.

This seems like a clever solution that simply deletes rows but provides a cumbersome mechanism to see history of delete data if they need it.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#84

Post by ttfkam »

brightball wrote: post #18
no_wizard wrote: post #9

Earlier quoted context omitted.

To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…

Another is the support case “Help! I accidentally deleted the wrong thing!” where it saves a huge amount of time compared to loading up a full DB backup.

This is precisely the situation the article solves but without repeating

  AND NOT is_deleted
after every DB query in every app accessing the database. No full DB backup/restore needed.

  INSERT INTO mytable
       SELECT recovered.\*
         FROM myaudittable audit
            , jsonb_populate_record(
                audit.jsoncolumn
              , NULL::mytable
              ) recovered
        WHERE audit.id = 8675309
  -- optionally merge if new data added
  ON CONFLICT DO UPDATE
          SET field1 = EXCLUDED.field1
            , field2 = …etc…
Postgres has a lot of great functionality making jsonb manipulation relatively simple and easy. Is it more complicated than a simple UPDATE? Yes, but you only have that complexity once rather than in every query on the table from every app and ORM and that recovery can be more nuanced since not every restore strategy is equally valid in every situation.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#85

Post by can3p »

As many commenters pointed out, the ability to revert deletions is a highly desired feature. I cannot remember how many times our customers have deleted records by mistake and while deleted_at solution makes a revert trivial (update .. set deleted_at = null where undo_condition = true) it's much more complicated with the proposed condition. Why?

In any living system db schema is something that continuously evolves. New foreign keys, new columns, dropped foreign keys, dropped columns, new indexes. If soft deleted data remains in the tables it evolves with the rest of the system, but that's not the case if it all went into a json blob. Are you sure you want to remember all changes made to your data while trying to restore it?

The problem with queries is there, but I'm wondering about the scale of the problem. From my experience, tables do not go from hard deleted to soft deleted often, hence it's more a matter of habit to check the structure of a table you see for the first time and take deleted_at into account in case it's there.

As the author says, it's all about trade-offs, I would use an audit log for debug purposes (deleted_at does not answer who did it, adding deleted_by to every table adds a risk of split brain - what if deleted_at is null, but deleted_by is not?) and deleted_by to enable quick reverts to accommodate mistakes users and developers do.

A more problematic pattern for me is to trace the changes for different columns. Let's say you tag your uses as being a superhero. The moment you introduce this prop, analysts will immediately ask you who, why and how many times has changed this field and what was the value of the field at the time X. One can say that it's not necessary for all the fields, but I do observe that I have much less trouble in the development if I accommodate for it from the start rather then add hacks to support it later

Re: Easy, alternative soft deletion: `deleted_record_insert`

#87

Post by ttfkam »

dragonwriter wrote: post #4
blondin wrote: post #2

oh nice. how do you recover deleted records?

It seems you should be able to do it fairly straightforwardly with dynamic sql with this structure, but, I don’t know why you wouldn’t just use proper history tables, rather than story only deleted records, but lumping them all into one table. I’ve never encountered a database where I needed to know about non-current records but only ever the last-before-deletion state of deleted records, whether for data recovery or…

No dynamic SQL necessary.

jsonb_populate_record(…) was made precisely for this kind of scenario. Combine with an INSERT…ON CONFLICT DO UPDATE statement and Bob's your uncle.

https://www.postgresql.org/docs/current/functions-json.html

Re: Easy, alternative soft deletion: `deleted_record_insert`

#88

Post by ttfkam »

berkle4455 wrote: post #83
LudwigNagasena wrote: post #80

If you want to delete something, delete it. If you want to restore something, get it from a backup. If you want to delete something, but you fear that it will ruin something in your db because the architecture is a mess and you are not really sure what references what and what will break, then soft-delete it. But what is the point of this?

soft deletes clutter up the table in question. They lead to bugs in code when people forget to exclude the soft deletes WHERE clause. It also can complicates patterns: user deletes account, a month later the same user wants to create a new account but the soft delete row prevents creation due to duplicate email address. This seems like a clever solution that simply deletes rows but provides a cumbersome mechanism to…

Not too terribly cumbersome when you have jsonb_populate_record in Postgres.

https://www.postgresql.org/docs/current/functions-json.html

Re: Easy, alternative soft deletion: `deleted_record_insert`

#89

Post by scythmic_waves »

simonw wrote: post #48
jl6 wrote: post #34

This is another solution to such a common problem that one might be surprised at there being no solution baked into the SQL standard. Instead, we have some vendor specific features like automatic audit tables and time travel, and a huge array of bespoked techniques like in the article: everything from adding a deleted_at column through to re-architecting your system around event-sourcing. Why such diversity of soluti…

The SQL:2011 standard does describe a mechanism for solving this called System Versioned Tables, but the only database I've encountered that implements it so far is MariaDB: https://mariadb.com/kb/en/system-versioned-tables/ https://en.wikipedia.org/wiki/Temporal_database lists a few more - apparently there are versions of this in Oracle, DB2 and SQL Server now.

I happened to fall down this rabbit hole a few days ago because of another HN thread. In it, a commenter mentioned [1] a talk by Markus Winand where he goes over some of the more interesting additions to the SQL standard since 92, including versioned tables [2] (I've linked to the timestamp, but the whole talk is good).

I got real excited about that feature because I could think of a few tables at work that could use it. Sadly, PostgreSQL doesn't implement it [3] (I can't permalink to the feature but you can search for "T180", "System-versioned tables", "T181", or "Application-time period tables").

[1] https://news.ycombinator.com/item?id=34182433

[2] https://www.youtube.com/watch?v=xEDZQtAHpX8&t=2276s

[3] https://www.postgresql.org/docs/current/unsupported-features...

Re: Easy, alternative soft deletion: `deleted_record_insert`

#90

Post by krab »

ectopod wrote: post #69
scrollaway wrote: post #64

Earlier quoted context omitted.

This is an area where technologists and lawyers will end up disagreeing and fighting about boundaries etc. Does it still count as your data if there is no normal way to retrieve/access it in the software? If you say "yes", here's what this implies: if you have deleted the data, but it's still on the disk because the drive heads haven't wiped it yet (it's just been deallocated), then it's still accessible . So, whenev…

Here's a simple test: would the data be turned up in legal discovery? Nobody is doing a sector level disk scan but you would be expected to turn over relevant audit data if you had retained it. So it needs to be accurate and GDPR applies.

Not really. You actually may have other legal requirements to keep the data but you shouldn't use it for daily business because of GDPR. Imagine banks and long closed accounts.

Moreover, technical backup solutions, where only a very limited set of people have access, are fine. If you store DB backups, you don't have to rewrite or delete them because a customer that used your service for a week decided to ask for deletion under GDPR.

Post reply on HN