Why does everyone run ancient Postgres versions? • Hacker News Board

Re: Why does everyone run ancient Postgres versions?

#311

Post by ttfkam » Fri, Oct 18, 2024, 2:20 PM UTC

tpmoney wrote: post #24

api wrote: post #9

Earlier quoted context omitted.

I've always wondered why Postgres is so insanely popular. I mean it has some nice things like very powerful support for a very comprehensive subset of SQL functionality, but most apps don't need all that. It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.

> I've always wondered why Postgres is so insanely popular. In no particular order, my preference for postgres is driven by: * Date / time functions that don't suck * UTF-8 is really UTF-8 * 99% of a backup can be done live with nothing more than rsyncing the data directory and the WAL files * Really comprehensive documentation * LTREE and fuzzy string match extensions * Familiarity from using it for years MySQL/Mari…

Transactional DDL!

Re: Why does everyone run ancient Postgres versions?

#312

Post by i_am_jsd » Fri, Oct 18, 2024, 2:21 PM UTC

mebcitto wrote: post #215

If there is anyone from Neon watching this thread, is there a way to suggest updates to the pgversions website? It currently pins Xata to 15.5, which is true for the shared cluster environment depending on the region, but one can start dedicated clusters up to 16.4 at the moment.

[dead]

Re: Why does everyone run ancient Postgres versions?

#313

Post by paulryanrogers » Fri, Oct 18, 2024, 2:21 PM UTC

jeltz wrote: post #286

paulryanrogers wrote: post #261

Earlier quoted context omitted.

What is this holding back? A redo based alternative to MVCC?

Nope, that is totally unrated. To support upgrade in place without an old version of PostgreSQL: 1. The new version of PostgreSQL would need to able to read all old catalog table formats and migrate them. 2. The new version of PostgreSQL would need to support all old versions of the parse tree to migrate views. 3. Likely a bunch of more things that I do not know of. I for example doubt it is trivial to just read an o…

Does pg_upgrade not do all that?

Or do you mean the new Pg server should transparently do the upgrade automatically? And while online?

Re: Why does everyone run ancient Postgres versions?

#314

Post by ttfkam » Fri, Oct 18, 2024, 2:22 PM UTC

sgarland wrote: post #252

fhdsgbbcaA wrote: post #91

Earlier quoted context omitted.

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.

Uhh… not wanting to see emojis is a matter of taste, not engineering.

Re: Why does everyone run ancient Postgres versions?

#315

Post by willsmith72 » Fri, Oct 18, 2024, 2:34 PM UTC

efields wrote: post #309

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…

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

older versions can also mean deprecated packages everyone's too scared to touch, failure to invest in maintenance and tech debt reduction, or use of old technologies which stopped receiving security updates

Re: Why does everyone run ancient Postgres versions?

#316

Post by stackskipton » Fri, Oct 18, 2024, 2:40 PM UTC

efields wrote: post #309

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…

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

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.

Re: Why does everyone run ancient Postgres versions?

#317

Post by dwwoelfel » Fri, Oct 18, 2024, 2:42 PM UTC

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 queue of queries to complete.

3. Wait for the query queue to drain and for replication to catch up.

4. Flip a feature flag that switches the connection from the old db to the new db.

5. Flip the flag to resume queries.

It helped that we were written in OCaml. We had to write our own connection pooling, which meant that we had full control over the query queue. Not sure how you would do it with e.g. Java's Hikari, where the query queue and the connection settings are complected.

We also had no long-running queries, with a default timeout of 30 seconds.

It helped to over-provision servers during the migration, because any requests that came in while the migration was ongoing would have to wait for the migration to complete.

Re: Why does everyone run ancient Postgres versions?

#318

Post by Spivak » Fri, Oct 18, 2024, 2:43 PM UTC

stackskipton wrote: post #316

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.

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.

Re: Why does everyone run ancient Postgres versions?

#319

Post by icedchai » Fri, Oct 18, 2024, 2:44 PM UTC

jes5199 wrote: post #21

stickfigure wrote: post #10

Earlier quoted context omitted.

What's the alternative? MySQL? No transactional DDL, immediate fail.

I worked for a company that migrated from mysql to postgres, but then got big enough they wanted to hire fulltime database experts and ended up migrating back to mysql because it was easier to find talent

Ugh. I worked with MySQL earlier in my career (until about 10 years ago.) All the companies since have been Postgres. All my personal projects are Postgres. I can't imagine going back.

Re: Why does everyone run ancient Postgres versions?

#320

Post by ttfkam » Fri, Oct 18, 2024, 2:46 PM UTC

arkh wrote: post #154

fzeindl wrote: post #76

Earlier quoted context omitted.

* transactional DDL * comprehensive transaction model using different modes * PostGIS and lots of other great extensions * supports most of the current SQL standard and is clear on interpretation of edge-cases in the documentation * support for writing stored procedures in any major programming language * many useful functions regarding dates, sets, ranges, json, xml, ... * custom datatypes * extremely thought-out an…

> * custom datatypes Good in theory. But last time I checked the main libs to connect to pgsql, everything you get back from the database are strings. So you need something in your app to convert those strings to the equivalent data structures.

You're thinking only in terms of application. Types in the db save storage space, allow for better validation than plain strings, can be correlated cleanly with other columns with the same type, etc.

Yes, more drivers and libraries should support the more expansive data type list, but even just within the database itself there are multiple advantages.