←back to thread

Why does everyone run ancient Postgres versions?

(neon.tech)
264 points davidgomes | 7 comments | 17 Oct 24 21:23 UTC | HN request time: 0s | source | bottom
Show context
noirscape ◴[18 Oct 24 08:45 UTC] No.41877487[source]▶
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 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.

replies(11): >>41877841 #>>41877921 #>>41877992 #>>41878101 #>>41878462 #>>41878670 #>>41879013 #>>41879161 #>>41879191 #>>41879259 #>>41879567 #
crabbone ◴[18 Oct 24 10:27 UTC] No.41877992[source]▶
> Postgres is software you want to be managed by your distro.

Why?

For production systems I generally try to avoid using anything not built internally. When it comes to PostgreSQL, for example, if you want to submit benchmarks, or you want to report bugs, an important or sometimes a mandatory component is to provide compilation flags. Also, seeing how every kind of storage can act in a different way, tuning of higher-level storage programs seems inevitable. Even though PostgreSQL has a lot of configurations that can be done to the compiled program, some (eg. block size) are compile-time that can only be changed by compiling the program.

replies(3): >>41878275 #>>41878277 #>>41880583 #
1. digitallis42 ◴[18 Oct 24 11:10 UTC] No.41878275[source]▶
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.
replies(1): >>41878437 #
2. throw0101c ◴[18 Oct 24 11:40 UTC] No.41878437[source]▶
> 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 new version format).

replies(4): >>41878593 #>>41879231 #>>41880330 #>>41890604 #
3. jamespo ◴[18 Oct 24 12:02 UTC] No.41878593[source]▶
How does that work for python/perl/ruby libs etc?
replies(1): >>41881646 #
4. necovek ◴[18 Oct 24 13:24 UTC] No.41879231[source]▶
> 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.

5. RealStickman_ ◴[18 Oct 24 15:28 UTC] No.41880330[source]▶
Using third party repos makes upgrading the OS in general more difficult though and is more dangerous than simply using what your distro provides.
6. danudey ◴[18 Oct 24 17:33 UTC] No.41881646{3}[source]▶
It's very rare that the client protocol changes dramatically and in an incompatible way, so typically nothing changes and no one notices.

I've seen a few rare cases where an update to the client library is required for improvements to the protocol, e.g. when MySQL switched to the new password/authentication format, but that was also backwards-compatible: the old format was used until an account's password was changed, password changes could be told to use the legacy format instead, and you could set the legacy format by default. The only issue that occurred (and it did occur) was old client libraries + new server password hashes, but, again, in this case it didn't affect existing installations/users/passwords.

7. crabbone ◴[19 Oct 24 20:38 UTC] No.41890604[source]▶
> This way when you upgrade your OS

Don't forget to do this on Friday afternoon! :D

Upgrading OS in production environment... this reminds me of a joke about police academy intake test:

The participants were offered a board with a round and a square hole and two pegs of similar shape. After the test the new recruits were sorted into two groups: very smart and very strong

So, yeah, you ought to be either very smart or very strong to upgrade OS in production environment, and then also discover changes to your database as you go along.

That's not to say that upgrades don't happen at all... but you'd be upgrading in a testing environment ten times before you try that "for real", and at that point you would have probably solved the question of whether you need to (re)install the database and how :)