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

341–350 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#341

Post by enraged_camel »

ttfkam wrote: post #335
occz wrote: post #99

Earlier quoted context omitted.

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.

We are planning to upgrade from 11 to 17 soon. Even thinking about it is giving me ulcers. Our infra provider said we actually need to upgrade to 13 first, and then to 17. They did not provide a reason.

Re: Why does everyone run ancient Postgres versions?

#342

Post by RealStickman_ »

throw0101c wrote: post #234
digitallis42 wrote: post #223

Earlier quoted context omitted.

Depends on your scale. If you're a startup or even just a small side service, performance isn't going to be a bottleneck and you/the org wants the thing to be fire and forget, including bug fixes and especially security patches. A distro takes care of all of those generally and makes sure the dependencies have the same care taken.

> A distro takes care of all of those generally and makes sure the dependencies have the same care taken. So does using the vendor repos: * https://apt.postgresql.org/ / https://yum.postgresql.org * https://dev.mysql.com/downloads/repo/ This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the…

Using third party repos makes upgrading the OS in general more difficult though and is more dangerous than simply using what your distro provides.

Re: Why does everyone run ancient Postgres versions?

#343

Post by Tostino »

whartung wrote: post #339
Tostino wrote: post #328

Earlier quoted context omitted.

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?

Nothing, they're unrelated.

However, I agree with OP that SQL Server does have a solid upgrade story and it's generally pain free.

They are just two aspects I really wish Postgres could do better on, and I miss working with those parts of SQL Server.

Re: Why does everyone run ancient Postgres versions?

#344

Post by ttfkam »

0xbadcafebee wrote: post #65

Because we don't really like it to begin with. The RDBMS has not fundamentally changed much since the 80's. It's the same basic design inside and out, with a shitload of tweaks and optimizations. Don't get me wrong - you can get very far with tweaks and optimizations. But the foundations root you to a particular model, and some things in that model will always be painful. The important question to me isn't why don't…

You think people choose Postgres because it's the incumbent? That's an… interesting take.

Re: Why does everyone run ancient Postgres versions?

#345

Post by icedchai »

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…

I worked at a place that was running a 8 year old install of Oracle 8i because upgrading was just too painful. Oracle 10g was out by that point. It's been over 15 years since... I wonder if they ever upgraded?

Re: Why does everyone run ancient Postgres versions?

#346

Post by ziml77 »

Spivak wrote: post #318
stackskipton wrote: post #316

Earlier quoted context omitted.

SRE here, not at all. Not chasing shiny is important but generally when tech debt builds up this high, life is generally hell in terms of outages, unable to accomplish basic tasks and dealing with a bunch of people who have NIH syndrome.

Which is why you build on a platform like Alma/Redhat that gives you 10 years of support. You can call it outdated I guess but I prefer "supported." Make everyone else work out the new bugs before upgrading-- it used to be the rule not to update to a .0 release but being a hip modern developer means moving the 0 to the other side and yoloing it.

The problem is that software ends up not getting touched for all those years, but eventually needs an upgrade when it's at end of support. And at that point you end up having to make changes to a system where no one remembers how it works or how to deploy it. Keeping software up to date to me is similar to how you practice disaster recovery scenarios. You do it regularly so no one forgets the processes and so any issues can be dealt with while you're not under a short time limit.

Re: Why does everyone run ancient Postgres versions?

#347

Post by ellisv »

I enjoyed this thread (and the article) as I'm preparing to upgrade several Postgres databases from 14 to 16, which should take about 25 minutes or less.

My experience has been that most people (including devs) hardly think about their database at all. Everyone just takes the database for granted until a dev runs a bad query that locks a table. Devs let their ORM manage everything for them and don't take the time to think for themselves.

Also I rarely see teams with a culture that prioritizes maintenance, even for their own software. It's common for teams to become wary of upgrading because they do it infrequently. Like most things, the more often you perform an upgrade the easier it is. Smaller changes are easier to revert. The more often you do something, the better at it you get. Delaying maintenance often just increases the work later.

Re: Why does everyone run ancient Postgres versions?

#348

Post by RegnisGnaw »

beeboobaa3 wrote: post #300
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…

This has been solved by logical replication a few versions ago. 1. Leave your old database running 2. Start your new database with new version 3. Run logical replication and let it catch up 4. Decide you want to switch over. 5. Using a script: Stop your apps writing to the database (or switch to read only mode), let logical replication catch up one final time, stop logical replication, run a procedure to fix your seq…

This has not been solved by logical replication. There are features in PostgreSQL not supported by logical replication. One of the issues I'm dealing with is our product switched from Oracle to PostgreSQL and the ORM stuff love LOBs, and you know LOBs can't be logical replicated.

Re: Why does everyone run ancient Postgres versions?

#349

Post by Tostino »

paulryanrogers wrote: post #231
arkh wrote: post #155

Earlier quoted context omitted.

Having to tinker with pg_hba.conf files on the server so manage how users can connect.

I'd agree that is annoying yet usually just a one off task, unless you really want different IP allowlists per user.

In complex environments it is not just a one off task. I dealt with it by automating my infrastructure with ansible, but without some tooling it sucks.

Re: Why does everyone run ancient Postgres versions?

#350

Post by ellisv »

beeboobaa3 wrote: post #300
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…

This has been solved by logical replication a few versions ago. 1. Leave your old database running 2. Start your new database with new version 3. Run logical replication and let it catch up 4. Decide you want to switch over. 5. Using a script: Stop your apps writing to the database (or switch to read only mode), let logical replication catch up one final time, stop logical replication, run a procedure to fix your seq…

> Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade.

I think this is the right answer for 99% of companies. Yes, everyone wants zero downtime, but I see incredibly few cases where it is actually worth the cost and effort.

Post reply on HN