Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why does everyone run ancient Postgres versions?

neon.tech

Post reply on HN
Print view View original

381–390 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#381

Post by LaGrange »

kayodelycaon wrote: post #301
LaGrange wrote: post #217

Earlier quoted context omitted.

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 soft…

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.

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

Re: Why does everyone run ancient Postgres versions?

#382

Post by elric »

WJW wrote: post #210
Dalewyn wrote: post #128

Earlier quoted context omitted.

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.

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 touch…

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

Re: Why does everyone run ancient Postgres versions?

#383

Post by sgarland »

ttfkam wrote: post #299
sgarland wrote: post #254

Earlier quoted context omitted.

> 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 I’ve seen this as well, but when pressed, none of them could articulate what part of its feature set they actually needed to use. > One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operationa…

> none of them could articulate what part of its feature set they actually needed to use. Transactional DDL: migration errors never leave the database in an intermediate/inconsistent state. Range types + exclusion constraint: just no way to do this in MySQL without introducing a race condition. Writeable CTEs: creating insert/update/delete pipelines over multiple tables deterministically. Seriously though, the RETURN…

That is a well-thought out list, and you’re clearly aware of and take advantage of the DB’s capabilities. Seriously, congrats. Especially RETURNING – it’s always baffling to me why more people don’t use it (or its sad cousin in MySQL that lets you get the last inserted rowid if using an auto-increment).

Most devs I’ve worked with don’t know about aggregations beyond COUNT and GROUP BY, and do everything in the app. I’ve pointed these out before, and am always told, “we don’t want to have additional logic in the DB.” So you want a dumb bit box then, got it – why are you using an RDBMS?

> Transactional DDL

I know this is a thing, and I’ve used it in Postgres, but I’ve also never found myself in MySQL being upset that I didn’t have it. Everything should be thoroughly tested in staging before prod.

> RLS

Yes, amazing feature if you use it correctly.

I will give MySQL a couple of nods in its favor: well, three.

1. Clustering index. If you design your schema around this fact, range queries can be WAY faster. Dropbox does this, IIRC.

2. Generated Virtual Columns. Not sure why Postgres still doesn’t support this.

3. ON UPDATE CURRENT_TIMESTAMP. It’s so nice to have the DB automatically track update times for you, IMO.

Re: Why does everyone run ancient Postgres versions?

#384

Post by sgarland »

ttfkam wrote: post #303
sgarland wrote: post #254

Earlier quoted context omitted.

> 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 I’ve seen this as well, but when pressed, none of them could articulate what part of its feature set they actually needed to use. > One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operationa…

> My experience has been that they spin up either a hideously under or over-provisioned RDS or Aurora instance, and then never touch it until it breaks That's a true shame considering how easy it is to make a read replica of any size and then fail over to it as the new primary. Definite skill issues.

It’s more like a “not knowing how fast something should be” in the case of under-provisioning, and “not knowing or caring to look at metrics” for over-provisioning.

I once was examining some queries being generated via Prisma, and found it was using LIMIT/OFFSET for pagination. I pointed this out to the devs, who replied that the query times were acceptable for their SLOs. I guess if you don’t know that a simple SELECT can and should often be in the sub-msec range, you might not be concerned that it’s taking 100 msec.

The other is just the normalization of cloud expenditure. Every large org has some kind of pricing agreement with their provider, and so are rarely incentivized to cut costs, since they have a minimum spend.

Re: Why does everyone run ancient Postgres versions?

#385

Post by chasil »

icedchai wrote: post #373
chasil wrote: post #17

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…

Impressive! What platform were you running that on?

HP-UX 10.20, which went out of support in 2002.

Re: Why does everyone run ancient Postgres versions?

#386

Post by literalAardvark »

djbusby wrote: post #326
literalAardvark wrote: post #269

Earlier quoted context omitted.

just as long as you don't forget to reindex after

It's a step of your script or runbook. Right?

It is, but it also takes a very long time during which production data integrity can be severely affected.

Re: Why does everyone run ancient Postgres versions?

#387

Post by chasil »

cafard wrote: post #369
chasil wrote: post #17

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…

Seriously, Oracle 7.3.4 in 2013? I am impressed.

I still have one of the HP-UX systems that ran it in the data center; I could start it up if I wanted.

Re: Why does everyone run ancient Postgres versions?

#388

Post by literalAardvark »

mxey wrote: post #294
literalAardvark wrote: post #269

Earlier quoted context omitted.

just as long as you don't forget to reindex after

> All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically.

Not true. Those scripts only cover problems caused by PostgreSQL itself, not, for example, a change in collations that will silently break your indexes, such as the one in Debian 12.

Re: Why does everyone run ancient Postgres versions?

#389

Post by literalAardvark »

ttfkam wrote: post #305
jeltz wrote: post #286

Earlier quoted context omitted.

Nope, that is totally unrated. To support upgrade in place without an old version of PostgreSQL: 1. The new version of PostgreSQL would need to able to read all old catalog table formats and migrate them. 2. The new version of PostgreSQL would need to support all old versions of the parse tree to migrate views. 3. Likely a bunch of more things that I do not know of. I for example doubt it is trivial to just read an o…

It is a PITA, but I've written scripts that pg_dump just the schema, load the schema into the new db with the new version, set up logical replication between the two, wait for them to sync, reset all the sequences, and rebuild indexes before doing the handover. It works with basically no downtime but I agree this kind of thing should definitely be easier, even turnkey.

I feel that really should be included in core, yes.

I get why it wasn't, but logical replication has been production ready for a while now, so it really should have at least a little sugar.

Re: Why does everyone run ancient Postgres versions?

#390

Post by fhdsgbbcaA »

homebrewer wrote: post #207
fhdsgbbcaA wrote: post #27

Earlier quoted context omitted.

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.

I won't defend that utf8 brain damage, but the defaults are sane since 2018 — you don't need to set the encoding, it's set to proper utf8 out of the box. MySQL 8 cleaned up a lot of this legacy stuff.

Good to hear they saw the light but after I switched to Postgres I never had a single regret.

In a competitive market where people make very long term engineering decisions based on stability and reliability you can’t fuck up this badly and survive.

Post reply on HN