21 ms·
Why does everyone run ancient Postgres versions?
- erik_seaberg 2y agoIf 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.
- bastawhiz 2y agoWrites 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.
- erik_seaberg 2y agohttps://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION-HA https://www.postgresql.org/docs/current/warm-standby.html#SY... mentions quorum-based synchronous replication, which sounds encouraging.
- bastawhiz 2y agoThat still only has one primary: you can't just start pointing writes at the upgraded standbys. Synchronous replication (besides having its own downsides) just shortens the time needed for the replicas to catch up. You still need to perform a cutover.
- paulryanrogers 2y agoUpgrades 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.
- api 2y agoI'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.
- stickfigure 2y agoWhat's the alternative? MySQL? No transactional DDL, immediate fail.
- jes5199 2y agoI 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
- bastawhiz 2y agoDunno if that says much about Postgres, but it says a lot about the company
- deleted 2y ago[deleted]
- appendix-rock 2y ago[dead]
- icedchai 2y agoUgh. I worked with MySQL earlier in my career (until about 10 years ago.) All the companies since have been Postgres. All my personal projects are Postgres. I can't imagine going back.
- cosmotic 2y agoIt'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.
- roenxi 2y agoI'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).
- OJFord 2y agoI 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.
- esperent 2y agoThis 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.
- shiroiushi 2y ago
- Havoc 2y agoThe 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
- cedws 2y agoThat and capitalism doesn’t reward slow and steady, keeping things stable and well oiled. It rewards delivering shoddy features at break neck speed even if they need to be recalled after two weeks. That’s what Wall Street and the shareholders want. Hence why senior management rarely cares about addressing tech debt. “Show me the incentive, I’ll show you the outcome”
- xpasky 2y agoRelated... postgres 1958 0.0 0.0 247616 26040 ? S Jul21 3:03 /usr/lib/postgresql/11/bin/postgres postgres 1085195 0.0 0.0 249804 24740 ? Ss Aug19 2:01 /usr/lib/postgresql/13/bin/postgres postgres 1085196 0.0 0.0 223240 27900 ? Ss Aug19 1:59 /usr/lib/postgresql/15/bin/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.
- heavyset_go 2y agoThat's because you install versioned packages like postgresql-15: https://tracker.debian.org/pkg/postgresql-15 https://tracker.debian.org/pkg/postgresql-15 That way you can have multiple versions of the same package.
- xpasky 2y agoYou mean there is some other way to install postgresql in Debian?
- heavyset_go 2y agoNo, what I meant is that the install path for Postgres on Debian involves installing versioned packages. It's the only approved way of installing Postgres from the Debian repos that I'm aware of.
- nathanaldensr 2y agoWeird that the maybe-AI-generated image of a column says "Postgres 13" on it when the article talks about Postgres 17.
- codetrotter 2y agoSeems 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.
- FearNotDaniel 2y agoYeah, I mean it's not hugely imaginative, but it kind of makes sense, if you want to describe pg 13 as "ancient" that you use some kind of Greek/Roman temple as a visual analogy for that. Not particularly well executed - a serif font might have helped to drive the "joke" home - but nobody's trying to win an art contest here.
- Apreche 2y agoBecause upgrading is a lot of work, and is higher risk than upgrading other software.
- kevin_thibedeau 2y agoSeems like a massive design fail if they can't maintain backwards compatability and provide a safe, low friction upgrade process.
- ggregoire 2y agoI 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.
- voidfunc 2y ago95% of projects are design fails in this regard... few projects think about Day 2 Ops
- aseipp 2y agoRelated, but Sandstorm is an app-hosting platform/solution that's very different in design than other solutions like Docker, and one of the reasons is that it's actually designed from the ground up for easy and effective use on homelab-style setups, because among other things (not limited to this, and 100% from memory): 1. Sandstorm apps need to always come back cleanly from a raw SIGKILL at any moment. 2. Sandstorm apps must be able to always upgrade ANY previous version of stored data, with no intervention, automatically, when newer versions are deployed. These are tough technical constraints and have huge implications on the whole design. For example, the client/protocol layer needs to possibly be multi-version aware for clean rollouts, client connection failover needs to be built in, etc. But the reality is if you do not have these two constraints, your software will never really work in a turnkey forget-about-it-way way where someone just runs an instance and then actually forgets about it, and it can be safe and secure. This kind of stuff is imperative to every actual computer user who isn't a programming nerd. This is why slapping Docker on a lot of existing pieces of software like databases doesn't really work in the grand scheme. Yes, it gets you started faster, which people value to a high degree. But it often lacks or has the same problems as other solutions on day 2, day 3, ... etc. So, you need to solve all the same problems anyway just in a different form (or externalize them onto some other solution.) Realistically, the solutions have to be designed in from the ground up. That's very difficult to do but necessary. Someone else mentioned in here that SQL Server always 100% works when upgraded in place from an old version. That's also my (limited, homelab-ish) experience and a good reason to like it.
- ldjkfkdsjnv 2y agoHonestly, 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
- ggregoire 2y agoPostgres 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 https://www.uber.com/en-JO/blog/upgrading-ubers-mysql-fleet
- TacticalCoder 2y ago> 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 https://en.wikipedia.org/wiki/Long-term_support
- Izkata 2y agoThey didn't say "quite a bit" (long time), they said "a bit" (short time).
- selcuka 2y agoPostgreSQL 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/ https://www.postgresql.org/support/versioning/
- hairyplanter 2y agoDebian doesn't have LTS and non-LTS. Debian has Stable. That's it.
- rationably 2y agohttps://wiki.debian.org/LTS https://wiki.debian.org/LTS
- hairyplanter 2y agoThis does not contradict what I said. Every Debian release is (and will be) an LTS release.
- chasil 2y agoIn 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.
- justin_oaks 2y agoMy 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.
- natmaka 2y agoSuggestion: add "End of life (no more maintenance for this version)? Upgrade"
- Gormo 2y agoWhy? If the implemented featureset meets your needs, and there are no unresolved bugs or security vulnerabilities relevant to your use cases, what further "maintenance" do you need?
- abraham 2y agoWhen a critical security patch comes out, you don't want to have to to do a major version upgrade to get it.
- deleted 2y ago[deleted]
- FearNotDaniel 2y agoBecause when the maintainers have stopped patching that version against all known security vulnerabilities, that doesn't stop the bad guys from looking for more vulnerabilities. When they find one, it will get exploited. So you either wake up to an email from Have I Been Pwned to say all your customer data has been exfiltrated [0], or (if you're lucky) you have a mad scramble to do that update before they get you. [0] Probably including those passwords you didn't hash, and those credit card numbers you shouldn't be storing in the first place because, what the heck, it meets your needs.
- throwaway918299 2y ago
- yen223 2y agoDatabases 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.
- polishdude20 2y agoWhat's the SQL alternative?
- p10_user 2y agoJSON - 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
- ogogmad 2y agoI think he was talking about the query language.
- aitchnyu 2y agoHopefully Edgedb. It could make most ORMs and heavyweight web frameworks redundant, maybe some BI tools and jobs too. https://www.edgedb.com/ https://www.edgedb.com/
- adhamsalama 2y agoLooks kind of like PRQL https://github.com/PRQL/prql https://github.com/PRQL/prql
- jart 2y agoHave postgres updates actually been requiring users do migrations? Or is this just a fear that something will go wrong?
- yobert 2y agoI have a large production deployment that is still on 9.6 because the software depends on table inheritance. (Oh man!)
- mnahkies 2y agoUnless I'm mistaken table inheritance is still a thing in current PG versions, in terms of partitioning at least it's just less commonly used in favour of declarative partitioning since it's easier to manage. It's been a long time since I worked with v9.x in anger, so I could well be forgetting things though https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE https://www.postgresql.org/docs/current/ddl-partitioning.htm...
- yobert 2y agoOur problem isn't the inheritance feature itself-- it's the removal of the config knob `sql_inheritance` which lets you change whether inherited tables are included in queries by default or not (behavior controlled manually by the * notation). It's a goofy behavior nobody liked, but we have a mountain of SQL that depends on that knob. It's fixable... Just a pain :)
- forinti 2y agoFor a while I had a separate version 9 cluster because of one application. But it wasn't really a nuisance. Postgresql doesn't give a DBA much to worry about.
- sublinear 2y agocorporate friction
- linuxandrew 2y agoExactly this. At my old employer we (the sysadmins/operational DBAs) were pushing for newer, supported versions of Postgres. The push back was always the regression testing cycle, amount of desired change and willingness of the product owner to even engage. The testing cycle was so long that I tried to convince them to test on Postgres beta, because it would well and truly be prod with a few bugfix releases by the time testing was completed (alas, they went with n-1 release of Postgres instead).
- dboreham 2y agoQuick note that the article is talking about why folks are on versions of PG that are not ancient (14, 15).
- craigds 2y agoright? we're on 13 and only now starting to consider upgrading to 16, and I don't think we're very abnormal and I don't consider 13 ancient. We have customers still using 9.3. (That last one does actually count as ancient.)
- briffle 2y agoSame exact boat. Logical replication is nicer than our 'in place' upgrades we did before, but still leaves lots of other issues. We are constantly making changes to our schemas (adding tables, columns, etc). Its never an issue on physical standby's, it just gets created, but logical replication, we have to manually run the changes on the subscriber. We have lots of instances where we create a new table for a feature coming, and alter another to add a column. If those get missed on the logical subscriber, you don't even know until someone tries to write data to that new table or new column. I know logical is supposed to be flexible, but I wish there was a setting to have a replica via logical, so I can handle upgrades easier.
- 0xbadcafebee 2y agoBecause we don't really like it to begin with. The RDBMS has not fundamentally changed much since the 80's. It's the same basic design inside and out, with a shitload of tweaks and optimizations. Don't get me wrong - you can get very far with tweaks and optimizations. But the foundations root you to a particular model, and some things in that model will always be painful. The important question to me isn't why don't people upgrade. It's why do people run Postgres? Is there no other kind of database? No, there are hundreds of different databases. Is it because Postgres does things fundamentally different and better than anything else? No, lots of things work somewhat like Postgres, with pros and cons. Is it because you can't do things without Postgres? No, there are always alternatives that you can make work. Is it because it's impossible for it to keep working without upgrading? No, any database with long-term support could continue working without upgrading. So why use Postgres at all? Answer: it's the incumbency, stupid. When a big fucking honking giant "thing" sucks up all the air in the room, provides for most of the use cases, and is accepted by all the users, then it's extremely hard to justify not using it. Incumbents usually win, even if they're old, disreputable, buggy, and annoying. Even if they're missing some obvious features other things have. Even if people loudly complain that they want change. It's just more annoying not to use them. We're used to them. We've adapted. Trying to do something else is going to be annoying and hard. So we stick to what we know. But that doesn't mean we have to stroke the balls while working the shaft. Upgrading isn't exactly fun. It doesn't even buy us anything, other than the promise of "support", or "compatibility" with whatever else will eventually require it. So we upgrade, eventually, when we must. But the constant mind-numbing march away from entropy isn't a fantastic reason to keep using the thing. When you have to convince yourself to stick with it, it's likely you're in an toxic relationship. If you're honest with yourself, you'll agree that it's time for a change. You deserve better. But switching is full of unknowns. Ominous. Time-consuming. Hard. You know you want to, maybe even need to. But there's too much to lose. So you stick to the familiar, to what you can handle. Maybe something better will come down the pike soon. You even tell yourself you're lucky to be here. But deep down you know you're just comforting yourself. You wish you had something else. Something that brings you joy. Something better. Something... right. But that something isn't here right now. So until Mr. Right gets here, you'll stick with Mr. Right Now. Time to get ready for the next upgrade..... sigh...
- 2y ago
- redslazer 2y agoFunnily enough neon does not offer an easy way to upgrade database compared to other managed database hosting.
- andrenotgiant 2y ago(Neon employee) We auto-upgrade minor versions as long as they can be done autonomously. For major versions, you're right it's still manual but we're working on improving that. Here is our version policy: https://neon.tech/docs/postgresql/postgres-version-policy https://neon.tech/docs/postgresql/postgres-version-policy
- webprofusion 2y agoLol, try upgrading old MongoDB stuff. Database engines (every single one) are notorious for incompatibilities between major versions, upgrading mission critical stuff means updating and re-testing entire applications, which in some cases can be a multi-million dollar process, before going into production. Even if you deeply know/think that there's no problem upgrading, if something does fail in production after an upgrade and it's mission critical..
- x-_-x 2y agoThis. I was tasked with upgrading Postgresql from a very old version (I think 9?) to one that was still supported a couple of years ago. Backwards compatibility is paramount and from my experience upgrading MySQL/MariaDB I know that changes in versions can break it. For this reason, I chose to upgrade to version 11 because it was only a couple of versions apart and still had repositories available at the time. So the first thing I do is stop the VM and take a snapshot. Then I start it back up and go check for database corruption before I dump them... wait there's no utility to check for corruption...? Yep that's right! You basically have to YOLO the whole thing and hope it works. OK... So I dump the databases and back up the directory. I shut down the old version and then install the new version from the repo. I start to import the databases and notice in the scrolling logs that there's some incompatibility... Oh F*$&. I google the error and spend a good hour trying to figure it out. Apparently there are external plugins for postgres that were installed in the old version. I search for the plugins online and they are long discontinued. OK, so let's just copy them over from the old version. I stop postgres, copy them over, and start it back up. It starts up ok. I reimport the databases and no more errors. Yay! I start the applicationsand pray to the SQL gods. So far so good, everything seems to work. Thankfully the applications tested well and worked post upgrade. All of this was done for a critical multi-million dollar healthcare platform. There were no official guides. Nothing. I had to find some random admin's blog for guidance. MySQL on the other hand has full documentation on just about every aspect of everything. The whole process was super hackish and not having any way to check database integrity would have been a show stopper for me had I designed this configuration.
- teddyh 2y ago
- vivzkestrel 2y agoi ll tell you why from my end. I installed Postgres14 via homebrew many years ago on my Apple M1 mac mini. I searched a lot on how to "upgrade" this installation but found nothing. I have a few databases running with data on it which I can't afford to lose if something goes down for more than 1 hour. I wish someone would guide me on how to actually install a newer postgres such as v17 without breaking an existing v14 install or losing data since I am not an expert by any means
- sofixa 2y agoAnd this, kids, is a good example of why homebrew is not a real package manager, and why macOS is not the best at running production services. I hope you have a backup somewhere else, not on the Mac Mini. One option would be to restore it on another machine on Postgres 14, follow the upgrade path to 17 (idk if you can jump directly but a few Google searches will cover this), verify the data, back up the new db, somehow upgrade Posgtres on your Mac, and restore the new back up. Done.
- mxey 2y agoI don’t see how this is homebrew‘s fault? Homebrew lets you install multiple versions of PostgreSQL at once, which you need for pg_upgrade, just like you can do with APT brew install postgresql@17
- deleted 2y ago[deleted]
- thrdbndndn 2y agoForget about something as major as Postgres, I have trouble updating packages (that aren't install via pip/npm/cargo) on Linux all the time as a newbie. The experience is worse than Windows for some reason. Hell, I have a hard time to tell the version of some system build-in binaries. A few months ago, I have trouble to unzip a file which turns out ot be AES-encrypted. Some answers on SO [1] saying I should update my `unzip` to newer version but I can't find any updates for my distro, and I have no idea (still no, so feel free to teach me) to update it manually to make my `unzip` supporting AES. And all the versions, the good and the bad, all say they're "version 6.0.0" despite they behavior obviously differently. [1] https://stackoverflow.com/questions/60674080/how-to-open-winzip-password-protected-archive/60676298#60676298 https://stackoverflow.com/questions/60674080/how-to-open-win...
- elric 2y agoLots of dogmatism in this discussion, it seems. A couple of things: 1. Most psql deployments are not exposed to the interwebz, they are typically only accessible to the applications that need them by virtue of network setup (firewalls etc). This limits the attack vector to whatever the application does. Good. 2. Distro vendors (RHEL et al) often stick to major psql release for the lifecycle of the OS version. If the OS lives longer than the psql major version, they take on the responsability of backporting critical security issues. 3. While upgrades aren't hard, they're not easy either. 4. Psql is pretty much feature complete for many workloads, and pretty stable in general. For many people, there is little need to chase the latest major version.
- xvinci 2y ago"What the application does" may not be what you think of, as it is dependent on how secure the application or the layers beneath it are. This is how people get everything pwned step by step. The database server may then reveal credentials to other apps etc.
- elric 2y agoSure. Defense in depth is important. But I hope that your application is only able to talk TCP/5432 to psql. No amount of psql upgrading will protect you against SQL injections in shitty application code.
- Dylan16807 2y agoIf the database server has significant "other apps", which it probably doesn't.
- xvinci 2y agoSure, but then chances are it's hosted on a nas with other data which you dont want ransomware'd, has access to other parts of the network, etc. - it's easy to underestimate the potential impact
- atoav 2y ago
- arend321 2y agoI guess I'm lucky and 10 minutes of downtime is not a problem for my customers during off-peak hours, every year or so. Upgrading has always been a painless experience with just a pg_dump and pg_restore on the upgraded db server.
- pjmlp 2y agoFor the same reason lots of companies are stuck on Java 8, .NET Framework, C++98 / C++11, C89,.....
- TomK32 2y agoHa, I run arch on my dev machine and they just LOVE upgrading postgres. t has always been a pain until my projects all got their postgres in a docker container. I just don't like to do extra steps for the upgrade process between major versions.
- jgb1984 2y agoI've used postgresql in most of my previous jobs, but using mariadb in my current one, and I must say it's a joy to administer. The replication and high availability that mariadb offers is rock solid, and much more advanced than what postgresql has to offer. It works out of the box, no plugins or third party shenanigans needed, and there is "one obvious way" to do it. Not a dozen options like in the postgresql ecosystem, each with it's own quirks and drawbacks. Also, upgrades are a dream. I did 4 major long term stable release upgrades so far and everything was fully automatic and smooth sailing all around. All of that with about 10 seconds downtime only for each upgrade, despite being a chunky 6TB database, thanks to the user friendly replication process. I respect postgresql a lot, but mariadb allows me to sleep much better at night thanks to replication, smooth upgrades and no VACUUM to worry about.
- deleted 2y ago[deleted]
- Gud 2y agoThis is highly interesting to me because I was on the PostgreSQL bandwagon way before it was cool(mostly because I’m a BSD/MIT license fanatic).
- kstrauser 2y agoTo be fair to PostgreSQL, it has multiple replication setups because they have different inherent properties. For instance, it comes with built in streaming and logical replication setups. Which should you use? It’s impossible to say without knowing your own exact use case. By analogy: should a database cluster optimize consistency or availability? Answer: there’s no way its author can guess which is more important to how you want to use it.
- homebrewer 2y agoMySQL/MariaDB have binary and logical replication too. They also support much more advanced topologies like multi-master out of the box. It's just that what you need for 99% of situations relies on a well trodden path and requires no configuration at all. I have suspected for a long time that most people who criticize MySQL have never actually worked with it, or have done so a couple of decades ago. It's often the default choice if you don't need some of the PostgreSQL features (like PostGIS) and can work with either one.
- indulona 2y ago[flagged]
- lousken 2y agopg15 is not ancient at all and it has been a part of latest debian hence I don't see an issue. Same with python or any other dependency. You rarely need the newest shiny these days and being within the support window is fine
- liampulles 2y agoOur team uses AWS managed postgres, and the ease of operations and upgrades really alleviates stress for us. Managed DBs are worth the cost.
- phendrenad2 2y agoWhy don't people upgrade? Why don't we ask why upgrading is necessary? I understand that Postgres is free software, and if you choose to use it, you're signing up for whatever upgrade cadence the developers give you. But really, I with the developers would spend a bit more time patching "old" versions. 6 years is really too short a time to have to do an upgrade, especially if you have many database servers.
- noirscape 2y agoBecause the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few maintenance routines in your upgrade path.) It requires having the previous version of Postgres installed, something which can mess with a number of distro policies (not to mention docker, which is the most popular way to deploy software that will typically rely on Postgres), and unlike most software with that issue, Postgres is software you want to be managed by your distro. Therefore, most people only upgrade by necessity - when their distro forces the upgrade or the version they're using reaches EOL.
- MichaelZuo 2y agoSo the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?
- throwaway19972 2y agoThere are many answers to this question and few are as satisfying as the question implies.
- phil21 2y agoMy opinion is Postgres was designed by software developers for software developers. The split on “which relational database to use” in my career has almost always been perfectly split between SWE vehemently demanding pgsql for the feature set, and the sysadmins having to support maintenance and production availability preferring MySQL. One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operational roles was their discovery that Postgres was utterly horrible to administer at a systems level. Apparently us lowly sysadmins may have had a point after all. This is a bit tongue in cheek but really not far from my lived reality. When the focus is on features and “correctness” at the near total expense of sane systems tooling folks can develop some myopia on the subject. So many arguments with devs on my teams over this subject that were utterly horrified to find we were running MySQL for a given service. Open source projects tend to fix the pain points its contributors experience, and I assume there were not too many contributors wanting to deal with the boring work of making administration and easy task - it’s thankless “sideways” work that won’t result in many accolades or personal satisfaction for most SWEs. The end users are almost always developers, most of whose experiences in production entail either the equivalent of a docker container level scale system, or are simply given a connection string and the rest is a black box to them. Under those contexts I’d personally prefer Postgres as well and it wouldn’t even be close. When you get into backups, clustering, upgrades, and high availability under extreme load? IMO the story falls apart real fast.
- ZiiS 2y agoBecause they already work great, you are unlikly to get forced to upgrade, and they are the part of your system doing the most critical work under the heviest load.
- buro9 2y agoUpgrading isn't automatic. Let me check what I'm on... brb... Postgres 14. Because it's not automatic I leave it, I leave it until it's so unsupported that I must upgrade the whole system, then I build a new system with a new Postgres and I migrate the old to the new. I want, so badly, for Postgres to just automatically update itself, that a new binary just works with the data directory of an old version, and that if required it does an in-place upgrade to those data files when it can (i.e. if it can detect the last version was the same major as the current version, upgrade the files transparently to the admin). My databases are all backed up each night, and these are single server Postgres with no replication or other trickery, an automatic upgrade for a single-server Postgres should be possible. As it's not done... I assume (incorrectly?) that there be dragons and risks, and I mitigate that by never upgrading, just waiting and migrating. Migrating puts all of the risk on me, human error, and I am definitely fallible, so I can best handle this risk by just not doing it until I must. Last migration I performed was from Postgres 7 > Postgres 14 in October 2021... I guess I have quite a few years of Postgres 14 ahead of me still. I would take downtime, my systems aren't zero downtime critical, just give me a super simple one-command no-questions upgrade as I really dislike migrations.
- Symbiote 2y agoIf you're using Debian or Ubuntu, investigate 'pg_upgradecluster'.
- teddyh 2y agoA full example procedure is documented in /usr/share/doc/postgresql-common/README.Debian.gz, under the heading “Default clusters and upgrading”.
- pelagicAustral 2y agoI feel like I've been on 14 for an eternity... everything is running smooth... I reckon I should upgrade a few of the servers... oh well..
- justinclift 2y agoIf you use Docker, then the pgautoupgrade project might be your kind of thing: https://github.com/pgautoupgrade/docker-pgautoupgrade https://github.com/pgautoupgrade/docker-pgautoupgrade
- Hawxy 2y agoAs a counterpoint a lot of managed postgres providers offer automatic upgrades. Upgrading Aurora Postgres is very easy and we just schedule it in once or twice a year.
- KronisLV 2y agoIn many orgs out there, the version that's picked when a project is started will stick around for a while. Suppose you join a project and see that the PostgreSQL version used is pretty old. Do you: A) convince people to migrate it for some nebulous benefits (features that aren't currently used, performance improvements that will be difficult to measure in lieu of tooling and aggregated statistics) while also taking on the risks of breaking everything or even worse, data loss (since you won't always be able to provision multiple instances and backup restore might take a while and you could still mess that up) B) or just leave it as it is and focus on anything else Many will prefer to not get burned even if there shouldn't be that many risks with upgrading your average PostgreSQL install, which is why you'll get minor/patch releases as best, alongside whatever the runtime environment is getting upgraded. Containers and bind mounts make all of this way easier, but then again, many places don't use containers.
- gonzo41 2y agoSo i've got a small but important app that I run on PG14 via a container. I literally just put the pgdata dir outside the container and I can keep the host OS totally fine and have an isolated db environment. it's a very nice way to not worry about the db.
- KronisLV 2y agoSome might disagree (believing that the system package manager should be responsible for the PostgreSQL install), but I agree that the approach you’re using is one of the better ones. That way you can update the database version when you want, while still installing the base system updates quite frequently. Plus, I think it’s really nice to separate the runtime from the persistent data, which such setups make obvious.
- nomercy400 2y agoFor me it is a data vs code thing. If I run my application/code v1 right now, I generate data. I expect that if I move to application/code v2, I can leave my data in place and it will automatically apply changes to my data. I do not get that with postgres. If I am on postgres 16, and I want to upgrade to postgres 17, I want to leave my data folder untouched. When I then start postgres 17, it should just work (tm). It should also work with code that assumes postgres 16, so I can upgrade my database separate from my application. I can not wait 10 days for a large database to be migrated from 16 to 17 without being able to run it. However, I can wait 10 days before updating my code to support features in 17. The current upgrade process does not give me such confidence in restoring data and uptime. So I don't upgrade until I really have to.
- Netcob 2y agoMy personal reason: While I haven't had to deal with a Postgres update at work yet, I've been running a pretty large Postgres 12 database in my homelab for a few years now. My homelab projects mostly center around a "everything is an MQTT message" idea. Zigbee sensors, Tasmota power readings, OwnTracks locations, surveillance camera events, motion sensors for light switches, currently active app on my PC, status of my 3D printer, whatever my vacuum robots are up to and so on. It all gets recorded into a Postgres db. From there I can use it for data mining experiments, but mostly as a source for Grafana. I tried counting the rows but that query didn't even complete while I was writing this comment. I like trying out all kinds of dockerized oss services, and I keep them updated using watchtower. I run a gitlab instance which is usually the most annoying service to update because it there's an upgrade path and post-start-migrations. With my Postgres instance, which is isolated from the internet, I'll have to figure out what the fastest way is to move all that data around, not leave a huge gap in the record and so on. Sounds like at least a day of work - and since it's technically all for "fun", it'll have to wait until it actually is that.
- Symbiote 2y agoA good approach for this is to use pg_upgrade in-place, which should give you a downtime of a few minutes at most. (I have 800GB at work and would expect 1-2 minutes for this.) I recommend installing PG12 on a temporary VM, duplicating the existing database, and test the upgrade in isolation. https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html A more complicated approach uses replication, and upgrades the standby server before promoting it to the primary server.
- Netcob 2y agoThank you! Looks like the best way to do this. And since I have backups, I might not even need the testing step, considering the low risk. Might do it anyway just out of curiosity at how long it would take to duplicate.
- olavgg 2y agoThe PostgreSQL team releases a data incompatible format every year. Do they really need to break the data format every year? In my opinion, the release model for PostgreSQL should be refactored. Breaking stuff is no fun for users, please stop doing that if you are a software developer. And if you have to, make it autonomous for the user.
- ttfkam 2y agoIf you want performance to steadily increase rather than steadily decrease, the answer to your question is yes. That doesn't mean tools couldn't be better, but there are clear technical advantages to their on-disk format updates.
- chistev 2y agoUse MySQL
- mebcitto 2y agoIf there is anyone from Neon watching this thread, is there a way to suggest updates to the pgversions website? It currently pins Xata to 15.5, which is true for the shared cluster environment depending on the region, but one can start dedicated clusters up to 16.4 at the moment.
- i_am_jsd 2y ago[dead]
- smellybigbelly 2y agoI wouldn’t upgrade major versions until the x.2 version is out. New major versions come with new bugs and I’d like to wait two minor versions until I start using the image in production.
- Narkov 2y ago> Postgres 17.0 has been out for a bit From the same blog: > Sep 26, 2024 - Postgres 17 is Now Available 3 weeks....for a new major release...and we are asking ourselves why people haven't updated?
- jeltz 2y agoYeah, upgrading to PostgreSQL 17 now would be weird unless you have some very specific feature you need in it and spent resources testing your application on the betas and rcs.
- majewsky 2y agoMy team has upgraded several dozen databases from 16.x to 17.3. Went entirely smoothly. The thing is that we're running on a process of upgrading all dependencies every Friday, and then promoting to prod on Monday unless there are specific issues, so our definition of "would be weird" is the reverse from what you say. (Granted, we have rather small DBs and simple applications where ON UPDATE SKIP LOCKED is about the most fancy feature we use.)
- cpburns2009 2y agoWhy? Because it's risky when a lot of business critical data and processes rely on it. I'm just happy I was able to convince my boss to let me upgrade our 9.1 instance to 14 two years ago. Now to upgrade those two pesky 9.4 instances.
- Ozzie_osman 2y agoIf you're on something like RDS, major version upgrades are pretty easy with Blue Green deployments. You can do it with just seconds of downtime and pretty low risk because it uses replication under the hood and handles the cutover for you.
- DonnyV 2y agoOne of the many things I love about Mongodb is the upgrade process. Shutdown server, delete old exes, copy new ones and start server. Done Any needed updates to databases are done by the new exe, automatically. Obviously backup everything before you do the upgrade. I've never had one fail.
- p5v 2y agoIt’s always a trade-off of how much you’ll get from an upgrade, vs. how much time, effort, and pain you’ll have to invest to do the switch. Postgres is at a stage, where a single version can easily outlive the duration of the software you’ve built with it. Let’s be honest here, a vast majority of software doesn’t need a fraction of the requirements we think it does, frankly, because no one uses it to the extent that latest and greatest features would make the experience more pleasant.
- mannyv 2y agoAny db upgrade is a crapshoot. You need to test -everything-, and even then you may not catch things that run occasionally.
- bityard 2y agoYou might as well ask, why does anyone run an older version or anything? The reasons will be largely the same. Most of the software on my machines are "old" because they are part of a Linux distribution that (aside from security issues) was frozen in time a year or two ago so that it could be tested, released, and maintained. I am quite happy to have a system that I know is not going to break (either itself, or my workflow) when I apply security updates. People who MUST HAVE the latest version of everything I feel either have some deeper FOMO issues to work out, suffer from boredom, or look at their computers as hobbies themselves rather than tools. (Which is fine, just be honest about what it is.) That said, much of my career has been spent working at companies who got so busy shipping features that upgrading infrastructure never makes it above the fold. You can tell the managers that working around old software adds costs that scale with the age of the infrastructure, but they don't always listen. I currently work at a company that still has loads of CentOS 7 hosts still in production, and only fairly recently began upgrading them to RHEL 8. (Not 9!)
- efields 2y agoThese are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y.
- willsmith72 2y agoolder versions can also mean deprecated packages everyone's too scared to touch, failure to invest in maintenance and tech debt reduction, or use of old technologies which stopped receiving security updates
- stackskipton 2y agoSRE here, not at all. Not chasing shiny is important but generally when tech debt builds up this high, life is generally hell in terms of outages, unable to accomplish basic tasks and dealing with a bunch of people who have NIH syndrome.
- Spivak 2y ago
- dwwoelfel 2y agoHere's how we did it at OneGraph (RIP), where we not only upgraded versions without downtime, but we also moved hosting providers from GCP to Aurora without downtime. 1. Set up logical replication to a new database server. We used https://github.com/2ndQuadrant/pglogical https://github.com/2ndQuadrant/pglogical, but maybe you don't need that any more with newer versions of postgres? 2. Flip a feature flag that pauses all database queries and wait for the queue of queries to complete. 3. Wait for the query queue to drain and for replication to catch up. 4. Flip a feature flag that switches the connection from the old db to the new db. 5. Flip the flag to resume queries. It helped that we were written in OCaml. We had to write our own connection pooling, which meant that we had full control over the query queue. Not sure how you would do it with e.g. Java's Hikari, where the query queue and the connection settings are complected. We also had no long-running queries, with a default timeout of 30 seconds. It helped to over-provision servers during the migration, because any requests that came in while the migration was ongoing would have to wait for the migration to complete.
- droobles 2y agoThat is awesome, I dream of being able to do zero downtime SQL migrations.
- brentjanderson 2y agoOne of the linked pieces in the Neon blog post is from Knock, where we pulled off a practically zero downtime migration: https://knock.app/blog/zero-downtime-postgres-upgrades https://knock.app/blog/zero-downtime-postgres-upgrades In that post we walk through all the steps we took to go from Postgres 11.9 to 15.3.
- wiz21c 2y agoMaybe they don't upgrade because currently installed PG instances are enough? Meaning that Oracle still holds the "big" deployments ?
- c0balt 2y agoAncient version like 15 or 16? I have recently encountered a PostgreSQL 12 instance. Once the database runs you are inclined to let it keep on running until you must do an upgrade (Bugfixes, EOS) . Upgrading for new features/ improved performance is nice but can be a pain and is prone to cause downtime. PostgreSQLs upgrade tolling story is also a bit painful but it has proven to be an awesome way to semi-regularly test my backup infrastructure.
- ellisv 2y agoI enjoyed this thread (and the article) as I'm preparing to upgrade several Postgres databases from 14 to 16, which should take about 25 minutes or less. My experience has been that most people (including devs) hardly think about their database at all. Everyone just takes the database for granted until a dev runs a bad query that locks a table. Devs let their ORM manage everything for them and don't take the time to think for themselves. Also I rarely see teams with a culture that prioritizes maintenance, even for their own software. It's common for teams to become wary of upgrading because they do it infrequently. Like most things, the more often you perform an upgrade the easier it is. Smaller changes are easier to revert. The more often you do something, the better at it you get. Delaying maintenance often just increases the work later.
- otabdeveloper4 2y agoBecause it's in a Docker container and nobody ever updates Docker containers.
- pronik 2y agoI'm still on 9.x in some systems and it's running great. I find this kind of sentiment a bit weird anyway: PostgeSQL 17 has been out for a couple of weeks, I'm certainly not in a rush to upgrade anything unless I need to. Never touch a running system is as valid as ever and on top of that I'm not a full-time DBA itching to upgrade as soon as possible. With containerization it's also more common to have multiple and right out many DB instances, I won't be going through all of them until someone requests it. Security updates is a completely different matter, but major versions? Don't get the rush, the developers will come around sooner or later.
- water9 2y agoBecause if it isn’t broken, don’t fix it and nobody Wants to be responsible with the production database going down for any significant period of time, Or worse loss of data
- imbradn 2y agoOften lost in these discussions is how much more difficult upgrading is at scale. The article talks about the challenges with upgrading a 4TB database. In my world - that’s a small database. Trying to setup a logical replica of a much larger, high write volume database is an entirely different story with its own set of challenges. In some cases it’s not even possible to do even with tricks like dropping and restoring indexes. Logical still struggles to keep up with high write loads. When something like vacuum freezes kicks off it’s not uncommon to see logical replication lag for significant periods. Then there are things like lack of DDL replication. While this can be mostly worked around - it adds complexity. And remember DB user management is DDL - so if you change a DB password on the primary it won’t replicate to the logical replica. When you have CDC systems using logical decoding from the systems you are upgrading you have to deal with resync conditions related to the fact that the logical replication slot will not be replicated and the new replica will lose its place in the replication stream. Most non-trivial instances have multiple streaming replicas for read offloads which need to be coordinated at cutover. While not difficult it increases the complexity. In addition - there are no guarantees of application compatibility. While this is rarely an issue in my experience- PG 14 (IIRC) changed the function signatures of a bunch of array functions which was particularly painful. That said - Postgres is improving the story here. PG 17’s ability to convert a streaming replica to a logical one and be able to upgrade it will be a game changer for standing up the logical replica. If they can get DDL replication into logical it will improve things even more. That said - it’s still way behind how modern databases like CockroachDB and Yugabyte handle things here.
- zahlman 2y ago>Postgres 17.0 has been out for a bit and it’s awesome, but here’s the reality: most Postgres users won’t upgrade right away. Most probably aren’t even on 16.4 or 16.anything —they’re probably still using Postgres 15 or an even older version. So, by "ancient", this author means possibly not even two years old, as Postgres 15 is barely that old (https://www.postgresql.org/about/news/postgresql-15-released-2526/ https://www.postgresql.org/about/news/postgresql-15-released...). Meanwhile, I've had to make posts this year to tell people no, seriously, stop using Python 2.7. And meanwhile in my package repo: $ apt show postgresql Package: postgresql Version: 14+238 Priority: optional Section: database Source: postgresql-common (238) Origin: Ubuntu Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com> This is Mint 21.3 btw. The author's expectations are incredibly unrealistic.
- _yb2s 2y agoI use old postgresql versions because they work perfectly for my use cases- there is no upside to upgrading. In general I don’t upgrade software that works unless there is a good reason to.