5 ms·

I just learned a new thing: Time Travel Queries[0]. I haven't used it but it seems queries are able to go back 25 hours by default, freaking cool. [0]https://w

I just learned a new thing: Time Travel Queries[0]. I haven't used it but it seems queries are able to go back 25 hours by default, freaking cool. [0]https://www.cockroachlabs.com/docs/stable/select-clause.html#select-historical-data-time-travel https://www.cockroachlabs.com/docs/stable/select-clause.html...

tyingq 8y ago
It seems like there should be a specialty database that lets you branch any point in time for not just queries, but also inserts, updates and deletes. I recall a fair amount of buzz around something called "thingamy" that was going to deliver that. Then it went quiet, closed source, and "call for pricing".
- SamReidHughes 8y ago
  That is certainly possible. What would you use it for?
  - tyingq 8y ago
    Instant what-if for end users that doesn't require IT help or lots of space/time, and doesn't interfere with the current prod data, etc. Think data savvy, but not IT savvy end users. Like financial analysts.
    - SamReidHughes 8y ago
      Thanks for answering. Myself, I'd only thought of performance for creating and using test environments in software development. Which, I guess, is another kind of what-if scenario.
  - foobiekr 8y ago
    Fork() the process, fork the database. That would be pretty interesting. Long running jobs where transforming a consistent snapshot is required.
  - keymone 8y ago
    Being able to deterministically look at what database looked like at any point in time is priceless for debugging/investigating sessions.
- tehlike 8y ago
  Eventsourcing/eventstore gets close.
- NightMKoder 8y ago
  Datomic does this. There’s (Clojure syntax) d/with that given a database at any point in time runs a transaction “virtually” (there isn’t a current db per se - you can use (d/db connection) to get a reasonably current one though) . You can then query that virtual database as you would a real database. If you do want to durably commit, you have to use (d/transact) which takes a transaction in the same format as d/with, but operates on a connection rather than a point in time database (the transaction can have special functions that read the active db at the point in time of the transaction, to implement things like cas).
- atombender 8y ago
  This "branching" could be thought of as a generalized kind of ACID transaction. After all, a transaction mimics having an isolated snapshot of your database, a one-level-deep temporary branch that is merged back on commit, and which can (like version control merges) fail on conflict. I'd love to see a database that supported branching in this way. The difficulty here isn't really the technical implementation of the branching at the data store level, which is rather trivial, but the merging. The challenge is exactly the same as a version control system like git. git can leave conflict merging to a human operator, but a database system can't, unless you bubble the conflict up to the client, which will work in some cases (interactive UIs) and not in others (automated sysrems). To support any kind of conflict resolution at all, values and updates need to be reformulated as CRDTs or some similar mechanism that can provide conflict-solving semantics. Instead of recording tuple changes as simple value assignments, they need to be merged as operations -- if x starts out as 42, then doing "update ... set x = x + 1" is the equivalent of "set x = 43" in a typical modern RDBMS transaction, but to preserve the semantics in the face of a conflicting merge, the transaction log needs to be recorded as "x = x + 1".
  - toomim 8y ago
    I'm working on something like this! I have been having the exact same idea.
ryanworl 8y ago
The implementation of these kinds of features is a lot easier than one might think if you use multi-versioned storage. You delay garbage collection of old versions of rows until the configured cut off (or never if you want to go to any time) and ignore rows with a higher timestamp when reading. Where you can hit trouble is if you can’t garbage collect fast enough and you run out of IOPS or disk bandwidth and the system wasn’t smart enough to issue back pressure before the new data was accepted into the system.
- grogers 8y ago
  However, the garbage collection is typically _heavily_ optimized for short lived garbage. Try pausing GC by keeping a transaction open for an hour or more and see how the DB performance changes. So I think there's still probably a little extra smarts going into something like mariadb's system versioned tables.
  - ryanworl 8y ago
    I may be misunderstanding the documentation, but it appears MariaDB system versioned tables don't do any garbage collection at all (it stores all versions forever), and requires reading over (skipping) old versions at runtime.
atombender 8y ago
Postgres used to have time travel -- it is relatively easy feature to build on a MVCC storage -- but it was deprecated in 6.2 and then removed [1]. Performance was one reason listed, as was space usage. It's easy to see why. For example, modern Postgres versions will in some cases reclaim dead tuples for new data, thus "cheating" by bypassing MVCC. If Postgres had to support time travel, such optimizations wouldn't be possible. I seem to remember developers talking about adding time travel back now that the codebase is better (and better understood) and it could be redesigned around current techniques. It's a cool feature to have. [1] https://www.postgresql.org/docs/6.3/c0503.htm https://www.postgresql.org/docs/6.3/c0503.htm
- evanweaver 8y ago
  FaunaDB is temporal as well and allows for unlimited historical retention. You can also compare two different times in history in a single transaction.

tyingq 8y ago

It seems like there should be a specialty database that lets you branch any point in time for not just queries, but also inserts, updates and deletes. I recall a fair amount of buzz around something called "thingamy" that was going to deliver that. Then it went quiet, closed source, and "call for pricing".

SamReidHughes 8y ago
That is certainly possible. What would you use it for?
- tyingq 8y ago
  Instant what-if for end users that doesn't require IT help or lots of space/time, and doesn't interfere with the current prod data, etc. Think data savvy, but not IT savvy end users. Like financial analysts.
  - SamReidHughes 8y ago
    Thanks for answering. Myself, I'd only thought of performance for creating and using test environments in software development. Which, I guess, is another kind of what-if scenario.
- foobiekr 8y ago
  Fork() the process, fork the database. That would be pretty interesting. Long running jobs where transforming a consistent snapshot is required.
- keymone 8y ago
  Being able to deterministically look at what database looked like at any point in time is priceless for debugging/investigating sessions.
tehlike 8y ago
Eventsourcing/eventstore gets close.
NightMKoder 8y ago
Datomic does this. There’s (Clojure syntax) d/with that given a database at any point in time runs a transaction “virtually” (there isn’t a current db per se - you can use (d/db connection) to get a reasonably current one though) . You can then query that virtual database as you would a real database. If you do want to durably commit, you have to use (d/transact) which takes a transaction in the same format as d/with, but operates on a connection rather than a point in time database (the transaction can have special functions that read the active db at the point in time of the transaction, to implement things like cas).
atombender 8y ago
This "branching" could be thought of as a generalized kind of ACID transaction. After all, a transaction mimics having an isolated snapshot of your database, a one-level-deep temporary branch that is merged back on commit, and which can (like version control merges) fail on conflict. I'd love to see a database that supported branching in this way. The difficulty here isn't really the technical implementation of the branching at the data store level, which is rather trivial, but the merging. The challenge is exactly the same as a version control system like git. git can leave conflict merging to a human operator, but a database system can't, unless you bubble the conflict up to the client, which will work in some cases (interactive UIs) and not in others (automated sysrems). To support any kind of conflict resolution at all, values and updates need to be reformulated as CRDTs or some similar mechanism that can provide conflict-solving semantics. Instead of recording tuple changes as simple value assignments, they need to be merged as operations -- if x starts out as 42, then doing "update ... set x = x + 1" is the equivalent of "set x = 43" in a typical modern RDBMS transaction, but to preserve the semantics in the face of a conflicting merge, the transaction log needs to be recorded as "x = x + 1".
- toomim 8y ago
  I'm working on something like this! I have been having the exact same idea.

ryanworl 8y ago

The implementation of these kinds of features is a lot easier than one might think if you use multi-versioned storage. You delay garbage collection of old versions of rows until the configured cut off (or never if you want to go to any time) and ignore rows with a higher timestamp when reading. Where you can hit trouble is if you can’t garbage collect fast enough and you run out of IOPS or disk bandwidth and the system wasn’t smart enough to issue back pressure before the new data was accepted into the system.

grogers 8y ago
However, the garbage collection is typically _heavily_ optimized for short lived garbage. Try pausing GC by keeping a transaction open for an hour or more and see how the DB performance changes. So I think there's still probably a little extra smarts going into something like mariadb's system versioned tables.
- ryanworl 8y ago
  I may be misunderstanding the documentation, but it appears MariaDB system versioned tables don't do any garbage collection at all (it stores all versions forever), and requires reading over (skipping) old versions at runtime.

atombender 8y ago

Postgres used to have time travel -- it is relatively easy feature to build on a MVCC storage -- but it was deprecated in 6.2 and then removed [1]. Performance was one reason listed, as was space usage. It's easy to see why. For example, modern Postgres versions will in some cases reclaim dead tuples for new data, thus "cheating" by bypassing MVCC. If Postgres had to support time travel, such optimizations wouldn't be possible. I seem to remember developers talking about adding time travel back now that the codebase is better (and better understood) and it could be redesigned around current techniques. It's a cool feature to have. [1] https://www.postgresql.org/docs/6.3/c0503.htm https://www.postgresql.org/docs/6.3/c0503.htm

evanweaver 8y ago
FaunaDB is temporal as well and allows for unlimited historical retention. You can also compare two different times in history in a single transaction.