←back to thread

Poor man's bitemporal data system in SQLite and Clojure

(www.evalapply.org)
169 points adityaathalye | 9 comments | 03 Sep 25 17:47 UTC | HN request time: 0s | source | bottom
Show context
whalesalad ◴[03 Sep 25 18:55 UTC] No.45119238[source]▶
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.

replies(4): >>45119379 #>>45119468 #>>45120038 #>>45123316 #
1. adityaathalye ◴[03 Sep 25 19:07 UTC] No.45119379[source]▶
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:

replies(2): >>45119774 #>>45122749 #
2. whalesalad ◴[03 Sep 25 19:52 UTC] No.45119774[source]▶
> OR, should be "everything is a log",

100%. This is a regret that I have in our lifecycle tracking. We effectively are updating a giant spreadsheet (table) and tracking side effects of those updates. I would much rather work in a log/event based system kinda flipping that on its head, where we track events like domain_renewed or domain_transferred.

As it stands we track that the renewed_at attribute changed, and have to infer (along with other fields) things like "were you a renewal event?" which has changed meaning over time.

Alas change tracking wasn't even part of the spec of this original feature... so I am glad I had the foresight to institute change tracking from 2022, as imperfect as it is currently.

replies(1): >>45119815 #
3. adityaathalye ◴[03 Sep 25 19:59 UTC] No.45119815[source]▶
So much this!

Straight-up copy-paste from my development notes...

* what pivots in current db vs temporal db?

What is the difference between these pictures?

#+BEGIN_SRC text

  ("Current" DB)

   CRUD records
         ^
         |
         v
  [ current view ] -- update --> [ facts log ]

  ---------------------------------------------------------

  [ current view ] <-- update -- [ facts log ]
         |                             ^
         v                             |
    READ records             WRITE records (as facts)

  ("Temporal" DB)
#+END_SRC

- Hint: It is /not/ the schema. It is /what/ "pivots".

- In both cases the current view can be understood to be a pivot table of the facts log.

- BUT in the current DB, we must "pivot" the /process/, i.e. take a CRUD op and transform it into an audit log. This /must/ be done synchronously in in real-time. Whereas in the Temporal DB's case, we must "pivot" the stored data, which we can do at any point in query time, as of any point in time of the log.

- The complexity of Current DBs derives from /live process management/ challenges. Whereas the complexity of Temporal DBs derives from /retroactive process management/ challenges.

/now/ is /never/ cheap. It is the most expensive non-fungible thing. Once it's gone, it's gone. Fail to transact an audit trail for the CRUD operation? Too bad. Better luck next time. Whereas disk space is cheap, and practically infinite which affords Temporal DBs greater opportunity to find a better trade-off between essential complexity and DB capabilities. At least as long as disk space remains plentiful and cheap.

This is why if we are modeling a Temporal DB over a Current DB, it is preferable to write all tables as /fact/ tables and query their auto-generated 'current view' versions, for normal query needs. For audit / analysis needs, we can snapshot the facts tables and operate on those out-of-band (detached from the live app). Impedance mismatch occurs when trying to run /both/ current CRUD tables (writing to audit logs) for some parts of the schema, in parallel with a "main" facts table for all fact-records. In a given data system, it is better to do either one or the other, not both at the same time.

replies(1): >>45120108 #
4. mamcx ◴[03 Sep 25 20:35 UTC] No.45120108{3}[source]▶
The problem with `everything is a log` is that is very undisciplined, and trigger from the actual main table have this very serious advantages:

* Your main table has the correct shape, and the derived log too! * MOST of the queries are to that and for now

replies(1): >>45123904 #
5. bbkane ◴[04 Sep 25 02:14 UTC] No.45122749[source]▶
Looking forward to the second blog!

As a side project, I'm writing a CLI to tie environment variables to project directories ( https://github.com/bbkane/enventory ) and share env vars between projects with a "reference" system. Similar to direnv but in a central SQLite DB.

See https://github.com/bbkane/enventory/tree/master/dbdoc for how the schema looks, but it's a few tables with foreign keys to each other to support the "reference" system.

Unfortunately those foreign keys mean I don't have a way to "undo" changes easily. So instead I added a confirmation prompt, but I'd still like that undo button.

Hopefully in subsequent blog posts you can follow up on your "Model an example domain of sufficient complexity" TODO and make this a bit less abstract (and hopefully more understandable) to me.

replies(1): >>45123138 #
6. bbkane ◴[04 Sep 25 03:15 UTC] No.45123138[source]▶
Maybe I could convert every table into a "log" table (i.e., append only similar to your blog) and add a timestamp column to each table (or UUID). Then I also store all of these UUIDs in another table to keep track of the global order. To "undo" I look up the previous UUID, find entries with that UUID, and append to the log table the values that make the state the same as the previous UUID....

This isn't as general as your setup, but I think it would work ok with my foreign key issue.

replies(1): >>45124823 #
7. adityaathalye ◴[04 Sep 25 05:33 UTC] No.45123904{4}[source]▶
Yes, if the log has no regular schema, and if the overall system has no regular design pattern.

This is the minimal "standard record" that one can use, for all facts pertaining to all domain entities in one's system:

  system_time, valid_time, Entity, Attribute, Value, assert/redact
Now the problem is:

- For small-middle-heavy usage (anything that (reasonably) fits on one machine) how to use existing database technology, so that one can store and query this stuff sufficiently fast. This is what I'm trying to do with SQLite... partly because the "V" of E/A/V benefits from SQLite's "Flexible Typing" system. In SQLite parlance, E, A are TEXT, and V is NUMERIC.

- For at-scale usage (anything that needs many machines), how to make a data system from scratch to store log-structured information efficiently, and how to query it efficiently. See what the Datomic, XTDB, and RedPlanetLabs/Rama people are doing. Essentially: separate storage and compute, event-source everything, and build a system from scratch to use it efficiently and fast at scale.

8. adityaathalye ◴[04 Sep 25 08:01 UTC] No.45124823{3}[source]▶
I believe UUIDv7 does not (cannot) guarantee total order. I am using it as a method to manage multiple timelines---each "fact" lives on its own virtual timeline (identified by a "valid_id", which is a UUIDv7). My DB system does not automatically disambiguate conflicting facts (because it cannot --- it simply does not have the meta-information). The users of the system can manually audit and fix inconsistencies by making correcting (append-only) entries.

For your case, I feel like your best bet would be to create the global order in the first place... i.e. force all writes through a single (and single-threaded) writer process, and fetch reads off one or more independent reader processes (I'm doing this with WAL-mode SQLite).

But I could be totally wrong --- this is my first foray into the freaky world of temporal data systems :D

replies(1): >>45130657 #
9. bbkane ◴[04 Sep 25 18:35 UTC] No.45130657{4}[source]▶
As this is a single-user CLI tool I don't need any concurrency. So I can probably use the timestamp directly instead of a UUID and not worry about multiple timelines.

On thinking more this morning I think I can keep my original tables and add append-only "log" tables with triggers to keep them up to date when the main tables change. That doesn't slow down my main tables performance (they only contain the current data) and also allows me to incrementally change my db (adding new tables + triggers is easier than changing existing ones).

I've made https://github.com/bbkane/enventory/issues/122 to track this idea, though I'm not sure when (or even if) I'll have time to really play with the idea (much less "productionalize" it- add support for all enventory events, tab completion on the CLI, tests, UI, etc...).

But I'm at least tracking it for when I get time and motivation! Thanks for writing the post and replying to my comment.