←back to thread

Why does everyone run ancient Postgres versions?

(neon.tech)
264 points davidgomes | 2 comments | 17 Oct 24 21:23 UTC | HN request time: 0s | source
Show context
paulryanrogers ◴[17 Oct 24 23:59 UTC] No.41875055[source]▶
Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes.

Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.

At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.

replies(7): >>41875126 #>>41876174 #>>41876232 #>>41876375 #>>41877029 #>>41877268 #>>41877959 #
slotrans ◴[18 Oct 24 04:00 UTC] No.41876232[source]▶
"Not as robust as MySQL"? Surely you're joking.
replies(3): >>41876309 #>>41876384 #>>41877139 #
sgarland ◴[18 Oct 24 04:20 UTC] No.41876309[source]▶
They’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going.

To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.

replies(3): >>41876387 #>>41876650 #>>41877061 #
pritambarhate ◴[18 Oct 24 07:19 UTC] No.41877061[source]▶
My experience has been exactly opposite. Ability to do Vacuums is good. MySQL doesn’t free up space taken by deleted rows. The only option to free up the space is to mysqldump the db and load it again. Not practical in most of the situations.
replies(3): >>41877166 #>>41877653 #>>41878767 #
benoitg ◴[18 Oct 24 07:42 UTC] No.41877166[source]▶
Not really, the innodb_file_per_table variable has been set to 1 for a long time. Running OPTIMIZE TABLE frees up the disk space in this case.
replies(1): >>41877285 #
sudhirj ◴[18 Oct 24 08:03 UTC] No.41877285[source]▶
Is this process materially different from a vacuum? Does it manage to optimise without a write lock?
replies(2): >>41877689 #>>41878011 #
1. homebrewer ◴[18 Oct 24 10:29 UTC] No.41878011{4}[source]▶
http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2...
replies(1): >>41886804 #
2. sudhirj ◴[19 Oct 24 09:49 UTC] No.41886804[source]▶
That helps a lot thanks. Will summarize it quickly for those who come later: MySQL (InnoDB really) and Postgres both use MVCC, so they write a new row on update. InnoDB however also additionally writes a record marking the old row for deletion.

To do a cleanup, InnoDB uses the records it kept to delete old data, while Postgres must do a scan. So InnoDB pays a record-keeping price as part of the update that makes it easier to clear data, while Postgres decides to pay this price of occasional scanning.