5 ms·

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

dkersten 5y ago
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.
- 1propionyl 5y ago
  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.
- fn1 5y ago
  > 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'));
  - dkersten 5y ago
    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.
mdaniel 5y ago
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#FUNCTIONS-SQLJSON-PATH 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

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.

1propionyl 5y ago
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.
fn1 5y ago
> 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'));
- dkersten 5y ago
  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.

mdaniel 5y ago

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#FUNCTIONS-SQLJSON-PATH 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