4 ms·

Do any databases have something like native support for soft deletes or ability to undo (other than SQL transactions rollbacks where you're having to specify th

Do any databases have something like native support for soft deletes or ability to undo (other than SQL transactions rollbacks where you're having to specify the undo checkpoint)? Something like what Git does where it keeps a history of edits? If this isn't common, is this a neglected area that should be addressed or it's just too hard of a problem? It feels like with SQL, there's minimal guardrails and it's just your own fault if you're not extra careful, compared to say using Git with code or using "restore from trash" with filesystems.

VTimofeenko 4y ago
Snowflake has time travel[1] keeping the original data for specified period of time. 1: https://docs.snowflake.com/en/user-guide/data-availability.html https://docs.snowflake.com/en/user-guide/data-availability.h...
jordanthoms 4y ago
Quite a few databases support time travel queries, in particular Oracle has for years and CockroachDB has them also. We can query the state of a table as it was at any point in the last 72hrs.
tyre 4y ago
Datomic treats all data as immutable, so it can wind back to any version. When new data is written, the entire block is copied and rewritten rather than changing the data in-place.
- mdavidn 4y ago
  This is similar to SQL checkpoints in that a rollback is all or nothing. It wouldn’t be segmented by tenant unless the each tenant has its own transactors.
  - eyelidlessness 4y ago
    The difference is that, unless you’re using a SQL database with direct/extended support for write-once immutability or a data model designed for it, once the transaction exits every successful committed change is ~final. A write-once immutability design (whether in the DB itself, or an extension, or a userspace implementation) lets you reconsider and rework mistakes after they’re committed, not unlike how you might do with git rebase etc.
- yawaramin 4y ago
  That would be difficult to use given requirements like GDPR's user data deletion.
  - closeparen 4y ago
    Real world immutable systems usually support the eventual garbage collection of soft-deleted or superseded records. No one actually wants disk usage to grow unbounded forever. It just means you are not going to support "overwrite immediately" semantics in ordinary application code paths. Datomic describes its capability here: https://docs.datomic.com/on-prem/reference/excision.html https://docs.datomic.com/on-prem/reference/excision.html
candiddevmike 4y ago
WAL archiving / point in time recovery can help with this.
wolf550e 4y ago
In MVCC systems like PostgreSQL, if you don't vacuum (garbage collect the old tuples), your database is append-only and you can query as if your transaction was started at some time in the past. I don't know how to set auto-vacuum to have a fixed delay, e.g. keeping 24h of changes, but I bet it can be added if it's not built-in.
- doctor_eval 4y ago
  I don’t understand why you’re being downvoted and I hope someone will explain.
- cryptonector 4y ago
  How do you set the time back for a query? How do you specify what txid to use for a point in time query?
- Too 4y ago
  This would just be equivalent to rollback to a checkpoint in time of the whole table. I think the question was more on a row-level. If you are just interested in global time traveling, there are many solutions, such as replaying the oplog from snapshots in time or delayed replication.
  - doctor_eval 4y ago
    Maybe I'm misunderstanding but if you know the txid at the time you're looking for then you can find the value of any specific row at any point in time using xmin and xmax at the row-level (if you're not running vacuum, as the parent suggested). Am I mistaken? The only problem is that you need to keep a map of timestamps to txids so you can find the txid that was valid at a particular moment in time. This doesn't sound to me like a significantly difficult problem, but maybe I'm mistaken. That said, it's not like you need super high time resolution for the use case in question.
    - aidos 4y ago
      Lots of people have updated_at time stamps on all tables, so you could probably inspect those to find your way back. I’ve never tried to query the history implicitly hidden in Postgres tables so I’m not sure how possible (or sensible) any of this is.
jasonwatkinspdx 4y ago
So, the fully fleshed out form of this in databases is usually called Bitemporality. The official SQL standard has included this for some years now, but it's not widely implemented by databases. An intuitive way to think of bitemporality is it's like MVCC, but with 4 timestamps per row version. One pair describes a range of time in "outside" or "valid" time, ie whatever semantic domain the database is modeling, the other pair describes a range of "system" time, which is when this record was present in the database. This lets you capture and reason about the distinction between when a fact the database models was true in the real world, and when the database was updated to reflect this fact (some people call this "as of" vs "as at"... the terms here aren't fully settled but the basic distinction is). So you can revise history, do complex time travel queries, all sorts of stuff. It's a very useful model that directly aligns with what sort of questions businesses need to answer in the context of a court case or revising their ground source of truth due to past bug or error. The downside is your database balloons with row versions, and many queries become far more complicated, perhaps needing addition joins, etc. Also from the perspective of database implementors there's a ton more complexity in the code. So that's why it's not widely supported despite the standard. There's also a niche of databases built around this model from the ground up, usually based on Datalog instead of SQL. There's also overlapping work with RDF and Semantic Web thinking (as awry as all that went). In practice how most organizations address this is operationally, by keeping generational and incremental backups that let them restore previous database states as needed. Though as the original post we're here for proves, that kind of operational solution can bite back hard when it goes wrong.
stubish 4y ago
Generally a database that allows you to revert only allows you to revert the entire database. Bits of information are often related to other bits of information, and if you only undo some of the changes, then you end up with inconsistent data (dangling references) and destroyed data (overriding information more recent than what you are restoring). So soft deletes tends to become a domain specific problem rather than a problem that can be solved with better tech. I'd argue that SQL has some pretty big guardrails. 'DELETE FROM CUSTOMER WHERE' will generally fail, because there is lots of data referring to the CUSTOMER table and the system will insist the data remain consistent.
max-hoffman 4y ago
Dolt is a SQL database that implements Git primitives at the storage layer, including commit, diff, and revert. We wrote a blog the other week summarizing how these features can serve as guardrails: https://www.dolthub.com/blog/2022-04-14-atlassian-outage-prevention/ https://www.dolthub.com/blog/2022-04-14-atlassian-outage-pre...
tpxl 4y ago
This is rather easily achieved using additional tables and triggers, so whenever you update or delete a row, the old version is written to the additional table. It requires some work (writing triggers, duplicate table definitions, etc.), but it's not that hard and the result is worth it.

VTimofeenko 4y ago

Snowflake has time travel[1] keeping the original data for specified period of time. 1: https://docs.snowflake.com/en/user-guide/data-availability.html https://docs.snowflake.com/en/user-guide/data-availability.h...

jordanthoms 4y ago

Quite a few databases support time travel queries, in particular Oracle has for years and CockroachDB has them also. We can query the state of a table as it was at any point in the last 72hrs.

tyre 4y ago

Datomic treats all data as immutable, so it can wind back to any version. When new data is written, the entire block is copied and rewritten rather than changing the data in-place.

mdavidn 4y ago
This is similar to SQL checkpoints in that a rollback is all or nothing. It wouldn’t be segmented by tenant unless the each tenant has its own transactors.
- eyelidlessness 4y ago
  The difference is that, unless you’re using a SQL database with direct/extended support for write-once immutability or a data model designed for it, once the transaction exits every successful committed change is ~final. A write-once immutability design (whether in the DB itself, or an extension, or a userspace implementation) lets you reconsider and rework mistakes after they’re committed, not unlike how you might do with git rebase etc.
yawaramin 4y ago
That would be difficult to use given requirements like GDPR's user data deletion.
- closeparen 4y ago
  Real world immutable systems usually support the eventual garbage collection of soft-deleted or superseded records. No one actually wants disk usage to grow unbounded forever. It just means you are not going to support "overwrite immediately" semantics in ordinary application code paths. Datomic describes its capability here: https://docs.datomic.com/on-prem/reference/excision.html https://docs.datomic.com/on-prem/reference/excision.html

candiddevmike 4y ago

WAL archiving / point in time recovery can help with this.

wolf550e 4y ago

In MVCC systems like PostgreSQL, if you don't vacuum (garbage collect the old tuples), your database is append-only and you can query as if your transaction was started at some time in the past. I don't know how to set auto-vacuum to have a fixed delay, e.g. keeping 24h of changes, but I bet it can be added if it's not built-in.

doctor_eval 4y ago
I don’t understand why you’re being downvoted and I hope someone will explain.
cryptonector 4y ago
How do you set the time back for a query? How do you specify what txid to use for a point in time query?
Too 4y ago
This would just be equivalent to rollback to a checkpoint in time of the whole table. I think the question was more on a row-level. If you are just interested in global time traveling, there are many solutions, such as replaying the oplog from snapshots in time or delayed replication.
- doctor_eval 4y ago
  Maybe I'm misunderstanding but if you know the txid at the time you're looking for then you can find the value of any specific row at any point in time using xmin and xmax at the row-level (if you're not running vacuum, as the parent suggested). Am I mistaken? The only problem is that you need to keep a map of timestamps to txids so you can find the txid that was valid at a particular moment in time. This doesn't sound to me like a significantly difficult problem, but maybe I'm mistaken. That said, it's not like you need super high time resolution for the use case in question.
  - aidos 4y ago
    Lots of people have updated_at time stamps on all tables, so you could probably inspect those to find your way back. I’ve never tried to query the history implicitly hidden in Postgres tables so I’m not sure how possible (or sensible) any of this is.

jasonwatkinspdx 4y ago

So, the fully fleshed out form of this in databases is usually called Bitemporality. The official SQL standard has included this for some years now, but it's not widely implemented by databases. An intuitive way to think of bitemporality is it's like MVCC, but with 4 timestamps per row version. One pair describes a range of time in "outside" or "valid" time, ie whatever semantic domain the database is modeling, the other pair describes a range of "system" time, which is when this record was present in the database. This lets you capture and reason about the distinction between when a fact the database models was true in the real world, and when the database was updated to reflect this fact (some people call this "as of" vs "as at"... the terms here aren't fully settled but the basic distinction is). So you can revise history, do complex time travel queries, all sorts of stuff. It's a very useful model that directly aligns with what sort of questions businesses need to answer in the context of a court case or revising their ground source of truth due to past bug or error. The downside is your database balloons with row versions, and many queries become far more complicated, perhaps needing addition joins, etc. Also from the perspective of database implementors there's a ton more complexity in the code. So that's why it's not widely supported despite the standard. There's also a niche of databases built around this model from the ground up, usually based on Datalog instead of SQL. There's also overlapping work with RDF and Semantic Web thinking (as awry as all that went). In practice how most organizations address this is operationally, by keeping generational and incremental backups that let them restore previous database states as needed. Though as the original post we're here for proves, that kind of operational solution can bite back hard when it goes wrong.

stubish 4y ago

Generally a database that allows you to revert only allows you to revert the entire database. Bits of information are often related to other bits of information, and if you only undo some of the changes, then you end up with inconsistent data (dangling references) and destroyed data (overriding information more recent than what you are restoring). So soft deletes tends to become a domain specific problem rather than a problem that can be solved with better tech. I'd argue that SQL has some pretty big guardrails. 'DELETE FROM CUSTOMER WHERE' will generally fail, because there is lots of data referring to the CUSTOMER table and the system will insist the data remain consistent.

max-hoffman 4y ago

Dolt is a SQL database that implements Git primitives at the storage layer, including commit, diff, and revert. We wrote a blog the other week summarizing how these features can serve as guardrails: https://www.dolthub.com/blog/2022-04-14-atlassian-outage-prevention/ https://www.dolthub.com/blog/2022-04-14-atlassian-outage-pre...

tpxl 4y ago

This is rather easily achieved using additional tables and triggers, so whenever you update or delete a row, the old version is written to the additional table. It requires some work (writing triggers, duplicate table definitions, etc.), but it's not that hard and the result is worth it.