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

321–330 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#321

Post by evanelias »

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's a good list [1]. A handful of these are already doable in modern MySQL and/or MariaDB though.

JSON can often be used in place of arrays, and JSON_TABLE in both MySQL and MariaDB converts JSON into tabular data. MySQL supports multi-valued indexes over JSON, where each row can have multiple index entries (or no entries, e.g. partial index).

MariaDB has built-in convenience types for ipv4, ipv6, and uuid. Or in MySQL you can just use virtual columns to add human-readable conversions of binary columns, although that is admittedly slightly annoying.

MariaDB supports RETURNING.

[1] Edit to add: I do mean that honestly, it's an accurate and insightful list of nice Postgres features, most of which aren't in MySQL or MariaDB. Honestly baffled as to why I'm being downvoted.

Re: Why does everyone run ancient Postgres versions?

#323

Post by icedchai »

justinclift wrote: post #277
buro9 wrote: post #162

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

If you use Docker, then the pgautoupgrade project might be your kind of thing: https://github.com/pgautoupgrade/docker-pgautoupgrade

Thank you for this! I was looking for an "easy" way to upgrade a dev container DB.

Re: Why does everyone run ancient Postgres versions?

#324

Post by necheffa »

efields wrote: post #309
bityard wrote: post #292

You 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 wo…

These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y.

> These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y.

Absolutely...not.

Slow does not mean stable. Slow means the floor is rotting out from under you constantly.

Being prudent about when and where to upgrade is a very active, intentional process that the typical company simply don't have the stomach or skill for.

Re: Why does everyone run ancient Postgres versions?

#325

Post by diffeomorphism »

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…

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.

Re: Why does everyone run ancient Postgres versions?

#326

Post by djbusby »

literalAardvark wrote: post #269
mxey wrote: post #218

Earlier quoted context omitted.

As someone who operates both I much prefer to run pg_upgrade every few years rather than dealing with mysqldump, replication issues and babysitting Orchestrator.

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

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

Re: Why does everyone run ancient Postgres versions?

#327

Post by c0balt »

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

Re: Why does everyone run ancient Postgres versions?

#328

Post by Tostino »

kardianos wrote: post #271
noirscape wrote: post #156

Because 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 m…

There are two primary issues with PostgreSQL: * Upgrades * Database Protocol MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time. The MS SQL Server protocol, TDS, supports native named parameters…

As someone who has a decade of professional postgres experience (and built my company on it), and 6 years of SqlServer experience, I couldn't agree more with your comment. I really, really like the TDS wire protocol and what it enables.

I also really miss their query optimizer. It was just so damn smart.

Re: Why does everyone run ancient Postgres versions?

#329

Post by droobles »

dwwoelfel wrote: post #317

Here'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 , 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 q…

That is awesome, I dream of being able to do zero downtime SQL migrations.

Re: Why does everyone run ancient Postgres versions?

#330

Post by ttfkam »

buro9 wrote: post #162

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

Unless you want support for the MERGE command (v15), bidirectional replication (v16), and some behind the scenes optimizations, you're not really missing anything.

Whether you go from v14 to v17 or v14 to v20, I doubt it'll make a difference in migration strategy. You've still got a fair amount of time before EOL. No need to stress or FOMO.

Post reply on HN