Most of our tables have a uuid, a jsonb entity along with relationships stored as uuid columns with a _fk suffix. We then index the FK's.
When to Avoid JSONB in a PostgreSQL Schema
81–90 of 115 posts
Re: When to Avoid JSONB in a PostgreSQL Schema
#82Earlier quoted context omitted.
This is an attractive trap. This mode of data modelling is actually quite terrible in terms of maintainability. It is precisely the problem with NoSQL database models. It doesn't mean there is no data model (schema), and it doesn't mean that the data model is flexible. It actually means you have a succession of distinct and undocumented schemas, which are updated on a haphazard, ad hoc basis, with no documentation or…
You think database schemes are somehow immune to evolve haphazardly over time? I've seen otherwise...
No one is arguing it's not impossible but it's certainly harder to mess up a schema than a document db or JSON field.
Re: When to Avoid JSONB in a PostgreSQL Schema
#83Definitely have been bitten with the query statistics issue before. I worked with a colleague once who was adamant that we build our backend on MongoDB, but I was able to convince him to build on Postgres because of it's JSONB support. I don't get why, since schema updates are generally very cheap with databases like Postgres (adding columns without a default or deleting columns is basically just a metadata change),…
You need some migrations anyway or you'll get cruft in the db or worse. Think of old documents with extra fields your sw don't use anymore or without fields that are needed. Multiply by embedded documents and you get ton of problems you can solve only by taking care of data.
This happens even in development before going live for the first time, and way more often as you keep changing sw. Even if you throw away the data every time you still have to update the seeding scripts (in a relational db you have seeding + schema changes).
Anyway, what did you do? Did you keep using JSONB with that planner config setting or did you extract some data to ordinary columns?
Re: When to Avoid JSONB in a PostgreSQL Schema
#84Earlier quoted context omitted.
I don't agree with that because you really just query the keys for users that have the attribute in question without inserting a ton of nulls and having to work out changes to the attributes when you realize "I wish I would have thought of that column" several iterations later. Spending time up front agonizing about the "overt schema" strikes me as an anti-pattern when you are likely going to pivot later and it's sup…
There is no need to do that. Should not do that if you have arbitrary values in your database. Just create another table with all the keys and values aligned like that: id | other_pk | key | value This of cause would require another query to the database and you might have to iterate through all retrieved rows in your code to build the hash table manually (although your JSON library will do the same). Im not saying t…
This well-trodden pattern is called EAV (entity, attribute, value). It's attractive up to the point where you want to do interesting things with multiple attributes, which is when you start doing multiple nested self joins on the EAV table and performance gets hairy.
For smaller scale auxiliary data it's fine, but so is JSONB.
Re: When to Avoid JSONB in a PostgreSQL Schema
#85Earlier quoted context omitted.
EAV is an anti-pattern? If you don't have JSONB, EAV is the only remotely-reasonable way to implement user-defined fields (e.g. product-specific attributes in e-commerce).
If you want to allow user-defined fields in a relational database, your realistic are either EAV or stuff json into a text column. EAV, if done extremely carefully, can be a good solution, but 99% of the time, it's going to be a huge pain.
> stuff json into a text column
You did see the article was about JSONB, which is significantly more sophisticated than "json in a text column", yes?
Re: When to Avoid JSONB in a PostgreSQL Schema
#86Earlier quoted context omitted.
"I might want to add more columns later" is not a good justification for avoiding a relational model. If you realize that you want an additional column, just run an ALTER TABLE statement.
Totally agreed. Some people have an unreasonable fear of migrations (not to try to put words in the above poster's mouth, just pointing out something that I've noticed). You generally shouldn't worry about migration-related downtime with Postgres if you're not using table constraints, using columns with defaults, or changing a column's type. There are very few migration procedures that require something like a table…
Migrations are a pain in the arse. But not as much as trying to maintain a "schemaless" app.
Re: When to Avoid JSONB in a PostgreSQL Schema
#87Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…
I have an unrelated question :-)
I read a presentation titled "Powering Heap" by Dan Robinson, Lead Engineer at Heap, which contains interesting info about how you use PostgreSQL. [1]
At Heap, do you try to keep rows belonging to the same customer_id contiguous on disk, in order to minimize disk seeks?
If yes, how do you it? Do you use something like pg_repack?
If no, don't you suffer from reading heap pages that contain only one or a few rows belonging to the requested customer_id?
[1] http://info.citusdata.com/rs/235-CNE-301/images/Powering_Hea...
Re: When to Avoid JSONB in a PostgreSQL Schema
#88One thing I would add to the list: Don't use it for data that you need to change. There are (at least at present) no built-in functions to modify values within JSON(B) objects, which makes it very tedious modify data once it has been stored.
It is much better for data that is stored once and then queried.
Re: When to Avoid JSONB in a PostgreSQL Schema
#89"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
Let me give an example: We process some large forms (say 100+ fields). We care a lot about maybe 15 fields off these forms, but we use the others sometimes. These forms change every so often, with some fields disappearing and new fields joining in. The fields we care a lot about don't change, but the ones we care a little about do. A few possibilities here: 1. You could have a table for every variant of the form 2. Y…
When you say 1. You could have a table for every variant of the form do you actually mean one table for each version of the form, or more like one table for the 15 fields you care about+1 field for the form version # (sort of a base class) and then one extra table to capture extra fields when needed?
The latter is a sort of "inheritance" done in SQL and I had used it with good results in the past.
Re: When to Avoid JSONB in a PostgreSQL Schema
#90Hey SQL newbie question: why use JSONB when you could split out tables into `user` and `user_meta`? isn't that how Wordpress works?
It all depends on what you're doing... But, we had a situation where we had a "user_meta" equivalent, but wanted to support different data types (and even possibly nested data) using JSONB allowed for simple modelling of something like `{ "age": 1, "school": "blah", "something": { "in": "depth" } }` which isn't as simple using an extra "meta" table. (Not to say it's the best thing to do (depending on the situation it…
Ahhhh so this will be super useful if I want to keep track of transaction events from a third party (like Shopify or Stripe). I could just keep table with `ID` `user_id` `time` `blob`.