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

261–270 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#261

Post by paulryanrogers »

jeltz wrote: post #235
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?

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?

Re: Why does everyone run ancient Postgres versions?

#262

Post by sofixa »

yxhuvud wrote: post #209
sofixa wrote: post #107

Earlier quoted context omitted.

I doubt it was true in 2012, because sysadmins would be the ones trying to make it run reliably, including things like replication, upgrades, etc. Pretty sure that even in 2012 MySQL had very easy to use replication, which Postgres didn't have well into the late 2010s (does it today? It's been a while since I've ran any databases).

In 2012 MySQL had several flavors of replications, each with its own very serious pitfalls that could introduce corruption or loss of data. I saw enough of MySQL replication issues in those days that I wouldn't want to use it. But sure, it was easy to get a proof of concept working. But when you tried to break it by turning off network and/or machines, then shit broke down in very broken ways that was not recoverable…

Maybe that was true in 2012 (maybe it was related to MyISAM) but by ~2015 with InnoDB MySQL replication was rock solid.

Re: Why does everyone run ancient Postgres versions?

#263

Post by tommy_axle »

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

With sqlite3 -backup There's an online backup api

Re: Why does everyone run ancient Postgres versions?

#264

Post by dalyons »

sgarland wrote: post #254
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…

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

> 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 higher velocity and more incentive aligned ownership than the old central gate keeping DBA team model.

Re: Why does everyone run ancient Postgres versions?

#266

Post by sgarland »

paulryanrogers wrote: post #228
fanf2 wrote: post #195

Earlier quoted context omitted.

Isn’t ON UPDATE related to foreign keys and independent of the data type? https://www.postgresql.org/docs/current/ddl-constraints.html...

Maybe they're thinking of TIMESTAMP in MySQL, which IIRC would auto update its value on any update to the row. Which was useful for uodated_at like columns. Though I think they later limited it to only the first TIMESTAMP column in a table.

No, it works for both [0] types. The first TIMESTAMP thing you’re referring to is that if a specific variable isn’t set, the first TIMESTAMP column automatically gets auto updates applied on creation and update, unless you explicitly defined it to not. This was the default behavior in 5.7, but has since been changed.

[0]: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initializa...

Re: Why does everyone run ancient Postgres versions?

#267

Post by johannes1234321 »

p_l wrote: post #145
Propelloni wrote: post #125

Earlier quoted context omitted.

It is hard to live down a reputation ;) MySQL was immortalized as the database in every LAMP stack. And just like PHP it improved considerably since then.

Also for licensing reasons for a long time there was surviving contingent of MySQL 3.23 in LAMP hosting.

While that change from LGPL to GPL affected only the client library (server always was GPL(+commercial)) and the MySQL company relatively quickly reacted with a FOSS exception to the GPL and by providing a reimplementation of the client library under PHP license (mysqlnd) to serve that market.

(I joined MySQL shortly after that mess, before the Sun acquisition)

Re: Why does everyone run ancient Postgres versions?

#268

Post by literalAardvark »

forinti wrote: post #255
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 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.

Re: Why does everyone run ancient Postgres versions?

#269

Post by literalAardvark »

mxey wrote: post #218
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…

As someone who operates both I much prefer to run pg_upgrade every few years rather than dealing with mysqldump, replication issues and babysitting Orchestrator.

just as long as you don't forget to reindex after

Re: Why does everyone run ancient Postgres versions?

#270

Post by sgarland »

dalyons wrote: post #264
sgarland wrote: post #254

Earlier quoted context omitted.

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

> 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 rarely any interest in doing so.

“You have an unpartitioned table with a few billion rows and a UUIDv4 PK. I’m amazed it’s been working as long as it has. You need to change your schema.”

“Or we can just buy a bigger instance.”

“…”

Rinse and repeat six months later. I’m aware this is an organizational problem, but from what I’ve seen, it’s endemic.

Re: incentives, yes, also a problem. Dev teams are generally incentivized by Product, who doesn’t care at all whether or not something is optimal, only that new features are being shipped. I despise this mentality, but it’s not usually the devs fault.

Post reply on HN