As with anything truly cool, it was cool long before most people noticed. I have been using Postgres on and off since ~7.2 and it has always been amazing. Being known as cool takes time.
What's the situation where minor version upgrades are giving you trouble?
Asking because it's not supposed to be troublesome. In theory (!), minor version upgrades don't require any change to the on-disk data, so you should be able to just upgrade your PG binaries then restart the database.
Ah, looks like psql seems to have switched to actually using minor and major versions properly during the last two years - I remember the dance from the 9.x versions and postgres-upgrade [1].
In any case it's way more straightforward with mysql.
When I started using Django 10 years ago during the Django/Rails era, Postgres was by far the most recommended DB to use. Not sure if that was also true in Rails community.
I definitely recall being on the talk committee one year for DjangoCon, and there was some rough discussion. (Context: The conference was generally a two track conference but keynotes and a few other sessions were single track). One of the single track talks was about Postgres. The discussion was roughly "If we have a Postgres talk we should have another talk like Mongo or MySQL" and the response was roughly "Everyone in Django is using Postgres and if you're not you should be at the talk to learn why you should".
Way more Rails apps used MySQL or other databases, it was largely Heroku winning Rails that led to the strong adoption amongst that community.
Started using rails 10 years ago, myself. Postgres was by far the most common recommendation. In fact I learned a rule of thumb, early on, that I abide by still today: Always start with postgres and only migrate out once you understand your data well enough to consider the migration a clear win.
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.
One example of the sort of trivia that's burned into my brain: You never want to use the utf8 encoding. It's broken. What everyone else calls utf8 MySQL calls "utf8mb4". MySQL is filled to the brim with this sort of thing.
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.
Postgres became cool when the scales fell off peoples eyes and they realized that very few use cases necessitate or even benefit from "NoSQL" databases.
It was excellent and recommended over MySQL before but I agree it became "cool" in the wake of the mongodb/nosql hype->trough of disillusionment transition.
I think that also came late. It was when Oracle bought Sun. People who needed a drop-in replacement moved to MariaDB, but it was the last straw for people who were creating new projects and sort of hated MySQL anyway. Everybody remembered the other one.
This is also my opinion and I think before that MySQL was known for fast and Postgres for features and correctness. I think the change went both ways, people realized that MySQL's casual approach only got them so far and at the same time Postgres focused more on performance without giving up its existing qualities. I think Postgres became cool when it became fast (too).
The last straw was when Oracle bought MySQL, and it was forked to MariaSQL. Postgres went from the nerdy awkward kid to the nerdy cool kid, while MySQL started drinking, getting split personalities, and living off his past fame.
It was my mistake, I misremembered MariaDB, but can't edit my comment now. In my defense, I've barely thought of it since 2009, when it was forked.
On the other hand, I've worked with MySQL a few times in the same period, mostly because Oracle, whatever its failings, has kept MySQL alive. Not thriving, but alive. I'm so glad they didn't buy PostgreSQL.
I think even Sun buying MySQL already hurt MySQL and helped PostgreSQL.
Back in 2006, the startup I was with moved from PostgreSQL to MySQL because the support we were able to find was both expensive (300 USD/hr) and not satisfactory. Back then MySQL AB (this was before Sun) gave us a 10K two year deal on three servers and they had excellent response times and knowledgeable support.
For PostgreSQL the year 2010 when Salesforce bought Heroku (big plus) and Oracle bought MySQL (big minus for MySQL so indirect big plus to PostgreSQL) was the breakthrough, I would say.
That MySQL didn't get CTE and window functions until 2018 is a sad joke.
I came from a MSSQL background and eventually worked on an MySQL project with somebody who had a TON of MySQL experience by way of symfony.
I was explaining that not all of his non-aggregated columns were in his group by, so his query wasn't going to work.... but what do you know, MySQL will just return whatever it feels like from the group in that situation. I expected it not to run at all.
I think there are some flags etc you can use to enable to stricter behavior but it was one of the wildest footguns I've ever seen.
You get JSONB with postgres, which, to me, is a significant difference. This means you can indexing by key/value, access keys directly, without parsing the whole json string, etc.
For my use case, JSONB support completely killed most NoSQL solutions.
Sorry, I should have said JSONB. Do you still consider a table with a single JSONB column SQL or NoSQL? Sure, it may be contained within a RDBMS but you are treating it more like a document store than a relationship database.
NoSQL is as popular as it has ever been though. Many of those most popular NoSQL DBs have outgrown the general database market for a decade and it doesn’t look to be slowing down.
Saying that, SQL databases are still the king by a wide margin. Postgres has grown more at other SQL DBs expense than anything.
I picked PostgreSQL over MySQL for a project I did in 2005.
I picked it since the project would benefit from many advantages which PostgreSQL already had over MySQL, and it would not particularly benefit from MySQL’s only often-touted advantage, speed. This turned out to be the correct choice, and this has not changed since.
The project did not need to be particularly fast, which was the only touted advantage which MySQL had at the time. In all other respects, PostgreSQL was the clear winner. (I edited my original post to be more clear.)
When I started working with Rails in 2008 having come from PHP, both communities were pretty squarely in the MySQL camp by default. That changed quickly over the following couple of years however. As I recall, the free Heroku Postgres offering had a huge influence.
idk. I did worked on a distributed database startup in the mid-2000s and we started with a PG base since that made sense. it became pretty clear that the market was still based around MySQL and so we pivoted.
maybe we were mistaken, but lets say the picture was still fuzzy
You're right, the recommendation in the LAMP era was MySQL, but it ate some of my data once (the disk got full and it kept writing) and I never trusted it again.
i used mysql for almost 20 years, since 3.23 and in the last 4 years i am exclusively using postgres (because of the hype and i thought i am missing out), and its not my cup of tea.
i just love mysql's hackable storage engine (particularly the new lsmt engines), and i hate toast so much, not to mention the permission system which is so convoluted and it is so easy to shoot yourself in the foot. and of course it does not play nice with low iops ebs.
its too expensive to migrate from pg now, but i would avoid it in the future
i usually dont use super sophisticated sql, so mysql is actually pretty good for me considering i can tune the knobs on some tables pretty well.
it could be just that i am more comfortable with hacking mysql, or just the early design principles of early mysql makes it a more hackable (its easier to lose some characters from acid haha)
nothing against postgres, i just think its a bit overhyped and its also harder to squeeze it in the cloud's ridiculous iops prices
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.
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 in the early-mid aughts I evaluated Postgres vs MySQL. At the time the conventional wisdom seemed to be that Postgres was focusing more on robustness and MySQL more on functionality. And a lot of people seemed to prefer MySQL because of this.
When I looped back around several years later Postgres had started to overcome MySQL. Conventional wisdom then was it was roughly at feature parity with MySQL but more robust.
So it would seem that working on having a robust inner core first paid off, even if it cost some early reputation.
Postgres started in 1986. its was never less featureful than MySQL...in fact MySQL tried to get by without _transactions_ for the longest time. the fact that MySQL had more market/mindshare at any point is more of a testament about crowd mentality than anything about either of the two databases.
I remember maybe circa 2004 debating Postgres and mysql with a colleague. I told him to unplug the machine that was hosting his mysql instance. He did and corrupted his database. He said it didn't matter, he had backups, speed was more important :p This was before mysql had the innodb storage engine, after that it wasn't so bad. I have always stood by Postgres though, it's a fantastic piece of open source software.
Sorry - I said functionality but meant performance. Doesn't look I can edit my post anymore. I don't know if that was even true, but that was what the wisdom of the crowds said at the time.
MySql did have better insert performance for a while, but this was due to unsafe defaults in conjunction with no transactions, which is only a good tradeoff if you're storing disposable data.
Developers were afraid it would become a tool for Oracle to monetize and trap people in their ecosystem. That never really happened and they have actually done some really good development work on it.
My experience says that PG wins in the SQL language feature department.
However IMHO MySQL still wins in operational efficiency and performance. These are things that only start to matter when you scale your database and most people never actually reach those sizes.
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.
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.
I didn't either until it bit me in the ass a few months back - often it's shitty proprietary software being only compatible with "genuine" (oracle) MySQL.
Interesting thread/post on Heroku's choice to go with Postgres, and speculation that that decision was pivotal in Postgres's rise its status as the relational database of choice for hackers: https://news.ycombinator.com/item?id=31425115
181 comments
[ 2.3 ms ] story [ 207 ms ] threadThat doesn't seem correct. Wasn't PostgreSQL Inc. the first PostgreSQL company?
Has been since before the Maria/MySQL split in my sphere of the world. Always interesting to see how different people perceive tools over time.
Honestly this sounds more like "When did psql go mainstream"?
Asking because it's not supposed to be troublesome. In theory (!), minor version upgrades don't require any change to the on-disk data, so you should be able to just upgrade your PG binaries then restart the database.
In any case it's way more straightforward with mysql.
[1] https://hub.docker.com/r/tianon/postgres-upgrade
https://hub.docker.com/r/pgautoupgrade/pgautoupgrade
---
That being said, those pgautoupgrade images are alpine Linux based.
People coming from non-alpine Linux images of PostgreSQL will probably need that older not-automatic approach you linked to.
https://www.slideshare.net/pgconf/elephant-roads-a-tour-of-p...
Way more Rails apps used MySQL or other databases, it was largely Heroku winning Rails that led to the strong adoption amongst that community.
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.
MariaDB.
On the other hand, I've worked with MySQL a few times in the same period, mostly because Oracle, whatever its failings, has kept MySQL alive. Not thriving, but alive. I'm so glad they didn't buy PostgreSQL.
I was there too when Oracle took over and everbody knew what was going to happen.
Happy also that pgsql was not a victim and is still alive!
Back in 2006, the startup I was with moved from PostgreSQL to MySQL because the support we were able to find was both expensive (300 USD/hr) and not satisfactory. Back then MySQL AB (this was before Sun) gave us a 10K two year deal on three servers and they had excellent response times and knowledgeable support.
For PostgreSQL the year 2010 when Salesforce bought Heroku (big plus) and Oracle bought MySQL (big minus for MySQL so indirect big plus to PostgreSQL) was the breakthrough, I would say.
That MySQL didn't get CTE and window functions until 2018 is a sad joke.
That's still the case.
In particular, MySQL is faster for updates (no MVCC), but that comes at a cost that I would be hesitant to pay.
I was explaining that not all of his non-aggregated columns were in his group by, so his query wasn't going to work.... but what do you know, MySQL will just return whatever it feels like from the group in that situation. I expected it not to run at all.
I think there are some flags etc you can use to enable to stricter behavior but it was one of the wildest footguns I've ever seen.
For my use case, JSONB support completely killed most NoSQL solutions.
Saying that, SQL databases are still the king by a wide margin. Postgres has grown more at other SQL DBs expense than anything.
Indeed. I was surprised to find that search volume is about as high as it has ever been: https://trends.google.com/trends/explore?date=all&q=nosql&hl...
Strange how people seems to have forgotten that.
maybe we were mistaken, but lets say the picture was still fuzzy
i just love mysql's hackable storage engine (particularly the new lsmt engines), and i hate toast so much, not to mention the permission system which is so convoluted and it is so easy to shoot yourself in the foot. and of course it does not play nice with low iops ebs.
its too expensive to migrate from pg now, but i would avoid it in the future
i usually dont use super sophisticated sql, so mysql is actually pretty good for me considering i can tune the knobs on some tables pretty well.
nothing against postgres, i just think its a bit overhyped and its also harder to squeeze it in the cloud's ridiculous iops prices
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.
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? :-)
When I looped back around several years later Postgres had started to overcome MySQL. Conventional wisdom then was it was roughly at feature parity with MySQL but more robust.
So it would seem that working on having a robust inner core first paid off, even if it cost some early reputation.
Anyone with experience of MSSQL and PostgreSQL who'd like to comment on how it they compare?
However IMHO MySQL still wins in operational efficiency and performance. These are things that only start to matter when you scale your database and most people never actually reach those sizes.
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.
...and that doesn't even account for differences in global variables/configuration, SQL syntax, functions, replication, etc.