Hacker News Reader

Comment by dkersten | Hacker News Reader

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.

View on HN

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.

Reply on news.ycombinator.com