4 ms·

We recently started investing in Postgres because of support of JSON fields and nested indexes in those fields. Should we have chosen MySQL?

deleted 8y ago
[deleted]
lathiat 8y ago
MySQL has I think all of these features now in 8.0 off the top of my head. Having said that it was only just released to stable very recently and like all good things it may pay to wait for a few more edge cases to be flexed out. Ultimately I’d always suggest the tool you are most familiar with if it’s doing a good enough job.
sethhochberg 8y ago
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.
- timewarrior 8y ago
  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.)
emilsedgh 8y ago
Probably no. As someone else pointed out, the reason so many similar tools exist for this task on mysql and there's no such tool for postgres is not that postgres isn't as popular. The reason is that this problem is almost non-existent on postgres as many table alterations do not lock the table.
- idunno246 8y ago
  All alters require a full read/write lock, it’s just that most return instantly. This can be a problem if you have long running transactions, as the alter blocks behind all open txns and all new queries block behind that. python for instance has a very strong opinion that you should be using transactions for everything, and is much more likely to have to deal with it than say ruby. But you’re right, my comment is mostly pedantic, that Postgres implements alters better so these tools aren’t needed.
  - timewarrior 8y ago
    This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.
  - paulryanrogers 8y ago
    There are some techniques for mitigating those, such as adding new columns as nullable without a default.
    - idunno246 8y ago
      Right, adding a column with a default means the alter takes time while holding that lock and nothing can be read/written so is generally unsafe for big tables, but it doesn’t help if the alter can’t acquire the lock in the first place
meritt 8y ago
fwiw, MySQL has supported JSON [1] and also allows nested indexes via functional/virtual indexes [2] since v5.7.8 (August 2015) 1. https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html 2. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...

deleted 8y ago

[deleted]

lathiat 8y ago

MySQL has I think all of these features now in 8.0 off the top of my head. Having said that it was only just released to stable very recently and like all good things it may pay to wait for a few more edge cases to be flexed out. Ultimately I’d always suggest the tool you are most familiar with if it’s doing a good enough job.

sethhochberg 8y ago

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.

timewarrior 8y ago
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.)

emilsedgh 8y ago

Probably no. As someone else pointed out, the reason so many similar tools exist for this task on mysql and there's no such tool for postgres is not that postgres isn't as popular. The reason is that this problem is almost non-existent on postgres as many table alterations do not lock the table.

idunno246 8y ago
All alters require a full read/write lock, it’s just that most return instantly. This can be a problem if you have long running transactions, as the alter blocks behind all open txns and all new queries block behind that. python for instance has a very strong opinion that you should be using transactions for everything, and is much more likely to have to deal with it than say ruby. But you’re right, my comment is mostly pedantic, that Postgres implements alters better so these tools aren’t needed.
- timewarrior 8y ago
  This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.
- paulryanrogers 8y ago
  There are some techniques for mitigating those, such as adding new columns as nullable without a default.
  - idunno246 8y ago
    Right, adding a column with a default means the alter takes time while holding that lock and nothing can be read/written so is generally unsafe for big tables, but it doesn’t help if the alter can’t acquire the lock in the first place

meritt 8y ago

fwiw, MySQL has supported JSON [1] and also allows nested indexes via functional/virtual indexes [2] since v5.7.8 (August 2015) 1. https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html 2. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...