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

71–80 of 115 posts

Re: When to Avoid JSONB in a PostgreSQL Schema

#71

Post by malisper »

strommen wrote: post #70
malisper wrote: post #42

Earlier quoted context omitted.

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…

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.

Re: When to Avoid JSONB in a PostgreSQL Schema

#72

Post by manigandham »

hendzen wrote: post #66
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.

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.

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?

Re: When to Avoid JSONB in a PostgreSQL Schema

#73

Post by malisper »

hendzen wrote: post #66
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.

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.

Postgres does use a variation of lz when using TOAST. TOAST is the technique Postgres uses to store attributes that are too big to store with the rest of the row. Unfortunately, TOAST only works within a single attribute, it doesn't work across multiple rows, or even across multiple attributes of the same row.

Re: When to Avoid JSONB in a PostgreSQL Schema

#74

Post by malisper »

manigandham wrote: post #72
hendzen wrote: post #66

Earlier quoted context omitted.

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.

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?

My guess - it's slightly non-trivial and people are working on more important things.

Re: When to Avoid JSONB in a PostgreSQL Schema

#75

Post by taspeotis »

chc wrote: post #34
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.

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…

> 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

Depending on your RDBMS this is not actually so bad. SQL Server has sparse column support which helps to make this sort of schema practical. It comes with some strings attached, however [1].

[1] https://msdn.microsoft.com/en-AU/library/cc280604.aspx

Re: When to Avoid JSONB in a PostgreSQL Schema

#76

Post by Matt3o12_ »

tgarma1234 wrote: post #62
pjlegato wrote: post #56

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

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 that you should not use JSONB for that (I probably would too for that example), Im just saying there are more ways to design your data storages.

Re: When to Avoid JSONB in a PostgreSQL Schema

#77

Post by inopinatus »

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…

> "Query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key".

Having learned that postgresql NULLs are cheap in storage (since they just use a bitmap) I stopped caring much about empty column proliferation.

I've also found that arbitrary user-defined keys are still better treated via a "known keys" table, the long-term visibility into data is invaluable, there's more join flexibility, and there's opportunity to assign metadata (e.g. type hints) for each user-defined key. At scale, these things make json columns look like technical debt.

The only use case I currently have for jsonb is when the absence of an element is meaningfully different to a null value e.g. options structures that wouldn't otherwise benefit from being in normalized tables.

Re: When to Avoid JSONB in a PostgreSQL Schema

#78

Post by inopinatus »

sk5t wrote: post #59
pjlegato wrote: post #56

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

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.

I think this approach creates bottlenecks and ultimately has an inner-platform effect.

Use your RDBMS to the max, they are powerful tools!

Re: When to Avoid JSONB in a PostgreSQL Schema

#79

Post by ngrilly »

inopinatus wrote: post #77
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…

> "Query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key". Having learned that postgresql NULLs are cheap in storage (since they just use a bitmap) I stopped caring much about empty column proliferation. I've also found that arbitrary user-defined keys are still better treated via a "known keys" table, the long-term visibility into data is inv…

What is a "known keys" table?

Is it a table with columns like user_defined_text_1, user_defined_text_2, ..., user_defined_text_10, user_defined_integer_1, user_defined_integer_2, ... user_defined_integer_n ?

Re: When to Avoid JSONB in a PostgreSQL Schema

#80

Post by azinman2 »

pjlegato wrote: post #56
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 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...

Post reply on HN