Hacker News Reader

Comment by sethhochberg | Hacker News Reader

They both have their issues (though I think in most cases Postgres has saner defaults. Alternative distributions of MySQL like Percona Server can help improve the situation somewhat for MySQL).

Doing anything meaningfully complex or mission-critical with either will always require care, attention, and understanding of how the database is doing its work. If you know MySQL internals particularly better, it may benefit you to focus your efforts there as modern MySQL is perfectly capable (decent online DDL support, decent native JSON support, etc).

If your team aren't experts with either, I'd invest my effort in learning Postgres.

View on HN

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.

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.

Also you cannot store foreign keys in JSON.

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?

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-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.)

Reply on news.ycombinator.com