5 ms·

Holy crap, an alternative to Percona? Why does MySQL get two awesome tools and Postgres nothing?

by pkulak 9y ago

Scarbutt 9y ago
mysql is a bigger target market.
- timewarrior 9y ago
  We recently started investing in Postgres because of support of JSON fields and nested indexes in those fields. Should we have chosen MySQL?
  - deleted 9y ago
    [deleted]
  - lathiat 9y 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 9y 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 9y 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 9y 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 9y ago
        Also you cannot store foreign keys in JSON.
        timewarrior 9y 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 9y 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 9y 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 9y 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 9y ago
        This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.
      - paulryanrogers 9y ago
        There are some techniques for mitigating those, such as adding new columns as nullable without a default.
        idunno246 9y 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 9y 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...
sethhochberg 9y ago
There are several other notable options - SoundCloud's LHM, Facebook's online schema change tool, etc. They all have their different quirks. (And as modern MySQL releases get better online DDL support, become less and less critical - though still useful for all of those edge cases where native lockless online DDLs can't work yet)
josegonzalez 9y ago
Postgres supports transactional DDL statements natively, and many alter table statements don't end up locking the table nearly as severely as some MySQL versions do.
- michaeldejong 9y ago
  Actually both lock for many (crucial) schema operators, and often severely enough to block your application from reading from the table(s) under change. I've been researching this stuff for a while. Check out http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-ddl-statements-mysqls-return/ http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-... . It's slightly outdated, but still holds.
- fipar 9y ago
  That is true, but I wanted to share another angle that may or may not affect PostgreSQL while it continues to affect MySQL even as it has crash-safe (though not transactional) DDL now: these schema changes are online for the master, but are not replication-aware and can have impact in replication delay on servers down the hierarchy. For this reason alone I think we'll continue to use schema-change tools on MySQL even if the server itself becomes better at those. In the specific case of gh-ost, another good point is that migrations can be completely paused, which in MySQL is not true of online DDL.
michaeldejong 9y ago
I felt the same way, so I've been working on QuantumDB for the last couple of years. Take a look at https://quantumdb.io https://quantumdb.io . QuantumDB doesn't use the binlog / WAL log like gh-ost does, but it does support foreign key constraints, and it allows you to perform several schema operations in one go without having to deal with the intermediates. It's still not ready for production, but feel free to try it out. Feedback is welcome!

Holy crap, an alternative to Percona? Why does MySQL get two awesome tools and Postgres nothing?

by pkulak 9y ago

Holy crap, an alternative to Percona? Why does MySQL get two awesome tools and Postgres nothing?

Scarbutt 9y ago

mysql is a bigger target market.

timewarrior 9y ago
We recently started investing in Postgres because of support of JSON fields and nested indexes in those fields. Should we have chosen MySQL?
- deleted 9y ago
  [deleted]
- lathiat 9y 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 9y 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 9y 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 9y 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 9y ago
        Also you cannot store foreign keys in JSON.
      - timewarrior 9y 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 9y 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 9y 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 9y 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 9y ago
      This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.
    - paulryanrogers 9y ago
      There are some techniques for mitigating those, such as adding new columns as nullable without a default.
      - idunno246 9y 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 9y 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...

sethhochberg 9y ago

There are several other notable options - SoundCloud's LHM, Facebook's online schema change tool, etc. They all have their different quirks. (And as modern MySQL releases get better online DDL support, become less and less critical - though still useful for all of those edge cases where native lockless online DDLs can't work yet)

josegonzalez 9y ago

Postgres supports transactional DDL statements natively, and many alter table statements don't end up locking the table nearly as severely as some MySQL versions do.

michaeldejong 9y ago
Actually both lock for many (crucial) schema operators, and often severely enough to block your application from reading from the table(s) under change. I've been researching this stuff for a while. Check out http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-ddl-statements-mysqls-return/ http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-... . It's slightly outdated, but still holds.
fipar 9y ago
That is true, but I wanted to share another angle that may or may not affect PostgreSQL while it continues to affect MySQL even as it has crash-safe (though not transactional) DDL now: these schema changes are online for the master, but are not replication-aware and can have impact in replication delay on servers down the hierarchy. For this reason alone I think we'll continue to use schema-change tools on MySQL even if the server itself becomes better at those. In the specific case of gh-ost, another good point is that migrations can be completely paused, which in MySQL is not true of online DDL.

michaeldejong 9y ago

I felt the same way, so I've been working on QuantumDB for the last couple of years. Take a look at https://quantumdb.io https://quantumdb.io . QuantumDB doesn't use the binlog / WAL log like gh-ost does, but it does support foreign key constraints, and it allows you to perform several schema operations in one go without having to deal with the intermediates. It's still not ready for production, but feel free to try it out. Feedback is welcome!