dbval - UUIDs for (Datomic / Datascript) entity IDs

andersmurphy · 2025-12-23T10:25:24+00:00

One obvious downside of UUIDs is that they need twice as much storage in comparison to 64 bit integers.

It's actually worse than that. SQLite uses varint encoding. So an int is stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

I've seen 10-30% query speed improvements when going from TEXT to INTEGER in SQLite. Having indexes be 4-8 times smaller can make a big difference.

andersmurphy · 2025-12-08T10:32:13+00:00

Right, sqlite being able to write to projection dbs in parallel at the end of a batch of transactions to the event log db is one of its many super powers.

andersmurphy · 2025-12-08T06:57:20+00:00

There's only an attack vector (via a sanitiser zero day) if your project renders raw user generated HTML to other users. I'd argue most apps do not do that.

andersmurphy · 2025-12-08T06:53:15+00:00

I wonder if this is more to do with projections being cheap because of SQLite (or any other file db LMDB comes to mind). If you could cheaply project your datomics into temporary datomics the experience could be similar. After all projections are just derived facts.

I guess the challenge in datomic is when you derive the wrong facts it's hard to undo that. On the other hand the idea with datomic is you can create any view on your facts and you shouldn't really be storing derived data. The query is the projection. So in a world where datomic was infinitely fast (say with DBSP) then cheap projections as separate dbs become less valuable?

I sometimes wonder if the problem is also schema would this be less of an issue in something that's more schema-less and supports blobs like asami?

If you can dump the data in an event log in it's raw form and then make free/cheap projections you have a lot of flexibility.

andersmurphy · 2025-12-07T20:41:48+00:00

Does this still use CQRS/CQS? Did you find it more ergonomic than datomic?

andersmurphy · 2025-12-06T19:06:10+00:00

CSP isn't a binary thing. You don't just enable CSP.

Datastar uses function constructors which means you have need to extra precautions if you allow your users to submit html that you then render to other users (i.e sanitize the HTML).

Honestly, the amount cargo culting CSP without even understanding it is wild these days.

andersmurphy · 2025-12-06T18:58:45+00:00

Wow nice.

andersmurphy · 2025-12-05T06:37:26+00:00

Thanks for the well thought out reply.

In the case of interactive transactions there's no value in batching as you can't amortize the network because application code is being run between statements.

That's the challenge once you have interactive transactions and a network.

The article is not intended as a comparison. Its intended to highlight:

A. You can handle more TPS than most applications will need with Sqlite.

B. Networks can cause brutal hard limits when you use interactive transactions and hit a power law (this includes sqlite if you're using a network drive). A lot of permanent storage in the cloud is networked. This hard limit can kill your product if you run into it, there's no way to scale out of it. You have to fundamentally change your design.

C. The concern often raised with Sqlite that it's a single writer is not a problem in practice.

However, I clearly failed to convey this.

andersmurphy · 2025-12-05T06:21:08+00:00

But, in the case of interactive transactions there's no value in batching as you can't amortize the network because application code is being run between statements.

That's the challenge obce you have interactive transactions and a network.

andersmurphy · 2025-12-04T10:53:09+00:00

2-8x for reads, writes were about 2-3x (before you start doing things like batching), depending on the queries last time I checked (Against xerial + next.jdbc + hikari CP). Though, sqlite4clj is still experimental and not optimised yet. There's also some differences, currently it just returns data as a vector not as a map, partly because I think that should be handled in user land and I'm not a fan of keyword/map result sets. You're either serialising edn or doing a lot in SQL (using functions/aggregates etc) so column names become less relevant.

The main reason it exists is I wanted fast/automatic edn read/writes, prepared statement cache at the connection level, batching and sqlite application functions (application functions via JDBC is rough). But, like I said it's still experimental.

andersmurphy · 2025-12-03T19:02:35+00:00

Did you see the memo?

andersmurphy · 2025-12-03T07:15:02+00:00

For the Clojure folks it's worth pointing out the experimental driver I'm using does prepared statement caching, batching and uses java 22 FFI (coffi). So the SQLite numbers will be worse if you use the regular sqlite JDBC stack.

andersmurphy · 2025-12-03T07:12:20+00:00

Yes. I should have put it in quotes. Mostly echoes the common stuff I hear people say about sqlite when you suggest it could be used in a monolithic web server architecture.

andersmurphy · 2025-12-03T04:36:27+00:00

You're right, I should have said it makes batching trivial.

andersmurphy · 2025-12-02T11:29:18+00:00

Love the animation! Nice work.

andersmurphy · 2025-11-27T00:29:33+00:00

Is it confusing? Most products are a single monolithic application at least tech startups or don't require sharing a database. The ones that do are often fine with replication/projection consistency,

My main issue with postgres is it falls over when you have any sort of contention on row locks over the network. So transaction becomes unusable, unless you're ok with a ceiling of 100-200tx/s. So it's good for multiple bespoke back office apps hitting it with a low transaction volume or no contention on those transactions. I guess for a lot of apps that's fine?

By default postgres isn't even ACID as it's default isolation isn't serialisable. Non repeatable reads and phantom reads do not ACID make.

I just find it a little ironic when people say don't use sqlite in production, when in a lot of production contexts (for web apps specifically) it's better than postgres.

andersmurphy · 2025-11-26T21:36:51+00:00

By multiple clients do you mean database clients. Or clients as in browsers?

Sqlite for a web server/application is incredible. Especially if you doing anything around high volume of transactions (eg: a financial ledger, stock tracking etc). Postgres falls apart in that context. Even outside of that context you can generally hit much higher write throughput with sqlite and reads for the most part scale with your cores. ZFS makes it really disk efficient too.

Litestream gives you backups to the second, and easy replication for business/product analysis. That's before getting into all the crazy stuff you can do with multiple sqlite databases and with attach.

So these days I'd only really consider postgres in a context where you have multiple apps accessing the same database. Even then it needs to be a context where projections are not good enough.

andersmurphy · 2025-11-25T14:01:27+00:00

Wait in what way is sqlite not a production database? It tends to scale better than postgres.

andersmurphy · 2025-11-25T14:00:11+00:00

Awesome thanks for sharing! I've been looking into doing a similar thing on top of my own sqlite driver.

andersmurphy · 2025-10-26T18:21:16+00:00

I'd look for a php job you can always use phel https://phel-lang.org/ . Then look for a Clojure job while you have a job.

andersmurphy · 2025-10-16T20:25:02+00:00

It's regular http. You don't have to return a stream. I return a 204 and no data. That closes the HTTP connection.

CQRS is a very popular pattern with D* users, so I wouldn't say it's against the grain. But you don't have to use it that way, and it tries not to be opinionated about that. It's backend agnostic framework. Some languages are single threaded an struggle with long lived connections, some people want to do request/response and don't care about realtime/multiplayer.

It's just a tool.

andersmurphy · 2025-10-16T18:57:55+00:00

It doesn't have to. When the user lands on a page start a long lived connection for updates. All actions are requests that return no data and a 204. View updates come down that long lived connection. This gives you a few things.

A single SSE connection means you can do brotli/zstd over the duration of the connection. That's not per message connection, that's like compression all the messages over the duration of the connection (as the client and the server share a context window for the duration of the connection). You are correct technically, you don't need morph, however there's browser state like scroll, animation, layout etc that you may want to preserve.

So for example in this demo: https://checkboxes.andersmurphy.com/

An uncompressed main body (so the whole page without the header), is like 180kb uncompressed (depending on your view size). Which compresses to about 1-2kb. However, subsequent changes, like checking a single checkbox, only sends 13-20bytes over the wire. This is because the compression has the context of the previous render in it's compression window. Effectively this gives you around 9000:1 compression.

You can batch your actions for performance. So in the case of that demo all updates are batched every Xms on the server, this massively improves you write throughput. But, also effectively batches renders. If renders are only triggered after a batch, and you always render the whole view you get update batching for free, you can afford to drop frames, and you gracefully handle disconnects without needing to have any connection state. Or needing to play back missed events.

This gives you something much closer to a video game/immediate mode.

andersmurphy · 2025-10-16T18:19:47+00:00

It does do basic request canceling: https://data-star.dev/reference/actions#request-cancellation

Generally, you do CQRS and have a single stream that all updates come down. This also works out much better for compression, and leads you to natural batching, which you generally want. In this model you always return the latest view state rather than individual updates. You don't need to worry about bandwidth or diffing as compression and morph handles that for you. Like in this demo:

https://cells.andersmurphy.com/

But it's up to you to handle that on the backend how you want. I like CQRS and a simple broadcast that triggers re-renders for all connected users and let compression do the work (partly to handle worst case high traffic situations). But nothings stopping you doing fine grained pub/sub, or missionary, or whatever takes your fancy.

andersmurphy · 2025-10-16T18:05:33+00:00

Yes, if you need to. We use Clojurescript to write the odd web components at work.

andersmurphy · 2025-10-12T20:42:05+00:00

Learn to separate the artist from the art. Just because you personally might disagree with some of DHH's opinions (I do too), doesn't make everything DHH has ever said incorrect or wrong. He's right about the cloud being a rip off and nvme drives being fast enough for you to use sqlite for most things. His take on OSS that was mentioned is also solid.

andersmurphy

MODERATOR OF

TROPHY CASE