Shredding JSON columns gave Logfire up to 1000x faster queries
pydantic.dev18 points by acossta 8 hours ago
18 points by acossta 8 hours ago
Re-discovering databases from first principles.
It is hilarious to me when companies act like they've made some huge discovery that's been well-known to anyone working in DBs for the past decade, if not more.
JSON is slow as hell, both for serdes, and especially querying it. Use it sparingly, if at all. Normalization is not that hard, and it's trivial now with LLMs. Tell Claude you want your schema in BCNF, or 5NF if you're feeling froggy. Denormalize if and only if you have performance issues.
The point they make is that user data is dynamic, meaning you can't just blindly normalize every json attribute into its own column. The challenge is determining which attributes deserve "shredding" and doing it automatically iiuc
Not even companies. I've seen this with colleagues. They wanted JSON parsing in a moderately embedded device. No thank you. Parse it elsewhere, give me the binary result
Meh, it's not ideal but it's not that bad either.
I'm working on an embedded system with a MCU and I've benchmarked JSON serialization at over 500 KiB/s (through a HTTP GET request) using my own library. Deserialization is slower, but that's partly because I have super-linear algorithms downstream of the parsing.
CBOR is much nicer and faster to work with (smaller documents, definite length strings directly consumable) and I do support it in my library, but I derive most of my performance gains by ditching DOM-style APIs and using stream processing instead. Allocating nodes and chasing pointers all over the heap sucks for performance, especially for large documents, regardless of the format (and that's assuming it even fits within available RAM).
If you have proper indexes into the JSON columns I wonder what the performance difference is, if any, for Postgres
> Rather than storing all attributes in a single JSON column, we automatically extract the most frequently accessed attributes into separate, strongly typed columns.
I'm glad they made the discovery but this is DB 101
Why not store everything in a single json column but dynamically generate index files, so finding rows is fast?
Then you never have to change the data. Only the index files.
This is quite an old technique. In ClickHouse, it appeared before it was named "shredding": https://github.com/ClickHouse/ClickHouse/issues/54864
[dead]
Let's just revert back 30 years and make the entire www plain text. What could possibly go wrong?