If you were paying attention, postgres always the better option, based on my experience starting around 2002 or so. MySQL had the better marketing in its name and was arguably a bit easier to get going from the start, but postgres was always more advanced and IMHO once you got past the most basic usage made a lot more sense in how it's configured.
The fact that it was the M in the LAMP stack gave it a huge edge too. What was at one point an all day, if not multiday setup and configuration process became a simple install script once LAMP became a thing.
MySql was the "default" for a long time until Oracle muddied the waters just enough to make devs look around a bit.
So you had startups which created their MVP on some shared webhost, which had MySQL and nearly non had pg. as those startups graduated into enterprise fame, they never left MySQL but instead fixed its problems along the way.
The mysql replication story was the big thing for me in the early/mid 2000s. Back then postgres replication seemed like every option was a brittle/incomplete 3rd party hack with different sets of tradeoffs.
Postgres also had a reputation for being correct but slower. The perf differential disappeared a) as postgres got faster and b) mysql got more reliable/robust.
It’s the same story as people building databases in Excel, while it’s small you want to look away from all the details so you choose a tool that doesn’t get in your way. Then when it grows and becomes unwieldy it’s difficult to migrate away because the data is a big mess and there is no model.
Postgres has the safety of the data as its utmost goal, while people will just rename genes because Excel automatically misinterprets them as dates.
I don’t agree. WAL/replication was complete junk for a long time. Not to mention needing stuff like pgpool and pgbouncer in more complicated deployments.
I run Postgres since about 2016. I used to run MySQL behind the same frontend app.
I barely know anything about Postgres beyond installation for our use case, backup and recover. I used to know loads about obscure MySQL optimization techniques, fixing broken tables, fiddling with scary parameters and recovering from hair raising situations.
Isn't that just more about the abundance of hardware we have these days? While on the MySQL side you had to delve into tweaking the different cache sizes and picking MyISAM or InnoDB depending on the use case, on Postgres you had to deal with stuff like manually running VACUUM at the right time or later when that arrived tweaking the autovacuum params.
These days even on my "underpowered" NAS I can just run a default docker image of my database and not worry about tweaking anything.
MariaDB doesn't have a commitment to binary compatibility with MySQL, though. For a time it was marketed as a drop-in replacement, but that has been increasingly untrue for over a decade now.
Aha, I didn't know that. Looks like the marketing worked too well on me! In any case, the lie of drop-in compatibility at least I think led some people to look for alternatives that wouldn't be hampered by Oracle-era MySQL.
This is the most direct explanation. The blog post avoids talking about this at all, but I'm guessing that's to keep the post informative and not let it degrade into a database flame war.
As a MySQL DBA for the past 20 years it was practically the only choice until Oracle bought Sun, then it was instantly radioactive. A shame because the fears people had were unfounded and some of the best development work has been done since then.
Postgres already had cachet before Oracle bought MySql, but it was seen more as a small scale oracle replacement for people who were serious about data integrity or for lower volume OLTP work in the web sphere. The MySql acquisition coincided with performance gains by Postgres that made it more applicable to web scale OLTP workloads, and JSONB support was really the nail in the coffin.
The only correct answer to this question is 2012-09-10, the PostgreSQL 9.2 release date. In that release they unveiled JSON datatypes, and developers were able to have their cake and eat it too.
It's funny because I was going to say circa 2012. Working at Linode I definitely saw a shift away from MySQL to Postgres around that time, but I didn't have any over arching reason beyond "it was trendy."
I think CTEs and window functions in version 8 were big, combined with udf you could write some very complicated queries for data analysis without exponentially growing number of joins.
When shopping for an open source SQL database, cool is not the first thing in your mind. You are looking for familiarity, compatibility, reliability, viability etc etc, lots of -ity words that are decidedly uncool but, ahem, realy important.
What made postgres "cool" for me was the realisation that you get more than what you bargain for:
> More than "just a database", it's a data platform
This feeling of being also relevant for the evolving world of data engineerimg comes from noSQL functionality like JSON support and also extensions that allow graph operations.
So postgres is cool because it is a reliable workhorse that wont let you down but its codebase and community have also the DNA of a racehorse that can win an occasional race for you.
I remember going to a general community-run tech conference in Vienna in 2002 and there was a talk on PostgreSQL which I'd never heard of at that point (had been using MySQL and Oracle). The person giving the talk was a nerd and quite enthusiastic, and quite sad that PostgreSQL wasn't more popular. Come to think of it, I guess that person is happy now :)
So I would say it was already "cool" at that point (albeit not in wide use, at least in my circles).
We weren't too happy with Oracle (pricing), and we'd only moved to that after being unhappy with MySQL in 2000 (no transactions!). I think PostgreSQL would have been a good choice for us, and I did give it a try, but migrating all the data out of Oracle just didn't really seem possible (Oracle didn't provide great export tools as you can imagine, and a "SELECT col1 || "," || col2 .." type of thing to produce a CSV would have taken hours per table and we had a few dozen tables, so would have either resulted in days of downtime, or some funky logic with a lookup table to say which database a user was in and moving them over one-by-one, but then what about FKs? what about a "messages" table where one user sends a message to another? etc. So on Oracle we stayed until the end of the product around 2012.
I'll argue that it was Heroku that made Postgres popular. It was easy and simple to get set up, and it pretty much just worked. It was the first time I'd used it, and the first place many of my friends and coworkers had used it. Heroku was "mediatemple for apps built in the 2010s" and Postgres being the default (and really only sql option outside the marketplace) was a huge win for Postgres.
Edit: I also think emoji becoming popular was a huge win for Postgres. Emoji didn't work in MySQL because "utf8" wasn't actually UTF-8 and "utf8mb4" made indexes super limited for dumb reasons. As people started realizing this, it hurt MySQL's reputation pretty badly and a lot of folks avoided it for new projects.
"utf8" doesn't support four-byte characters from UTF-8. If you use it and instead mean "utf8mb4" (which was historically not the default), data would be silently truncated (with a warning, but who queries for MySQL warnings?) and lead to all sorts of weird production issues. For many years this was the case until "utf8mb4" was made the default in 2017. Which is to say, Unicode was broken by default in MySQL until just over five years ago.
I've been running postgres since, I think, 2008. Mainly I don't like MSFT in general (although I admit VS Code and WSL are pretty cool). I tried MySQL on my first blog and a couple projects and it just seemed too be a bit to chaotic (how many engines do you need?), then a bit too gross (post acquisition). Some of the guys I work with use MariaDB, but at this point I've met Michael Stonebraker and some hardcore postgres folks and it just feels like the right way. Plus it seems like the cloud vendors tend to support Postgres-like features (e.g. Redshift) so that seems like a hella safe bet.
What you have to understand about WSL is that it relies on one of the three userspaces that the NT/VMS kernel was designed to offer: Win32, POSIX, and OS/2.
Cygwin and busybox performance is awful in code that calls fork() often, but I understand that WSL1 behavior is very different, because fork() isn't fighting through layers of Windows.
The reason that the POSIX layer exists in NT is that Microsoft was the largest UNIX vendor in the early 80s with their XENIX variant, where the largest market segment ran on the TRS-80 Model 2 (68k-based, 3 simultaneous users, two attached rs-232 terminals).
"Broad software compatibility was initially achieved with support for several API "personalities", including Windows API, POSIX, and OS/2 APIs – the latter two were phased out starting with Windows XP."
WSL2 is cool [for me] because it's boring - boring and stable is what I need on my workhorse. Other "cool" would be distrohopping and unixporn, but that's teenager-like coolness in reverse of the former.
Postgresql will become cool once they start thinking on operators - they still lack simple `show slave status\G` statements for quick checking replication status. Googling everytime for the WTH of tables needs to be queried.
Readit News
The fact that it was the M in the LAMP stack gave it a huge edge too. What was at one point an all day, if not multiday setup and configuration process became a simple install script once LAMP became a thing.
MySql was the "default" for a long time until Oracle muddied the waters just enough to make devs look around a bit.
- way easier to install
- way easier to maintain (not vacuuming, etc)
- and had a replication story.
So you had startups which created their MVP on some shared webhost, which had MySQL and nearly non had pg. as those startups graduated into enterprise fame, they never left MySQL but instead fixed its problems along the way.
Postgres also had a reputation for being correct but slower. The perf differential disappeared a) as postgres got faster and b) mysql got more reliable/robust.
PostgreSQL was more esoteric and lots of stuff wouldn’t directly support it or would only do so half heartedly.
So MySQL took off. But people programming against a database would lean towards pSQL.
Postgres has the safety of the data as its utmost goal, while people will just rename genes because Excel automatically misinterprets them as dates.
I don’t agree. WAL/replication was complete junk for a long time. Not to mention needing stuff like pgpool and pgbouncer in more complicated deployments.
https://www.youtube.com/watch?v=b2F-DItXtZs
Edit: what happened to highscalability.com?
I barely know anything about Postgres beyond installation for our use case, backup and recover. I used to know loads about obscure MySQL optimization techniques, fixing broken tables, fiddling with scary parameters and recovering from hair raising situations.
I like my current state of ignorance.
I love not having to be trivia king.
These days even on my "underpowered" NAS I can just run a default docker image of my database and not worry about tweaking anything.
Deleted Comment
For a time, MariaDB was the new option, but its commitment to binary compatibility with MySQL made it feel bogged down.
Fun fact: Julian Assange contributed code to PostgreSQL a long time ago: https://news.ycombinator.com/item?id=18464671
As a MySQL DBA for the past 20 years it was practically the only choice until Oracle bought Sun, then it was instantly radioactive. A shame because the fears people had were unfounded and some of the best development work has been done since then.
https://www.postgresql.org/about/news/postgresql-92-released...
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
What made postgres "cool" for me was the realisation that you get more than what you bargain for:
> More than "just a database", it's a data platform
This feeling of being also relevant for the evolving world of data engineerimg comes from noSQL functionality like JSON support and also extensions that allow graph operations.
So postgres is cool because it is a reliable workhorse that wont let you down but its codebase and community have also the DNA of a racehorse that can win an occasional race for you.
What else can you ask of a horse? :-)
So I would say it was already "cool" at that point (albeit not in wide use, at least in my circles).
We weren't too happy with Oracle (pricing), and we'd only moved to that after being unhappy with MySQL in 2000 (no transactions!). I think PostgreSQL would have been a good choice for us, and I did give it a try, but migrating all the data out of Oracle just didn't really seem possible (Oracle didn't provide great export tools as you can imagine, and a "SELECT col1 || "," || col2 .." type of thing to produce a CSV would have taken hours per table and we had a few dozen tables, so would have either resulted in days of downtime, or some funky logic with a lookup table to say which database a user was in and moving them over one-by-one, but then what about FKs? what about a "messages" table where one user sends a message to another? etc. So on Oracle we stayed until the end of the product around 2012.
Edit: I also think emoji becoming popular was a huge win for Postgres. Emoji didn't work in MySQL because "utf8" wasn't actually UTF-8 and "utf8mb4" made indexes super limited for dumb reasons. As people started realizing this, it hurt MySQL's reputation pretty badly and a lot of folks avoided it for new projects.
The "mb4" stands for "multi byte 4".
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
Interesting view with cloud vendors, there does seem to be a shift from traditional LAMP stack.
Cygwin and busybox performance is awful in code that calls fork() often, but I understand that WSL1 behavior is very different, because fork() isn't fighting through layers of Windows.
The reason that the POSIX layer exists in NT is that Microsoft was the largest UNIX vendor in the early 80s with their XENIX variant, where the largest market segment ran on the TRS-80 Model 2 (68k-based, 3 simultaneous users, two attached rs-232 terminals).
"Broad software compatibility was initially achieved with support for several API "personalities", including Windows API, POSIX, and OS/2 APIs – the latter two were phased out starting with Windows XP."
https://en.m.wikipedia.org/wiki/Windows_NT
Postgresql will become cool once they start thinking on operators - they still lack simple `show slave status\G` statements for quick checking replication status. Googling everytime for the WTH of tables needs to be queried.