3 ms·
I really wish someone would write an article about when to use regular columns instead of JSONB and whether you should split your data into multiple JSONB colum
by Eridrus 10y ago
I really wish someone would write an article about when to use regular columns instead of JSONB and whether you should split your data into multiple JSONB columns, etc.
So far the only thing I've figured out is that updating pieces of a JSONB structure seems like a bad idea at the moment since it will not always succeed (notably you can't add entries to an array once it has 999 elements already).
In my problem I've given each analysis tool I run and API I query it's own JSONB column so that they only ever need to be written once, or if they are changed, they would be overwritten entirely and keep a few normal columns that contain some metadata, but I'm not sure if this is a good approach.
- hobs 10y agoYour idea of a mixed system that has the best of both approaches (some stored state which may be hard to compute and/or may be complicated for you to figure out how to store) and data that is often updated not inside the JSONB column but related to it either via a foreign key or another column in your table is perfectly fine. The thing you want to avoid in that type of system is having data in both locations that you need to update to keep in sync, this is a bad sign and you probably need to move some data out of the JSONB column. Also, I would almost never choose anything but normal row data unless I absolutely had to and produce some json at the end of the pipeline. However, your situation has a special type of work you can save and it would be silly to redo it all over again on each request.
- drob 10y agoI'm actually halfway through writing this post! I was hoping to finish it today. :)