Clojurians Log v2

Well, uh, the "https://clojurians.slack.com/archives/C1Q164V29/p1749033270154529?thread_ts=1746178509.141159&cid=C1Q164V29" got a little out of hand, and turned into a... https://clojurians.slack.com/archives/C8NUSGWG6/p1752517643294549... essay, let's say. Please feel free to complain about bad takes, errors, and category errors, which I will not bet against having made.

Hm, running a window function over 10M records ... isn't a great idea.

OMG... I'm doing it wrong, but also right, but also wrong... brb fixing it

That window function thing in the "now" read path is the problem but it's also the solution... a problem for for live queries, but the solution to trashing and restoring the "current" materialised view wholesale.

So, seen this way, ~ 15 seconds to fully materialise a "now" snapshot of a 10M record append-only database isn't too shabby for a one-time snapshot restore!

does xt2 still need kafka for the log?

not necessarily, we do also support in memory and local disk - but it's currently the only distributed log available, yes

(and also as much as we can that's Kafka API compatible, which gives a fair few options if you don't want to run actual Kafka yourself)

Are there any other games in town (other than Kafka-like things) that could be used, that might simplify a production-grade setup?

(by which I mean, could XT build or use something completely different that serves the same purpose?)

the main constraint is the totally-ordered log, in that all consumers need to agree on the message order

I'd really love to use something like xt but the managed (or if not managed the operation) costs of kafka are just prohibitive

right, yeah, understandable 🙂 we have been looking at implementing a totally ordered, distributed log ourselves, but tbh that's only really a pipe dream/hack-day PoC at the moment

Tansu may be worth a try if anyone is curious https://discuss.xtdb.com/t/deployment-recommendations-for-small-apps/483/7

we're also keenly awaiting https://cwiki.apache.org/confluence/display/KAFKA/KIP-1150%3A+Diskless+Topics, of course 🙂

Adding for context, notes to crib from, to reify Henderson's Tenth Law into this SQLite-backed SaaS app, preemptively, well before it becomes "any sufficiently complicated data system" 🤪). https://clojurians.slack.com/archives/C8NUSGWG6/p1747043692087469

Already I realise why I can't make :transaction_time a primary key in my "world facts" table.

you should post this on some blog -- hackernews will treat you like the second coming of aphyr

soon... this thread has been forming through org-mode notes and off-the-cuff commentary, which eventually finds its way into the wild wild web as blog posts... previously: Poor man's bitemporal data system in SQLite and Clojure (http://evalapply.org) https://news.ycombinator.com/item?id=45118585 172 points by adityaathalye 4 months ago | 54 comments

Okay, I think I can live with this... • "facts" table: 12M+ records ◦ single process writes to it ◦ ~ 400 transactions/second ◦ append-only table, enforced via SQLite "before" triggers • "now" table: ◦ updates on every assert/redact on "facts" table, via triggers ◦ currently at "limit case": for each read it is empty, or very small, because writes do back-to-back assert/redact of the same fact ◦ gets reads from two reader threads (evenly split) ◦ ~41,000 reads/second • all reads are concurrent with writes (poor man's futures)

user> (pmap deref
            (conj (mapv #(->read-proc %)
                        (range 1 (inc num-readers)))
                  (write-proc))))

=== Kicked off bench. 990000 reads from 2 reader threads, And 10000 writes from writer thread. ===
started writer proc
started read proc no:  2
started read proc no:  1

"Elapsed time: 12014.970873 msecs"
finished read proc no:  2

"Elapsed time: 12091.609606 msecs"
finished read proc no:  1"

Elapsed time: 24522.448975 msecs
"finished writer proc
user> (/ 10000 (/ 24522.448975 1000))
407.78961392456114

user> (/ 990000 (/ (+ 12014.970873 12091.609606) 1000))
41067.62470365385

user> (time (read-facts! (#'user/system-state) ::app {:select [[[:count :*]]]}))
Elapsed time: 182.61717 msecs
{:app [{:COUNT(*) 12802250}]}

And it shows 400 Tx/sec 1,600 Tx/sec throughput for sequential assert-read-redact-read cycle (because I'll be running the app on the cheapest-available VPS).

started proc doing assert facts -> read now -> redact facts -> read now for 10000 iterations.
"Elapsed time: 23342.93597 msecs
"finished sequential assert-read-redact-read proc
user> (* 4 ;; transactions for each iteration
        (/ 10000 ;; iterations
          (/ 23342.93597 1000) ;; seconds total run time
       ))
1713.5805046720523

The single-process behaviour is what really matters to me because I want to run this thing on puny boxen. So let's say I'm wrong by 10x in this measurement---or worse---100x wrong. This still puts the app at between 10 to 100 immediate-read-after-write Txns/sec on a single-tenant-per-DB basis. And I feel like Tens of Tx/second is more than most B2B SaaS apps, or internal business apps see, on a per-tenant basis.

Rationale forthcoming. Our customer support department thanks you for your patience and continued patronage.

I feel like the beginning is tailored for Sean, given where he works. :D

Riffing off relationship advice subreddits :p

But yes, if anyone could help, it would be Sean, being in the very niche business of marrying SQL with human relationship-making :)

Just in case, since you mentioned you don't know what you don't know - SQLite has a lot of limitations that, as I've learned, a surprising amount of people doesn't know about. Well, everybody knows some specific limitations, but much fewer people know an extensive amount. Personally, I've been burned by its limitations enough times to just stop considering it from the get go. And I'm not talking about limitations that make you do a lot of extra work, like changing the type of a column. I'm talking about things that prevent you from doing something in any sort of reliable manner altogether.

For me, SQLite's "flexible types" are an advantage... I'm actually banking on this.

Same for migrations --- I am planning for append-only migrations of the World Facts table. All derived tables must be re-build-able at will.

And in terms of operations, WAL mode + litestream for hot-standby server.

> SQLite's "flexible types" Well, I also don't know what I don't know. :) Or maybe I understand something else by "flexible types". How is it different from creating a type alias in PostgreSQL, for example? something like col MY_TYPE in SQLite will still use plain text, and you can have col MY_TYPE in PostgreSQL as well, with plain text being the actual storage type.

SQLite authors themselves explain... • https://sqlite.org/flextypegood.html • https://sqlite.org/quirks.html

What if you want your Value column to be serialised EDN? • #inst <the thing> • #double <the thing> • [:x, :y, :z] ?

In SQLite, this would be just "TEXT", and it is my headache to craft the appropriate ser/des code, which I am fine with.

Ah yes, that reminded me - if you use something like INTERVAL for the column type, the actual type will be INT. shudders > In SQLite, this would be just "TEXT". Exactly the same thing you can achieve in PostgreSQL by creating a custom domain. It will be col EDN but you will get plain text in your app's data. No need for custom encoders/decoders, but the type name is still accurate.

> Ah yes, that reminded me - if you use something like INTERVAL for the column type, the actual type will be INT. shudders Yes, and other such gotchas --- all very well documented!

Indeed. Well, as long as you're aware and can maintain that awareness. :)

Yup.. the SQLite manual is always at hand (same for PG, if I'm using PG). All DBs have sharp edges and constraints, and my memory is garbage, so I have developed a habit of compulsively referring to the manual before I make any new choices.

Anyway, I think my "small software" needs will remain small enough that I can take a week (or a few) to refactor away from SQLite catastrophy to PG or something. The only thing I want to ensure is I never lose data, and that the DB doesn't do coercion magic. SQLite will happily store a record as TEXT in a column type-hinted INT (because I named it INTERVAL). Later I can read it back correctly using the appropriate function(s). (i.e. it will never take the Interval and try to force it into an Integer, if it does not correctly parse as Integer.)

So yes, application logic has to be carefully written. I am willing to take that burden, in return for operational simplicity and other business advantages.

happy to help out with the implementation if it's a learning exercise, of course 🙂 but just to clarify the constraints - if your constraints are instead "bitemporal SQL and in-memory database" (i.e. using SQLite because it's easy to set up rather than a requirement for SQLite specifically), I'd check out #xtdb 2 (disclaimer: I'm obviously biased 😄) > SQLite's "flexible types" are an advantage we're fully dynamic, Clojure-style - put whatever you want in documents/tables. > that the DB doesn't do coercion magic we don't - values are round-tripped as whatever type you put them in > What if you want your Value column to be serialised EDN? we store a superset of EDN, and then round-trip it back as the same EDN (i.e. you won't know that we don't store it as EDN underneath)

re your impl: > • A "WORLD FACTS" table emulates a durable immutable log of assertions. > • Other tables emulate a "Current Database" lens over the WORLD FACTS log > ◦ These are generated using triggers > ◦ These are Real and Virtual tables > • Read/write model: > ◦ Only priviledged readers/writers can touch the "WORLD FACTS" table. > ◦ All application-produced Writes go to the WORLD FACTS table. > ◦ All application-originated reads are sourced from the trigger-generated "Current Database". fwiw, this isn't too dissimilar to what we do, so assuming we're on the right lines then you are too 🙂 one slight difference is that we choose not to materialise the current bitemporal state of the world (your triggers) - instead, we have filters that quickly reduce the facts log down to only the facts relevant to the current query. a large part of that filter is partitioning that table s.t. we can quickly rule out whole partitions of data without reading them, particularly for common (e.g. as-of now) queries

so, looking at your r/w model: > • Only privileged readers/writers can touch the "WORLD FACTS" table. yep, same > • All application-produced Writes go to the WORLD FACTS table. yep > • All application-originated reads are sourced from the trigger-generated "Current Database". sort of the same - except we don't do this with triggers, it's built-in to XT at a low level (as you'd maybe expect, being a bitemp db 🙂)

> And in terms of operations, WAL mode + litestream for hot-standby server. yep, definitely, good idea - we've gleaned a lot of value from https://martin.kleppmann.com/2015/11/05/database-inside-out-at-oredev.html, having the WAL front-and-centre

For ids, also consider Snowflake IDs. They are simple to implement and give you good knobs for tuning it for your usecase, and are roughly half the size. If you put UUIDs as your primary key, it will bloat the size of all of your indexes.

Since I was sort of invoked in this thread, I don't have anything to add: I share Eugene's concerns about SQLite 🙂 and I have nothing to add about bitemporal design beyond what James said 🙂

@jarohen echoing the off-topic thread... Once again... wow, thanks for all of the above. I'm so relieved to hear from someone who is biased for all the right reasons 😀, because I've been groping my way through the dark, trying to work things out from first principles. I'd have picked XTDB in a heartbeat, but for a) A domain constraint I want to respect, for the SaaS I'm trying to build (even though it's just an old-skool forms and CRUD app), b) an itch I want to scratch, for which SQLite is a good fit (single tenant architecture), c) I can only use a widely-understood SQL dialect, again for business reasons. Aside: I don't want an in-memory store per se, but I do want a durable in-process one.

I'll take a look at Snowflake IDs.

I just want to say that I"m a relative DB n00b. So your input has helped more than you can imagine.

understood, yep - just to clarify a couple of those: a) fair 🙂 b) XT is still embeddable in-process, so you can have as many tenants as you need - we have people with one XT node in their process, and some finding it lightweight enough to use it with one node per tenant in a multi-tenant process c) XT 2 is SQL:2011 compliant, and where we're not we use Postgres's dialect as a de-facto standard - we use SQLite's test suite so we're pretty confident in its completeness, but if there's something missing, give us a shout 🙂 we're Postgres wire-compatible too now, so you can connect through Postgres's JDBC driver, or any SQL tooling that understands how to connect to Postgres > Aside: I don't want an in-memory store per se, but I do want a durable in-process one. yep, embedded XT can persist to local disk too as well as being in-mem

instead, we have filters that quickly reduce the facts log down to only the facts relevant to the current query

I'll admit I also haven't done/read a great deal on the local-first apps side, will defer to experts there 🙂

Which is the long way of saying, "yes, I'm in favour of ephemeral / on-demand views" over the durable log.

I've been speculating that it's important to keep the following distinct and independent:

SELECT ... FROM foo; -- current time
SELECT ... FROM foo [FOR VALID_TIME AS OF ...] [FOR SYSTEM_TIME AS OF ...]
SELECT ... FROM foo [FOR ALL VALID_TIME] [FOR ALL SYSTEM_TIME]

> c) XT 2 is SQL:2011 compliant, > ... > yep, embedded XT can persist to local disk too as well as being in-mem Oh! I should look again. I confess to not having given XT a fair shake.

> • And all schemas be "just data" which can be shipped independently of the durable log. yep, agreed - we take the EDN/Transit view on this one in that the transport format (Apache Arrow, in our case) is self-describing - like Clojure, the client deals with the data it's given, rather than being tied to a schema

heh, no worries - there's so much going on in our industry, no-one can read everything 😅 but yeah, if you do give it a go and have any questions, can always find us over on #xtdb xtdb

> happy to help out with the implementation if it's a learning exercise, of course Thank you for saying this. But the work is for a commercial-ish purpose (which will probably make no money, and will be a grant-funded thing at best). So I'll say this is more of a learning the hard way exercise 😅

but yeah, if you do give it a go and have any questions, can always find us over on #xtdb

> A domain constraint I want to respect right now XT doesn't offer stateful, db-wide constraints, but you can express constraints during each transaction in the form of ASSERT statements (and the onus is on you to include them with every transaction if you want to enforce some constraints globally to prevent 'invalid' writes)

@vale referencing back to the UUIDv7 thing... an explanation about how I'm https://www.evalapply.org/posts/poor-mans-time-oriented-data-system/index.html#realities-are-arrows.-time-marks-flight.-uuidv7-is-time. that claim to have occurred at the same wall-clock time. This, in turn, relies on everyone having the same standard valid-time-id generator algorithm, no matter the language or computing environment they are sending facts from.

XT1 used clock-time for transaction identification for too long, clock skew stuffed us - was a good day when I migrated that to use incrementing ints for transaction ids 🙂

there was one issue in particular where two transactions raced in Postgres (don't recall whether SQLite does the same thing), the one that minted the clock time first took longer to insert, so the second one got indexed and the first one was effectively lost

(even auto-increments in Postgres are vulnerable to that one, because they aren't serialised, even in a serialisable transaction)

so yeah, using something that's designed for total ordering there was a great simplification

So, inside the database, I'm using SQLite's built-in auto-increment rowid for all joins and cross-reference type things (e.g. return facts matching full text search). All tables already have id unless I force them to be "without id" tables (not a good idea in general).

I'm using the UUID for disambiguation + wall-clock time (untrusted).

A plus point of having cut teeth in ops is I am afraid of clock skew 😅

(defn search-world-facts-as-of-now
  "Run the given search query against the FTS table and return a
  match from the original world_facts table."
  ([where-search-clause-raw-sql]
   (search-world-facts-as-of-now (partial format "fts_world_facts_as_of_now MATCH %s")
                                 where-search-clause-raw-sql))
  ([search-term-formatter where-search-clause-raw-sql]
   (hsql/format
    {:select [:world_facts.*]
     :from [:fts_world_facts_as_of_now]
     :join [:world_facts [:= :fts_world_facts_as_of_now.rowid :world_facts.rowid]]
     :where [:raw (search-term-formatter where-search-clause-raw-sql)]
     :order-by [:rank]}
    {:inline true})))

are you expecting those tx_ts to be totally ordered?

No, only globally unique ... as far as I'm concerned, all wall-clock time is untrusted. It's only meaningful to fulfill some search query according to someone's expectation of the state of the world

can't even expect a stable sort order from monotonic UUIDs ... no idea how they are sorted

I'm still feeling my way through the problem space... Where I think the UUIDs will help. Say I have facts-db1, facts-db2, ... facts-dbN (maybe monthly ledgers). At some point I want to mapreduce across all of them --- make a filtered-facts-db for some analysis. In such a case, all facts conform to the same schema and are guaranteed to not collide while merging. After that, it's up to the user to figure out how to make sense of the merged facts.

There is no way to guarantee total ordering across DBs, but I can guarantee non-colliding records.

that's my current way to work around the fact that sqlite is a mutable store ... it's a poor man's bitemporal db after all 😅

wait i hope i haven't written "total order" in the post... brb

phew, no i haven't ... "globally unique", yes ... "totally ordered", no

been musing about it in the dev notes...

Hair loss is happening ... but of course I am being forced to make a Content Management System. "Everything is a tree", it seems.

I suppose it's a rite-of-passage

Shower thoughts: Ah yes, SQLite's "flexible typing" has its merits. Certainly. Real life: Meanwhile, the muddling [0] and the noodling [1] continues... [0] VACUUM The Ultimate... https://clojurians.slack.com/archives/C1Q164V29/p1748712353212049 [1] When your commit messages are screeds, or blog posts, or tweets... https://gist.github.com/adityaathalye/8a76b38dd17288555d67fd745a4ab383

Thought for the day: "One person's transaction_time is another person's valid_time". Especially when "one person" is you getting audited, and "another person" is the auditor. Facts are true only when the auditor signs off that they are true.

So, in the event of conflicting facts for any given entity, "my" db should know which one of the fact-set over tx_time x valid_time have I chosen (resolved) to represent "my" truth. However, even if tx_time=valid_time in my database, when I send the chosen fact over to the auditor, they must necessarily treat my time as a claim (i.e. validtime in their system)_, not truth. Because they may be trying to disambiguate a global order book on which many concurrent claims exist for a given fact.

--- narrator: And so, the poor sod plods on, unwittingly, uncomprehendingly of just how deep this rabbit hole goes. narrator 2: cue: Tennyson's The Charge of the Light Brigade. (Except in (very) slow motion.)

Let's just say, I'm beginning to believe in uuidv7. (That, or a better, globally unique, indexing-friendly composite time identifier.)

wasn't the snowlake id optimized for that?

I'm using whatever I have access to in SQLite. (Also, as far as I can tell, there is no "the" snowflake ID. It's more like a concept with all sorts of implementations. Which is also why I'm using uuidv7 --- it's simple and easy to understand.)

And my bets are hedged... if I'm wrong about uuidv7, then I'll switch to something else.

Generally-speaking, I have two thoughts (correct me if I've misunderstood) about Snowflake-style uids, that make them unattractive to me. 1. Encoding machine information (the data emitter) in identifiers feels like a double-edged sword. Inside one's own system I can see how a trace to the exact machine (or process) can be useful. However, as soon as the identifier is sent out into the world, it has leaked something about the inside of one's system. 2. One can't reeeeally distribute a Snowflake value in an open-ended, forward-and-backward-compatible way, because they are not standard. All parties consuming my snowflake ID must be aware of which generator / checker scheme I am using. With something like uuidv7, people can observe a global standard and build open systems.

don't uuids also contain the machine id / hostname?

I suppose that is implementation-dependent (based on what I understood from the RFC 9562). Regardless, the format is a standard, unlike Snowflake and the other vendor-specific uid formats. Anyway, at this point I only care about ease of use. The ID choice will become significant when it becomes significant... I just want to "get away with it", for now 😅

Protagonist... (Downtown. Smoking a hypothetical on a grimy corner, looking askance from beneath the off-kilter hat. His galoshes damming a little rivulet on the gray pavement. The passing masses are returning home after the day's labour in the data mines... Trenchcoats drawn tight, hands in pocket, hurrying. Faceless under black brollies). "It's always the right term in the wrong place. Makes all the difference. Fools you into thinking you have the right answers." "Look at these people. Everyone here. Walking around with broken assertions in their heads. They have no idea. All it takes is to pause a bit. A hammock and a nap maybe. And just like that, you see it. It was right there all along, staring you in the face. The obvious mistake. But they don't understand. Never will. Moving too fast." <draws one more breath> <stubs out the hypothetical> "What was broken was Git reverted. And the world keeps turning as if nothing happened." <turns to walk back up the stairwell to his backroom office, where the door shuts and there are no open plans> "The work? ... Hardly revolutionary. Just old fashioned following the readouts and the traces. Night after night. Looping. Refactoring. Testing. Not knowing. Waiting. See, you got to let understanding arrive in her own way. She has graced others with her presence before. Loves arriving fashionably late. Can't be pushed around, that one. Like she did at 3 a.m., unannounced. Didn't even have to say anything. Looked me dead in the eye. Placed the envelope on the standing desk and left. Just like that. There is no mystery now. Tonight will be a long night. But satisfying."

Interesting to follow your journey, thanks for sharing! Which extensions are you using in particular?

Thanks for following along, and all the generous feedback! You've been helping more than you know... Time travel is a lonely business. None of my friends understand why I don't just make a normal db with normal audit trails.

For now, text and uuid from sqlean: https://github.com/nalgeon/sqlean (but might end up using some crypto functions too).

> all the generous feedback ... time travel is a lonely business Yesterday, I asked the GPT to make stuff up based on what little I understand of the subject. It generated pleasant remarks about me wanting to use uuidv7 in my schema --- I feel great about using an "advanced modern approach". https://gist.github.com/adityaathalye/4a9c8ab37c11abb350a3d2c60eef4135#prompt-2 > Thus generat'd the GPT: > > Using UUIDv7 for time values (instead of DATETIME) in your bitemporal database is an advanced and modern approach. UUIDv7 encodes timestamps while maintaining ordering, making it suitable for time-based keys in distributed or immutable systems.

Now, if only it pointed me to the source material, so I could educate myself...

why uuid rather than datetime, sorry? think I missed some context there. assuming you mean for IDs rather than the time values themselves (looks like AI has put them as the time values?)

oh, ignore the GPT ... stream of consciousness

I figured as much, yep 😊

yeah, wouldn't advise UUIDs in place of time cols 😅

I told the GPT to use uuid7, because I want to use uuid7... I'll share notes on why (which I'm not certain about ... still experimenting)... a blog post is slowly appearing in the brain dump.

(I started typing a TL;DR and it's becoming blog length... so, will share the post when it arrives.)

Thanks for the admonition about uuids in place of time cols!

well, maybe premature on my part, just my initial pre-coffee impressions 🙂 what properties were you looking to take advantage of?

Well... in the process of solving other problems. I think I've discovered a (much) better "now" query... Still dog slow for live queries, but it's probably as fast as it gets, to re-materialise the latest "now" state from the whole "facts" state. Could even be good enough to trash and rebuild the whole "now" table on-the-fly, with some degradation to the service. This is still a "napkin benchmark" on that synthetic 10M record table. This was the only thing going on in the db.

sqlite> select count(*) from facts_app_tbl; 
QUERY PLAN `--SCAN facts_app_tbl USING COVERING INDEX facts_app_tbl_idx_E
 | count(*) |
 |----------|
 | 10460585 |
 Run Time: real 1.999 user 1.948861 sys 0.049995

sqlite> insert into now_app_tbl select ea, rowid, txn_time, valid_time, v, user_ea_ref, fact_meta from facts_app_tbl where rowid in (select rowid FROM (SELECT ea, rowid, row_number() OVER (PARTITION BY ea ORDER BY txn_time DESC, valid_time DESC, asserted DESC) AS row_num FROM facts_app_tbl WHERE asserted = 1) where row_num = 1);
QUERY PLAN
|--SEARCH facts_app_tbl USING INTEGER PRIMARY KEY (rowid=?)
`--LIST SUBQUERY 2
|--CO-ROUTINE (subquery-1)
|  |--CO-ROUTINE (subquery-4)
|  |  `--SEARCH facts_app_tbl USING INDEX facts_app_tbl_idx_70b4afa1_v2 (asserted=?)
|  `--SCAN (subquery-4)
`--SCAN (subquery-1)
Run Time: real 2.520 user 2.451799 sys 0.067036'

sqlite> CREATE INDEX facts_app_tbl_idx_70b4afa1_v2 ON facts_app_tbl(asserted DESC, ea, txn_time DESC, valid_time DESC);
Run Time: real 11.055 user 6.942960 sys 3.902272

It remains to be seen whether the revised query is correct, of course (eyeballs say yes ... tests are pending). But this does give me an upper and lower bound for time cost of full-state rebuilding.

Narrator: no it is not correct.

Was the Y2K debacle one of two-digit year rollovers, or was it a layer deep, a debacle of overwriting records in current databases, because that's the world-model current database technology... normalises (haha).

Idle thought triggered by episode two of the delightful "Futility Closet" podcast, during this afternoon's traffic-infused commute.

Napkin-math benching this now... The "Poor Man's Bitemporal SQLite"'s performance is ... poor (big surprise). But not unsalvageable?

2 Million total write transactions. One assert immediately followed by one redact. Append-only. No concurrent reads. Each transaction appends 5 records. So, total 10M records in the DB.

sqlite> select count(*) from facts_app_tbl;
QUERY PLAN
`--SCAN facts_app_tbl USING COVERING INDEX facts_app_tbl_idx_EA
| count(*) |
|----------|
| 10006790 |

x-posting: https://clojurians.slack.com/archives/C0FVDQLQ5/p1756676408229249?thread_ts=1755687862.647539&cid=C0FVDQLQ5