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

61–70 of 115 posts

Re: When to Avoid JSONB in a PostgreSQL Schema

#61

Post by knucklesandwich »

teraflop wrote: post #60
tgarma1234 wrote: post #53

Earlier quoted context omitted.

You will never think of all the attributes anymore. For example 8 months later you want to score your contacts based on derived or imputed values. Who knows what you will want to slap in there when you scale up to 5 billion rows and start including click data from your website for example... there are basics from relational concepts that are obvious, which is why you are using postgres in the first place but table st…

"I might want to add more columns later" is not a good justification for avoiding a relational model. If you realize that you want an additional column, just run an ALTER TABLE statement.

Totally agreed. Some people have an unreasonable fear of migrations (not to try to put words in the above poster's mouth, just pointing out something that I've noticed). You generally shouldn't worry about migration-related downtime with Postgres if you're not using table constraints, using columns with defaults, or changing a column's type. There are very few migration procedures that require something like a table rewrite these days, or even an index rebuild.

Re: When to Avoid JSONB in a PostgreSQL Schema

#62

Post by tgarma1234 »

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…

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 super convenient to just use the same tables as you have been using all along. New users are different in that case but not so totally different so just but the new attributes into the column keyed on "pivot1" or whatever.

Re: When to Avoid JSONB in a PostgreSQL Schema

#63

Post by colanderman »

inopinatus wrote: post #38
vog wrote: post #17

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

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

To be fair, you can write CHECK conditions which are arbitrary expressions involving JSONB values. Though at that point you might as well just use a normal column.

Re: When to Avoid JSONB in a PostgreSQL Schema

#64

Post by malisper »

knucklesandwich wrote: post #58

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

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

Re: When to Avoid JSONB in a PostgreSQL Schema

#66

Post by hendzen »

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.

Re: When to Avoid JSONB in a PostgreSQL Schema

#67

Post by knucklesandwich »

malisper wrote: post #64
knucklesandwich wrote: post #58

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

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

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.

Re: When to Avoid JSONB in a PostgreSQL Schema

#68

Post by simcop2387 »

vog wrote: post #18
vog wrote: post #17

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

By the way, another related anti-pattern is the introduction of an additional table that has a 1:1 relationship to the original table. Because the original table was "full" of columns, or something, so a second table had to be opened to contain the remaining columns. This is an anti-pattern because you can't model "1:1" that way. Instead, it will be "1:0..1", and now you have some nasty corner cases when the first ta…

The way I have seen for the issue of which table is to introduce a view and setup triggers on the view to run a stored proc when you insert or update it to handle all the tables....

Re: When to Avoid JSONB in a PostgreSQL Schema

#69

Post by strommen »

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.

Data lives far, far longer than applications. You may not have multiple apps using the database now, but if your system lasts 10 years or more than it certainly will.

Re: When to Avoid JSONB in a PostgreSQL Schema

#70

Post by strommen »

malisper wrote: post #42
cstavish wrote: post #40

Earlier quoted context omitted.

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…

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

Post reply on HN