Why does everyone run ancient Postgres versions?

Lots 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.

"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.

Sure. 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.

Also:

5. If your IT department is spread thin already and that old version is running fine, the incentive to potentially create more work for yourself is not gigantic.

One of the first laws of the universe that a good engineer learns is: Do not fix what is not broken.

And no, being old is not broken.

> 3. While upgrades aren't hard, they're not easy either

I guess it depends on scale? I was surprised how easy it was on Ubuntu. There was an automatic migration script, and it worked. Took less than 5 minutes to upgrade.

Sure, there was downtime, but I think most applications out there can live with scheduled downtime of a few minutes.

If you can't have downtime, then nothing is easy.

If the database server has significant "other apps", which it probably doesn't.

Lots of companies pretend like they can't afford 5 minutes of scheduled downtime. Then they end up with 12 hours of unscheduled downtime when shtf.

> 3. While upgrades aren't hard, they're not easy either.

And in my experience, apart from ease of use there's also a major trust issue here. If you're upgrading your app server framework/language, it's easy enough to do a rollback. With databases, people are worried that they might not notice errors right away and then you have to merge the data accumulated since the upgrade with the last backup in case of a rollback.

Not saying that this is entirely rational...

Also, new features on the SQL level are hard to sell if all you're doing is lowest common denominator ORM ("New window functions and faster lateral joins? But we're doing all that in our code!").

> 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.

To drive this a little further, "latest and greatest" doesn't always apply. I've chosen software - even databases - for greenfield deployments one or two releases behind for their known characteristics.

Stability doesn't imply perfection, but rather, predictability.

For SRE (site reliability engineering) the term of art is 'error budget'

To others reading, be mindful: database upgrade times depend greatly on the data stored within them/where/who is using them. Your development environment doesn't represent production. If the distinction even exists!

A five minute upgrade can become indefinite with a single lock [row/table, depending on the storage engine/etc]

I do agree, however I think it’s often easier to upgrade iteratively and deal with smaller issues that arise as opposed to upgrading a huge version diff and struggling to understand and fix all the failing parts.

This is solid advice, however I would caveat that you can't know if it is broken if you are not checking. E.g. if your old as hell database has a known vulernability that can be expoited, unless you can rule out that it can be used in your setup it is broken by all definitions of the word.

I think there's a balance to be struck there. On large databases, upgrade time can be very slow. Is it worth upgrading iteratively at great expense if there are no benefits to be gained (e.g. no bugfixes that affect you, no critical security issues)? Maybe, maybe not.

It's not a caveat since checking is a prerequisite to the law that shall not be violated.

If you are "fixing" a Schroedinger's Cat, my dude the Supreme Court of the Universe will not be kind.

It leads to a lot of old software which is not going to be upgraded ever. Then the entire project dies and gets rewritten from the scratch, because nobody wants to work with Windows 2003 server running Delphi 7, Java 1.4 and Oracle 9i in 2020 (personal experience).

Old software is not necessarily broken, but it is always a tech debt. And you can't live in debt forever, our IT does not work this way.

Being old is not being broken, but being old can make it so that something that is broken cannot be fixed.

Upgrading a datastore is a massive, massive risk.

It requires a lot of work, planned downtime, or some way to smear updates across the estate.

The cost of any failure is very high. The benefit of any major upgrade is also vanishingly small. Unless you need a specific feature, its just not worth it.

The entire field of maintenance engineering would like a word. Over longer periods of time it's vastly cheaper to regularly fix things even before they break, and software is no exception.

Amongst other reasons:

- Performing regular upgrades is an easy and cheap way to maintain a healthy knowledge base about the system. It's always easier to fix a system that is well understood than a black box which nobody has touched in 20 years. Upgrading regularly also leads to people being more comfortable with the upgrade process and it is likely to become a "regular thing" with checklists and other process improvements that make maintenance safer and more reliable.

- Doing preemptive maintenance means YOU get to choose when the system is unavailable and then you can plan accordingly, perhaps by planning it during a period with low usage or even arranging for a redundant system to take up the load. The alternative is leaving it up to chance when a system will fail, and that's frequently at high-load moments when you can least afford it.

- As a corollary to the previous point: a team which is in control of its systems can have more predictable output of feature work, since there will be fewer "unexpected" events.

Not maintaining your systems is only cheaper in the long run if you don't count the engineering time required to fix things when they break or need to be replaced. Ounce of prevention vs pound of cure and all that.

One of the first laws of universe that an experienced engineer learns is that "do not fix what is not broken" never actually applies, and is only brought up by people invulnerable to consequences.

That doesn't mean "upgrade recklessly," but it does mean you should know _why_ you're either upgrading or _NOT_ upgrading. That's your job, much more than the act of upgrading itself.

Unpublished vulnerabilities in old software are not a hypothetical. And very old packages are usually broken, just coped with at the expense of significant lost opportunity cost - or because the failure is a combination of rare and impactful that means once it happens everyone is out of job anyway.

Seriously, I've yet have to encounter a sysadmin using that old, silly adage at me and not later have to admit I was right.

Edit: so no, you don't stay on an ancient version of the database because "it's not broken." You're staying on it because _the upgrade process itself_ is so broken you're terrified of it.

> The entire field of maintenance engineering would like a word. Over longer periods of time it's vastly cheaper to regularly fix things even before they break, and software is no exception.

I mean I think it's because maintenance is so unglamorous. So when it happens and everything doesn't collapse, nobody remembers and starts asking why we even do it (and then sysadmins and middle management suddenly aren't a thing, and companies and IT become exactly as fragile as anyone with _actual_ understanding of management would expect).

Meanwhile when regular maintenance fails in progress, it often ends up in the news with a body count attached.

One of my favourite podcasts has a running joke that you should never do maintenance (because so many industrial disasters happen during it). Of course the bias is the point of the joke - and usually the reason things went bad is because either the maintenance was neglected until that point, or the engineering picked Postgresql, I mean, didn't consider the necessity of maintenance.

I think this is a really good take. It all boils down to "if it ain't broke don't fix it."

No matter how easy it is, it takes more effort and thought to do an upgrade than it does to not do an upgrade at all, and for most users the upside is basically invisible if it exists at all.

Preventative maintenance is a thing in both software and hardware world.

It's often lower risk to apply frequent smaller changes than wait years for one enormous "bundled" upgrade.

Not to mention:

- If you're scared to perform upgrades, this is a good indicator that you lack confidence in your backup/recovery/reversion policies and procedures, probably for good reason, so maybe fix that.

I generally follow if it’s not broken, fixes need to be carefully planned. I can’t tell you how many times I thought I’d quickly do an upgrade and things would go wrong, like all of my home automation stop working right before bed.

Sure, 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

Or when the version is EOL, not getting security patches, and/or only compatible with an OS that isn't getting security updates.

…or you're on AWS RDS, which will automatically bump your db cluster if it goes EOL and you ignore the notices for more than a year.

Maintenance engineering is in full agreement with that. You are maintaining the old version, backporting fixes etc. instead of tearing down the whole thing and replacing it with new shiny all the time.

This is alternately known as Chesterton's Fence.

https://en.wikipedia.org/wiki/G._K._Chesterton#Chesterton's_...

I mean, _yeah_, for anything important you should move as carefully as possible. Just, "not upgrading" ain't that.

> The entire field of maintenance engineering would like a word. Over longer periods of time it's vastly cheaper to regularly fix things even before they break, and software is no exception.

and

> Not maintaining your systems is only cheaper in the long run if you don't count the engineering time required to fix things when they break or need to be replaced. Ounce of prevention vs pound of cure and all that.

Those are pithy assertions, but I don't think they're universally factually correct. This applies at some scales, but not at others. One size does not fit all, nor does one maintenance strategy fit all. I do believe I specifically called out the dogmatism in this discussion, and yet here we go piling on more of the same. I don't think that's terribly helpful. I assume that everyone is well aware of all the points you raised, as I suspect that most people running PostgreSQL are not in fact incompetent.

Not everyone has a team which "is in control of its systems", nor can everyone afford such a team. It may well be cheaper for some people to run something old until it burns only to have The Database Consultant come in one day to raise it from the dead.