Why dont document databases automatically save common keys in some kind of lookup table?
Seems like a basic feature to improve space savings and processing speed.
When to Avoid JSONB in a PostgreSQL Schema
31–40 of 115 posts
Re: When to Avoid JSONB in a PostgreSQL Schema
#32Author 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've found a few good applications for jsonb so far:
1) Using jsonb to store user-generated forms and submissions to those forms. As an example, you can create a form with text inputs, checkboxes, etc., and others can submit responses with that form. I find that these forms and their submissions are best stored as jsonb because their contents are largely opaque (I don't care about the contents except where they are rendered on the client), their structure is highly dynamic, and their schema changes frequently.
2) As a specialized case on #2, applying filters to user-generated form submissions. jsonb supports subset operators (@> and 3) Storing/munging/slicing relatively low-volume log data is fantastic with jsonb. This is always for admin/diagnostic reasons, so it's not as performance-critical, and the ability to group on and do subset operations on jsonb fields makes slicing your data really easy.
Re: When to Avoid JSONB in a PostgreSQL Schema
#33Does adding a Gin index to the JSONB help this?
A Gin index only helps with querying the data from the table. It won't help with making the proper join choice or with getting a bitmap scan between multiple indexes. Additionally, you are unable to query numeric values by an inequality with a Gin index.
Re: When to Avoid JSONB in a PostgreSQL Schema
#34"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. You could have a table with every field that's ever been used as a nullable field and add new columns every time a new version of the form appears
3. You could have a table with columns for the important fields and a JSONB column for the variable data
Of the three options, 3 seems the most elegant to me. The other two are basically allowing the ugliness of the source data to spew complexity all over your database.
Re: When to Avoid JSONB in a PostgreSQL Schema
#35Earlier quoted context omitted.
Maybe I'm missing something, but I think of optional columns as nullable (but declared) values. It sounds like you use JSONB to store arbitrarily declared values. If so, then I'm still confused then by how you're able to hoist values from JSONB data to save on perf and space. That implies these values weren't that arbitrary to begin with.
My bad. As of right now, we use jsonb to store all of the properties of events besides the user_id, the event_id, and the time of the event. We have lots of builtin properties which are stored in some events but not others (for example, the text of a button that someone clicks on). We thought storing these properties in jsonb was a good idea because at the time we didn't know any of the downsides of jsonb. The issues…
Got it. I got hung up on the meaning of "optional values", JSONB makes sense for storing arbitrary metrics. Thank you for clarifying!
Re: When to Avoid JSONB in a PostgreSQL Schema
#36"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.
actually we have a table which would contain over 100 fields. while we only need to query 10. actually we store only 6 things inside the table and the rest inside the jsonb. however we sill miss like 8 values which we are using inside a list, which are slow, but materialized view to the rescue. however we may pull them out at some point, still need to figuring out since the jsonb data set is also the value of a hash.…
You can index the contents of lists in Postgres using GIN indexes.
Re: When to Avoid JSONB in a PostgreSQL Schema
#37"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.
I was also wondering about this one. This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing. Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need. I would even go one step further and say: It is a common anti-pattern to introduc…
Just a note that NULL columns take up very little space in Postgres. IIRC there is a NULL bitmap for every row. So yes, don't worry and just use columns, if the columns are actually part of your schema.
Re: When to Avoid JSONB in a PostgreSQL Schema
#38"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.
I was also wondering about this one. This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing. Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need. I would even go one step further and say: It is a common anti-pattern to introduc…
I'll add another reason: subtle application issues creep in with value states.
With JSONB I can receive a boolean element with five states: null, true, false, invalid (i.e. actually a string, number or array value), or simply nonexistent, and it's up to the application to deal with all such cases. You may have to treat the database as a source of potentially invalid data that must be sanitized.
With regular DB columns you can reasonably assume that a boolean not null column will a) exist for all records and b) return either true or false. You need only scan the database schema at application start to verify this.
On the other hand, if you're using JSONB for an options structure then the possibility of key nonexistence may actually be useful, since it implies "use system default". (Yes this can differ from the meaning of a NULL value, especially if you are merging options structures).
You might question whether it is wise to have a "boolean" key/value pair with four semantically distinct states. I can only say I have done this and I am not proud of it.
Re: When to Avoid JSONB in a PostgreSQL Schema
#39Earlier quoted context omitted.
I was also wondering about this one. This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing. Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need. I would even go one step further and say: It is a common anti-pattern to introduc…
Just a note that NULL columns take up very little space in Postgres. IIRC there is a NULL bitmap for every row. So yes, don't worry and just use columns, if the columns are actually part of your schema.
This is correct. Postgres keeps a bitmap in each row that designates which of the rows are null. Postgres initially allocates one byte for the bitmap so it will work for up to eight columns. Once your table has more than 8 columns, postgres will allocate eights bytes for an additional 64 columns. That means if you have more than 8 columns, you can keep making "optional" fields proper columns until you have a total of 72 columns because null values are completely free.
Re: When to Avoid JSONB in a PostgreSQL Schema
#40"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…
I use JSONB columns for similar use-cases, but to play devil's advocate, you can accomplish that a fourth way, which is almost certainly better than 1 or 2. A table for fields, one row per field. A table for forms, which has a many-to-many relation to fields. Entries in a link table compose a form of arbitrary fields. Answers can be stored in a separate responses table, indexed by form_id and column_id. I don't know enough about database implementation to speculate on how that would perform at scale, but conceptually that's how I think of the problem.