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

Using PostgreSQL for JSON Storage

info.crunchydata.com

Post reply on HN
Print view View original

11–20 of 29 posts

Re: Using PostgreSQL for JSON Storage

#11

Post by thoraway1010 »

Also because the JSONB is so close to a relational data model it's a bit easier I've found to pick up some relational data work when needed or visa versa.

Accounting for user balances vis a vis some clearing and payment processing accounts? Let's do that with ref integrity and relational model.

Developers want to add all sorts of cruft and notes and weird data to the user record or order record they may want to look back on once in a while or display somehow, give them JSONB.

The alternative seems to a docDB that folks stretch 100 ways to sunday to try and shoehorn a relational model in, or visa versa (exploding schemas and slow dev velocity as whoever manages DB complains).

And with AWS doing hosted postgresql RDS somewhat recently with t3 micros as an option - it's really easy to reach for this hammer. I just do a 1 year reserved instance at $5/month for development, and you can deploy to any size.

Re: Using PostgreSQL for JSON Storage

#12

Post by thisgoodlife »

koolba wrote: post #8

> Let's get the users last name. SELECT json_content ##> {person, last_name} FROM mytable; > The #> or #> is the JSON path navigator with the difference being #> returns JSON and the ##> returns the JSON text value. That should be #>> (two ">"), not ##> (two "#"). The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/doc…

I don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.

Re: Using PostgreSQL for JSON Storage

#13

Post by kumarvvr »

Not to complain, but it seems the Web has become a huge spam bucket with useful nuggets few and far between.

I have seen such articles a gazillion times till now.

An article that talks about a subject, very very superficially, includes an obvious example, and concludes with a generic statement.

Maybe the "users are the content creators" is not such a good idea after all.

Re: Using PostgreSQL for JSON Storage

#14

Post by kumarvvr »

stickfigure wrote: post #10

I use JSONB heavily for a few things: * Tags. Basically the same as array of strings, which postgres also supports, but I don't need to remember a different set of operators. * Polymorphic data. Instead of countless nullable fields that represent the union of all fields of all subtypes, I just include a single (or in some cases, a couple) JSONB columns whose schema varies by type. The schema is enforced by types at t…

> Polymorphic data. Instead of countless nullable fields that represent the union of all fields of all subtypes, I just include a single (or in some cases, a couple) JSONB columns whose schema varies by type. The schema is enforced by types at the application layer.

I am surprised there aren't many database interface libraries that build upon this. This is a very powerful aspect of JSONB in PGSQL.

Re: Using PostgreSQL for JSON Storage

#15

Post by agentultra »

I use JSONB heavily in a large application in production for many years. A few things to be careful of:

Unbounded Data this will have performance problems for your data set as the size of your table grows. One single value in one row that is 8MB of JSON will bring down the performance of an entire collection.

which leads to

Schema keep your documents structured and validate them before they touch the database. At least the parts your indexes care about. GIN indexes can get pretty big.

Indexes GIN indexes are fairly costly to produce on large data sets. You may need to consider partitioning and careful use of GIN indexes to maintain fast query speeds with online updates.

Re: Using PostgreSQL for JSON Storage

#16

Post by fyp »

This is probably premature optimization, but what's the performance cost of updating a deeply nested field inside a jsonb? What about indexing it?

(compared to storing in some normalized form or in other popular document stores)

Re: Using PostgreSQL for JSON Storage

#17

Post by baq »

thisgoodlife wrote: post #12
koolba wrote: post #8

> Let's get the users last name. SELECT json_content ##> {person, last_name} FROM mytable; > The #> or #> is the JSON path navigator with the difference being #> returns JSON and the ##> returns the JSON text value. That should be #>> (two ">"), not ##> (two "#"). The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/doc…

I don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.

They’d have to invent a syntax for infix function calls akin to Haskell’s backticks which other people would complain about.

(I’m happy we don’t have to write queries in yaml, only half joking.)

Re: Using PostgreSQL for JSON Storage

#18

Post by craigkerstiens »

kumarvvr wrote: post #13

Not to complain, but it seems the Web has become a huge spam bucket with useful nuggets few and far between. I have seen such articles a gazillion times till now. An article that talks about a subject, very very superficially, includes an obvious example, and concludes with a generic statement. Maybe the "users are the content creators" is not such a good idea after all.

I don't disagree that this article isn't news to anyone following Postgres and having used it. Yet I also have a conversation with someone about once a week that has no idea Postgres has any form of JSON support much less whats possible with it. There are a lot more beginners out there in the world and this type of content is useful to them.

A deep dive of how GIN indexing works for JSON might be a useful nugget for those that want to dive really deep, but there is also a place for plenty of beginner content and things that may seem obvious to you.

Re: Using PostgreSQL for JSON Storage

#19

Post by spousty »

koolba wrote: post #8

> Let's get the users last name. SELECT json_content ##> {person, last_name} FROM mytable; > The #> or #> is the JSON path navigator with the difference being #> returns JSON and the ##> returns the JSON text value. That should be #>> (two ">"), not ##> (two "#"). The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/doc…

I guess PostgreSQL doesn't do everything for me :D Thanks for catching those and I will be updating the post soon.

Did you get a chance to try the hands on exercise?

Re: Using PostgreSQL for JSON Storage

#20

Post by kumarvvr »

craigkerstiens wrote: post #18
kumarvvr wrote: post #13

Not to complain, but it seems the Web has become a huge spam bucket with useful nuggets few and far between. I have seen such articles a gazillion times till now. An article that talks about a subject, very very superficially, includes an obvious example, and concludes with a generic statement. Maybe the "users are the content creators" is not such a good idea after all.

I don't disagree that this article isn't news to anyone following Postgres and having used it. Yet I also have a conversation with someone about once a week that has no idea Postgres has any form of JSON support much less whats possible with it. There are a lot more beginners out there in the world and this type of content is useful to them. A deep dive of how GIN indexing works for JSON might be a useful nugget for…

I understand your point. But my contention is that there is a lot of spam, especially in the beginner content. So much so that real useful information is nowhere to be found in search engines.

I have recently started learning React and when I search for a topic, I have to wade through a myriad of articles that are basically "Get Node, Get React, CRA new, etc. etc." Nothing of actual use, but search engines pick up because of SEO I guess.

Finding something of value is becoming increasingly difficult. On top of that, I use DDG as my primary search engine and it doesn't help.

For articles regarding PGSQL, their documentation is the ultimate source and I frankly see no reason for these articles to exist.

Post reply on HN