Re:Migrating Facebook to MySQL 8.0
by dkersten on Thursday July 22, 2021 @05:32PM
(#27924604)
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.
[ Parent
| on HN ]
Re:Migrating Facebook to MySQL 8.0
by 1propionyl on Thursday July 22, 2021 @08:49PM
(#27926111)
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.