9 ms·

> The trends for the last 8 years are clear: Oracle and SQL Server are constantly declining in popularity; MySQL is slightly declining; and PostgreSQL is clearl

> The trends for the last 8 years are clear: Oracle and SQL Server are constantly declining in popularity; MySQL is slightly declining; and PostgreSQL is clearly growing in popularity. But while PostgreSQL almost tripled in popularity in these eight years, it is still far behind the other three. It's great to see Postgres growing, but I often wonder why it isn't growing faster, especially when compared to MySQL. Without getting into a religious flame war, I'm curious what the HN thinks about that?

sl1ck731 6y ago
Wordpress is probably the elephant in the room.
xmodem 6y ago
At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have to deal with data at scale should be built on Cassandra. My cries of "we wouldn't have this problem with Postgres" have mostly fallen on death ears.
- pizza234 6y ago
  Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject. We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.
  - xmodem 6y ago
    We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table. This not the only problem, but it is the most pernicious
    - Thaxll 6y ago
      MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...
    - pizza234 6y ago
      I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost
      - tomnipotent 6y ago
        > adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
        zten 6y ago
        In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
        pizza234 6y ago
        Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...
    - spacemanmatt 6y ago
- Thaxll 6y ago
  I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.
  - kstrauser 6y ago
    Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.
    - tomnipotent 6y ago
      > PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).
  - Shish2k 6y ago
    In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)
fire7000 6y ago
MySQL is a bit simpler even though pgadmin is a wonderful tool for learning postgres advantages. For me the difference became clear when needing to plot locations on a globe. PostGIS is super helpful
- sam_lowry_ 6y ago
  MySQL has way better UX for command line users. Postgres can't get away from legacy Ingres design, with a postgres user and a horrible command line client. Also, database/schema/tables hierarchy is a penalty over database/tables in MySQL.
  - jasonpbecker 6y ago
    psql is easily one of my favorite tools. What makes the CLI "horrible" in your mind? Also, database/schema/tables is great when doing things like multi-tenancy where you split customers by schema (which also makes horizontal scaling super easy if needed).
    - syspec 6y ago
      I would say the commands are just not intuitive. For example, when you type quit instead of quitting it instead prints out that you must type a different command to quit
      - fire7000 6y ago
        One complaint is that it doesnt like camelCase identifiers
      - mattashii 6y ago
        Last time I tested (literally seconds ago) this works. All of \q, quit, and exit work fine starting psql 11?
        syspec 6y ago
DaiPlusPlus 6y ago
> I'm curious what the HN thinks about that? People don't need those white-label commodity cPanel or Plesk shared-hosting or VPS services to run SaaS applications anymore - it's far better for everyone[1] when they switch to a major cloud provider, namely AWS, Azure, GCP, etc. I argue that most web/saas devs today grew up with - and tinkered-with - said white-label services: which means they got their experience with MySQL because it was part of the stock default configuration for all those web-host accounts: with PHP, maybe Perl, and many preinstalled web-applications like phpBB, Wordpress, Coppermine, phpNuke, and so on... and that's probably a solid 15+ years of commonplace web-hosting market saturation (thinking 2001 through 2016, with 2014 being the tipping-point for AWS/Az/GC being the home of SaaS). You'd be in high-school age-range (14-18?) and get bored of simply running other peoples' programs on your web-space, and you saw these web-applications were written in PHP so you'd follow some series of online tutorials for PHP and when it inevitably leads to databases they assume you have MySQL - and MySQL (at the time) had not only the widest installbase, but also had a far more forgiving SQL engine than anyone else. While PostgreSQL was often only a few clicks away via quick installers built-in to cPanel/Plesk/etc, MySQL was a safer-bet by everyone involved. ...but those days are past: as a personal anecdote: at no-point in the past 10 years have I come across any "serious" PHP web-application software intended for private or on-prem usage since vBulletin or SVN. [1]Except free-as-in-freedom software advocates...
- rubyskills 6y ago
  I think around that time, postgres was handicapped because of performance reasons. It was considered slow because of ACID compliance and MySQL was all the rage with MyISAM. If only postgres won the marketing battle back then...
  - DaiPlusPlus 6y ago
    > If only postgres won the marketing battle back then... Be glad they didn't - otherwise their support dept. would be buried under questions from web-hosting newbs.
  - count 6y ago
    PG also shipped with EXTREMELY conservative defaults for buffers/etc., so it ran like total ass 'out of the box' for years. A quick change to buffer allocations and things in the config file made it fly, but you had to 'know' that was a thing, where mysql just...didn't need that tweaking.
aynyc 6y ago
In my little world, MySQL was popular because we could get enterprise level support directly from MySQL. It was the default choice from that point on. I don't know if that has changed. AWS changed that pretty quickly.
throwaway_dcnt 6y ago
Enterprise dba shops have deep expertise with oracle (and sometimes sql server). I have personally run into serious resistance from these teams when I explicitly ask for postgres. They try to warn me that oracle is the recommended option for real production use cases because of the deep in-house expertise and that I am pretty much on my own if I go with postgres. Most people back off at that point.
- derekp7 6y ago
  I've seen this so much, and can empathize with them. When you have a product that took you years to master, the value that you bring to your employer is wrapped up in that product. So it isn't so much that someone is a DBA, they are an Oracle Database software specialist. You've learned all the quirks so that when something goes south, you "have the answer". So naturally you will have a number of reasons why another product isn't as good. I feel the same way with Unix/Linux. At one point I was told that my organization may be migrating away from Linux, and redeploy everything under Windows. The management layer really couldn't understand that they would lose nearly every Linux admin on staff (that happened to also have deep understanding of their apps and infrastructure). "Why would someone have a problem with switching platforms -- we'll provide you the training" was their answer. Fortunately we were able to come up with a number of technical (and cost related) issues that the project was shot down.
  - lumost 6y ago
    At sufficiently large companies, deep in-house experience with a given DB technology may be roughly equivalent to a startup engineers deep experience on their companies current monolith. Companies with large and complex db installations often need the complexity for various reasons that are difficult to escape, even if you're working on a green field project.
- matwood 6y ago
  PG over mysql is obvious IMO. PG over mssql, not so obvious. People often forget the 'management system' part of RDBMS. MSSQL provides an enormous amount of tooling in the typical enterprise install. Analysis services, data tools, etc... provide a lot of value beyond a data store. Of course if you don't need any of these additional tools or can't afford them, then yeah pg is a great choice.
knodi 6y ago
I think a lot of people just don't know the new capabilities of PostgreSQL. PostgreSQL's competition isn't MySQL, its the new hybrid DBs like CockroachDb. DBAs and Engineers that know MySQL will always use MySQL. In corps MySQL is a safe choice (sadly).
wayneftw 6y ago
Postgres never gave me the ability to quickly and easily build stored procedures that return multiple heterogeneous resultsets with ease. In SQL Server you do it like this: CREATE PROCEDURE get_customer_and_orders @id int BEGIN SELECT id, first_name, last_name, email, etc FROM customers WHERE id = @id; SELECT id, store_id, created_at, etc FROM customer_orders WHERE customer_id = @id; END I've quickly built entire applications with this tactic as the centerpiece. You can argue that it moves business logic into the database layer and to that I'd say "good", at least for apps that are maintained by IT departments with many strong SQL people and not so many developers. If you know TSQL, you know that you can also do branching, looping and other logic operations within this same procedure - you can even decide to send back 3 resultsets instead of 2 if you want to and the client API allows you to handle whatever it received quite elegantly. I think that features like this are why many businesses will stay on SQL Server. Also the high quality of tools for SQL Server that have no match in Postgres such as SQL Server Management Studio, SQL Server Data Tools, SQL Server Profiler, SQL Server Integration Services among many other such tools that are extremely well integrated.
- dvaun 6y ago
  My limited experience with smaller companies (and now local government) shares this sentiment. For “small” databases (say, not hundreds or more of terabytes) I’ve experienced using stored procedures as a core of the ETL process. That being said, haven’t some recent versions of Postgres added support for stored procedures or some variant? I’m curious if we’d seen any changes in performance if we experimented with switching over.
  - Tostino 6y ago
    They did, but there is still not a good way to return multiple result sets... I mean you can return cursors, but it's...strange.
- ubercore 6y ago
  This isn't meant to detract from your point, but just a fun anecdote -- my first job out of college was working on a product that had its entire business logic layer built in stored procedures. All of it. Hundreds and hundreds of lines per procedure. It "worked", but man was maintenance a bear. In a way, it was good; ever since I've been very comfortable with SQL. But I can't recommend it.
hospadar 6y ago
Personal guess: because switching DBs is expensive and if it works, why bother? I personally LOVE pg over all other choices, but I've got some 5-year-old apps sitting on a little mysql DB and no intention to move them. Dbs are small, performance is good enough, cost of instances wouldn't be very different on PG, but cost of moving everything over is probably a week or two of work that would be pretty hard to justify given the current situation.
runako 6y ago
The popularity of PG relative to MySQL is going to be unavoidably skewed by the popularity of WordPress and its tight linkage to MySQL.
Macha 6y ago
1. MySQL is offered as the default "open source" database in most discussions. This puts the onus on Postgres to people who go "I need a better database", rather than people going "I need a database" in a "Nobody got fired for choosing MySQL" way. 2. Companies looking for enterprise support don't see anyone as high profile as Microsoft, Oracle, and err, Oracle supporting Postgres. (Whether ongoing interaction with Oracle is a net positive is of course debatable. At a previous company our only interaction with Oracle was them being inflexible and expensive that ultimate meant we had a Oracle DB on prem serving a AWS hosted service because it would have cost too much to put it in AWS. Note, this was many years ago, before AWS had a compatible offering, and apparently Oracle even have loosened up a bit here since.
mrweasel 6y ago
MySQL/MariaDB have several relatively easy ways of building multi-master setups. Postgresql doesn’t really have a that great solutions for master-master replication. Our customers go for MariaDB to get master-master replication We see some customers building new solutions on Postgresql, but for large setups, it’s still MariaDB/Galera or Oracle and there isn’t a big push to drop Oracle.
- otabdeveloper4 6y ago
  Postgres doesn't have a great solution for any replication, not just master-master. Lots of things are just half-baked and very clumsy in Postgres compared to MySQL (MariaDB).
  - Tostino 6y ago
    Streaming replication works great in Postgres.
  - skeletal88 6y ago
    Would downvote if I could, PostgreSQL has Patroni, for example, the newest replication management tool, which works. They got rid of the requirement for an external DCS with the latest major release, so.. it's easy to set up and manage When PostgreSQL would get a built-in replication tool would increase it's popularity a lot, but I guess the core team wants to focus on the DB, and management tools are left to the others to develop.
    - otabdeveloper4 6y ago
      I think you just proved my point. IMO replication is a core feature of a DBMS (like transactions or SQL support), the fact that you need to install obscure third-party tools to bring Postgres into the modern age is bonkers.
__s 6y ago
I worked at a place that was running SQL Server 2012 in compatibility mode with an older version. The place had a pretty high turn over rate, so a lot of the expertise is in the existing tech rather than the people. It was hard enough to try convince them to upgrade SQL Server (I wanted to leverage row level security to get away from having a dozen copies of what was essentially the same app, but that required SQL Server 2016) Also, when I brought up postgres for a new project & mentioned it being open source, response was "that means it could vanish any day" Inertia is real
pyrophane 6y ago
Have used both extensively. I generally prefer MySQL because I find it easier to manage for a few reasons: 1. MySQL has a simpler permission model, so user management is less of a headache. 2. Connections are cheaper in MySQL so you don't have to use an external connection pooler like pgbouncer. 3. There's more/better documentation on MySQL performance tuning, especially from Percona. That's really it for me.
- gkop 6y ago
  Permissions management in Postgres is indeed a pain.
  - xyzzy_plugh 6y ago
    I agree, but can you enumerate a bit more what specifically is painful?
    - tpetry 6y ago
      Try the lock down e.g. a user to only allow reads. Not easy, when testing if it‘s working you will see so many „strange“ behaviours e.g. you can still create tables in public namespace, databases created before/after (dont remember which one) your user are accessible etc... Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier.
      - ilikepi 6y ago
        > Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier. I don't think this is quite right. When a new user is created in Postgres, I don't believe any permissions are implicitly GRANTed by default aside from CONNECT. And I believe the only default privileges granted on a given database are to its owner. Is it possible your experience has only been with configurations in which your PG user was either the owner of the database or a superuser? EDIT: clarification
ahachete 6y ago
OP. This is a very good and fair question. Postgres is growing a lot, but is it growing enough, according to its true potential? It has everything: incredibly robust and trusted; very large feature set; not under any company's direction; extremely liberal license. Should be conquering the database market, and by far is not! I don't have an answer. I have potentially, many. Possibly I will blog about this at some time. But I believe that definitely it should be growing more and becoming more relevant than it is right now. This is no detriment at all to all the fantastic work done by everybody; but just the ambition that Postgres can and should go farther.
linuxhiker 6y ago
In terms of overall adoption, PostgreSQL doesn't need to catch Oracle or MySQL. The people that enjoy legacy platforms are not going to change (necessarily). Real work gets done with PostgreSQL. You can't buy something from Amazon.com without using PostgreSQL. The more Amazon talks about that in their ecosystem, the more PostgreSQL becomes the default choice for all new developers within that (very large) ecosystem. When you combine that with the loud presence of another 800lb Gorilla promoting PostgreSQL (MSFT), the market has spoken.
sofixa 6y ago
People go with what they know, the the LAMP stack was a classic for multiple years. And even today PostgreSQL's HA tooling isn't that great ( one can set up MySQL/MariaDB in active/active in a few minutes, and that's impossible with PgSQL, and even active/passive with easy failover requires more work and oftentimes external tooling).
- fuy 6y ago
  How many people need multi master, though? I'd guess it's much less than 1% of user base
  - sofixa 6y ago
    It's not about needing it, it's about the security of knowing that you have proper redundancy that will work if you need it ( e.g. one goes down) without manual intervention.
ryandvm 6y ago
As a contractor, I'm glad that MySQL is still around. It's an extremely useful red flag for potential clients. It is almost guaranteed that if a client is running on MySQL, you can be certain their entire code base is going to be plagued with less-than-best-practices. Not to be too harsh, but MySQL's most prominent use case is for projects that start with "let's just stand-up a DB real quick and we'll sort out the hard stuff later".
- spacemanmatt 6y ago
  Golly, if that doesn't describe my current employ. Everything about the mess I inherited reeks of, "let's just...and sort out the hard stuff later"

sl1ck731 6y ago

Wordpress is probably the elephant in the room.

xmodem 6y ago

At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have to deal with data at scale should be built on Cassandra. My cries of "we wouldn't have this problem with Postgres" have mostly fallen on death ears.

pizza234 6y ago
Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject. We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.
- xmodem 6y ago
  We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table. This not the only problem, but it is the most pernicious
  - Thaxll 6y ago
    MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...
  - pizza234 6y ago
    I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost
    - tomnipotent 6y ago
      > adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
      - zten 6y ago
        In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
      - pizza234 6y ago
        Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...
  - spacemanmatt 6y ago
Thaxll 6y ago
I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.
- kstrauser 6y ago
  Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.
  - tomnipotent 6y ago
    > PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).
- Shish2k 6y ago
  In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)

fire7000 6y ago

MySQL is a bit simpler even though pgadmin is a wonderful tool for learning postgres advantages. For me the difference became clear when needing to plot locations on a globe. PostGIS is super helpful

sam_lowry_ 6y ago
MySQL has way better UX for command line users. Postgres can't get away from legacy Ingres design, with a postgres user and a horrible command line client. Also, database/schema/tables hierarchy is a penalty over database/tables in MySQL.
- jasonpbecker 6y ago
  psql is easily one of my favorite tools. What makes the CLI "horrible" in your mind? Also, database/schema/tables is great when doing things like multi-tenancy where you split customers by schema (which also makes horizontal scaling super easy if needed).
  - syspec 6y ago
    I would say the commands are just not intuitive. For example, when you type quit instead of quitting it instead prints out that you must type a different command to quit
    - fire7000 6y ago
      One complaint is that it doesnt like camelCase identifiers
    - mattashii 6y ago
      Last time I tested (literally seconds ago) this works. All of \q, quit, and exit work fine starting psql 11?
      - syspec 6y ago

DaiPlusPlus 6y ago

> I'm curious what the HN thinks about that? People don't need those white-label commodity cPanel or Plesk shared-hosting or VPS services to run SaaS applications anymore - it's far better for everyone[1] when they switch to a major cloud provider, namely AWS, Azure, GCP, etc. I argue that most web/saas devs today grew up with - and tinkered-with - said white-label services: which means they got their experience with MySQL because it was part of the stock default configuration for all those web-host accounts: with PHP, maybe Perl, and many preinstalled web-applications like phpBB, Wordpress, Coppermine, phpNuke, and so on... and that's probably a solid 15+ years of commonplace web-hosting market saturation (thinking 2001 through 2016, with 2014 being the tipping-point for AWS/Az/GC being the home of SaaS). You'd be in high-school age-range (14-18?) and get bored of simply running other peoples' programs on your web-space, and you saw these web-applications were written in PHP so you'd follow some series of online tutorials for PHP and when it inevitably leads to databases they assume you have MySQL - and MySQL (at the time) had not only the widest installbase, but also had a far more forgiving SQL engine than anyone else. While PostgreSQL was often only a few clicks away via quick installers built-in to cPanel/Plesk/etc, MySQL was a safer-bet by everyone involved. ...but those days are past: as a personal anecdote: at no-point in the past 10 years have I come across any "serious" PHP web-application software intended for private or on-prem usage since vBulletin or SVN. [1]Except free-as-in-freedom software advocates...

rubyskills 6y ago
I think around that time, postgres was handicapped because of performance reasons. It was considered slow because of ACID compliance and MySQL was all the rage with MyISAM. If only postgres won the marketing battle back then...
- DaiPlusPlus 6y ago
  > If only postgres won the marketing battle back then... Be glad they didn't - otherwise their support dept. would be buried under questions from web-hosting newbs.
- count 6y ago
  PG also shipped with EXTREMELY conservative defaults for buffers/etc., so it ran like total ass 'out of the box' for years. A quick change to buffer allocations and things in the config file made it fly, but you had to 'know' that was a thing, where mysql just...didn't need that tweaking.

aynyc 6y ago

In my little world, MySQL was popular because we could get enterprise level support directly from MySQL. It was the default choice from that point on. I don't know if that has changed. AWS changed that pretty quickly.

throwaway_dcnt 6y ago

Enterprise dba shops have deep expertise with oracle (and sometimes sql server). I have personally run into serious resistance from these teams when I explicitly ask for postgres. They try to warn me that oracle is the recommended option for real production use cases because of the deep in-house expertise and that I am pretty much on my own if I go with postgres. Most people back off at that point.

derekp7 6y ago
I've seen this so much, and can empathize with them. When you have a product that took you years to master, the value that you bring to your employer is wrapped up in that product. So it isn't so much that someone is a DBA, they are an Oracle Database software specialist. You've learned all the quirks so that when something goes south, you "have the answer". So naturally you will have a number of reasons why another product isn't as good. I feel the same way with Unix/Linux. At one point I was told that my organization may be migrating away from Linux, and redeploy everything under Windows. The management layer really couldn't understand that they would lose nearly every Linux admin on staff (that happened to also have deep understanding of their apps and infrastructure). "Why would someone have a problem with switching platforms -- we'll provide you the training" was their answer. Fortunately we were able to come up with a number of technical (and cost related) issues that the project was shot down.
- lumost 6y ago
  At sufficiently large companies, deep in-house experience with a given DB technology may be roughly equivalent to a startup engineers deep experience on their companies current monolith. Companies with large and complex db installations often need the complexity for various reasons that are difficult to escape, even if you're working on a green field project.
matwood 6y ago
PG over mysql is obvious IMO. PG over mssql, not so obvious. People often forget the 'management system' part of RDBMS. MSSQL provides an enormous amount of tooling in the typical enterprise install. Analysis services, data tools, etc... provide a lot of value beyond a data store. Of course if you don't need any of these additional tools or can't afford them, then yeah pg is a great choice.

knodi 6y ago

I think a lot of people just don't know the new capabilities of PostgreSQL. PostgreSQL's competition isn't MySQL, its the new hybrid DBs like CockroachDb. DBAs and Engineers that know MySQL will always use MySQL. In corps MySQL is a safe choice (sadly).

wayneftw 6y ago

Postgres never gave me the ability to quickly and easily build stored procedures that return multiple heterogeneous resultsets with ease. In SQL Server you do it like this: CREATE PROCEDURE get_customer_and_orders @id int BEGIN SELECT id, first_name, last_name, email, etc FROM customers WHERE id = @id; SELECT id, store_id, created_at, etc FROM customer_orders WHERE customer_id = @id; END I've quickly built entire applications with this tactic as the centerpiece. You can argue that it moves business logic into the database layer and to that I'd say "good", at least for apps that are maintained by IT departments with many strong SQL people and not so many developers. If you know TSQL, you know that you can also do branching, looping and other logic operations within this same procedure - you can even decide to send back 3 resultsets instead of 2 if you want to and the client API allows you to handle whatever it received quite elegantly. I think that features like this are why many businesses will stay on SQL Server. Also the high quality of tools for SQL Server that have no match in Postgres such as SQL Server Management Studio, SQL Server Data Tools, SQL Server Profiler, SQL Server Integration Services among many other such tools that are extremely well integrated.

dvaun 6y ago
My limited experience with smaller companies (and now local government) shares this sentiment. For “small” databases (say, not hundreds or more of terabytes) I’ve experienced using stored procedures as a core of the ETL process. That being said, haven’t some recent versions of Postgres added support for stored procedures or some variant? I’m curious if we’d seen any changes in performance if we experimented with switching over.
- Tostino 6y ago
  They did, but there is still not a good way to return multiple result sets... I mean you can return cursors, but it's...strange.
ubercore 6y ago
This isn't meant to detract from your point, but just a fun anecdote -- my first job out of college was working on a product that had its entire business logic layer built in stored procedures. All of it. Hundreds and hundreds of lines per procedure. It "worked", but man was maintenance a bear. In a way, it was good; ever since I've been very comfortable with SQL. But I can't recommend it.

hospadar 6y ago

Personal guess: because switching DBs is expensive and if it works, why bother? I personally LOVE pg over all other choices, but I've got some 5-year-old apps sitting on a little mysql DB and no intention to move them. Dbs are small, performance is good enough, cost of instances wouldn't be very different on PG, but cost of moving everything over is probably a week or two of work that would be pretty hard to justify given the current situation.

runako 6y ago

The popularity of PG relative to MySQL is going to be unavoidably skewed by the popularity of WordPress and its tight linkage to MySQL.

Macha 6y ago

1. MySQL is offered as the default "open source" database in most discussions. This puts the onus on Postgres to people who go "I need a better database", rather than people going "I need a database" in a "Nobody got fired for choosing MySQL" way. 2. Companies looking for enterprise support don't see anyone as high profile as Microsoft, Oracle, and err, Oracle supporting Postgres. (Whether ongoing interaction with Oracle is a net positive is of course debatable. At a previous company our only interaction with Oracle was them being inflexible and expensive that ultimate meant we had a Oracle DB on prem serving a AWS hosted service because it would have cost too much to put it in AWS. Note, this was many years ago, before AWS had a compatible offering, and apparently Oracle even have loosened up a bit here since.

mrweasel 6y ago

MySQL/MariaDB have several relatively easy ways of building multi-master setups. Postgresql doesn’t really have a that great solutions for master-master replication. Our customers go for MariaDB to get master-master replication We see some customers building new solutions on Postgresql, but for large setups, it’s still MariaDB/Galera or Oracle and there isn’t a big push to drop Oracle.

otabdeveloper4 6y ago
Postgres doesn't have a great solution for any replication, not just master-master. Lots of things are just half-baked and very clumsy in Postgres compared to MySQL (MariaDB).
- Tostino 6y ago
  Streaming replication works great in Postgres.
- skeletal88 6y ago
  Would downvote if I could, PostgreSQL has Patroni, for example, the newest replication management tool, which works. They got rid of the requirement for an external DCS with the latest major release, so.. it's easy to set up and manage When PostgreSQL would get a built-in replication tool would increase it's popularity a lot, but I guess the core team wants to focus on the DB, and management tools are left to the others to develop.
  - otabdeveloper4 6y ago
    I think you just proved my point. IMO replication is a core feature of a DBMS (like transactions or SQL support), the fact that you need to install obscure third-party tools to bring Postgres into the modern age is bonkers.

__s 6y ago

I worked at a place that was running SQL Server 2012 in compatibility mode with an older version. The place had a pretty high turn over rate, so a lot of the expertise is in the existing tech rather than the people. It was hard enough to try convince them to upgrade SQL Server (I wanted to leverage row level security to get away from having a dozen copies of what was essentially the same app, but that required SQL Server 2016) Also, when I brought up postgres for a new project & mentioned it being open source, response was "that means it could vanish any day" Inertia is real

pyrophane 6y ago

Have used both extensively. I generally prefer MySQL because I find it easier to manage for a few reasons: 1. MySQL has a simpler permission model, so user management is less of a headache. 2. Connections are cheaper in MySQL so you don't have to use an external connection pooler like pgbouncer. 3. There's more/better documentation on MySQL performance tuning, especially from Percona. That's really it for me.

gkop 6y ago
Permissions management in Postgres is indeed a pain.
- xyzzy_plugh 6y ago
  I agree, but can you enumerate a bit more what specifically is painful?
  - tpetry 6y ago
    Try the lock down e.g. a user to only allow reads. Not easy, when testing if it‘s working you will see so many „strange“ behaviours e.g. you can still create tables in public namespace, databases created before/after (dont remember which one) your user are accessible etc... Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier.
    - ilikepi 6y ago
      > Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier. I don't think this is quite right. When a new user is created in Postgres, I don't believe any permissions are implicitly GRANTed by default aside from CONNECT. And I believe the only default privileges granted on a given database are to its owner. Is it possible your experience has only been with configurations in which your PG user was either the owner of the database or a superuser? EDIT: clarification

ahachete 6y ago

OP. This is a very good and fair question. Postgres is growing a lot, but is it growing enough, according to its true potential? It has everything: incredibly robust and trusted; very large feature set; not under any company's direction; extremely liberal license. Should be conquering the database market, and by far is not! I don't have an answer. I have potentially, many. Possibly I will blog about this at some time. But I believe that definitely it should be growing more and becoming more relevant than it is right now. This is no detriment at all to all the fantastic work done by everybody; but just the ambition that Postgres can and should go farther.

linuxhiker 6y ago

In terms of overall adoption, PostgreSQL doesn't need to catch Oracle or MySQL. The people that enjoy legacy platforms are not going to change (necessarily). Real work gets done with PostgreSQL. You can't buy something from Amazon.com without using PostgreSQL. The more Amazon talks about that in their ecosystem, the more PostgreSQL becomes the default choice for all new developers within that (very large) ecosystem. When you combine that with the loud presence of another 800lb Gorilla promoting PostgreSQL (MSFT), the market has spoken.

sofixa 6y ago

People go with what they know, the the LAMP stack was a classic for multiple years. And even today PostgreSQL's HA tooling isn't that great ( one can set up MySQL/MariaDB in active/active in a few minutes, and that's impossible with PgSQL, and even active/passive with easy failover requires more work and oftentimes external tooling).

fuy 6y ago
How many people need multi master, though? I'd guess it's much less than 1% of user base
- sofixa 6y ago
  It's not about needing it, it's about the security of knowing that you have proper redundancy that will work if you need it ( e.g. one goes down) without manual intervention.

ryandvm 6y ago

As a contractor, I'm glad that MySQL is still around. It's an extremely useful red flag for potential clients. It is almost guaranteed that if a client is running on MySQL, you can be certain their entire code base is going to be plagued with less-than-best-practices. Not to be too harsh, but MySQL's most prominent use case is for projects that start with "let's just stand-up a DB real quick and we'll sort out the hard stuff later".

spacemanmatt 6y ago
Golly, if that doesn't describe my current employ. Everything about the mess I inherited reeks of, "let's just...and sort out the hard stuff later"