21 ms·

When to Avoid JSONB in a PostgreSQL Schema

183 points by matm 10y ago

drob 10y ago
Author 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 works particularly well.
- willlll 10y ago
  Was the 30% disk saving over petabyte+ data set on a single-node Postgres or on your Citus cluster?
  - drob 10y ago
    This isn't live yet, but we expect it to be across our citus cluster. The ~30% figure comes from the profiling we did on individual postgres nodes.
- mcdee 10y ago
  I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue. Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
  - malisper 10y ago
    Using @> instead of ->> only causes the selectivity estimate of the predicate to be a different hard coded estimate. It doesn't fix the underlying problem of Postgres not keeping statistics on JSONB.
    - mcdee 10y ago
      True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.
      - malisper 10y ago
        > but it does bring the query response time down to the same order of magnitude as the non-JSON table. In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
        mcdee 10y ago
        There are two separate issues. The lack of statistics is one thing, but the use of ->> instead of @> is another. Look at https://explain.depesz.com/s/zJiT https://explain.depesz.com/s/zJiT Vs https://explain.depesz.com/s/ihwk https://explain.depesz.com/s/ihwk for the difference.
        malisper 10y ago
        Your queries are executing different plans. The first one is executing a nested loop join which filters out 1,246,035,384 intermediate rows. The second one is executing a index join which doesn't filter out any intermediate rows at all. This seems like it was caused either by the scientist_labs_pkey index not being there in the first trial or just random luck due to a difference in statistics.
- iEchoic 10y ago
  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 <@, if i remember correctly), which makes easy work of dynamically filtering form submissions on custom form fields even for complex filter conditions. 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.
- ngrilly 10y ago
  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_Heap_with_PostgreSQL_and_CitusDB_-_Dan_Robinson.pdf http://info.citusdata.com/rs/235-CNE-301/images/Powering_Hea...
  - malisper 10y ago
    Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a user at a time. Instead we look for specific types of events in a given time range and group by the user. Clustering by time winds up being a much bigger win (benchmarks showed 10x compared to sorted by user for some uncached queries) here as almost all of our queries are constrained to a given time period. Currently, maintaining the clustering has only been best-effort. We sort our data whenever we copy it from one location to another and the data comes in sorted by time, so it's fairly easy to maintain a high row correlation with time.
    - ngrilly 10y ago
      My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).
      - drob 10y ago
        The data is sharded by customer and then sub-sharded by end user within the customer. For all but the tiny customers, 100% of the data on a logical shard will belong to the same customer. That means our subqueries will never touch data from more than one customer unless the customer is very small. (And, if the customer is that small, it should be easy to make the query fast anyway.)
rhinoceraptor 10y ago
Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet. But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object property to its parent table, which wouldn't be possible with serialized objects.
- drob 10y ago
  We have a bag of utils internally to paper over the missing JSONB functions. This was definitely a headache at first. This is mostly fixed in 9.5: http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-even-more-powerful-tools/ http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-ev...
- pgaddict 10y ago
  And why is this a headache? Every time a new feature is introduced, you have to worry about the PostgreSQL version. If you're developing an application in-house, this is not a big deal - you can make sure you have the right PostgreSQL version. If you're hosting the application on a shared database server, well, you're exactly in the same situation as with other software products.
leothekim 10y ago
"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.
- malisper 10y ago
  At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
  - wvenable 10y ago
    That's perfectly reasonable. But then you also let them query on those arbitrary custom properties and that's where the performance issues are? If so, that's a fairly hard problem to solve. Taking the well-defined subset of searchable properties and making them columns, as described in the article, is the really the best solution.
    - malisper 10y ago
      As of right now, our schema is literally: user_id | event_id | time | data where data is a JSONB blob that contains every other piece of information about an event. Currently, we get a row estimate of one for pretty much every query. We've been able to work around the lack of statistics by using a very specific indexing strategy (discussed about in a talk Dan gave[0]) that gives the planner very few options in terms of planning and additionally by turning off nested loop joins. We are planning on pulling out the most common properties that we store in the data column, which will give us proper statistics on all of those fields. I am currently experimenting with what new indexing strategies we will be able to use thanks to better statistics. [0] https://www.youtube.com/watch?v=NVl9_6J1G60 https://www.youtube.com/watch?v=NVl9_6J1G60
  - leothekim 10y ago
    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.
simiano 10y ago
Keep in mind that Heap collects a huge amount of data and has a huge dataset. I like this[1] talk, it gives you an overview of their architecture. Great post though, thank you for sharing. [1] https://www.youtube.com/watch?v=NVl9_6J1G60 https://www.youtube.com/watch?v=NVl9_6J1G60
vog 10y ago
> It has no way of knowing, for example, that record ->> 'value_2' = 0 will be true about 50% of the time Can't this be solved by introducing an expression index[1] for "record ->> 'value_2'"? This would add a specialized index that will be used of all queries that have a filter like "WHERE record ->> 'value_2' = 0 AND ...". [1] https://www.postgresql.org/docs/current/static/indexes-expressional.html https://www.postgresql.org/docs/current/static/indexes-expre...
- drob 10y ago
  The expression index will make it fast to retrieve the rows for which that predicate is true, but it won't help the planner know that this will be the case for 50% of rows, so I don't think it will change the join that the planner selects (which is the problem here). In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.
  - parenthephobia 10y ago
    Postgres stores statistics for expression indexes, so it can know that the predicate is true for half the rows. In the worked example, adding expression indices for the integer values of value_1, value_2, and value_3 makes the JSONB solution only marginally less efficient than the full-column solution. On my computer, ~300ms instead of ~200ms. (This is Postgres 9.5)
    - malisper 10y ago
      A while ago I came across this thread[0] in which Tom Lane brings up the fact that statistics are kept on functional indexes. I can't remember why, but for some reason I couldn't get the planner to do what I specifically wanted. It may have been a weird detail about composite types. Separately, the big downside I see with this approach is that it requires indexes on every field you would ever query by. If we were to create expression indexes on each field in the jsonb blob, that would effectively double the amount of space being used as well as dramatically increase the write cost. [0] https://www.postgresql.org/message-id/6668.1351105908%40sss.pgh.pa.us https://www.postgresql.org/message-id/6668.1351105908%40sss....
silverlight 10y ago
Does adding a Gin index to the JSONB help this?
- malisper 10y ago
  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.
  - throwa 10y ago
    Does using a UNION OR UNION ALL query instead of a join query reduce the implication of jsonb column not having statistics, especially since the postgresql query planner might not use the nested loop join.
manigandham 10y ago
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.
- tracker1 10y ago
  How do you compare null, true, false, '', 'Y', 'True' in such a case, all are values that might wind up in a boolean field... though if you're using json-schema for your schemas, that's less of an issue in the common case. IMHO anything that is to be queried against regularly should be normalized into an actual column.
  - manigandham 10y ago
    I'm not talking about the values, just the key names themselves... databases should be smart enough to realize that "username" and "id" and "timestamp" are keys repeated in most/every record and normalize them away so there isnt as big of a storage cost.
- hendzen 10y ago
  It's much simpler to just use an off-the-shelf compression (zlib, lz4, etc) on the database pages. This basically has the same effect, but also compresses common values.
  - manigandham 10y ago
    In that case, why isnt that normal and key name size a non-issue with document stores and JSON columns? Am I missing something on why this isnt done already and automatically?
    - malisper 10y ago
      My guess - it's slightly non-trivial and people are working on more important things.
    - hendzen 10y ago
tgarma1234 10y ago
The fact that you can pass attributes for a record into the JSONB field without defining the table structure in advance is really the decisive feature because then you never have to bother with changing your data model. For example, if you have contacts streaming into your table from Android devices you don't need to say whether or not there should be a column for "work email" and "home email2" etc etc... you just send everything into a column with key/value pairs and you can put whatever key/value pairs you want in that column. And then query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key. You can also do updates on the json column AND you get all of the benefits of relational databases with other columns in the same table. I can't really see how anyone would not love this data type now that I have been exposed to it in production.
- pjlegato 10y ago
  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 thought given to this event. Every version of every app and every support program ever written then has to know about each and every historic variant of the data model that ever existed. This is a maintenance nightmare when your app is more than a few iterations old, and when you have several decoupled support systems trying to use the database. With an overt schema, you are required to at least think about what you're doing and to do it in a centralized fashion, rather than slip changes in haphazardly in any app that ever touches the database, and you're required to ensure that the data already in the database actually conforms to the new schema. You won't have one app that puts the work email in "work-email" with a dash, and another that tries to use "work_email" with an underscore, for example.
  - sk5t 10y ago
    Multiple, independent apps poking changes into the database is a kind of failure too. For writer apps > 1 it is often preferable to route them through a middle tier / web API / etc.
crorella 10y ago
I usually use JSONB but keep a non jsonb column as the PK.
edoceo 10y ago
I used JSONB to keep the Tags on an object. Used to be EAV. Works way better here
buremba 10y ago
In fact, JSONB is probably not a good idea when it comes to analytics. The storage is almost x2, accessing attributes are expensive than tabular data even though JSONB is indexed, the data can be dirty (the client can send extra attributes or invalid values for existing attributes, since JSONB doesn't have any schema, Postgresql doesn't validate and cast the values) and as the author mentioned, Postgresql statistics and indexes don't play nicely with JSONB.
palmdeezy 10y ago
Hey SQL newbie question: why use JSONB when you could split out tables into `user` and `user_meta`? isn't that how Wordpress works?
- bruce_one 10y ago
  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 might be better to have explicit columns for those fields) but it's an example of how it can be more powerful than just having an extra "meta" table.)
  - palmdeezy 10y ago
    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`.
knucklesandwich 10y ago
Definitely 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), but some developers believe its worth the headache of going schema-less to avoid migrations. In a sense, that suggestion kind of bit me in the ass when we started having some painfully slow report generation queries that should have been using indexes, but were doing table scans because of the lack of table statistics. In a much larger sense, I'm still thankful we never used MongoDB. Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction if you're really sure the query planner is giving you guff. On more structured data that Postgres can calculate statistics on, let it do its magic. [1]: https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...
- malisper 10y ago
  > Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction We've wanted to do this but the last I checked, Citus, the software we use to shard our postgres databases, isn't able to handle setting configs in a query.
  - knucklesandwich 10y ago
    Ah, bummer, yeah that's a convenient short term fix. It sounds like you all have this handled pretty well though, I think you'll definitely appreciate the move to a more traditional schema. For events data, JSONB support can be nice for infrequently accessed attributes, so its not an all or nothing proposition, but I found I had a lot less headaches after adding more table structure.
    - agentgt 10y ago
      One of the tricks I have found for Postgres to manage analytics and unstructured data is using its inheritance with check constraints aka partitioning features. I mention it because not many people seem to know about this bad ass feature of postgres. We use inheritance (with check constraints) [1] for both time partitioning as well as for custom (aka unstructured) events. Most events have several (100s in our case) common columns. When you see a new custom event with custom fields you want to separate out you can create a subtable and have it inherit from the base event table. Now querying for those custom events is extremely fast and you can query from the base event type or the subtable directly. [1]: https://www.postgresql.org/docs/current/static/ddl-partitioning.html https://www.postgresql.org/docs/current/static/ddl-partition...
warmwaffles 10y ago
Gee go figure that using native columns is faster and better to query over.
sgt 10y ago
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.
IsmaOlvey 10y ago
One 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.

183 points by matm 10y ago

drob 10y ago

Author 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 works particularly well.

willlll 10y ago
Was the 30% disk saving over petabyte+ data set on a single-node Postgres or on your Citus cluster?
- drob 10y ago
  This isn't live yet, but we expect it to be across our citus cluster. The ~30% figure comes from the profiling we did on individual postgres nodes.
mcdee 10y ago
I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue. Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
- malisper 10y ago
  Using @> instead of ->> only causes the selectivity estimate of the predicate to be a different hard coded estimate. It doesn't fix the underlying problem of Postgres not keeping statistics on JSONB.
  - mcdee 10y ago
    True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.
    - malisper 10y ago
      > but it does bring the query response time down to the same order of magnitude as the non-JSON table. In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
      - mcdee 10y ago
        There are two separate issues. The lack of statistics is one thing, but the use of ->> instead of @> is another. Look at https://explain.depesz.com/s/zJiT https://explain.depesz.com/s/zJiT Vs https://explain.depesz.com/s/ihwk https://explain.depesz.com/s/ihwk for the difference.
        malisper 10y ago
        Your queries are executing different plans. The first one is executing a nested loop join which filters out 1,246,035,384 intermediate rows. The second one is executing a index join which doesn't filter out any intermediate rows at all. This seems like it was caused either by the scientist_labs_pkey index not being there in the first trial or just random luck due to a difference in statistics.
iEchoic 10y ago
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 <@, if i remember correctly), which makes easy work of dynamically filtering form submissions on custom form fields even for complex filter conditions. 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.
ngrilly 10y ago
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_Heap_with_PostgreSQL_and_CitusDB_-_Dan_Robinson.pdf http://info.citusdata.com/rs/235-CNE-301/images/Powering_Hea...
- malisper 10y ago
  Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a user at a time. Instead we look for specific types of events in a given time range and group by the user. Clustering by time winds up being a much bigger win (benchmarks showed 10x compared to sorted by user for some uncached queries) here as almost all of our queries are constrained to a given time period. Currently, maintaining the clustering has only been best-effort. We sort our data whenever we copy it from one location to another and the data comes in sorted by time, so it's fairly easy to maintain a high row correlation with time.
  - ngrilly 10y ago
    My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).
    - drob 10y ago
      The data is sharded by customer and then sub-sharded by end user within the customer. For all but the tiny customers, 100% of the data on a logical shard will belong to the same customer. That means our subqueries will never touch data from more than one customer unless the customer is very small. (And, if the customer is that small, it should be easy to make the query fast anyway.)

rhinoceraptor 10y ago

Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet. But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object property to its parent table, which wouldn't be possible with serialized objects.

drob 10y ago
We have a bag of utils internally to paper over the missing JSONB functions. This was definitely a headache at first. This is mostly fixed in 9.5: http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-even-more-powerful-tools/ http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-ev...
pgaddict 10y ago
And why is this a headache? Every time a new feature is introduced, you have to worry about the PostgreSQL version. If you're developing an application in-house, this is not a big deal - you can make sure you have the right PostgreSQL version. If you're hosting the application on a shared database server, well, you're exactly in the same situation as with other software products.

leothekim 10y ago

"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.

malisper 10y ago
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
- wvenable 10y ago
  That's perfectly reasonable. But then you also let them query on those arbitrary custom properties and that's where the performance issues are? If so, that's a fairly hard problem to solve. Taking the well-defined subset of searchable properties and making them columns, as described in the article, is the really the best solution.
  - malisper 10y ago
    As of right now, our schema is literally: user_id | event_id | time | data where data is a JSONB blob that contains every other piece of information about an event. Currently, we get a row estimate of one for pretty much every query. We've been able to work around the lack of statistics by using a very specific indexing strategy (discussed about in a talk Dan gave[0]) that gives the planner very few options in terms of planning and additionally by turning off nested loop joins. We are planning on pulling out the most common properties that we store in the data column, which will give us proper statistics on all of those fields. I am currently experimenting with what new indexing strategies we will be able to use thanks to better statistics. [0] https://www.youtube.com/watch?v=NVl9_6J1G60 https://www.youtube.com/watch?v=NVl9_6J1G60
- leothekim 10y ago
  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.

simiano 10y ago

Keep in mind that Heap collects a huge amount of data and has a huge dataset. I like this[1] talk, it gives you an overview of their architecture. Great post though, thank you for sharing. [1] https://www.youtube.com/watch?v=NVl9_6J1G60 https://www.youtube.com/watch?v=NVl9_6J1G60

vog 10y ago

> It has no way of knowing, for example, that record ->> 'value_2' = 0 will be true about 50% of the time Can't this be solved by introducing an expression index[1] for "record ->> 'value_2'"? This would add a specialized index that will be used of all queries that have a filter like "WHERE record ->> 'value_2' = 0 AND ...". [1] https://www.postgresql.org/docs/current/static/indexes-expressional.html https://www.postgresql.org/docs/current/static/indexes-expre...

drob 10y ago
The expression index will make it fast to retrieve the rows for which that predicate is true, but it won't help the planner know that this will be the case for 50% of rows, so I don't think it will change the join that the planner selects (which is the problem here). In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.
- parenthephobia 10y ago
  Postgres stores statistics for expression indexes, so it can know that the predicate is true for half the rows. In the worked example, adding expression indices for the integer values of value_1, value_2, and value_3 makes the JSONB solution only marginally less efficient than the full-column solution. On my computer, ~300ms instead of ~200ms. (This is Postgres 9.5)
  - malisper 10y ago
    A while ago I came across this thread[0] in which Tom Lane brings up the fact that statistics are kept on functional indexes. I can't remember why, but for some reason I couldn't get the planner to do what I specifically wanted. It may have been a weird detail about composite types. Separately, the big downside I see with this approach is that it requires indexes on every field you would ever query by. If we were to create expression indexes on each field in the jsonb blob, that would effectively double the amount of space being used as well as dramatically increase the write cost. [0] https://www.postgresql.org/message-id/6668.1351105908%40sss.pgh.pa.us https://www.postgresql.org/message-id/6668.1351105908%40sss....

silverlight 10y ago

Does adding a Gin index to the JSONB help this?

malisper 10y ago
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.
- throwa 10y ago
  Does using a UNION OR UNION ALL query instead of a join query reduce the implication of jsonb column not having statistics, especially since the postgresql query planner might not use the nested loop join.

manigandham 10y ago

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.

tracker1 10y ago
How do you compare null, true, false, '', 'Y', 'True' in such a case, all are values that might wind up in a boolean field... though if you're using json-schema for your schemas, that's less of an issue in the common case. IMHO anything that is to be queried against regularly should be normalized into an actual column.
- manigandham 10y ago
  I'm not talking about the values, just the key names themselves... databases should be smart enough to realize that "username" and "id" and "timestamp" are keys repeated in most/every record and normalize them away so there isnt as big of a storage cost.
hendzen 10y ago
It's much simpler to just use an off-the-shelf compression (zlib, lz4, etc) on the database pages. This basically has the same effect, but also compresses common values.
- manigandham 10y ago
  In that case, why isnt that normal and key name size a non-issue with document stores and JSON columns? Am I missing something on why this isnt done already and automatically?
  - malisper 10y ago
    My guess - it's slightly non-trivial and people are working on more important things.
  - hendzen 10y ago

tgarma1234 10y ago

The fact that you can pass attributes for a record into the JSONB field without defining the table structure in advance is really the decisive feature because then you never have to bother with changing your data model. For example, if you have contacts streaming into your table from Android devices you don't need to say whether or not there should be a column for "work email" and "home email2" etc etc... you just send everything into a column with key/value pairs and you can put whatever key/value pairs you want in that column. And then query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key. You can also do updates on the json column AND you get all of the benefits of relational databases with other columns in the same table. I can't really see how anyone would not love this data type now that I have been exposed to it in production.

pjlegato 10y ago
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 thought given to this event. Every version of every app and every support program ever written then has to know about each and every historic variant of the data model that ever existed. This is a maintenance nightmare when your app is more than a few iterations old, and when you have several decoupled support systems trying to use the database. With an overt schema, you are required to at least think about what you're doing and to do it in a centralized fashion, rather than slip changes in haphazardly in any app that ever touches the database, and you're required to ensure that the data already in the database actually conforms to the new schema. You won't have one app that puts the work email in "work-email" with a dash, and another that tries to use "work_email" with an underscore, for example.
- sk5t 10y ago
  Multiple, independent apps poking changes into the database is a kind of failure too. For writer apps > 1 it is often preferable to route them through a middle tier / web API / etc.

crorella 10y ago

I usually use JSONB but keep a non jsonb column as the PK.

edoceo 10y ago

I used JSONB to keep the Tags on an object. Used to be EAV. Works way better here

buremba 10y ago

In fact, JSONB is probably not a good idea when it comes to analytics. The storage is almost x2, accessing attributes are expensive than tabular data even though JSONB is indexed, the data can be dirty (the client can send extra attributes or invalid values for existing attributes, since JSONB doesn't have any schema, Postgresql doesn't validate and cast the values) and as the author mentioned, Postgresql statistics and indexes don't play nicely with JSONB.

palmdeezy 10y ago

Hey SQL newbie question: why use JSONB when you could split out tables into `user` and `user_meta`? isn't that how Wordpress works?

bruce_one 10y ago
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 might be better to have explicit columns for those fields) but it's an example of how it can be more powerful than just having an extra "meta" table.)
- palmdeezy 10y ago
  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`.

knucklesandwich 10y ago

Definitely 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), but some developers believe its worth the headache of going schema-less to avoid migrations. In a sense, that suggestion kind of bit me in the ass when we started having some painfully slow report generation queries that should have been using indexes, but were doing table scans because of the lack of table statistics. In a much larger sense, I'm still thankful we never used MongoDB. Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction if you're really sure the query planner is giving you guff. On more structured data that Postgres can calculate statistics on, let it do its magic. [1]: https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...

malisper 10y ago
> Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction We've wanted to do this but the last I checked, Citus, the software we use to shard our postgres databases, isn't able to handle setting configs in a query.
- knucklesandwich 10y ago
  Ah, bummer, yeah that's a convenient short term fix. It sounds like you all have this handled pretty well though, I think you'll definitely appreciate the move to a more traditional schema. For events data, JSONB support can be nice for infrequently accessed attributes, so its not an all or nothing proposition, but I found I had a lot less headaches after adding more table structure.
  - agentgt 10y ago
    One of the tricks I have found for Postgres to manage analytics and unstructured data is using its inheritance with check constraints aka partitioning features. I mention it because not many people seem to know about this bad ass feature of postgres. We use inheritance (with check constraints) [1] for both time partitioning as well as for custom (aka unstructured) events. Most events have several (100s in our case) common columns. When you see a new custom event with custom fields you want to separate out you can create a subtable and have it inherit from the base event table. Now querying for those custom events is extremely fast and you can query from the base event type or the subtable directly. [1]: https://www.postgresql.org/docs/current/static/ddl-partitioning.html https://www.postgresql.org/docs/current/static/ddl-partition...

warmwaffles 10y ago

Gee go figure that using native columns is faster and better to query over.

sgt 10y ago

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.

IsmaOlvey 10y ago

One 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.