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

281–290 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#281

Post by majewsky »

jeltz wrote: post #240
Narkov wrote: post #230

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

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

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

Re: Why does everyone run ancient Postgres versions?

#282

Post by thomaslord »

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…

I'll confess - I have a project that uses Heroku's managed Postgres and my preferred upgrade method is to set the maintenance window to the middle of the night, create a backup, and be awake at 1am to make sure that nothing is broken after they force the upgrade. Their auto-upgrade process hasn't failed me so far, but there's no way to manually trigger it.

Re: Why does everyone run ancient Postgres versions?

#283

Post by necovek »

sgarland wrote: post #270
dalyons wrote: post #264

Earlier quoted context omitted.

> 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, at which point they might ask for help, or they might just make it bigger. Yep that’s exactly what I’ve seen too :). I still overall prefer this distributed database model - yes you spend more and people make mistakes (and learn). But if you can afford it you get hig…

I don’t mind the model IFF the team has interest in learning how to do it correctly. My biggest complaint as both an SRE and now DBRE has been that dev-managed infrastructure inevitably means during an incident that I had nothing to do with, I’ll be paged to fix it anyway. Actually, that’s not the problem; the problem is later when I explain precisely how and why it broke, and how to avoid it in the future, there’s r…

UUID (version does not matter for storage, only for generation and distribution) is basically a 128-bit unsigned int, so a double "word" on 64-bit platforms, and it's natively supported by Postgres since at least 8.3 (earliest version with docs up).

While most versions ensure it's random, there are plenty of indexing algorithms that make searching through that quick and close to O(1), so that should not be the schema problem.

Unless you used a string field, but there is a quick workaround for that with Postgres too (make an index on `UUID(field)`, and look it up by `UUID(value) = UUID(field)`).

That's why both "devops" (DBAs?) and "devs" prefer Postgres over many other databases: you can easily handle some small mistakes in schemas too while you do the full migration in parallel.

Re: Why does everyone run ancient Postgres versions?

#284

Post by forinti »

literalAardvark wrote: post #268
forinti wrote: post #255

Earlier quoted context omitted.

I have always compiled from source so that I can have more than one version in place. The installation takes less than 1GB and is quite easy and fast to compile and I've never had any issues with upgrades. If you forget to compile an extension, you can do it later, copy the .so to the right folder, and you don't even have to restart the cluster. If you don't want to use pg_upgrade, you can dump from one cluster and p…

> It couldn't be easier If you have a trivial database, yes. If you have a large, complicated database that takes hours/days to restore and has an HA SLA, very hell no.

That's not going to be easy with any database.

Re: Why does everyone run ancient Postgres versions?

#285

Post by necovek »

throw0101c wrote: post #234
digitallis42 wrote: post #223

Earlier quoted context omitted.

Depends on your scale. If you're a startup or even just a small side service, performance isn't going to be a bottleneck and you/the org wants the thing to be fire and forget, including bug fixes and especially security patches. A distro takes care of all of those generally and makes sure the dependencies have the same care taken.

> A distro takes care of all of those generally and makes sure the dependencies have the same care taken. So does using the vendor repos: * https://apt.postgresql.org/ / https://yum.postgresql.org * https://dev.mysql.com/downloads/repo/ This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the…

> This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the new version format).

At least on Ubuntu (and likely Debian), your existing Postgres version is never dropped until you are ready to manually upgrade yourself.

Re: Why does everyone run ancient Postgres versions?

#286

Post by jeltz »

paulryanrogers wrote: post #261
jeltz wrote: post #235

Earlier quoted context omitted.

To avoid having to slow down development of new PostgreSQL features. Improving upgrades in a way where PostgreSQL does not need to either maintain multiple different versions of parts of the code and/or lock down internal interfaces which now can change freely every major version so they cannot be refactored and improved in the future is not a trivial task, maybe even impossible. Even just the existence of pg_upgrade…

What is this holding back? A redo based alternative to MVCC?

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 old catalog without having a fully up and running cluster which supports almost everything in that old cluster. The catalog has TOAST tables and indexes for example.

Right now 1 and 2 are implemented in pg_dump plus by having pg_dump call functions in a running old version of PostgreSQL.

Re: Why does everyone run ancient Postgres versions?

#287

Post by anymouse123456 »

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…

Also a fan of Postgresql, but compiling and installing this tool is also a complete nightmare.

Every bit of the experience before getting it working, feels like it was designed by an MBA who owns a Postgres hosting service.

Other than that, it's amazing.

Re: Why does everyone run ancient Postgres versions?

#288

Post by RegnisGnaw »

whizzter wrote: post #220
MichaelZuo wrote: post #179

Earlier quoted context omitted.

So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?

I think one really BIG factor is that built-in logical replication wasn't introduced until PostgreSQL 10 in 2017, before that you only had physical replication for master-slave but iirc that didn't work between versions so doing a "hot" upgrade was more or less impossible without third-party tools iirc. So even if it's available these days, the amount of people still subjected to upgrades from older version still lea…

There is still major issues with logical replication, mainly limited feature support.

Re: Why does everyone run ancient Postgres versions?

#290

Post by zaphar »

gramakri2 wrote: post #258
eru wrote: post #246

Earlier quoted context omitted.

Of course, sqlite is even easier on the sys-admins, (but not necessarily the right tool for the job.)

How does one backup a sqlite without stopping the app or disrupting service? I couldn't find a simple answer to this

The typical solution is https://litestream.io/. Comparatively easy to setup for most usages of sqlite.
Post reply on HN