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.
In my group, the person who helped the vendor set up our new DB has convinced management to convert all of our metadata tables (which we use for every filter operation) to JSON. As you can guess, he's never benchmarked a DB in his life.
Since our visualization tool went from 2 seconds per click (when I set it up the original DB) to over 40 now, even before implementing this change, I'm watching in amazement, waiting for someone to complain. It's truly incredible how tolerant users are. It's one of the reasons I think AI will take our jobs much sooner than most think: the average user has no useful perspective on silly things like performance or determinism, "that's just how it is".
Whenever I call some client support service to complain about their website being broken, incorrect, malfunctioning, or something similar, and I explicitly ask them to notify their IT system about it, I feel like some weird alien or raging boomer asking for something impossible. They don't even understand it, and my complaints just become useless. There are no more e-mails to contact, nothing that can be done to improve the situation. It's enshittification all the way down, and too bad for those of us who had known a better state of affairs, we just have to become blind to it.
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
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).
As with everything, it depends. Postgres JSON[B] columns support GIN indices (technically they also support BTree and Hash, but using them can be confusing), which supports a handful of operators [0]. You can also do partial indices with these (e.g. CREATE INDEX foo_data_pidx ON foo (data) WHERE data ->> 'is_active' = 'true'), though that can then create confusion for using it, because while the index predicate accepts / requires equality (SELECT ... WHERE data ->> 'is_active' = 'true'), the rest of the object requires an operator like @> (contains), so it can be awkward to write. This is the BTree expression index I stated at the beginning could be confusing. I have no idea if ORMs / query builders get this right or not.
The other major problem is lack of statistics. JSON[B] is opaque to Postgres; it has no idea about the distribution of values within an object. This starts to become very obvious if you're doing JOINs and have predicates from the JSON object. Take for example geographical information: maybe you want to find some number of customers from Delaware who have onboarded in the last week. If that geographical information is in a JSON object, and you have 5,000,000 rows, Postgres has no idea how many rows it should expect, so it may not choose the optimal join strategy. The same is true in the opposite direction - it's probable that you have more customers in California than you do in Delaware purely based on relative population, but because the planner doesn't know that, it's unlikely to choose the optimal strategy.
To be fair, for correlated columns (extending the above example, think of postal_code and state), the planner can also make the same mistake in underestimations, because by default it assumes the columns are uncorrelated. This can be solved with extended statistics [1], where you explicitly tell it which column[s] are correlated, and in what manner.
I created a table in PG18 on my Mac, with 5,000,000 rows of fake customers (various columns like names, geo information, email, opaque ids, integer id, date times, etc.), and another where most of it was shifted into a JSONB column, exact same information, same ordering of rows. In general, simple point lookups on the scalar table took 0.1 msec, as you'd expect; the JSONB table could at best do 2.5 msec, if and only if the correct operator was used. Use the wrong operator, and it still works, but it reverts to a parallel sequential scan, which took ~175 - 400 msec, depending on the specific operator being used. For joins, it was 20-30% slower with an expression index, and 20-350% (yes, large range) slower with a GIN index.
JSON[B] also struggles with memory usage during queries, and WAL generation. Since the entire object has to be extracted, intermediate query results can get quite large; if you don't have a large-enough `work_mem`, this slows things down (the same is true for scalars, of course, but JSON objects tend to be larger, if nothing else due to repeated keys). If you ever have to UPDATE a JSON[B] object, that's a full re-write, and an update to any GIN indices it may have. I measured 5.5 sec vs. 2 sec for updating 1 attribute on 100K rows, and 831 MB WAL vs. 227 MB WAL generation. Those times would be much, much more if this wasn't on a fast local NVMe drive, as well.
There's also TOAST/DE-TOAST [2] time, which I didn't measure, because my objects were small. With a large enough value (it doesn't have to be JSON, but it usually is) - by default, 2 KB - Postgres will first attempt to compress, and then shift those values out to a separate table, vaguely similar to InnoDB's DYNAMIC page format. That means when you're querying with predicates on the TOAST'd object, it now has to do another indirect lookup, retrieve the page, and check the predicate. At scale, this can severely slow things down.
Finally, and most importantly IMO, guaranteeing invariants is next to impossible with JSON objects. Was that field left NULL when it shouldn't have been? Did the email field actually contain an email address? Hopefully your application is checking everything rigorously, and no one has changed its logic from what was intended.
Oh yeah I would not do things like joins but for simple filtering I have found that it works very well. Of course if I knew I needed to filter on that value beforehand it would not be in a json column but I found that it's sufficient enough that it beats doing a re-architecture or dual writes if you realize you actually do need to do a filter after the fact
In defense of the post: In cases where you want flexibility & rapid experimentation, accessible JSON columns give that capability whilst still respecting the table structure of the SQL DB.
When the structure ossifies, converting the structure into its tabular representation makes sense.
------
Every single other comment here shitting on the choices made act as if their commits have always been perfect first try, with no need for modifications or improvements.
When the demands of your target customers constantly shift & have not yet been ossified, the optimizations made early in its process more often than not bite back hard & painful.
> 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
Yes, things like nested repeated fields (or structs) will mess you up hard time. When I was playing with Duckdb querying parquet several years ago I had issues with column names and even was digging some source code to improve statistics use, but it was doable and reasonably efficient on my datasets.
Although yes. If you have resources of large companies, both computational and manpower, you can do very nice things with your data. All that determination of “what would be nice to shred” based on real query statistics, compaction, regular optimization and improvements of data at rest by maintenance jobs… again, use better databases/engines and there’s no free lunch, you get what you paid for (hopefully).
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.
In my group, the person who helped the vendor set up our new DB has convinced management to convert all of our metadata tables (which we use for every filter operation) to JSON. As you can guess, he's never benchmarked a DB in his life.
Since our visualization tool went from 2 seconds per click (when I set it up the original DB) to over 40 now, even before implementing this change, I'm watching in amazement, waiting for someone to complain. It's truly incredible how tolerant users are. It's one of the reasons I think AI will take our jobs much sooner than most think: the average user has no useful perspective on silly things like performance or determinism, "that's just how it is".
They do hate it; they have been taught that no matter the complaints, the bad experience will never change, so why waste time complaining.
> I'm watching in amazement, waiting for someone to complain
Sometimes you have to be the change you want to see in the world.
(the other possibility is that much fewer people are looking at this data than you'd expect ...)
Whenever I call some client support service to complain about their website being broken, incorrect, malfunctioning, or something similar, and I explicitly ask them to notify their IT system about it, I feel like some weird alien or raging boomer asking for something impossible. They don't even understand it, and my complaints just become useless. There are no more e-mails to contact, nothing that can be done to improve the situation. It's enshittification all the way down, and too bad for those of us who had known a better state of affairs, we just have to become blind to it.
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
As with everything, it depends. Postgres JSON[B] columns support GIN indices (technically they also support BTree and Hash, but using them can be confusing), which supports a handful of operators [0]. You can also do partial indices with these (e.g. CREATE INDEX foo_data_pidx ON foo (data) WHERE data ->> 'is_active' = 'true'), though that can then create confusion for using it, because while the index predicate accepts / requires equality (SELECT ... WHERE data ->> 'is_active' = 'true'), the rest of the object requires an operator like @> (contains), so it can be awkward to write. This is the BTree expression index I stated at the beginning could be confusing. I have no idea if ORMs / query builders get this right or not.
The other major problem is lack of statistics. JSON[B] is opaque to Postgres; it has no idea about the distribution of values within an object. This starts to become very obvious if you're doing JOINs and have predicates from the JSON object. Take for example geographical information: maybe you want to find some number of customers from Delaware who have onboarded in the last week. If that geographical information is in a JSON object, and you have 5,000,000 rows, Postgres has no idea how many rows it should expect, so it may not choose the optimal join strategy. The same is true in the opposite direction - it's probable that you have more customers in California than you do in Delaware purely based on relative population, but because the planner doesn't know that, it's unlikely to choose the optimal strategy.
To be fair, for correlated columns (extending the above example, think of postal_code and state), the planner can also make the same mistake in underestimations, because by default it assumes the columns are uncorrelated. This can be solved with extended statistics [1], where you explicitly tell it which column[s] are correlated, and in what manner.
I created a table in PG18 on my Mac, with 5,000,000 rows of fake customers (various columns like names, geo information, email, opaque ids, integer id, date times, etc.), and another where most of it was shifted into a JSONB column, exact same information, same ordering of rows. In general, simple point lookups on the scalar table took 0.1 msec, as you'd expect; the JSONB table could at best do 2.5 msec, if and only if the correct operator was used. Use the wrong operator, and it still works, but it reverts to a parallel sequential scan, which took ~175 - 400 msec, depending on the specific operator being used. For joins, it was 20-30% slower with an expression index, and 20-350% (yes, large range) slower with a GIN index.
JSON[B] also struggles with memory usage during queries, and WAL generation. Since the entire object has to be extracted, intermediate query results can get quite large; if you don't have a large-enough `work_mem`, this slows things down (the same is true for scalars, of course, but JSON objects tend to be larger, if nothing else due to repeated keys). If you ever have to UPDATE a JSON[B] object, that's a full re-write, and an update to any GIN indices it may have. I measured 5.5 sec vs. 2 sec for updating 1 attribute on 100K rows, and 831 MB WAL vs. 227 MB WAL generation. Those times would be much, much more if this wasn't on a fast local NVMe drive, as well.
There's also TOAST/DE-TOAST [2] time, which I didn't measure, because my objects were small. With a large enough value (it doesn't have to be JSON, but it usually is) - by default, 2 KB - Postgres will first attempt to compress, and then shift those values out to a separate table, vaguely similar to InnoDB's DYNAMIC page format. That means when you're querying with predicates on the TOAST'd object, it now has to do another indirect lookup, retrieve the page, and check the predicate. At scale, this can severely slow things down.
Finally, and most importantly IMO, guaranteeing invariants is next to impossible with JSON objects. Was that field left NULL when it shouldn't have been? Did the email field actually contain an email address? Hopefully your application is checking everything rigorously, and no one has changed its logic from what was intended.
0: https://www.postgresql.org/docs/current/datatype-json.html#J...
1: https://www.postgresql.org/docs/current/sql-createstatistics...
2: https://www.postgresql.org/docs/current/storage-toast.html
Oh yeah I would not do things like joins but for simple filtering I have found that it works very well. Of course if I knew I needed to filter on that value beforehand it would not be in a json column but I found that it's sufficient enough that it beats doing a re-architecture or dual writes if you realize you actually do need to do a filter after the fact
Everyone needs their aha moment :}.
In defense of the post: In cases where you want flexibility & rapid experimentation, accessible JSON columns give that capability whilst still respecting the table structure of the SQL DB.
When the structure ossifies, converting the structure into its tabular representation makes sense.
------
Every single other comment here shitting on the choices made act as if their commits have always been perfect first try, with no need for modifications or improvements.
When the demands of your target customers constantly shift & have not yet been ossified, the optimizations made early in its process more often than not bite back hard & painful.
> 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
This is quite an old technique. In ClickHouse, it appeared before it was named "shredding": https://github.com/ClickHouse/ClickHouse/issues/54864
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.
if you dynamically create indices of everything in the json, you've just doubled your storage space usage for nothing.
DuckDB recently announced faster JSON in its variant: https://duckdb.org/2026/08/17/duckdb-20-highlights#2-variant...
Use better databases? Learn prior art?
Dremel paper explained shredding in like 2010? Parquet had it in a while?
https://blog.x.com/engineering/en_us/a/2013/dremel-made-simp...
Yes, things like nested repeated fields (or structs) will mess you up hard time. When I was playing with Duckdb querying parquet several years ago I had issues with column names and even was digging some source code to improve statistics use, but it was doable and reasonably efficient on my datasets.
Although yes. If you have resources of large companies, both computational and manpower, you can do very nice things with your data. All that determination of “what would be nice to shred” based on real query statistics, compaction, regular optimization and improvements of data at rest by maintenance jobs… again, use better databases/engines and there’s no free lunch, you get what you paid for (hopefully).
Re-discovering databases from first principles.
Feels insane to not do this.
you don't say? Using the database as a database makes a database run like a proper database!
Let's just revert back 30 years and make the entire www plain text. What could possibly go wrong?
[dead]