20 ms·
Easy, alternative soft deletion: `deleted_record_insert`
- blondin 4y agooh nice. how do you recover deleted records?
- ttfkam 4y agohttps://news.ycombinator.com/item?id=34207659 https://news.ycombinator.com/item?id=34207659
- ericpauley 4y agoExactly. The whole point of soft delete is data recovery, as it’s not sufficient for anything else (like auditing).
- dragonwriter 4y agoIt 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 any other purpose.
- ttfkam 4y agoNo 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 https://www.postgresql.org/docs/current/functions-json.html
- nemothekid 4y ago>and without the expectation that it’ll be used to undelete data (which probably wouldn’t work for deleted_at anyway).
- deleted 4y ago[deleted]
- xupybd 4y agoI think the author is suggesting that you often can't do it with soft deletes anyway. https://brandur.org/soft-deletion#undelete https://brandur.org/soft-deletion#undelete
- Foobar8568 4y agoReinventing standard database features like audit tables, sql server has such out of the box, same for any major databases.
- deleted 4y ago[deleted]
- jimmytucson 4y agoMy question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.
- no_wizard 4y agoTo 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 exist. The alternative is to write the data to a separate database and/or table that’s meant for archive purposes. I think it’s better than these soft delete tricks but it’s got complexity too. More resilient IMO and let’s production systems run leaner over time IMO
- brightball 4y agoAnother 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.
- deleted 4y ago[deleted]
- ttfkam 4y agoThis 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.
- rubyfan 4y agoI did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.
- hdra 4y agoI considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change.. how did solve that particular issue? or was it not a requirement in your case?
- nkrumm 4y agoWe did something like this, by using postgresql's `set_config()` before every transaction. The trigger functions can read that value using `current_setting()`. It works for us, but for a relatively low-traffic internal application.
- doctor_eval 4y agoI have experimented with mapping application users 1:1 to individual database users, and using SET ROLE at the start of every transaction. I got it working in a PoC, and with the right configuration of roles, this pattern would give you user identity and audit right down to the database itself. Sadly I haven’t pulled it off in production environment yet, but if I ever get the chance to work at this level in an enterprise app again, I’ll definitely do it.
- drittich 4y agoWe do this in SQL Server by running a SET CONTEXT_INFO command every time we open a connection. This makes the current user ID available to any SQL that runs.
- rubyfan 4y agoI implemented in the app layer so had the user available to implement the new audit record. I suppose doing it in the DB layer would impose a challenge. The few ways to solve that that I’m aware of would require a) your app to kick off a function with the user id of the mutation or b) pushing user auth into the DB layer and principal switching your DB connection. Both have downsides IMO. I don’t typically favor DB level solutions because bleeding app logic beyond basic schema and integrity means you lose some portability and/or your architecture messes separation of concerns pretty quick. Maybe that means you don’t get the full benefit of the DB but it’s a trade I usually make.
- ufxdldbx 4y ago[flagged]
- duskwuff 4y agoThere's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging around, you need some way to figure out who they belong to. And I don't see any way to do that with this schema, short of rehydrating all the rows and following the original foreign key relations.
- username1407 4y agoCan't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon'; [1] https://www.postgresql.org/docs/15/functions-json.html https://www.postgresql.org/docs/15/functions-json.html
- duskwuff 4y agoThat assumes that all of a user's data is a single foreign key relationship away from the user, which often isn't the case. It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.
- username1407 4y agoThat's true. To give an example for your 1st point: If you have the schema users->messages->message_attachments and message_attachments does not store the user_id, you'll have trouble finding all attachments from a specific user.
- simonw 4y agoPostgreSQL can create indexes on the results of expressions, so it's possible to accelerate queries that select against a nested value inside a JSON blob.
- 4y ago
- deleted 4y ago[deleted]
- YouWhy 4y agoWhat I like about this construct is the fact that it uses the looser JSON semantics for the typically ad-hoc nature of recovering deleted entries - which does not necessitate the full power of referntial consistency. What I'm not sure about is how will it will behave with blob fields and other data types with "problematic" serializations.
- brightball 4y agoI’ve been using this exact approach for years and strongly advocate for it. The deleted_at approach is rife with problems since you have to ensure it’s checked in every single query, including joins where it may be referenced. Getting the record out of the table is critical.
- AlexeyMK 4y agoYou can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.
- TylerE 4y agoBwttwr yet, encode the logic in a view and run your queries against the view. DRY
- AlexeyMK 4y agoSure, but you need an "undeleted" view per table now - I haven't seen an ORM offer maintaining those views capability out of the box - any to look at?
- jbverschoor 4y agoDon’t forget versions of the schema, and data that is not migrated to the new schema
- yen223 4y agoIn postgres you cannot make a foreign-key reference to a view, which effectively means you can't prevent another table from referencing a soft-deleted record.
- skissane 4y agoI wish Postgres had something like a "CHECK contraint" on a foreign key. There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status <> 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, populate both id and id_active to same value; when you soft-delete it, set id_active to NULL. Actually, maybe a simpler solution is to make id_active a "GENERATED ALWAYS AS ... STORED" column–although I'm not sure if Postgres supports them for foreign keys? That's a relatively new Postgres feature and I haven't done much yet with the more recent versions in which that feature was added.
- solidsnack9000 4y agoWith a procedural language and SQL introspection features, you can programmatically generate triggers, functions and backing tables for these kinds of things. For Postgres, this SQL can generate temporal tables backed by JSONB for any table: https://github.com/solidsnack/macaroon/blob/master/temporal.sql https://github.com/solidsnack/macaroon/blob/master/temporal.... It would be pretty easy to change this to perform deletion-with-interning like mentioned in the article. In an older version, the code actually replicated the schema of the source table in the log table; but it can lead to problems during migrations.
- piyh 4y agoJSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.
- masklinn 4y agoUnless you’re really into the implementation details of individual postgres column types that seems of little interest. Jsonb just means “parsed json” (lit. “json, binary”), meaning postgres parses the data to a binary representation upfront which allows for more efficient json operations. However that comes at increased insert and storage costs. It also leads to postgres normalisation so values don’t round-trip textually (which can surprise).
- sandGorgon 4y agojsonb is a viable alternative to mongodb. im not doing blow by blow feature parity...but in 90 percentile usecase, you can replace mongodb with jsonb
- eitland 4y agoGood to see you have your priorities sorted. Sincerely, a dad with 5 kids.
- egeozcan 4y agoGot my first one nearly 2 months ago. How are you still alive? I'm joking of course... But really, how?
- nonethewiser 4y ago> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just include a where deleted at is null check. Hide it behind some interface in your ORM if you dont want to think about it.
- maxbond 4y agoIt's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures. A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like the idea of my ORM being even more magical than it already was.
- nonethewiser 4y agoId rather have the magic in an ORM than the database. Sometimes this desire to keep people as far as possible from understanding the database schema just makes everything much more complicated.
- maxbond 4y agoIt's funny, that's exactly how I feel about ORMs! (That they're a way to create distance between the programmer and the database, when what you should have is understanding.) Understanding the schema is important I agree - a great way to achieve that is to make the schema simpler to understand & to eliminate the need to for special case logic as much as possible. In my view triggers like this are great, they're keeping the integrity rules you've established for your data ("though shalt not delete a record without creating a copy for observability and manual recovery by incident responders") out of your application code (where shifting requirements and much higher change rate put them in jeopardy) and enshrining them in the schema (where that can stay for decades, through rewrites etc.).
- jl6 4y agoThis 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 solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs. It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.
- simonw 4y agoThe 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://mariadb.com/kb/en/system-versioned-tables/ https://en.wikipedia.org/wiki/Temporal_database https://en.wikipedia.org/wiki/Temporal_database lists a few more - apparently there are versions of this in Oracle, DB2 and SQL Server now.
- scythmic_waves 4y agoI 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 https://news.ycombinator.com/item?id=34182433 [2] https://www.youtube.com/watch?v=xEDZQtAHpX8&t=2276s https://www.youtube.com/watch?v=xEDZQtAHpX8&t=2276s [3] https://www.postgresql.org/docs/current/unsupported-features-sql-standard.html https://www.postgresql.org/docs/current/unsupported-features...
- ivank 4y agohttps://github.com/xocolatl/periods https://github.com/xocolatl/periods periods does this for PostgreSQL.
- ttfkam 4y agoGreat solution. Unfortunately only works on installations that support extensions, which excludes most managed database services like AWS RDS. I like how you tried to track the standard as close as possible. I've seen (and written) ad hoc solutions that hard-code too much or mandate certain columns to be present. That said, unlike the standard and most other RDBMSs, Postgres supports range types. Seems a shame to rely simply on two timestamptz columns when one tstzrange should suffice.
- ibbtown 4y agoSounds like a DSGVO/GDPR nightmare. And I really don't want to explain why the deleted data are part of some sold stolen data blob. There might be some technical use cases, but in general it has many culprit
- ocimbote 4y agoIf this ils well documented, I don't see how this would be a problem for GDPR. Can you elaborate?
- simonw 4y agoCompliance there feels to me like it would be quite straightforward to implement: if there is a legal requirement for the deleted data to be fully disposed of there's a very obvious single place to delete it from.
- blumomo 4y agoOften other tables have foreign keys to the record to be deleted. What’s a good pattern for treating those related tables for every time a relationship row gets deleted/removed as per the OP blog post?
- config_yml 4y agoReally depends on the related data, but there‘s the option to use ON DELETE CASCADE|RESTRICT and set null or set default. Cascade can be a bit of a footgun, as this can trigger a waterfall of deletes.
- danparsonson 4y agoCascades can also lead to deadlocks as I found to my cost the one and so far only time I tried using hard deletes.
- porsager 4y agoSetting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would be crazy to have a system where you will need any extra where clauses for the default queries being made. You can even make a simple function that sets a statement level variable checked in the rls policy so that deleted rows can be included or only deleted rows be returned. The only negative thing I see about using a deleted_at column is that cancelling the delete in the before delete trigger changes the resulting "deleleted rows" count to be 0 instead of the expected number.
- bastawhiz 4y agoExcluding 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.
- paulryanrogers 4y ago> 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.)
- bastawhiz 4y agoIn 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.
- fb03 4y agoI wrote a specific library to do this (automatically generates a mixin for SQLAlchemy and installs a hook which rewrites all queries, removing soft-deleted items from queries and also relationships) so that the soft-delete problem becomes a non issue and you still have the data there if you want to revert/activate something https://github.com/flipbit03/sqlalchemy-easy-softdelete https://github.com/flipbit03/sqlalchemy-easy-softdelete
- revskill 4y agoIn most case, i just see deletion as change status from `active` to `deleted`. You should never remove the actual record from database.
- hgsgm 4y agoThat's illegal in Europe, and unethical everywhere.
- quesera 4y agoThat depends on the industry. In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years). There are ways to comply without soft-deletion, but in practice they are much more expensive to implement and never used. 0: "Illegal" is the wrong word, really "non-compliant with regulations, resulting in significant fines but except in case of gross negligence or fraud, probably not criminal charges".
- isitmadeofglass 4y ago> In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years). There are also laws that require you to delete customer information. For instance patient data in situations where consent is withdrawn. So either way you can’t rely on “it’s often”. And on the flip side of things the legal requirements you are referring to are typically the audit requirements, which are still better served using audit tables, since it’s not enough to have a “current data” and “data that is currently not active”, since you need logs of who changed the data when, and you can easily run into situations where something is soft deleted, then restored, then soft deleted again.
- quesera 4y agoThis is true, and in practice the solution frequently ends up being a combination of soft deletion and audit logs. Just pointing out that in some common cases, the goals of consumer data control are impossible to implement, due to conflicting requirements for record retention.
- japanman425 4y agoWhat’s the actual use case for soft deletes. Audits? Have an audit log then. There’s little reason to have deleted but not really deleted items in your database.
- lazide 4y ago‘Recycle bin’ type functionality for data is usually highly desirable for customers. They want the ability to undelete stuff they accidentally delete, since accidental deletes happen all the time. True for consumer users. Super super true for enterprise users.
- hgsgm 4y agoWhat's a use case for not even allowing soft deletes?
- japanman425 4y agoNormal record keeping activities
- douge1 4y agoI’ve recently put together an awesome list about temporality, including: soft delete, time travel, slowly changing dimensions, and bitemporality. https://github.com/daefresh/awesome-data-temporality https://github.com/daefresh/awesome-data-temporality
- LudwigNagasena 4y agoIf 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?
- berkle4455 4y agosoft 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.
- ttfkam 4y agoNot too terribly cumbersome when you have jsonb_populate_record in Postgres. https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html
- can3p 4y agoAs 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
- pw262 4y agoIn some cases a soft delete is the only option. Say I run a chain of stores and wish to close a particular store mid year. The existing sales data still needs to reference the now closed store, but any other attempt to interact with the store should be prevented. The application and its use of data must take this into account. I don't believe there is some magic bullet here. However, audit tables are invaluable for tracking changes and culprits. Eventually, the store and all its data can be deleted using cascade delete to maintain referential integrity.
- ljm 4y agoI’ve found similar and settled on three distinct solutions that tackle different aspects of the problem that soft deleting covers. The first is analytics/reporting, which can be used to aggregate or otherwise store data without being coupled to the main application (therefore, not a problem to delete data if it’s been processed already). The second is auditing to maintain a paper trail. The third is a soft delete backed by a TTL, basically so there’s a window of time to undo the operation, but the data doesn’t remain there forever (which might be a problem with GDPR and the like - depending on what your data is.) Main reason I had for trying to break it down this way was because soft deletes in a relational database, with foreign key constraints, becomes unintuitive pretty fast. You need escape hatches to properly delete records, you need to remember to filter deleted ones from your results, and then there’s even more complication when trying to handle this across associations. So the final piece is only to soft delete things that require it, rather than enforcing it across all tables.
- douge1 4y agoWould “AS OF SYSTEM TIME” from SQL:2011[0] standard do the trick? [0] https://en.wikipedia.org/wiki/SQL:2011 https://en.wikipedia.org/wiki/SQL:2011