4 ms·

I have never regretted it. We dropped Mongo completely about a year after JSONB support was released for Postgres. JSONB indexes perform very well, and can be t

I have never regretted it. We dropped Mongo completely about a year after JSONB support was released for Postgres. JSONB indexes perform very well, and can be tuned with different index types (e.g. btree, gin, hash). You get the best of both worlds, i.e. relational models with joins and aggregates, as well as flexible and dynamic document storage. If you want a pure JSON interface to your database, use PostgREST: https://postgrest.org/en/v5.0/api.html https://postgrest.org/en/v5.0/api.html

sv12l 8y ago
Just checking, as far as I understand, at the moment Postgres can't add statistics on the JSONB type. Did that affect your query optimizer by any chance? or noticed any degradation in performance?
- netcraft 8y ago
  You can get around that with functional indexes https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-and-statistics/ https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-a... I'm not working with tens of millions of records, but I haven't needed to go beyond GIN indexes yet - might just be our documents or our workload though. ymmv.
  - ris 8y ago
    But ... once you're deciding what expression indexes to add you effectively have a schema, yeah?
    - qaq 8y ago
      well thats not too different from NoSQL you will get something like GIN index by default at best. In mongo you also have to create indexes.
ganonm 8y ago
My experience with JSONB is that you should think very carefully before choosing to use it as it can become a real footgun if used inappropriately. An example of a (possibly) good use case would be e.g. user preferences/settings - there is often a lot of them and the relationship between them is often hierarchical. You could use e.g. a junction table that maps users to settings, but this can be unwieldy. A JSONB value can just be sent directly to client. It is possible to be reasonably certain that you won't want to do aggregate SQL operations on the entire table and thus just being able to do CRUD operations on single rows of the table avoids the pain of JSONB aggregate queries. For the record, I would probably still use a SQL solution in this case. An example of a bad use case (which I saw in the wild) would be lumping entire user conversations (think Facebook comments) into a single JSONB value. This might naively seem like a time saver since you don't need a 'conversations' table, a 'conversation_messages' table nor the JOIN to aggregate the required data to reconstruct an entire conversation. However, as soon as you want to do _aggregate_ operations across several/all conversations it becomes a real hassle. Say for example you wanted to gather all messages that had been sent in the last hour - using a traditional SQL solution, this would be trivial and highly performant. With a JSONB solution you have to hope you have a JSONB index on the timestamp and then you also have to work out the correct JSONB syntax to do the query. Constructing non-trivial JSONB queries can get really gnarly - trust me, I've been there. From my (somewhat) limited experience with JSONB I would now generally avoid it except for the cases where either the user wants to quite literally store JSON (and now we get some nice JSON validation for free) _or_ for the case where I am absolutely 100% sure I will never need to do any non-trivial aggregate operations on the JSONB and that the upfront implementation cost of a SQL solution would be stupendously high compared to JSONB. An example that comes to mind would be an Evernote style application where the user data is semi-structured and we don't generally speaking want to run aggregate operations on it (it's their private data).
- emidln 8y ago
  We abstracted JSONB operations into the Clojure Query AST library HoneySQL and never really looked back. It was something one person on the team learned thoroughly for a half day and promptly forgot about. Now we say things like: (jsonb/get-in :jsonb-column [:field :sub-field]) ;; jsonb_column#>>'{field, subfield}' (jsonb/get-in :jsonb-column [:field :sub-field] "default") ;; COALESCE(jsonb_column#>>'{field, subfield}', 'default') (jsonb/get-in :jsonb-column [:field]) ;; jsonb_column->>'field' Which each generate the appropriate data structures for HoneySQL to do some combination of using #>>, ->> and COALESCE as needed. I can't imagine this is too difficult in any language. Even if you are writing static SQL, I don't think learning the syntax to go with a feature is too high of an ask to use a feature.

sv12l 8y ago

Just checking, as far as I understand, at the moment Postgres can't add statistics on the JSONB type. Did that affect your query optimizer by any chance? or noticed any degradation in performance?

netcraft 8y ago
You can get around that with functional indexes https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-and-statistics/ https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-a... I'm not working with tens of millions of records, but I haven't needed to go beyond GIN indexes yet - might just be our documents or our workload though. ymmv.
- ris 8y ago
  But ... once you're deciding what expression indexes to add you effectively have a schema, yeah?
  - qaq 8y ago
    well thats not too different from NoSQL you will get something like GIN index by default at best. In mongo you also have to create indexes.

ganonm 8y ago

My experience with JSONB is that you should think very carefully before choosing to use it as it can become a real footgun if used inappropriately. An example of a (possibly) good use case would be e.g. user preferences/settings - there is often a lot of them and the relationship between them is often hierarchical. You could use e.g. a junction table that maps users to settings, but this can be unwieldy. A JSONB value can just be sent directly to client. It is possible to be reasonably certain that you won't want to do aggregate SQL operations on the entire table and thus just being able to do CRUD operations on single rows of the table avoids the pain of JSONB aggregate queries. For the record, I would probably still use a SQL solution in this case. An example of a bad use case (which I saw in the wild) would be lumping entire user conversations (think Facebook comments) into a single JSONB value. This might naively seem like a time saver since you don't need a 'conversations' table, a 'conversation_messages' table nor the JOIN to aggregate the required data to reconstruct an entire conversation. However, as soon as you want to do _aggregate_ operations across several/all conversations it becomes a real hassle. Say for example you wanted to gather all messages that had been sent in the last hour - using a traditional SQL solution, this would be trivial and highly performant. With a JSONB solution you have to hope you have a JSONB index on the timestamp and then you also have to work out the correct JSONB syntax to do the query. Constructing non-trivial JSONB queries can get really gnarly - trust me, I've been there. From my (somewhat) limited experience with JSONB I would now generally avoid it except for the cases where either the user wants to quite literally store JSON (and now we get some nice JSON validation for free) _or_ for the case where I am absolutely 100% sure I will never need to do any non-trivial aggregate operations on the JSONB and that the upfront implementation cost of a SQL solution would be stupendously high compared to JSONB. An example that comes to mind would be an Evernote style application where the user data is semi-structured and we don't generally speaking want to run aggregate operations on it (it's their private data).

emidln 8y ago
We abstracted JSONB operations into the Clojure Query AST library HoneySQL and never really looked back. It was something one person on the team learned thoroughly for a half day and promptly forgot about. Now we say things like: (jsonb/get-in :jsonb-column [:field :sub-field]) ;; jsonb_column#>>'{field, subfield}' (jsonb/get-in :jsonb-column [:field :sub-field] "default") ;; COALESCE(jsonb_column#>>'{field, subfield}', 'default') (jsonb/get-in :jsonb-column [:field]) ;; jsonb_column->>'field' Which each generate the appropriate data structures for HoneySQL to do some combination of using #>>, ->> and COALESCE as needed. I can't imagine this is too difficult in any language. Even if you are writing static SQL, I don't think learning the syntax to go with a feature is too high of an ask to use a feature.