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

251–260 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#251

Post by KronisLV »

gonzo41 wrote: post #225
KronisLV wrote: post #165

In many orgs out there, the version that's picked when a project is started will stick around for a while. Suppose you join a project and see that the PostgreSQL version used is pretty old. Do you: A) convince people to migrate it for some nebulous benefits (features that aren't currently used, performance improvements that will be difficult to measure in lieu of tooling and aggregated statistics) while also taking o…

So i've got a small but important app that I run on PG14 via a container. I literally just put the pgdata dir outside the container and I can keep the host OS totally fine and have an isolated db environment. it's a very nice way to not worry about the db.

Some might disagree (believing that the system package manager should be responsible for the PostgreSQL install), but I agree that the approach you’re using is one of the better ones.

That way you can update the database version when you want, while still installing the base system updates quite frequently. Plus, I think it’s really nice to separate the runtime from the persistent data, which such setups make obvious.

Re: Why does everyone run ancient Postgres versions?

#252

Post by sgarland »

fhdsgbbcaA wrote: post #91
sgarland wrote: post #71

Earlier quoted context omitted.

> This means if you use UFT-8 in MySQL, you can’t use emoji for example. I for one have always viewed this as a perk.

A database that doesn’t give you back what you put into it is never a perk. It literally can’t handle storing and retrieving the data.

I don’t want to see emoji in my database. The customer is only right in matters of taste, not engineering.

Re: Why does everyone run ancient Postgres versions?

#253

Post by creshal »

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…

Yeah, everywhere I worked there's usually two clusters of postgres databases

- whatever made it past the postgres 10 hump is on the most recent version and keeps getting migrated

- whatever is still on 9.x is scheduled to remain that way until the product using it will be EOL'd

Re: Why does everyone run ancient Postgres versions?

#254

Post by sgarland »

phil21 wrote: post #185
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?

My opinion is Postgres was designed by software developers for software developers. 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, and the sysadmins having to support maintenance and production availability preferring MySQL. One of the few things I’ve enjoyed with the move into devops and companies forcing…

> 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 operational roles was their discovery that Postgres was utterly horrible to administer at a systems level.

Are you (or your devs, rather) actually running your own DBs? If so, respect. 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.

Re: Why does everyone run ancient Postgres versions?

#255

Post by forinti »

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 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 pipe directly into another, with no need for a temporary file. It couldn't be easier.

Can't say the same for Oracle. Even applying a patch can ruin several weekends.

Re: Why does everyone run ancient Postgres versions?

#256

Post by sgarland »

GoblinSlayer wrote: post #140
lelanthran wrote: post #96

Earlier quoted context omitted.

My experience of both is that MySQL is easier for developers, PostgreSQL is easier for sysads. That was true in 2012; dunno if it still applies though.

>MySQL is easier for developers Except that search doesn't work, because all text is in latin1 encoding.

While obviously I prefer Unicode, latin-1 is perfectly acceptable for most use cases in Western cultures.

What part of searching have you found to not work well using it?

Re: Why does everyone run ancient Postgres versions?

#257

Post by sgarland »

lol768 wrote: post #237
p_l wrote: post #144

Earlier quoted context omitted.

Some places still have columns declared as utf8 instead of utf8mb4, and there's a special place in hell for authors of the MySQL general clusterfuck regarding encodings - it was all nice and great if you didn't care about anything other than latin1 or ASCII - go outside that before utf8 option and it was horror that even experienced operators managed to fuckup (I have a badge from a Google conference in 2017 with nic…

Yeah, the whole charset/collation design is a complete dumpster fire.

In fairness, reasoning about collations is like peering into the abyss. I get why they’re required to have so many levels of detail, and the Unicode Consortium has done a fantastic job, but to say they’re complicated is putting it mildly.

Re: Why does everyone run ancient Postgres versions?

#258

Post by gramakri2 »

eru wrote: post #246
phil21 wrote: post #185

Earlier quoted context omitted.

My opinion is Postgres was designed by software developers for software developers. 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, and the sysadmins having to support maintenance and production availability preferring MySQL. One of the few things I’ve enjoyed with the move into devops and companies forcing…

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

Re: Why does everyone run ancient Postgres versions?

#259

Post by sgarland »

pritambarhate wrote: post #120
sgarland wrote: post #74

Earlier quoted context omitted.

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

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.

VACUUM rarely reclaims space from the OS’ perspective, if that’s what you meant. It can in certain circumstances, but they’re rare. VACUUM FULL is the equivalent to OPTIMIZE TABLE – both lock the table to do a full rewrite, and optimally binpack it to the extent that is posssible.

EDIT: my mistake, OPTIMIZE TABLE is an online DDL. I’ve been burned in the past from foreign key constraint metadata locks essentially turning it into a blocking operation.

Re: Why does everyone run ancient Postgres versions?

#260

Post by sgarland »

erik_seaberg wrote: post #81
slotrans wrote: post #67

Earlier quoted context omitted.

"Not as robust as MySQL"? Surely you're joking.

Early MySQL versions made egregious design choices like quietly ignoring missing foreign keys and enum typos, truncating long strings, and randomly choosing rows from groups. https://web.archive.org/web/20230922210124/https://grimoire....

Yeah, it was bad. What kills me is SQLite has its own absurd set of gotchas [0] yet is seen as amazing and wonderful by devs. PKs can have NULLs? Sure! Strings can have \0 in the middle of them? Why not? FKs aren’t enforced by default? Yeah, who needs referential integrity, anyway?

My only conclusion is that the majority of devs don’t actually read documentation, and rely purely on the last blog post they read to influence their infrastructure decisions.

[0]: https://www.sqlite.org/quirks.html

Post reply on HN