3 ms·

I have extensive experience with MySQL. In fact I used to run a really big social network (70M+ users) based on MySQL db. Main reason we chose Postgres was tha

I have extensive experience with MySQL. In fact I used to run a really big social network (70M+ users) based on MySQL db. Main reason we chose Postgres was that JSON fields have been around for a few years. We really like the Mongo feature-set, but aren't very happy with reliability. In every discussion about Mongo, people used to recommend Postgres instead.

toomanybeersies 8y ago
I'm currently working on a product that uses JSONb columns extensively. To be honest, I don't like it. I'm not sure if it's bad design, or if it's just bad to mix relational databases with JSON, but I'm constantly battling to do things that I would find trivial in SQL. I guess it really depends on your requirements though. I've found that JSONb is great for storing historical data and results, write-once sort of stuff. I've found it's not so good for storing objects that get modified, especially if a relation can change.
- codedokode 8y ago
  Also you cannot store foreign keys in JSON.
- timewarrior 8y ago
  If I want to use it as a write only table where I would like to get virtual indexes for values inside the JSONb column. Would you recommend using Postgres for this usecase?
  - guiriduro 8y ago
    This discussion might be useful re: indexing JSONb columns and a comparison of performance (a bit out of date, things have probably improved even further); http://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes http://bitnine.net/blog-postgresql/postgresql-internals-json... The GIN index is an inverted index, if you're expecting to query against several keys; alternatively if you have a large keyspace and no need to query outside a small number of properties, you could create individual hash or btree indexes for each one. Postgres is good for this usecase, but as always, YMMV, consider alternatives/optimizations if your scale or write-volume dictate otherwise (e.g. sharding, Citusdb etc.)

toomanybeersies 8y ago

I'm currently working on a product that uses JSONb columns extensively. To be honest, I don't like it. I'm not sure if it's bad design, or if it's just bad to mix relational databases with JSON, but I'm constantly battling to do things that I would find trivial in SQL. I guess it really depends on your requirements though. I've found that JSONb is great for storing historical data and results, write-once sort of stuff. I've found it's not so good for storing objects that get modified, especially if a relation can change.

codedokode 8y ago
Also you cannot store foreign keys in JSON.
timewarrior 8y ago
If I want to use it as a write only table where I would like to get virtual indexes for values inside the JSONb column. Would you recommend using Postgres for this usecase?
- guiriduro 8y ago
  This discussion might be useful re: indexing JSONb columns and a comparison of performance (a bit out of date, things have probably improved even further); http://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes http://bitnine.net/blog-postgresql/postgresql-internals-json... The GIN index is an inverted index, if you're expecting to query against several keys; alternatively if you have a large keyspace and no need to query outside a small number of properties, you could create individual hash or btree indexes for each one. Postgres is good for this usecase, but as always, YMMV, consider alternatives/optimizations if your scale or write-volume dictate otherwise (e.g. sharding, Citusdb etc.)