3 ms·

I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to

I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to fully grasp their value, and I don’t think I’m alone in that lack of immediate intuition. And granted non-SQL databases like Datomic are much better positioned for use cases where people realize they want the feature. And granted it definitely can’t be more storage efficient to store bitemporal data than not to. But I think it’s a much more compelling feature, from the outside. As far as memory complexity, this got me thinking a userspace implementation (I’ve prototyped one for Postgres using VIEWs for fun and personal education) could reasonably borrow optimization techniques from persistent data structures common in FP languages. But I don’t know how well a query planner would cope with that, and my instinct is that the optimization would be better applied at the storage engine level (again as I see it, advantage: Maria).

ttfkam 4y ago
The key to temporal tables in Postgres is the following: • add a timestamp range column • create a history table that matches the main table • using table inheritance, have the live table inherit from the history table At this point all queries on the main table are "live" and queries on the history include past and present data. • add an insert trigger that initializes the live table's timestamp range to start "now()" • add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges Bonus • have event triggers automatically set up the temporal tables on CREATE TABLE
- eyelidlessness 4y ago
  So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true. The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.

ttfkam 4y ago

The key to temporal tables in Postgres is the following: • add a timestamp range column • create a history table that matches the main table • using table inheritance, have the live table inherit from the history table At this point all queries on the main table are "live" and queries on the history include past and present data. • add an insert trigger that initializes the live table's timestamp range to start "now()" • add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges Bonus • have event triggers automatically set up the temporal tables on CREATE TABLE

eyelidlessness 4y ago
So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true. The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.