5 ms·

I feel the same way. I've been using and tracking both since the mid to late 90s. I've always wondering why, in a world where PostgreSQL exists, would anyone

• gaius 8y ago
  I've always wondering why, in a world where PostgreSQL exists, would anyone use MySQL I am a solid Postgres guy, but MySQL still has a better multi-master replication story. Confident that Postgres will eventually surpass it, possibly even in a later iteration of 10. Now in a world where Postgres exists why would anyone use MongoDB is a valid question...
  • ptman 8y ago
    Postgres 11? Postgres 10 will only get bug fixes. The version numbering was changed with 10
• greenhouse_gas 8y ago
  1. LAMP - If you're working with shared hosting, there isn't much of a choice. 2. WordPress. 3. If you need something simple and basic, MySQL is much easier to get up and running.
  • nik736 8y ago
    I agree with 1 and 2 but I don't understand 3. Can you please elaborate?
    • lstamour 8y ago
      Biggest difference for me is that MySQL always pushed a simpler user structure while most *nix Postgres packages prefer having corresponding native OS users by default (PAM vs password auth). Also, it always seemed easier to migrate/upgrade MySQL data folders compared to Postgres ones. The specifics aren’t coming to mind right now, though. Finally I know way too many folks who prefer phpMyAdmin, especially when provided from their cheap shared hosting provider. Don’t get me wrong, I prefer Postgres generally too, but I can definitely see wins to MySQL on ease-of-use. Especially the less standard but more forgiving SQL syntax and simple DESCRIBE commands. Yes the quirks add up for MySQL but Postgres has its share of quirks too.
    • greenhouse_gas 8y ago
      I'm not a DBA, and it could very well be that it's because I'm coming from the LAMP world, but when I need something light and simple, MySQL or MariaDB fit the bill a lot more than Postgres. In MySQL there are five steps: Install MySQL (creating root user at the same time), CREATE DATABASE X, USE DATABASE X, CREATE table Y, work. If I need something more secure, I'll add a "Create User"/add permissions step in there. For some reason, step 2-4 (create user and set up database/table) give me a headache in Postgres.
      • youseecomrade 8y ago
        Sounds like Postgres could use some initiative to make the experience easier/faster for beginners and new deployments (if popularity is something valuable for them). That's what many people value in MySQL apart from the familiarity. I rarely come across tutorials for beginners where MySQL isn't treated like the only option, even if only by omission. I wouldn't be surprised if students only found out about the viability of different databases after a few years.
      • marcosdumay 8y ago
        Install Postgres; `su postgres` or whatever user it uses; run `psql`; `CREATE USER X IDENTIFIED BY PASSWORD;` `CREATE DATABASE X OWNER X;' Now X can connect there and add tables as he wishes. Postgres onboarding coule be better, but it is naive to choose a tool you will use for years based on the first 5 minutes experience.
        • mgkimsal 8y ago
          CREATE USER X IDENTIFIED BY PASSWORD Googled that and not one result on the first page mentioned PostgreSQL. I don't think this approach is a common one at all. More to the point, I'm not sure i've ever seen that set of instructions anywhere, ever, for pgsql. Every tutorial and intro focuses on running 'createuser'. Oh, yeah, and you'll need to be root to go in and futz with your pg_hba.conf file, then have permissions to write it out and restart the server (probably as root). This 'createuser' approach hasn't been a '5 minute experience' - it's been the approach that is promoted as the default answer/approach almost everywhere I've ever looked for several years.
• kbenson 8y ago
  Initial setup of users and permissions in MySQL is vastly simpler that in Postgres, IMO. It may seem like they are close, but in a world where pg_hba.conf ships with a config that easily confuses those that are unaware of Postgres's ident verification, you'll have people get frustrated often, and in a non-easy to diagnose way (at least for people at a familiarity level where they'll run into this).
• evanelias 8y ago
  MySQL is still a perfectly reasonable choice for massive-scale OLTP workloads. In my mind, a few things MySQL excels at: * Threaded connection model. Postgres still uses process-per-conn right? This is very painful in situations with high connection churn / fire-and-forget db conns, which are required in some circumstances (heavily sharded environments where keeping persistent connection pools between all app servers and db servers is not practical) * Ecosystem and experienced talent pool. Among companies with the largest database fleets, a plurality (maybe even a majority) use MySQL, or at least started out on MySQL. This translates to a greater availability of engineers worldwide with experience using and automating giant sharded MySQL fleets, more exposure to MySQL's edge cases, more familiarity with very specific performance characteristics and tuning, etc. * MySQL's pluggable storage engine API allows flexibility for interesting future possibilities. Facebook migrated part of their MySQL fleet's storage engine from InnoDB (btree-based) to MyRocks (LSM-based) and achieved incredible compression benefits as a result, which translates to huge cost savings, while still being much easier than moving to another DBMS altogether. And AFAIK Postgres does not have comparable compression levels to MyRocks at this time. * Replication options. I'm sure Postgres will catch up here, but its logical replication support is still relatively new, whereas MySQL has always used logical replication and supports a lot of different topologies and configurations. (Although, I won't cite master-master support as many folks do... it is not a good setup in MySQL and has long been discouraged in the community.) * InnoDB's use of a clustered index for primary key means that it performs better than a non-clustered storage solution for some (many?) workloads. Don't get me wrong, Postgres is a wonderful database and absolutely has many qualities and features that MySQL lacks. But it's not the black-and-white situation that some make it out to be.
  • sjwright 8y ago
    And don't forget the TokuDB storage engine. It has some very interesting and different scaling/performance characteristics which can provide dramatic improvements for some workloads. (Even though TokuDB is probably not the "best" choice for my workload, I love it for my text-heavy tables because its performance is still great and its on-disk compression ratios are superb.)
    • 8y ago
• gtowey 8y ago
  This covers probably the most important reasons to use MySQL over PostgreSQL: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ The TL;DR is that PostgreSQL on-disk storage format leads to much higher IOPs for the same workload. For companies running OLTP at scale, the cost differences can be huge.
  • PeterMikhailov 8y ago
    I've been to a few talks from the PostgreSQL lead developers where they mention this. Their opinion was mostly "Uber could have just asked us the right way to do it" and "We believe Uber's new engineering leader needed something to do"
• rogerdpack 8y ago
  I've had count(*) queries take like literally days in Postgres. Want to insert a lot of data? Expect it to cost a lot of RAM. It's got high features but some really rough edges too :|
  • zht 8y ago
    the same would happen in MySQL right? select count(*) does a table scan
    • mfjordvald 8y ago
      count(*) doesn't do full table scan as it's not tied to specific data. It has special handling logic. The only case where it might have to do a full table scan is if you try to count() on a specific (nullable?) column without an index.
      • zht 8y ago
        I've always had select count(*) do a full table scan (or at least take considerable time) in innodb... Did something change?
        • evanelias 8y ago
          Without a WHERE clause, select count(*) still does a full table scan (or full index scan) in InnoDB, and probably always will. It is optimized in MyISAM via table-level metadata, which is only possible because MyISAM doesn't use MVCC or support transactions. In InnoDB, you can get an estimated row count from SHOW TABLE STATUS, but the estimate is based on table stats and can be wildly inaccurate.
    • beberlei 8y ago
      Depends on the storage engine afair, but both innodb an myisam have either index or global metadata based solution for this. I think in myisam its an estimste if you use count(*) so you need to use count(id) to get an exact value. This is all well documented
• sjwright 8y ago
  > why, in a world where PostgreSQL exists, would anyone use MySQL. You could also ask why would anyone use Postgres? Either question is equally absurd. If MySQL and its forks work well enough for Google, Facebook, Uber, AirBNB, Alibaba and Linkedin, why should anyone take it on face value that Postgres is an unambiguously better choice? That said I've toyed with switching in the past. Ten years ago, the MySQL query optimiser was fairly junk and I wanted to switch for that alone.[1] The main thing that held me back was that my application has hundreds—quite probably thousands—of SQL queries coded into it. Despite "SQL" being a notional standard, even the most basic queries would need to be extensively reformatted to be compatible with Postgres. The effort required to rewrite all of these queries simply can't be justified, particularly because we simply don't have any problems with MariaDB. The query optimiser is a lot better now, such that I now rarely encounter situations where a sensible query doesn't yield an efficient execution plan. [1] Between 2000 and 2007 I had spent a lot of time with MSSQL for a different project and I had grown to appreciate its excellent query optimiser and execution plan visualiser. Ten years on and I still haven't seen any F/OSS solution that does as good a job as SQL Server 2000 in this regard. The most recent version I've used is 2005 so it might be even better today. And believe me, I was not a fan of Microsoft in the 2000s.
  • th3sly 8y ago
    regarding query plan visualizer, workbench has added one in recent years: https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html https://dev.mysql.com/doc/workbench/en/wb-performance-explai...