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

421–430 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#421

Post by jeltz »

paulryanrogers wrote: post #313
jeltz wrote: post #286

Earlier quoted context omitted.

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?

Yes, and it does it by starting an instance of the old version and runs pg_dump against it. And that was one thing the original poster complained about.

Re: Why does everyone run ancient Postgres versions?

#423

Post by water9 »

Too wrote: post #302
forinti wrote: post #284

Earlier quoted context omitted.

That's not going to be easy with any database.

With mongodb in a HA setup you can perform a staggered upgrade of individual nodes in the cluster with no downtime. Very smooth in my experience.

Yeah, but what is your dataset is partitioned horizontally? It’s very smooth until it’s not. For example, if you are using the postGIS extensions and something changes with the new version that could be a showstopper

Re: Why does everyone run ancient Postgres versions?

#424

Post by magicalhippo »

forinti wrote: post #284
literalAardvark wrote: post #268

Earlier quoted context omitted.

> 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.

That's not going to be easy with any database.

We have customers with not quite TB levels, but at least several hundreds GB databases.

Upgrading the database server is as simple as shutting down the service, installing new executables and restarting the service. Downtime is measured in minutes.

When upgrading major versions, one might not get full functionality of the new features unless one upgrades the on-disk format (unload-reload), but you can run the new server version with older on-disk format just fine.

Re: Why does everyone run ancient Postgres versions?

#425

Post by sgarland »

ttfkam wrote: post #399
sgarland wrote: post #291

Earlier quoted context omitted.

Who said this was Postgres? MySQL (with the default InnoDB engine) and MSSQL both are clustering indexes; they store tuples around the PK. For a UUIDv4 PK – or anything else non-k-sortable, for that matter – this results in a massive amount of B+tree bloat from the random inserts. But sure, let’s talk about Postgres. After all, it stores tuples in a heap, and so is immune to this behavior. Except that its MVCC implem…

> For a UUIDv4 PK To be fair, this blows out any db that supports clustered indexes as well. Non-k-sortable primary keys are just a bad idea all around. With UUIDv7, the WAL write amplification problem goes away just as the clustered index issues do.

Agreed. My preferred PK in descending order is: natural key if it makes sense and would speed up queries, integer of an appropriate size, UUIDv7. I only rank it below integers because at best, they’re 16 bytes, and even a BIGINT is only 8 bytes.

Re: Why does everyone run ancient Postgres versions?

#426

Post by sgarland »

ttfkam wrote: post #398
dalyons wrote: post #264

Earlier quoted context omitted.

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

> the old central gate keeping DBA team model I have mixed feelings about this. On the one hand I agree that ownership should be shared. On the other, app developers really don't consider their data structures as carefully in SQL as they do in-memory. It's odd. The right data structure matters more than a good algorithm since algorithms are easier to change. Once you settle on a list vs a set vs a queue, you're stuck…

100% on all points. I’m a fan of gatekeeping things that are hard to get right, and hard to undo. If you can prove that you know what you’re doing, by all means, own your stuff. But until then, yes, I’d like to watch over your shoulder, and hopefully teach you how to do it right.

The fact that DBs are data structures but are ignored has always irritated me. If I pushed a PR using lists for everything regardless of their practicality, I’d be rightly told to try again. But push a PR with a schema using similar suboptimal choices, and no one blinks an eye.

Re: Why does everyone run ancient Postgres versions?

#427

Post by cenamus »

anticensor wrote: post #417
cenamus wrote: post #118

Earlier quoted context omitted.

Is slony some sort of language joke? Slon is czech (probably slavic in general) for elephant. (which may be actually derived from the turkish aslan, for lion, but somehow the animal got mixed up)

Not really, the older form of Turkish aslan is actually arslan .

I just read that some time ago

https://en.wiktionary.org/wiki/Reconstruction:Proto-Slavic/s...

Re: Why does everyone run ancient Postgres versions?

#428

Post by tpmoney »

The_Colonel wrote: post #108
o11c wrote: post #100

Earlier quoted context omitted.

From what I can tell, MySQL is supposed to be safe since 2018 if you have no data from before 2010. The fact that you still can't use DDL in transactions makes life exceedingly painful , but it's technically safe if you write your migration code carefully enough.

Lack of transactional DDL is certainly painful, but not unique for MySQL. Oracle doesn't support it either.

Oracle also didn't support Boolean data types for a long time, and had a 20 some odd year public thread arguing that no one needed a Boolean data type (https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_...). They finally added it in Oracle 23 which is nice, but I wouldn't consider it to be in good company to be lacking something Oracle also lacks.

Re: Why does everyone run ancient Postgres versions?

#429

Post by dalyons »

ttfkam wrote: post #398
dalyons wrote: post #264

Earlier quoted context omitted.

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

> the old central gate keeping DBA team model I have mixed feelings about this. On the one hand I agree that ownership should be shared. On the other, app developers really don't consider their data structures as carefully in SQL as they do in-memory. It's odd. The right data structure matters more than a good algorithm since algorithms are easier to change. Once you settle on a list vs a set vs a queue, you're stuck…

I agree but also I think I could be clearer about the key advantage of the distributed many dbs model… it’s that I don’t have to care if people are good at anything that you said :). If teams want to do dumb things with their schema, fine, that’s on them and they’ll have to deal with the consequences. If it matters, they’ll learn and get better. If they want to buy their way out of it with their latency and infra budget instead, that’s honestly fine too.

With many smaller databases owned separately, the blast radius of bad db decisions is small/local.

Re: Why does everyone run ancient Postgres versions?

#430

Post by whizzter »

RegnisGnaw wrote: post #288
whizzter wrote: post #220

Earlier quoted context omitted.

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…

There is still major issues with logical replication, mainly limited feature support.

What's lacking? Specific types or schema changes?
Post reply on HN