Hacker News Reader

Comment by boxed | Hacker News Reader

Seconded. Coming from MySQL this is a huge regression that makes Postgres look like something from the 80s. I still wonder why this isn't seen as the absolutely highest priority.

View on HN

I have not ran MySQL for some years but it at least used to have exactly the same issue. Upgrading a database with MySQL can take a long time if you have many tables. The main difference is only really that PostgreSQL does it with a separate tool, pg_upgrade, while MySQL does it as part of the main binary.

For both MySQL and PostgreSQL you will need to use some kind of logical upgrades if you want no downtime.

MySQL has advocated for decades spinning up a replica with the upgraded version, waiting for it to catch up to master before promoting it to the new master. You can do the same thing with Postgres.

Exactly, MySQL and PostgreSQL are the same here. Maybe one is a bit faster than the other at doing major version upgrades but the behaviours are quite similar.

They don't change the on-disk structure all the time though...

Mostly because MySQL development is slower.

Even when MySQL development velocity was more rapid, they maintained binary table format compatibility across major version upgrades the vast majority of the time. Literally the only exception I can think of, which necessitated a table rebuild, was the fractional timestamp storage change when going from MySQL 5.5 (2010) to 5.6 (2013).

No, the main difference is that MySQL bundles the code needed to interact with the old db version in the newer server binaries (effectively by not changing the on-disk binary format!) while pg_upgrade requires you to have both old and new installs living side-by-side to reuse logic/code from old binaries. It is a more bulletproof method and less susceptible to bugs and (upstream) developer errors, but is (or at least can be) harder for the sysadmin+dbadmin.

(For example, ports under FreeBSD doesn’t let you install multiple Postgres versions as they are marked as conflicting packages so installing one necessarily uninstalls the other. The saving grace here is that most (virtually all) FreeBSD installations have root on ZFS and you can employ ZFS snapshots (via the hidden .zfs folder) to access the old binaries after upgrading to the new postgres version, but not many people know this trick!)

Probably because it's an open source project and apparently none of its users cared about this feature enough to develop it or fund it.

It is also a bit tricky tradeoff. You do not want to be stuck with the same data format forever. So databases like MySQL and PostgreSQL need a downtime when doing a major version upgrade. They both try to keep it short, usually seconds, but minutes can happen in either database.

Reply on news.ycombinator.com