31–40 of 44 posts
For anyone wondering why temporality matters and how this is different from adding a “create_time” to each row, I would highly recommend watching Rich Hickey’s talk title, “Value of Values”
This is fascinating. I've got two basic questions, however: 1) Is this always going to be performant with indices? It seems like "time" is kind of like another index here, and when designing queries which indices are used and in which order can be the difference between taking milliseconds and taking an hour. It's not obvious to me whether this will have hidden gotchas or query execution complexities, or if it's desi…
I haven't thought about this too deeply, but I think "simple" is overstating it. Being able to turn on versioning for any table by basically just pushing a button seems really powerful.
There's application-layer stuff like paper_trail for rails that can do this for you, but you're stuck if your language doesn't have a good one.
Building it into the db also means that any out-of-band direct edits to the DB also get tracked.
TimescaleDB competitor?
Now all they need is materialized views and they'll be close to postgres.
> mysqldump does not read historical rows from versioned tables, and so historical data will not be backed up. Also, a restore of the timestamps would not be possible as they cannot be defined by an insert/a user. Given this caveat, this seems unusable for production systems.
I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.
Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions. [0] https://www.splitgraph.com/product/data-lifecycle/research
This is fascinating. I've got two basic questions, however: 1) Is this always going to be performant with indices? It seems like "time" is kind of like another index here, and when designing queries which indices are used and in which order can be the difference between taking milliseconds and taking an hour. It's not obvious to me whether this will have hidden gotchas or query execution complexities, or if it's desi…
If it's present in every table, the database can be optimised for it.
Earlier quoted context omitted.
Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions. [0] https://www.splitgraph.com/product/data-lifecycle/research
Change tracking is not a fully bitemporal scheme, though. A bitemporal table tracks _two_ timelines. One is about when facts in the world were true ("valid time" or "application time"), the other is about the history of particular records in the database ("transaction time" or "system time"). Change tracking can only capture the second.
There’s two cases where valid/applicable time might be meaningful:
1. Future state known at present time (e.g. when you know now that a fact will change at a specific future point in time).
2. Corrections to current state which should be applied to historical state (e.g. when you know your application produced invalid state and you want to produce a valid state both at present and in historical representations).
The first case is used more in the literature I found, but didn’t really make the distinction clearer for me because I have limited domain use for that kind of behavior. The correction case really drove the point home for me, because my use cases would benefit from it considerably.
I hope this helps other readers interested in the topic but struggling to visualize the two timelines and how they could be used.
I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.
I work at a company where (many years ago) we built an extension to Postgres (and some helper libs in SQLAlchemy, Go) for implementing decently-performant bitemporal tables (biggest history tables have hundreds of millions of rows). Pretty much our entire company runs on it today. We implemented the “minimum viable” features (i.e. automatic expiring, non-destructive updates, generated indexes and generated table decl…