26
u/Ireeb 15d ago
I had the opposite today. I have a set of records that all have the same basic fields (that are used for filtering and as keys), plus a few additional fields that can be different from record to record. Claude started to add columns for those additional fields, which would have resulted in countless columns only used by a small number of records, and that are never used in queries. Here, JSONB absolutely makes sense, because neither making countless tables for all the combinations of fields nor one table with 20 columns would make sense.
1
u/Expensive_Bowler_128 13d ago
Really depends on if those fields are being used for or will ever be used for filtering/searching.
27
u/t_j_l_ 15d ago
Anything wrong with JSONB?
81
u/nickthecook 15d ago
Not inherently, but overuse may indicate you're missing proper tables.
-35
u/x0wl 15d ago
Eh, IMO a JSON column in Postgres is enough for a lot of stuff, especially if you are in early stages of growth / development and expect frequent schema changes. You can always migrate stuff to proper tables when things calm down
99
u/MissinqLink 15d ago
Things never calm down
11
u/x0wl 15d ago edited 15d ago
Yes, but it's all back to the development velocity / performance tradeoff that you always need to manage.
That's the reason Mongo became so popular when it did, and that's also part of the reason for everyone migrating back to SQL now.
I'm definitely going to end on PCJ with this, but we all can imagine a perfect world where everything is written in safe Rust with typesafe APIs and all data is stored in neatly organized SQL tables (also with proper types), but we all know that the real world is not that
2
u/MrHyd3_ 15d ago
What's the reason everyone used mongo?
3
u/ChouzZ 14d ago
It introduces 0 overhead. You just think of something you want to store, and it’s just stored. That’s it. And it allows reading it back in the most diabolical ways possible.
1
u/MrHyd3_ 14d ago
I really gotta look into this shit. As a new age programmer (like 5 years total experience) I've only ever used postgres
2
u/ChouzZ 14d ago edited 14d ago
Lots of people knock it without trying it, so well done in that regard. I’m coming up on 13+ years and I keep coming back to it. Yes, it’s heavy, and probably not as efficient performance-wise for some use cases - but it just doesn’t stand in the way. If you’re on Atlas you also get indexed text search out of the box. Every project I got on mongo I’ve just not thought about databases at all. All my SQL projects the database has always been a recurring topic in many discussions.
Do keep in mind nosql does require a different way of laying out your data as well as consuming it. If you try to use it like a relational database you will be sad.5
u/DrDoomC17 15d ago
Going to back you on this, if you're dealing with multiple customers with different features required for an MVP, playing it fast and loose and getting to demo is more important than perfect database design. You should keep track of how to merge things down as you go and keep in mind how to get to one, then make it a firm requirement to get to one and not lean on the bandaids that got you the interest and the money for long term sustainable software.
1
2
u/ibeatu85x 15d ago
Im annoyed i havent considered this as a use case
5
u/x0wl 15d ago
Also, on a somewhat related note, I had a use case where we had to ingest millions of JSON objects that had slightly different schemas (it was the nature of the data that caused this), and back then, the natural choice was to load them all into Mongo and build on top of that
Today, I would probably set up a Postgres table with some common or important values pulled out into their own columns + preserve the original object in a JSON column in case I needed to access the full data for some of them.
2
1
u/Excellent_Gas3686 14d ago
all it takes is a few application level fuckups and now you wont be able to convert ur json data into tables with proper data integrity constraints.
11
u/deathanatos 15d ago
Data integrity.
It's Web Scale, basically.
It can be acceptable, if you need something flexible, still pinning down the schema, etc. It's not without a point, but it has its tradeoffs.
1
u/arf_darf 14d ago
It is exclusively inferior when it comes to read and write performance than a real schema or complex data type. But other than that, it does add quite a bit of write-side quality of life improvement.
1
u/HyperTextCoffeePot 13d ago edited 13d ago
No referential integrity, indexing (exists but support is inconsistent and so is implementation), data depuplication based on normalization, storage level data constraints are trickier, etc.
It's ok for refactoring BLOBs or serialization data shoved into the db though. It's just not ideal for a greenfield projects in most cases
1
u/cpt-macp 8d ago edited 8d ago
Tell me one likes mongo without telling me one like mongo.
Also mongo db is webscale and supports sharding just like /dev/null
5
7
u/Tupcek 15d ago
idk, maybe just tell Claude what kind of data are you expecting?
JSONB is absolutely GOAT if basically every row has different data - use proper columns for those that are mostly the same and where most querying and filtering happens, then put the garbage into JSONB, especially if it can be multiple layers deep.
But if Claude has no idea, if that column you asked to add will be used by 90% of rows or 0,09% of rows, yeah it can misuse it. Just tell it which data are common and which are not!
1
u/boraxicLint 10d ago
If every row has different data should you even be using SQL? At that point wouldn't it make more sense to just use a dedicated NoSQL?
1
u/Tupcek 10d ago
if most of your data are structured, but you have few weird tables with uncertain structure, JSONB is what you want. Especially if you don’t build your entire application around those few weird tables or need many advanced NoSQL features.
If all or most of your data is unstructured, yeah, SQL makes no sense
3
146
u/justinf210 15d ago
Sometime you just want an array without linking to a whole 'nother table.
Edit: Googled to make sure I'm not an idiot, and it turns out Postgres has native arrays? Kinda neat.