4 ms·

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, wh

by TekMol 9y ago

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

e12e 9y ago
Like other comments here state, in general, use what you know. That said, postgresql has ~always been the better rdbms. Some would argue mysql at times have been a better ~key-value store with ~sql interface. Apparently postgresql is difficult to run in a properly restricted bsd jail. Other than that, I'd be hard pressed to come up with reasons to prefer mysql on technical merit over either postgresql, or, say redis or something.
- Avernar 9y ago
  I'm running postgresql in a FreeBSD vnet jail without issue. The network stack is not shared as it's a vnet jail. I have it set for per jail sysvipc, new in FreeBSD 11, which was the last thing I needed to fully isolate it from other jails. Is there anything else that's needed to properly restrict the jail?
  - e12e 9y ago
    Mostly that afaik a jail with sysvipc set isn't really isolated any more - it works, but defeats some of the purpose of using a jail in the first place? https://forums.freebsd.org/threads/59371/ https://forums.freebsd.org/threads/59371/ Apparently this can(should?) be tweaked a bit since 11-release: https://www.freebsd.org/cgi/man.cgi?query=jail&sektion=&n=1 https://www.freebsd.org/cgi/man.cgi?query=jail&sektion=&n=1 > allow.sysvipc A process within the jail has access to System V IPC primitives. This is deprecated in favor of the per-mod- ule parameters (see below). When this parameter is set, it is equivalent to setting sysvmsg, sysvsem, and sysvshm all to ``inherit''. I interpret that as it still being the case that setting this to the (equivalent of) the deprecated setting, one looses much of the protection a jail normally gives.
leesalminen 9y ago
I chose MySQL (well, MariaDB) for a project that started in 2013. I chose it because I have the most experience maintaining it and have a pretty good handle on how to tune.
- porker 9y ago
  Ditto. I still choose it for small to medium sized webapps because - let's face it - the data storage is not their pain point. MySQL is fast enough, usually has enough features, I know how to tune it and queries, and I normally only bump into 2-3 things in a project which would've been easier with PostgreSQL. And can work around them. The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating. tl;dr: yes. But PostgreSQL would be easier for some items
  - mseebach 9y ago
    > The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating. Uhm, err, that change is to stop the database from silently trowing away your data. It's not about "fast enough", or "easy". It's about your database actually saving your data. If you're happy for your "database" to keep quietly throwing away data in order to keep legacy apps "working", you can just disable strict mode.
    - porker 9y ago
      It's not just changes to stop data being thrown away. This issue has been painful: https://github.com/doctrine/doctrine2/pull/6143 https://github.com/doctrine/doctrine2/pull/6143 Across multiple businesses, they've upgraded servers and got MySQL 5.7 installed, only to wonder why the app has stopped working. People didn't expect that from a point release/the default new operating system package. We could argue the point, but it's the expectation of businesses vs software engineers.
falcolas 9y ago
Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision. Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.
- jeffdavis 9y ago
  What storage engines and options do you use?
  - falcolas 9y ago
    InnoDB is my default goto, TokuDB when on-disk size is a consideration, Archive DB for the exceptional cases where I need even more, rarely accessed, data on disk, and MyISAM for its full text searching capabilities (usually de-normalized from a backing InnoDB table). Those are the big ones. There's also the NDB and Galara Cluster, but you want a specific use-case to justify the added complexity and overhead. I've seen Blackhole used in production, but that was an odd one involving a... unique... replication setup. The CSV engine is pretty awesome for huge data imports, but again, pretty unusual for actual use.
  - forgot-my-pw 9y ago
    XtraDB if you can, InnoDB if you can't.
    - cookiecaper 9y ago
      FWIW, MariaDB 10.2 no longer defaults to XtraDB, now considering InnoDB mostly on-par. [0] [0] https://mariadb.com/kb/en/mariadb/why-does-mariadb-102-use-innodb-instead-of-xtradb/ https://mariadb.com/kb/en/mariadb/why-does-mariadb-102-use-i...
sandGorgon 9y ago
Most e-commerce systems start off on magento, which is MySQL only. So a LOT of MySQL projects are simply the answer to : 1. Did you use magento/Drupal/WordPress/etc PHP framework 2. Did you host on a 20$ per month server with unlimited bandwidth and free database ?
brightball 9y ago
Nope, because using MySQL instead of Postgres essentially cripples your dev tools once you know about everything PG opens up for you. http://www.brightball.com/articles/why-should-you-learn-postgresql http://www.brightball.com/articles/why-should-you-learn-post...
junkilo 9y ago
Having used both from the prototyping stage to very large scale my answer: It doesn't matter. They are both plenty feature complete to work well for relational data type problems. Grab the one you have more exposure to and you'll be fine. That said, I'm still using mysql-innodb (latest percona) because the built-in replication has had more time to bake and its concurrency model (think 100k qps+) is plain better. For further reading see: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
deathanatos 9y ago
https://grimoire.ca/mysql/choose-something-else https://grimoire.ca/mysql/choose-something-else That's essentially my reasons for preferring PostgreSQL.

by TekMol 9y ago

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

e12e 9y ago

Like other comments here state, in general, use what you know. That said, postgresql has ~always been the better rdbms. Some would argue mysql at times have been a better ~key-value store with ~sql interface. Apparently postgresql is difficult to run in a properly restricted bsd jail. Other than that, I'd be hard pressed to come up with reasons to prefer mysql on technical merit over either postgresql, or, say redis or something.

Avernar 9y ago
I'm running postgresql in a FreeBSD vnet jail without issue. The network stack is not shared as it's a vnet jail. I have it set for per jail sysvipc, new in FreeBSD 11, which was the last thing I needed to fully isolate it from other jails. Is there anything else that's needed to properly restrict the jail?
- e12e 9y ago
  Mostly that afaik a jail with sysvipc set isn't really isolated any more - it works, but defeats some of the purpose of using a jail in the first place? https://forums.freebsd.org/threads/59371/ https://forums.freebsd.org/threads/59371/ Apparently this can(should?) be tweaked a bit since 11-release: https://www.freebsd.org/cgi/man.cgi?query=jail&sektion=&n=1 https://www.freebsd.org/cgi/man.cgi?query=jail&sektion=&n=1 > allow.sysvipc A process within the jail has access to System V IPC primitives. This is deprecated in favor of the per-mod- ule parameters (see below). When this parameter is set, it is equivalent to setting sysvmsg, sysvsem, and sysvshm all to ``inherit''. I interpret that as it still being the case that setting this to the (equivalent of) the deprecated setting, one looses much of the protection a jail normally gives.

leesalminen 9y ago

I chose MySQL (well, MariaDB) for a project that started in 2013. I chose it because I have the most experience maintaining it and have a pretty good handle on how to tune.

porker 9y ago
Ditto. I still choose it for small to medium sized webapps because - let's face it - the data storage is not their pain point. MySQL is fast enough, usually has enough features, I know how to tune it and queries, and I normally only bump into 2-3 things in a project which would've been easier with PostgreSQL. And can work around them. The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating. tl;dr: yes. But PostgreSQL would be easier for some items
- mseebach 9y ago
  > The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating. Uhm, err, that change is to stop the database from silently trowing away your data. It's not about "fast enough", or "easy". It's about your database actually saving your data. If you're happy for your "database" to keep quietly throwing away data in order to keep legacy apps "working", you can just disable strict mode.
  - porker 9y ago
    It's not just changes to stop data being thrown away. This issue has been painful: https://github.com/doctrine/doctrine2/pull/6143 https://github.com/doctrine/doctrine2/pull/6143 Across multiple businesses, they've upgraded servers and got MySQL 5.7 installed, only to wonder why the app has stopped working. People didn't expect that from a point release/the default new operating system package. We could argue the point, but it's the expectation of businesses vs software engineers.

falcolas 9y ago

Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision. Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.

jeffdavis 9y ago
What storage engines and options do you use?
- falcolas 9y ago
  InnoDB is my default goto, TokuDB when on-disk size is a consideration, Archive DB for the exceptional cases where I need even more, rarely accessed, data on disk, and MyISAM for its full text searching capabilities (usually de-normalized from a backing InnoDB table). Those are the big ones. There's also the NDB and Galara Cluster, but you want a specific use-case to justify the added complexity and overhead. I've seen Blackhole used in production, but that was an odd one involving a... unique... replication setup. The CSV engine is pretty awesome for huge data imports, but again, pretty unusual for actual use.
- forgot-my-pw 9y ago
  XtraDB if you can, InnoDB if you can't.
  - cookiecaper 9y ago
    FWIW, MariaDB 10.2 no longer defaults to XtraDB, now considering InnoDB mostly on-par. [0] [0] https://mariadb.com/kb/en/mariadb/why-does-mariadb-102-use-innodb-instead-of-xtradb/ https://mariadb.com/kb/en/mariadb/why-does-mariadb-102-use-i...

sandGorgon 9y ago

Most e-commerce systems start off on magento, which is MySQL only. So a LOT of MySQL projects are simply the answer to : 1. Did you use magento/Drupal/WordPress/etc PHP framework 2. Did you host on a 20$ per month server with unlimited bandwidth and free database ?

brightball 9y ago

Nope, because using MySQL instead of Postgres essentially cripples your dev tools once you know about everything PG opens up for you. http://www.brightball.com/articles/why-should-you-learn-postgresql http://www.brightball.com/articles/why-should-you-learn-post...

junkilo 9y ago

Having used both from the prototyping stage to very large scale my answer: It doesn't matter. They are both plenty feature complete to work well for relational data type problems. Grab the one you have more exposure to and you'll be fine. That said, I'm still using mysql-innodb (latest percona) because the built-in replication has had more time to bake and its concurrency model (think 100k qps+) is plain better. For further reading see: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/

deathanatos 9y ago

https://grimoire.ca/mysql/choose-something-else https://grimoire.ca/mysql/choose-something-else That's essentially my reasons for preferring PostgreSQL.