Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

When to Avoid JSONB in a PostgreSQL Schema

blog.heapanalytics.com

Post reply on HN
Print view View original

41–50 of 115 posts

Re: When to Avoid JSONB in a PostgreSQL Schema

#41

Post by kornish »

cstavish wrote: post #40
chc wrote: post #34

Earlier quoted context omitted.

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…

Sounds kind of like the EAV relational data model: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80....

Re: When to Avoid JSONB in a PostgreSQL Schema

#42

Post by malisper »

cstavish wrote: post #40
chc wrote: post #34

Earlier quoted context omitted.

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…

This is roughly EAV. EAV is where you have a with a schema:

    entity_id | attribute_id | value
EAV is typically considered to be an anti-pattern for several reasons: it becomes very expensive to rematerialize a entity with all of its attributes/values, it becomes difficult to have any kind of constraints on the values (this is also a problem with jsonb), and it's hard for the database to maintain good statistics on the values of different attributes as most databases don't keep multi-column statistics. Don't worry, I've had similar ideas before.

Re: When to Avoid JSONB in a PostgreSQL Schema

#43

Post by BillinghamJ »

cstavish wrote: post #40
chc wrote: post #34

Earlier quoted context omitted.

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…

I would expect that to perform poorly at scale.

There is also a fifth option, though it is not very space efficient. That is to store every field in the JSONB column. Personally, that is what I would do.

This approach is known to be used successfully by many high scale companies. It ensures the highest degree of flexibility and still allows for full indexing of the fields. The schema would need to be enforced by your application, but that should happen anyway.

Re: When to Avoid JSONB in a PostgreSQL Schema

#44

Post by tgarma1234 »

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.

Re: When to Avoid JSONB in a PostgreSQL Schema

#46

Post by 00098345 »

tgarma1234 wrote: post #44

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 se…

This is good but please, to preserve your own sanity, first design the table struct properly to capture all attributes you can think of in separate columns and add the appropriate primary key. Then add the extra JSON(B) column for unforeseen data. Keep iterating till the table is still small.

Re: When to Avoid JSONB in a PostgreSQL Schema

#47

Post by parenthephobia »

drob wrote: post #23
vog wrote: post #21

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

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, bu…

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)

Re: When to Avoid JSONB in a PostgreSQL Schema

#48

Post by tracker1 »

leothekim wrote: post #11

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

For me a great example is additional/diagnostic information related to other systems...

In one such example would be a transaction table, where the transaction info I care about are dedicated columns, but additional details from say paypal, or another payment processor would be stored as JSON, as there is little value to parsing the detailed information into a unified format, that will always have "extra" information for that processor, and without the need of dedicated tables for each payment processor.

Another example would be logging, where some information is consistent, but other additional information is mainly just stored. I also like jsonl (line terminated json, one record per line) in a gzip stream works very well... as an aside from a db server.

There are other considerations as well, in some cases a document store such as mongo or rethinkdb may be closer to what you ideally want, for that matter you may need something closer to Cassandra.

Re: When to Avoid JSONB in a PostgreSQL Schema

#50

Post by tracker1 »

manigandham wrote: post #31

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.

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.

Post reply on HN