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

331–340 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#331

Post by evanelias »

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…

Many of the largest US tech companies were successfully using MySQL replication in 2012 without frequent major issues.

source: direct personal experience.

Re: Why does everyone run ancient Postgres versions?

#332

Post by ttfkam »

olavgg wrote: post #203

The PostgreSQL team releases a data incompatible format every year. Do they really need to break the data format every year? In my opinion, the release model for PostgreSQL should be refactored. Breaking stuff is no fun for users, please stop doing that if you are a software developer. And if you have to, make it autonomous for the user.

If you want performance to steadily increase rather than steadily decrease, the answer to your question is yes. That doesn't mean tools couldn't be better, but there are clear technical advantages to their on-disk format updates.

Re: Why does everyone run ancient Postgres versions?

#333

Post by chasil »

Tostino wrote: post #328
kardianos wrote: post #271

Earlier quoted context omitted.

There are two primary issues with PostgreSQL: * Upgrades * Database Protocol MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time. The MS SQL Server protocol, TDS, supports native named parameters…

As someone who has a decade of professional postgres experience (and built my company on it), and 6 years of SqlServer experience, I couldn't agree more with your comment. I really, really like the TDS wire protocol and what it enables. I also really miss their query optimizer. It was just so damn smart.

I've only done it once, but doing an "rpm -Uvh" on the SQL Server binaries in Linux, then starting up the database did an immediate upgrade of the database files.

Oracle's DBUA is so much more painful.

Re: Why does everyone run ancient Postgres versions?

#334

Post by throwaway894345 »

bityard wrote: post #292

You might as well ask, why does anyone run an older version or anything? The reasons will be largely the same. Most of the software on my machines are "old" because they are part of a Linux distribution that (aside from security issues) was frozen in time a year or two ago so that it could be tested, released, and maintained. I am quite happy to have a system that I know is not going to break (either itself, or my wo…

I don’t necessarily need to be on the latest version, but I prefer to take many small upgrades rather than one big upgrade at least when it comes to databases. Frequent upgrades also forces an organization to get good at upgrading and managing the risk.

Re: Why does everyone run ancient Postgres versions?

#335

Post by ttfkam »

occz wrote: post #99
justin_oaks wrote: post #19

My upgrade policy for everything: Significant security vulnerability? Upgrade Feature you need? Upgrade All other reasons: Don't upgrade. Upgrading takes effort and it is risky. The benefits must be worth the risks.

Upgrading when multiple versions behind is significantly more risky than doing it when the update is relatively fresh. Additionally, actions done frequently are less risky than actions done rarely, since you develop skills in performing that action as an organization - see high deployment frequency as a strategy of managing deployment risk. This adds up to continuous upgrading being the least risky option in aggregat…

Upgrading from v11 to v16 is not materially different in Postgres from v14 to v16. Same tools. Same strategies.

Re: Why does everyone run ancient Postgres versions?

#336

Post by throwaway894345 »

necheffa wrote: post #324
efields wrote: post #309

Earlier quoted context omitted.

These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y.

> These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y. Absolutely...not. Slow does not mean stable. Slow means the floor is rotting out from under you constantly. Being prudent about when and where to upgrade is a very active, intentional process that the typical company simply don't have the stomach or skill for.

Yeah, eventually you will have to upgrade and deal with all of the accumulated debt. You don’t have to be on the bleeding edge but you should still be updating regularly.

Re: Why does everyone run ancient Postgres versions?

#337

Post by Tostino »

mschuster91 wrote: post #293
sgarland wrote: post #270

Earlier quoted context omitted.

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

> Rinse and repeat six months later. I’m aware this is an organizational problem, but from what I’ve seen, it’s endemic. Easy enough: almost no one writes SQL queries by hand these days, not for querying the database nor for doing schema upgrades. It's all done by tools - Doctrine in the PHP world for example. And pretty much no one but actual CS graduates knows anything deeper about databases. Result is, devs are ha…

You would be very surprised to see the workflow of DB heavy development teams. Some oracle devs have entire backends coded in pl/sql.

My last company had an absolute ton of pl/pgsql written to support hundreds of ELT pipelines, migrations were all hand written and managed with liquibase.

There are more of them than you'd think out there. Just generally supporting some boring b2b or backend software.

Re: Why does everyone run ancient Postgres versions?

#338

Post by chasil »

necheffa wrote: post #324
efields wrote: post #309

Earlier quoted context omitted.

These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y.

> These are the companies you want to be at IMHO. Provided the compensation is adequate, slow and stable > fast and pivot-y. Absolutely...not. Slow does not mean stable. Slow means the floor is rotting out from under you constantly. Being prudent about when and where to upgrade is a very active, intentional process that the typical company simply don't have the stomach or skill for.

We are still running OS2200 EXEC-8.

That platform got SMP in 1964.

Re: Why does everyone run ancient Postgres versions?

#339

Post by whartung »

Tostino wrote: post #328
kardianos wrote: post #271

Earlier quoted context omitted.

There are two primary issues with PostgreSQL: * Upgrades * Database Protocol MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time. The MS SQL Server protocol, TDS, supports native named parameters…

As someone who has a decade of professional postgres experience (and built my company on it), and 6 years of SqlServer experience, I couldn't agree more with your comment. I really, really like the TDS wire protocol and what it enables. I also really miss their query optimizer. It was just so damn smart.

I'm sorry, not to quibble about the quality of the TDS protocol, but what does it have to do with the upgradability of SqlServer?

Re: Why does everyone run ancient Postgres versions?

#340

Post by brentjanderson »

droobles wrote: post #329
dwwoelfel wrote: post #317

Here's how we did it at OneGraph (RIP), where we not only upgraded versions without downtime, but we also moved hosting providers from GCP to Aurora without downtime. 1. Set up logical replication to a new database server. We used https://github.com/2ndQuadrant/pglogical , but maybe you don't need that any more with newer versions of postgres? 2. Flip a feature flag that pauses all database queries and wait for the q…

That is awesome, I dream of being able to do zero downtime SQL migrations.

One of the linked pieces in the Neon blog post is from Knock, where we pulled off a practically zero downtime migration: https://knock.app/blog/zero-downtime-postgres-upgrades

In that post we walk through all the steps we took to go from Postgres 11.9 to 15.3.

Post reply on HN