Hacker News Reader

Comment by gunapologist99 | Hacker News Reader

If you use any of the really amazing JSON or Array stuff, you're stepping outside of the standard.. but it's worth it!

I absolutely love Postgres' JSON and array support. Its saved me from needing a document database in a few cases, which drastically simplified the overall deployment (one DB instead of two, or trying to represent relational stuff in a document DB). I really like JSON for stuff that is user defined, that I don't need to query into, but postgres' support means I still can should I ever need to. Which has happened once or twice for debugging.

100%. I've dealt with many small-to-medium sized projects where the usual approach would have been some form of EAV mess.

Just using a BSON column for such attributes hits the Pareto 80/20 sweet spot, _and_ I can query against them much more easily than in an EAV schema (far fewer joins).

They're great for when the schema is mostly normalized, but there's just a little bit of denormalized stuff that doesn't warrant completely architecting the schema around it.

> but postgres' support means I still can should I ever need to.

And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE:

CREATE INDEX ON publishers((info->>'name'));

Yes, Postgres' jsonb and array support lets you not only query into these fields, but also, like you say, index into them too. Its really the best of both worlds: you get a great solid relational database, but if you need document database features, you don't really lose out on anything by using json, except that the queries are a bit more ugly. I'm super happy with postgres these days, its rock solid and full of fantastic features.

The word "any" there is mildly misleading about the JSON things, since there is now SQL/JSON which PG supports: https://www.postgresql.org/docs/13/functions-json.html#FUNCT...

It would be horribly painful to stick to _just_ SQL/JSON for manipulations, and I have absolutely zero idea what the coverage is in other platforms, but portability isn't completely off the table like it used to be

Reply on news.ycombinator.com