Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes.
Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.
At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.
I've always wondered why Postgres is so insanely popular. I mean it has some nice things like very powerful support for a very comprehensive subset of SQL functionality, but most apps don't need all that.
It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.
> I've always wondered why Postgres is so insanely popular.
In no particular order, my preference for postgres is driven by:
* Date / time functions that don't suck
* UTF-8 is really UTF-8
* 99% of a backup can be done live with nothing more than rsyncing the data directory and the WAL files
* Really comprehensive documentation
* LTREE and fuzzy string match extensions
* Familiarity from using it for years
MySQL/Maria I'm sure is fine, but it's one of hose things where it's just different enough and I haven't encountered a compelling use case for changing my preference.
UTF-8 is what made me switch. It’s insane MySQL has something called UTF-8 that isn't really UTF-8, but do have a type UTF8MB4 that actually is correct. This means if you use UFT-8 in MySQL, you can’t use emoji for example.
Postgres limits btree keys to 2704 bytes, which is actually slightly smaller than MySQL's limit of 3072 bytes, assuming the default InnoDB storage engine.
That said, when using utf8mb4 in an index key, MySQL uses the "worst case" of each character being 4 bytes. So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set.
For practical purposes, this doesn't cause much pain, as it's generally inadvisable to use complete long-ish strings as a key. And there are various workarounds, like using prefixes or hashes as the key, or using binary strings as keys to get the full 3072 bytes (if you don't need collation behaviors).
It's not just DDL that isn't transactional, there's a whole bunch of other things that aren't. And they break the transactionality silently. It's like an obstical course where bumping into something might be fatal.
What specific non-DDL things are you referring to here?
Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES.
I worked for a company that migrated from mysql to postgres, but then got big enough they wanted to hire fulltime database experts and ended up migrating back to mysql because it was easier to find talent
It requires a ton of somewhat arcane maintenance at scale. Vacuum shenanigans, Index fragmentation requiring manual reindexing, Txid wraparounds. I like Postgres but it’s definitely way more work to maintain a large instance than mysql. MySQL just kinda works
I started with MySQL in 2006 for my personal projects, but what first won me over to psql was those commands.
Today I use CLIs like usql to interact with MySQL and SQLite so I can continue to use those commands.
At first glance they may be less obvious, but they are significantly more discoverable. \? Just shows you all of them. In MySQL it always feels like I need to Google it.
I assume this is really what it comes down to. If psql added those verbose-but-descriptive commands a whole bunch of people comfortable with mysql would be a lot happier using postgres.
Postgres is the only thing on my Debian that doesn't seamlessly automatically upgrade across dist-upgrades, but instead leaves old versions around for me to deal with manually... which I seem to never get around to.
In Oracle, ALTER TABLE MOVE in 8i was a godsend, finally enabling a table reorganization without export/import.
My timid management forbade an upgrade from Oracle 7.3.4 until 2013. It was agony to remain on that museum piece for as long as we did.
I am upgrade-minded, but my management is not. I always lose.
I am retiring in two years. I will not miss their problems, not at all.
Edit: Oracle 10g was the last release that (for us) brought must-have features. Sure, upgrading to 19 or 23 would be great, but it doesn't bring anything that I really want.
Databases tend to be "stickier" than other parts of any large software system. Largely because database migrations are costly. You can't just tear down an old database and rebuild a new one, you have to figure out how to move all that data across too.
The consequence is that things in database-land tends to move slower than other types of software. This I think is the major reason why we still use SQL.
Well if it's self-hosted you have to do it yourself. You can either backup your databases from the old version and restore it to the new version once installed, or you can use pg_upgrade to upgrade/copy a old version data directory to the new version.
I don't think this is done automatically when you simply install a new postgres version, but I'm not certain of that.
JSON - er JSON-based document storage - documents with unique identifiers. and the ability to define and set schemas for the JSON, and ... we're back to a relational database
I think it's more about avoiding downtime (I just upgraded a pg with 1TB of data from v11 to v16 and I didn't notice any breaking changes). In an ideal world, every client of the DB should be able to handle the case where the DB is down and patiently wait for the DB to come back to keep doing its job. But from my experience, it's rarely the case, there is always at least 1 micro service running somewhere in the cloud that everybody forgot about that will just crash if the DB is down, which could mean losing data.
If PostgreSQL has replication, why are they talking about "minimal" downtime? Is there no quorum strategy that delivers high availability? I don't know as much as I should.
Writes happen on your primary. At some point, you need to stop accepting writes, wait for the replica to fully catch up, reverse the replication so the replica is the new primary, then direct writes to the new primary. That's hard to do without any downtime.
There's no option where the nodes all accept writes.
I've always found it fascinating that there is a vocal contingent at HN that seems to legitimately hate advertising. But then an article like this turns up that is obvious advertising and is also a good article - we get a nice summary of what the major performance features over different postgres versions are, and some interesting case studies (I'd never even heard of the NOT VALID option although apparently it is nothing new).
This is something I've heard called "permission marketing". The idea is that you show genuinely useful ads to only the few people who will benefit from them, rather than indiscriminately blasting millions of innocent bystanders. Then these few people will actually welcome your marketing efforts.
The classic example is advertising a new improved fishing reel in a fishing magazine. People buy the magazine (well, 20 years ago they did) because they want to know about things like new improved fishing reels.
It's a world away from the overwhelming avalanche of bullshit that is modern advertising/spam. There's nothing at all weird about hating advertising in general but being ok with permission marketing.
If you follow this idea further you'll find that very few people, even the most vocal, genuinely hate advertising. We all want to know about useful products and services. We just don't want to see a million ads a day for Apple, Coke, Pepsi, Nike, erectile dysfunction, fake single women in your area, Nigerian princes...
Because when it reaches a certain scale, and when too many psychological tricks are being played, and everything is always, BRIGHT, BIG, hyper-sexualized, when you can't walk down any street, watch anything, read anything, without seeing people richer, smarter, younger, sexier, happier than you, it goes far beyond just advertising. It's brainwashing. It has to stop because it's extremely unhealthy for our societies, our mental health, our children.
I rarely see much objection to contentful 'advertising' like this. Anyway, the answer really is that it's fully handled by submission/voting/flagging mechanisms, doesn't matter what anyone might say.
Yes but Neon databases is a funder of Postgres development. So I'm interested in hearing what they have to say. If they're advertising then I think helping open source is the right way to go about it. To me it sounds like they just want to make sure people benefit from all the money they're spending.
Honestly, I've aside from React and Java (8 -> 21 is big but still not that big), there's very little software that I updated and noticed a major step change difference in the system. Once it works, its fine
Postgres and mysql usually have changes in each new version that are important enough to motivate an upgrade, whatever it is new features or better performance or both. Although it really depends if your are using the features they are improving or not (e.g. if you don't use partitions, well of course that 30% perf improvement on write operations on partitions won't benefit you).
You can check this article about Uber migrating its Mysql from v5 to v8 posted here 3 days ago [1]. Among other things, they observed a "~94% reduction in overall database lock time." The before/after graph is pretty impressive. It also gave them window functions and better JSON support, which are two very big features.
Seems perfectly reasonable to me. The article is about people not upgrading from older versions. One could imagine that PostgreSQL 13.0 is the “pillar” of some company, that their whole system relies upon. The article then goes into detail on what they are missing out on by not upgrading PostgreSQL to a more recent major version, and why it might be that so many stay on ancient versions, and also how you can actually perform major version upgrades of PostgreSQL.
Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes.
Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.
At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.
I've always wondered why Postgres is so insanely popular. I mean it has some nice things like very powerful support for a very comprehensive subset of SQL functionality, but most apps don't need all that.
It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.
> I've always wondered why Postgres is so insanely popular.
In no particular order, my preference for postgres is driven by:
MySQL/Maria I'm sure is fine, but it's one of hose things where it's just different enough and I haven't encountered a compelling use case for changing my preference.UTF-8 is what made me switch. It’s insane MySQL has something called UTF-8 that isn't really UTF-8, but do have a type UTF8MB4 that actually is correct. This means if you use UFT-8 in MySQL, you can’t use emoji for example.
And the fact that adding real utf-8 support limited (limits?) the length of strings that can be indexed
Postgres limits btree keys to 2704 bytes, which is actually slightly smaller than MySQL's limit of 3072 bytes, assuming the default InnoDB storage engine.
That said, when using utf8mb4 in an index key, MySQL uses the "worst case" of each character being 4 bytes. So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set.
For practical purposes, this doesn't cause much pain, as it's generally inadvisable to use complete long-ish strings as a key. And there are various workarounds, like using prefixes or hashes as the key, or using binary strings as keys to get the full 3072 bytes (if you don't need collation behaviors).
What's the alternative? MySQL? No transactional DDL, immediate fail.
It's not just DDL that isn't transactional, there's a whole bunch of other things that aren't. And they break the transactionality silently. It's like an obstical course where bumping into something might be fatal.
What specific non-DDL things are you referring to here?
Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES.
This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html. Hardly an "obstical course".
I worked for a company that migrated from mysql to postgres, but then got big enough they wanted to hire fulltime database experts and ended up migrating back to mysql because it was easier to find talent
Dunno if that says much about Postgres, but it says a lot about the company
[dead]
> It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.
How software "feels" is subjective. Can you be more specific?
It requires a ton of somewhat arcane maintenance at scale. Vacuum shenanigans, Index fragmentation requiring manual reindexing, Txid wraparounds. I like Postgres but it’s definitely way more work to maintain a large instance than mysql. MySQL just kinda works
The command line experience is old school style i.e. to show tables.
Versus:I started with MySQL in 2006 for my personal projects, but what first won me over to psql was those commands.
Today I use CLIs like usql to interact with MySQL and SQLite so I can continue to use those commands.
At first glance they may be less obvious, but they are significantly more discoverable. \? Just shows you all of them. In MySQL it always feels like I need to Google it.
I assume this is really what it comes down to. If psql added those verbose-but-descriptive commands a whole bunch of people comfortable with mysql would be a lot happier using postgres.
That's psql.
It’s also faster to type.
Because it's not tainted and cursed by Oracle, like MySQL (and Oracle).
My upgrade policy for everything:
Significant security vulnerability? Upgrade
Feature you need? Upgrade
All other reasons: Don't upgrade.
Upgrading takes effort and it is risky. The benefits must be worth the risks.
Here’s another reason to upgrade: your version is end of life and your cloud provider forced it.
Thank you Amazon!
Suggestion: add "End of life (no more maintenance for this version)? Upgrade"
Related...
Postgres is the only thing on my Debian that doesn't seamlessly automatically upgrade across dist-upgrades, but instead leaves old versions around for me to deal with manually... which I seem to never get around to.That's because you install versioned packages like postgresql-15: https://tracker.debian.org/pkg/postgresql-15
That way you can have multiple versions of the same package.
The risk/reward ratio of fucking with something that works perfectly fine as is is not great.
So for fresh installs yes but existing ones not so much
In Oracle, ALTER TABLE MOVE in 8i was a godsend, finally enabling a table reorganization without export/import.
My timid management forbade an upgrade from Oracle 7.3.4 until 2013. It was agony to remain on that museum piece for as long as we did.
I am upgrade-minded, but my management is not. I always lose.
I am retiring in two years. I will not miss their problems, not at all.
Edit: Oracle 10g was the last release that (for us) brought must-have features. Sure, upgrading to 19 or 23 would be great, but it doesn't bring anything that I really want.
Databases tend to be "stickier" than other parts of any large software system. Largely because database migrations are costly. You can't just tear down an old database and rebuild a new one, you have to figure out how to move all that data across too.
The consequence is that things in database-land tends to move slower than other types of software. This I think is the major reason why we still use SQL.
Have postgres updates actually been requiring users do migrations? Or is this just a fear that something will go wrong?
Well if it's self-hosted you have to do it yourself. You can either backup your databases from the old version and restore it to the new version once installed, or you can use pg_upgrade to upgrade/copy a old version data directory to the new version.
I don't think this is done automatically when you simply install a new postgres version, but I'm not certain of that.
What's the SQL alternative?
JSON - er JSON-based document storage - documents with unique identifiers. and the ability to define and set schemas for the JSON, and ... we're back to a relational database
Because upgrading is a lot of work, and is higher risk than upgrading other software.
Seems like a massive design fail if they can't maintain backwards compatability and provide a safe, low friction upgrade process.
I think it's more about avoiding downtime (I just upgraded a pg with 1TB of data from v11 to v16 and I didn't notice any breaking changes). In an ideal world, every client of the DB should be able to handle the case where the DB is down and patiently wait for the DB to come back to keep doing its job. But from my experience, it's rarely the case, there is always at least 1 micro service running somewhere in the cloud that everybody forgot about that will just crash if the DB is down, which could mean losing data.
I have a large production deployment that is still on 9.6 because the software depends on table inheritance. (Oh man!)
If PostgreSQL has replication, why are they talking about "minimal" downtime? Is there no quorum strategy that delivers high availability? I don't know as much as I should.
Writes happen on your primary. At some point, you need to stop accepting writes, wait for the replica to fully catch up, reverse the replication so the replica is the new primary, then direct writes to the new primary. That's hard to do without any downtime.
There's no option where the nodes all accept writes.
corporate friction
I've always found it fascinating that there is a vocal contingent at HN that seems to legitimately hate advertising. But then an article like this turns up that is obvious advertising and is also a good article - we get a nice summary of what the major performance features over different postgres versions are, and some interesting case studies (I'd never even heard of the NOT VALID option although apparently it is nothing new).
This is something I've heard called "permission marketing". The idea is that you show genuinely useful ads to only the few people who will benefit from them, rather than indiscriminately blasting millions of innocent bystanders. Then these few people will actually welcome your marketing efforts.
The classic example is advertising a new improved fishing reel in a fishing magazine. People buy the magazine (well, 20 years ago they did) because they want to know about things like new improved fishing reels.
It's a world away from the overwhelming avalanche of bullshit that is modern advertising/spam. There's nothing at all weird about hating advertising in general but being ok with permission marketing.
If you follow this idea further you'll find that very few people, even the most vocal, genuinely hate advertising. We all want to know about useful products and services. We just don't want to see a million ads a day for Apple, Coke, Pepsi, Nike, erectile dysfunction, fake single women in your area, Nigerian princes...
Because when it reaches a certain scale, and when too many psychological tricks are being played, and everything is always, BRIGHT, BIG, hyper-sexualized, when you can't walk down any street, watch anything, read anything, without seeing people richer, smarter, younger, sexier, happier than you, it goes far beyond just advertising. It's brainwashing. It has to stop because it's extremely unhealthy for our societies, our mental health, our children.
I rarely see much objection to contentful 'advertising' like this. Anyway, the answer really is that it's fully handled by submission/voting/flagging mechanisms, doesn't matter what anyone might say.
Yes but Neon databases is a funder of Postgres development. So I'm interested in hearing what they have to say. If they're advertising then I think helping open source is the right way to go about it. To me it sounds like they just want to make sure people benefit from all the money they're spending.
Honestly, I've aside from React and Java (8 -> 21 is big but still not that big), there's very little software that I updated and noticed a major step change difference in the system. Once it works, its fine
Postgres and mysql usually have changes in each new version that are important enough to motivate an upgrade, whatever it is new features or better performance or both. Although it really depends if your are using the features they are improving or not (e.g. if you don't use partitions, well of course that 30% perf improvement on write operations on partitions won't benefit you).
You can check this article about Uber migrating its Mysql from v5 to v8 posted here 3 days ago [1]. Among other things, they observed a "~94% reduction in overall database lock time." The before/after graph is pretty impressive. It also gave them window functions and better JSON support, which are two very big features.
[1] https://www.uber.com/en-JO/blog/upgrading-ubers-mysql-fleet
> Postgres 17.0 has been out for a bit and ...
No. It's been released in September 2024. That's not "quite a bit".
Now as to why people aren't all on 17 and not even on 16 yet, here's an acronym for you: LTS [1]
Debian 11 Bullseye is the current LTS. It came out in 2021.
[1] https://en.wikipedia.org/wiki/Long-term_support
PostgreSQL doesn't have a long term support policy [1]. They release a new version around this time every year, and support it for about 5 years.
[1] https://www.postgresql.org/support/versioning/
Debian doesn't have LTS and non-LTS.
Debian has Stable. That's it.
They didn't say "quite a bit" (long time), they said "a bit" (short time).
Weird that the maybe-AI-generated image of a column says "Postgres 13" on it when the article talks about Postgres 17.
Seems perfectly reasonable to me. The article is about people not upgrading from older versions. One could imagine that PostgreSQL 13.0 is the “pillar” of some company, that their whole system relies upon. The article then goes into detail on what they are missing out on by not upgrading PostgreSQL to a more recent major version, and why it might be that so many stay on ancient versions, and also how you can actually perform major version upgrades of PostgreSQL.