5 ms·

Poor man's bitemporal data system in SQLite and Clojure

172 points by adityaathalye 1y ago

moomin 1y ago
It's a pity that Clojure is kind of a hermetic space these days, because the concept of bitemporality really deserves much more attention. It's amazing how often you want to know "What was the P&L for March using the data available on Apr 4?" and how uncommon it is to find a database design that supports that kind of query.
- galkk 1y ago
  Spanner does that with ease (I worked there, so I’m clearly biased). https://cloud.google.com/spanner/docs/timestamp-bounds#exact_staleness https://cloud.google.com/spanner/docs/timestamp-bounds#exact...
  - refset 1y ago
    That only covers the 'transaction time' axis though? And the page says retention is limited to 1 week. No doubt useful for some things, but probably not end-user reporting requirements.
- lemonberry 1y ago
  "It's a pity that Clojure is kind of a hermetic space these days" If you don't mind sharing, I'm curious why you feel this way.
  - deleted 1y ago
    [deleted]
  - jonahbenton 1y ago
    We hermits enjoy its use in our lairs, worlds of pure thought.
- necubi 1y ago
  It's not particularly rare in analytical databases/query engines, nearly all of which support AsOf joins these days, including * Clickhouse * DuckDB * BigQuery * Snowflake * QuestDB
  - refset 1y ago
    AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns. In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.
    - sterlinm 1y ago
      This is the book I like for an overview of the problem. https://www.amazon.com/Temporal-Relational-Kaufmann-Management-Systems/dp/1558608559 https://www.amazon.com/Temporal-Relational-Kaufmann-Manageme...
- 0_gravitas 1y ago
  'Hermetic' is an interesting word-choice, considering Clojure has a habit/raison detre of attaching to other host languages/ecosystems i.e. Jank (C++), ClojureScript (JS), Basilisp (Python), Phel (PHP), Babashka (general scripting), and of course, Clojure itself on the JVM.
  - Garlef 1y ago
    Sure. But the other way around? Why is there no JS client for datomic (that is not abandoned)?
    - monsieurbanana 1y ago
      datomic =/= clojure Clojure doesn't have much manpower, being a niche language and all, so maybe that's where your impression comes from. It's hardly on purpose, and there's many examples of software that ships with another language as public API. Rama is written in clojure but the main api is java Klipse is written in cljs but is marketed as a "javascript library" Even Datomic maintains a java api
    - fulafel 1y ago
      Rama[1] for example launched with Java API first. Probably for some reason Datomic just didn't get traction with JS users for some nontechnical reason. [1] https://redplanetlabs.com/ https://redplanetlabs.com/
refset 1y ago
tl;dr CREATE VIEW IF NOT EXISTS world_facts_as_of_now AS SELECT rowid, txn_time, valid_time, e, a, v, ns_user_ref, fact_meta FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY e, a ORDER BY valid_preferred DESC, txn_id DESC ) AS row_num FROM world_facts ) sub WHERE row_num = 1 AND assert = 1 ORDER BY rowid ASC; ...cool approach, but poor query optimizer! It would be interesting to see what Turso's (SQLite fork) recent DBSP-based Incremental View Maintenance capability [0] would make of a view like this. [0] https://github.com/tursodatabase/turso/tree/main/core/incremental https://github.com/tursodatabase/turso/tree/main/core/increm...
- adityaathalye 1y ago
  It is a poor man's database, after all :) I really need to complete this thing and run some data through it... like, how poor is poor really? Can it be just enough for me to make a getaway with smol SaaS apps?
nickpeterson 1y ago
I don’t understand footnote 16? It sounds like he’s saying he’s ceo of htmx and datastar?
- adityaathalye 1y ago
  Yes... This is a running joke in both communities. IYKYK :)
  - nickpeterson 1y ago
    Oh ok, I’m out of the loop on it ;) thought it was some sort of ai hallucination.
    - adityaathalye 1y ago
      All eleven thousand words were hand-typed. No AIs are used or abused in the making of any of my blog posts. (Because I write to think, because writing is nature's way of showing me how sloppy my thinking is. So it goes...)
      - lemonberry 1y ago
        "Because I write to think" Hallelujah!!
        lambduhhhh 1y ago
        this part (and this is how you know he's a true Clojurian)
      - fnordsensei 1y ago
        If you don’t mind a tangent, this is close to my way of working out thoughts as well, including with code. When I write code, it’s as much a cognitive tool as it is a tool to make things happen in the system. It develops thoughts as much as it develops system behavior. Involving AI changes this quite a bit, but I feel like I’m making my way to a balance where it supports rather than replaces (or worse: disrupts) my cognitive processes.
whalesalad 1y ago
I've been absolutely waist deep in a bitemporal system on top of PostgreSQL using tstzrange fields. We manage an enormous portfolio of hundreds of thousands of domain names. Every time our core db is modified, before/after states are emitted to a change table. We've been doing this since 2022. Those changes get lightly transformed via trigger into a time travel record, with the valid from/to range and a gist index to make asking questions about the state of the world at a particular point in time easy. For perspective our change table has 90M rows. All of it works quite well and is decently performant. We can ask questions like, how many domains did we own on March 13th, 2024? Or look at the entire lifecycle of a domains ownership (owned, released, re-acquired, transfered, etc). The big challenge and core issue we discovered though is that our data sucks. QAing this new capability has been a moving target. Tons of mistakes over time that were partially undone or manually undone without proper audit trail. Ghost records. Rapid changes by our bulk editor tool a->b->a->b that need to get squashed into just a->b. The schema of our database has evolved over time, too, which has made this tough to view a consistent representation of things even if the fields storing that data were renamed. When the system was first introduced, we had ~5 columns to track. Now we have over 30. Suffice to say if I were to do things over again, I would implement a much better change tracking system that bakes in tools to clean/erase/undo/soft-delete/hard-delete mistakes so that future me (now) wouldn't have so many edge cases to deal with in this time traveling system. I'd also like to just make the change tracking capable of time travel itself, versus building that as a bolt-on side table that tracks and works from the change table. Transitioning to an EAV (entity-attr-value) approach is on my spike list, too. Makes it easier to just reduce (key,val) tuples down into an up to date representation versus looking at diffs of before/after. Really interesting stuff. I learned a lot about this from Clojure/Datomic and think its quite neat that so many Clojurists are interested in and tackling this problem. As the author notes in this post, XTDB is another one.
- adityaathalye 1y ago
  For my little system (blog author here) I've decided that all tables will be append-only logs of facts. In the post, I tried keeping traditional schema alongside a "main" facts table. Of course, the problem of audit logs comes up for any change made to traditional "current-database-view" tables. And then who audit logs the audit log? I feel like "two systems" is the problem. Writes should either be traditional schema -> standard log of all changes, OR, should be "everything is a log", and the system keeps the "current" view updated, which is just a special case of the "as of" query, where "as of" is always "now". How badly my schema will behave (in my SQLite-based architecture) is to be discovered. I will hopefully be in a position to run a reasonable benchmark next week. A follow-up blog post is likely :sweat-smile:
thom 1y ago
Perhaps because of the domains I work in I continue to find bitemporality completely uninteresting. Stop trying to make fetch-as-of happen.
xanth 1y ago
Shutout to XTDB, super friends folks and awesome tech to boot https://github.com/xtdb/xtdb https://github.com/xtdb/xtdb
sergeyprokhoren 1y ago
See "DSL for Bitemporal Sixth Normal Form with UUIDv7" https://github.com/sergeyprokhorenko/6NF_DSL https://github.com/sergeyprokhorenko/6NF_DSL
- adityaathalye 1y ago
  Oh, this is neat... thank you for sharing! Naurally, the "I don't know what I don't know" problem plagues me, as a solo maker trying to feel his way about this (temporal) space. Maybe it's time (hehe) someone started a 6NF Conf.
fulafel 1y ago
Tangential but > Git simply refuses to transact the conflict into the live state of the codebase, until someone a) fixes the conflict manually and b) tells git that the conflict is resolved. This is something that a lot of people seem to forget or maybe never knew, they think that the git cli's merge heuristics are somehow sound and go all YOLO with rewriting history with maybe-correctly merged code (rebase). Sometimes the same people who voice loud objections about LLM coding assistants...

172 points by adityaathalye 1y ago

It's a pity that Clojure is kind of a hermetic space these days, because the concept of bitemporality really deserves much more attention. It's amazing how often you want to know "What was the P&L for March using the data available on Apr 4?" and how uncommon it is to find a database design that supports that kind of query.

galkk 1y ago
Spanner does that with ease (I worked there, so I’m clearly biased). https://cloud.google.com/spanner/docs/timestamp-bounds#exact_staleness https://cloud.google.com/spanner/docs/timestamp-bounds#exact...
- refset 1y ago
  That only covers the 'transaction time' axis though? And the page says retention is limited to 1 week. No doubt useful for some things, but probably not end-user reporting requirements.
lemonberry 1y ago
"It's a pity that Clojure is kind of a hermetic space these days" If you don't mind sharing, I'm curious why you feel this way.
- deleted 1y ago
  [deleted]
- jonahbenton 1y ago
  We hermits enjoy its use in our lairs, worlds of pure thought.
necubi 1y ago
It's not particularly rare in analytical databases/query engines, nearly all of which support AsOf joins these days, including * Clickhouse * DuckDB * BigQuery * Snowflake * QuestDB
- refset 1y ago
  AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns. In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.
  - sterlinm 1y ago
    This is the book I like for an overview of the problem. https://www.amazon.com/Temporal-Relational-Kaufmann-Management-Systems/dp/1558608559 https://www.amazon.com/Temporal-Relational-Kaufmann-Manageme...
0_gravitas 1y ago
'Hermetic' is an interesting word-choice, considering Clojure has a habit/raison detre of attaching to other host languages/ecosystems i.e. Jank (C++), ClojureScript (JS), Basilisp (Python), Phel (PHP), Babashka (general scripting), and of course, Clojure itself on the JVM.
- Garlef 1y ago
  Sure. But the other way around? Why is there no JS client for datomic (that is not abandoned)?
  - monsieurbanana 1y ago
    datomic =/= clojure Clojure doesn't have much manpower, being a niche language and all, so maybe that's where your impression comes from. It's hardly on purpose, and there's many examples of software that ships with another language as public API. Rama is written in clojure but the main api is java Klipse is written in cljs but is marketed as a "javascript library" Even Datomic maintains a java api
  - fulafel 1y ago
    Rama[1] for example launched with Java API first. Probably for some reason Datomic just didn't get traction with JS users for some nontechnical reason. [1] https://redplanetlabs.com/ https://redplanetlabs.com/

refset 1y ago

tl;dr CREATE VIEW IF NOT EXISTS world_facts_as_of_now AS SELECT rowid, txn_time, valid_time, e, a, v, ns_user_ref, fact_meta FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY e, a ORDER BY valid_preferred DESC, txn_id DESC ) AS row_num FROM world_facts ) sub WHERE row_num = 1 AND assert = 1 ORDER BY rowid ASC; ...cool approach, but poor query optimizer! It would be interesting to see what Turso's (SQLite fork) recent DBSP-based Incremental View Maintenance capability [0] would make of a view like this. [0] https://github.com/tursodatabase/turso/tree/main/core/incremental https://github.com/tursodatabase/turso/tree/main/core/increm...

adityaathalye 1y ago
It is a poor man's database, after all :) I really need to complete this thing and run some data through it... like, how poor is poor really? Can it be just enough for me to make a getaway with smol SaaS apps?

nickpeterson 1y ago

I don’t understand footnote 16? It sounds like he’s saying he’s ceo of htmx and datastar?

adityaathalye 1y ago
Yes... This is a running joke in both communities. IYKYK :)
- nickpeterson 1y ago
  Oh ok, I’m out of the loop on it ;) thought it was some sort of ai hallucination.
  - adityaathalye 1y ago
    All eleven thousand words were hand-typed. No AIs are used or abused in the making of any of my blog posts. (Because I write to think, because writing is nature's way of showing me how sloppy my thinking is. So it goes...)
    - lemonberry 1y ago
      "Because I write to think" Hallelujah!!
      - lambduhhhh 1y ago
        this part (and this is how you know he's a true Clojurian)
    - fnordsensei 1y ago
      If you don’t mind a tangent, this is close to my way of working out thoughts as well, including with code. When I write code, it’s as much a cognitive tool as it is a tool to make things happen in the system. It develops thoughts as much as it develops system behavior. Involving AI changes this quite a bit, but I feel like I’m making my way to a balance where it supports rather than replaces (or worse: disrupts) my cognitive processes.

whalesalad 1y ago

I've been absolutely waist deep in a bitemporal system on top of PostgreSQL using tstzrange fields. We manage an enormous portfolio of hundreds of thousands of domain names. Every time our core db is modified, before/after states are emitted to a change table. We've been doing this since 2022. Those changes get lightly transformed via trigger into a time travel record, with the valid from/to range and a gist index to make asking questions about the state of the world at a particular point in time easy. For perspective our change table has 90M rows. All of it works quite well and is decently performant. We can ask questions like, how many domains did we own on March 13th, 2024? Or look at the entire lifecycle of a domains ownership (owned, released, re-acquired, transfered, etc). The big challenge and core issue we discovered though is that our data sucks. QAing this new capability has been a moving target. Tons of mistakes over time that were partially undone or manually undone without proper audit trail. Ghost records. Rapid changes by our bulk editor tool a->b->a->b that need to get squashed into just a->b. The schema of our database has evolved over time, too, which has made this tough to view a consistent representation of things even if the fields storing that data were renamed. When the system was first introduced, we had ~5 columns to track. Now we have over 30. Suffice to say if I were to do things over again, I would implement a much better change tracking system that bakes in tools to clean/erase/undo/soft-delete/hard-delete mistakes so that future me (now) wouldn't have so many edge cases to deal with in this time traveling system. I'd also like to just make the change tracking capable of time travel itself, versus building that as a bolt-on side table that tracks and works from the change table. Transitioning to an EAV (entity-attr-value) approach is on my spike list, too. Makes it easier to just reduce (key,val) tuples down into an up to date representation versus looking at diffs of before/after. Really interesting stuff. I learned a lot about this from Clojure/Datomic and think its quite neat that so many Clojurists are interested in and tackling this problem. As the author notes in this post, XTDB is another one.

adityaathalye 1y ago
For my little system (blog author here) I've decided that all tables will be append-only logs of facts. In the post, I tried keeping traditional schema alongside a "main" facts table. Of course, the problem of audit logs comes up for any change made to traditional "current-database-view" tables. And then who audit logs the audit log? I feel like "two systems" is the problem. Writes should either be traditional schema -> standard log of all changes, OR, should be "everything is a log", and the system keeps the "current" view updated, which is just a special case of the "as of" query, where "as of" is always "now". How badly my schema will behave (in my SQLite-based architecture) is to be discovered. I will hopefully be in a position to run a reasonable benchmark next week. A follow-up blog post is likely :sweat-smile:

thom 1y ago

Perhaps because of the domains I work in I continue to find bitemporality completely uninteresting. Stop trying to make fetch-as-of happen.

xanth 1y ago

Shutout to XTDB, super friends folks and awesome tech to boot https://github.com/xtdb/xtdb https://github.com/xtdb/xtdb

sergeyprokhoren 1y ago

See "DSL for Bitemporal Sixth Normal Form with UUIDv7" https://github.com/sergeyprokhorenko/6NF_DSL https://github.com/sergeyprokhorenko/6NF_DSL

adityaathalye 1y ago
Oh, this is neat... thank you for sharing! Naurally, the "I don't know what I don't know" problem plagues me, as a solo maker trying to feel his way about this (temporal) space. Maybe it's time (hehe) someone started a 6NF Conf.

fulafel 1y ago

Tangential but > Git simply refuses to transact the conflict into the live state of the codebase, until someone a) fixes the conflict manually and b) tells git that the conflict is resolved. This is something that a lot of people seem to forget or maybe never knew, they think that the git cli's merge heuristics are somehow sound and go all YOLO with rewriting history with maybe-correctly merged code (rebase). Sometimes the same people who voice loud objections about LLM coding assistants...