Disclaimer: I am working on this.
1) Rkyv uses a binary tree vs Lite³ B-tree (B-trees are more cache and space efficient).
2) Rkyv is immutable once serialized. Lite³ allows for arbitrary mutations on serialized data.
3) Rkyv is Rust only. Lite³ is a 9.3 kB C library free of dependencies.
4) Rkyv as a custom binary format is not directly compatible with other formats. Lite³ can be directly converted to/from JSON.
I have not benchmarked Lite³ against Rust libraries, though it would be an interesting experiment.
[1]: https://lite3.io/design_and_limitations.html#autotoc_md31 [2]: https://github.com/couchbase/fleece/
How does Lite^3 compare to PG's JSONB? PG's JSONB is also a serialized, indexed data structure. One of the key things about JSONB is that for arrays (and so objects) it encodes first their lengths, then the values, but every so many elements (32 is the default IIRC) it encodes an offset, and the reason for this design is that when they encoded offsets only the result did not compress well (and if you think about it it will be obvious why). The price they pay for this design is that finding the offset to the nth element's value requires first finding the offset of the last entry before n that has an offset, then adding all the lengths of the entries in between. This way you get a tunable parameter for trading off speed for compressibility.
EDIT: Ok, I've looked at the format. Some comments:
- Updating in place is cool but you need to clear unused replaced data in case it's sensitive, and then unless you re-encode you will use up more and more space -- once in a while you need a "vacuum". Though vacuuming a Lite^3 document is quite simple: just traverse the data structure and write a new version, and naturally it will be vacuumed.
- On the whole I like Lite^3 quite a bit. Very clever.
- JSONB is also indexed as encoded, but IIUC it's not in-place updateable (unless the new items are the same length as the old) without re-encoding. Though I can imagine a way to tombstone old values and replace them with offsets into appended data, then the result would also need a "vacuum" once in a while.
- I'm curious about compressibility. I suspect not having long runs of pointers (offsets) helps, but still I suspect JSONB is more compressible.
I love the topic of serialization formats, and I've been thinking for some time about ASN.1 compilers (since I maintain one). I've wanted to implement a flatbuffers / JSONB style codec for ASN.1 borrowing ideas from OER. You've given me something to think about! When you have a schema (e.g., an ASN.1 module) you don't really need a B-tree -- the encoded data, if it's encoded in a convenient way, is the B-tree already, but accessing the encoded data by traversal path rather than decoding into nice in-memory structures sure would be a major improvement in codec performance!
Another difference is that JSONB is immutable. Suppose you need to replace one specific value inside an object or array. With JSONB, you would rewrite the entire JSONB document as a result of this, even if it is several megabytes large. If you are performing frequent updates inside JSONB documents, this will cause severe write amplification. Despite the fact that offsets are grouped in chunks of 32, Postgres still rewrites the entire document. This is the case for all current Postgres versions.
On the other hand, Lite³ supports replacing of individual values where ONLY the changed value needs updating. For this to work, you need separate offsets. Postgres makes a tradeoff where they get some benefits in size, but as a result become completely read-only. This is the case in general for most types of compression.
Also JSONB is not suited to storing binary data. The user must use a separate bytea column. Lite³ directly implements a native bytes type.
JSONB was designed to sacrifice mutability in favor of read performance, but despite this, I still expect Lite³ to exceed it at read performance. Of course it is hard to back this up without benchmarks, but there are several reasons:
1) JSONB performs runtime string comparison loops to find keys. Lite³ uses fixed-size hash digests comparisons, where the hashes are computed at compile time.
2) JSONB must do 'walking back' because of the 32-grouped offset scheme.
3) Lite³ has none of the database overhead.
Again, the two formats serve a different purpose, but comparing just the raw byte layouts.
Loading data into DuckDB is super easy, I was surprised :
SELECT avg(sale_price), count(DISTINCT customer_id) FROM '/my-data-lake/sales/2024/*.json';
and you can also load into a JSON type column and can use postgres type syntax col->>'$.key'
but i would say, comparing duckdb and sqlite is a little bit unfair, i would still use sqlite to build system in most of cases, but duckdb only for analytic. you can hardly make a smooth deployment if you apps contains duckdb on a lot of platform
someone should smush sqlite+duckdb together and do that kind of switching depending on query type
That being said, it would be trivial to tweak the above script into two steps, one reading data into a DuckDB database table, and the second one reading from that table.
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
For example, if you want to store settings as JSON, you first have to parse it through e.g. Zod, hope that it isn't failing due to schema changes (or write migrations and hope that succeeds).
When a simple key/value row just works fine, and you can even do partial fetches / updates
Lesson learned: even if you know your tools well, periodically go check out updated docs and see what's new, you might be surprised at what you find!
That said, this is pretty much what you have to do with MS-SQL's limited support for JSON before 2025 (v17). Glad I double checked, since I wasn't even aware they had added the JSON type to 2025.
Most of the JSON functions added in iirc MS-SQL 2016 really performed poorly and is a significant reason why denormalized JSON data was used very sparingly... with actual JSON data types (assuming a binary deserialized form of storage), then queries and operations against that underlying data structure can run significantly faster.
I've been pretty critical of it since I tried using it for a few things a few years ago... it still worked well enough for the needs of what it was doing, but I'm glad that it's doing better.
For reference, what it was being used for was to semi-normalize most stored procedures to receive 2 argumenst and return 2. All JSON... the first argument would be the claims portion of the JWT for the service, the second would be a serialized typed request object representing the request to the service and the two results are the natural results to the sproc as well as an error result if an error occurred. This allowed for a very simplified API surface (basically 4 utility methods being used for all API calls), in the project in question it was a requirement for data logic to be inside the database, of which I'm not a fan, but it did work out pretty well for what it was. Other isseus not withstanding.
i.e. something like this: CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))?
i guess caveat here is that slight change in json path syntax (can't think of any right now) can cause SQLite to not use this index, while in case of explicitly specified Virtual Generated Columns you're guaranteed to use the index.
It's pretty fragile...
-- Just changing the quoting
select * from events where json_extract(data, "$.type") = 'click';
-- Changing the syntax
select * from events where data -> '$.type' = 'click';
You need to ensure your queries match your index, but when isn’t that true :)
When you write another query against that index a few weeks later and forget about the caveat, that slight change in where clause will ignore that index.
> The ability to index expressions was added to SQLite with version 3.9.0 (2015-10-14).
So this is a relatively new addition to SQLite.
> So, thanks bambax!
You're most welcome! And yes, SQLite is awesome!!
It's much harder to setup proper indexes, enforce constraints, and adds overhead every time you actually want to use the data.
* The data does not map well to database tables, e.g. when it's tree structures (of course that could be represented as many table rows too, but it's complicated and may be slower when you always need to operate on the whole tree anyway)
* your programming language has better types and programming facilities than SQL offers; for example in our Haskell+TypeScript code base, we can conveniently serialise large nested data structures with 100s of types into JSON, without having to think about how to represent those trees as tables.
I find this one of the hardest part of using JSON, and the main reason why I rather put it in proper columns. Once I go JSON I needs a fair bit of code to deal with migrartions (either doing them during migrations; or some way to do them at read/write time).
Another example is a classifieds website, where your extra details for a Dress are going to be quite a bit different than the details for a Car or Watch. But, again, you don't necessarily want to inflate the table structure for a fully normalized flow.
If you're using a concretely typed service language it can help. C# does a decent job here. But even then, mixing in Zod with Hono and OpenAPI isn't exactly difficult on the JS/TS front.
tryna map everything in a relational way etc - you're in a world of pain
But the more complex it is, the more complex the relational representation becomes. JSON responses from some API's could easily require 8 new tables to store the data in, with lots of arbitrary new primary keys and lots of foreign key constraints, your queries will be full of JOIN's that need proper indexing set up...
Oftentimes it's just not worth it, especially if your queries are relatively simple, but you still need to store the full JSON in case you need the data in the future.
Obviously storing JSON in a relational database feels a bit like a Frankenstein monster. But at the end of the day, it's really just about what's simplest to maintain and provides the necessary performance.
And the whole point of the article is how easy it is to set up indexes on JSON.
Typical example is a price-setting product I work on.. there's price ranges that are universal (and DB columns reflect that part) but they all have weird custom requests for pricing like rebates on the 3rd weekend after X-mas (but only if the customer is related to Uncle Rudolph who picks his nose).
There's no reason to put all those extra fields in the same table that contains the universal pricing information.
Edit: This should now be fixed for you.
You can do the same with DuckDB and Postgres too.
That migration would be making two changes: document-based -> relational, and server -> library.
Have you considered migrating to Postgres instead? By using another DB server you won't need to change your application as much.
Why?
But I suspect with JSON the overhead of parsing it each time might make it more efficient to update all the indices with every insert.
Then again, it's probably quicker still to insert the raw SQL into a temporary table in memory and then insert all of the new rows into the indexed table as a single query.
No, in section 2 the table is created afresh. All 3 sections start with a CREATE TABLE.
Particularly with drizzle, it means I can use sqlite on device with expo-sqlite, and store our data format in a single field, with very little syntax, and the schema and queries all become fully type safe.
Also being able to use the same light orm abstraction server side with bun:sqlite is huge.
It's a feature, not a replacement.
But this technique I guess is very common now.
What?