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

81–90 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#81

Post by erik_seaberg »

slotrans wrote: post #67
paulryanrogers wrote: post #2

Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes. Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL. At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of dow…

"Not as robust as MySQL"? Surely you're joking.

Early MySQL versions made egregious design choices like quietly ignoring missing foreign keys and enum typos, truncating long strings, and randomly choosing rows from groups.

https://web.archive.org/web/20230922210124/https://grimoire....

Re: Why does everyone run ancient Postgres versions?

#82

Post by wbl »

sgarland wrote: post #74
slotrans wrote: post #67

Earlier quoted context omitted.

"Not as robust as MySQL"? Surely you're joking.

They’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going. To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound,…

Yeah but you don't need to worry about your data existing. MySQL has been known to silently fail the one job of a DB.

Re: Why does everyone run ancient Postgres versions?

#83

Post by stephenr »

georgyo wrote: post #46
threeseed wrote: post #23

Earlier quoted context omitted.

The command line experience is old school style i.e. to show tables. \c database \dt Versus: use database show tables

I started with MySQL in 2006 for my personal projects, but what first won me over to psql was those commands. Today I use CLIs like usql to interact with MySQL and SQLite so I can continue to use those commands. At first glance they may be less obvious, but they are significantly more discoverable. \? Just shows you all of them. In MySQL it always feels like I need to Google it.

> At first glance they may be less obvious, but they are significantly more discoverable. \? Just shows you all of them. In MySQL it always feels like I need to Google it.

In MySQL either `?` or `help` or `\?` will show you the help...

Re: Why does everyone run ancient Postgres versions?

#84

Post by fweimer »

evanelias wrote: post #59
bastawhiz wrote: post #56

Earlier quoted context omitted.

> So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. This is exactly what I mean. 768 characters for an index is woefully bad. And for no obviously great reason: you can just index the encoded UTF-8 text. This was literally reason why a former company (who will remain nameless) refused to add Unicode support. It's not even an imagined problem.

Why did you need to index fairly long strings in their entirety in a way that preserves collation behaviors? And why is a 768 character limit woefully bad, but a 2704 character limit is totally fine?

You might just load someone else's data, and the index is desirable in general for speeding up analytic queries. It's possible to work around that, of course. But depending on what you do, it can make writing efficient queries against the data more difficult. That's just a distraction because most of the time, those long columns won't matter anyway.

Re: Why does everyone run ancient Postgres versions?

#85

Post by FearNotDaniel »

Gormo wrote: post #66
natmaka wrote: post #30

Earlier quoted context omitted.

Suggestion: add "End of life (no more maintenance for this version)? Upgrade"

Why? If the implemented featureset meets your needs, and there are no unresolved bugs or security vulnerabilities relevant to your use cases, what further "maintenance" do you need?

Because when the maintainers have stopped patching that version against all known security vulnerabilities, that doesn't stop the bad guys from looking for more vulnerabilities. When they find one, it will get exploited. So you either wake up to an email from Have I Been Pwned to say all your customer data has been exfiltrated [0], or (if you're lucky) you have a mad scramble to do that update before they get you.

[0] Probably including those passwords you didn't hash, and those credit card numbers you shouldn't be storing in the first place because, what the heck, it meets your needs.

Re: Why does everyone run ancient Postgres versions?

#86

Post by evanelias »

bastawhiz wrote: post #70
evanelias wrote: post #59

Earlier quoted context omitted.

Why did you need to index fairly long strings in their entirety in a way that preserves collation behaviors? And why is a 768 character limit woefully bad, but a 2704 character limit is totally fine?

A URL, for instance, can't be safely stored in 768 characters, but it can be stored safely in 2704. If you then wanted to sort those URLs so that all URLs for each domain and path within that domain are adjacent, you need an index. Especially if you want to paginate over them with a cursor. Doing that without an index on the raw value is a royal pain in the ass. Hell, even just being able to sort user-submitted strin…

> A URL, for instance

VARBINARY is typically ok for that I'd think? Then you can utilize the full 3072 byte limit for the key, since there's no character set applied.

> even just being able to sort user-submitted strings up to a kilobyte

As a software engineer, I completely agree. But as a DBA, I am obligated to make a "tsk" sound and scowl disapprovingly!

Re: Why does everyone run ancient Postgres versions?

#87

Post by kalleboo »

fhdsgbbcaA wrote: post #63
eYrKEC2 wrote: post #61

Earlier quoted context omitted.

Not after you have to google, "What's the equivalent of `show tables` in postgres?", because the psql command names are completely arbitrary.

Which you need to do exactly once.

I need to manually admin my database server maybe once every 2 years or so. Definitely not remembering them 2 years later.

Re: Why does everyone run ancient Postgres versions?

#88

Post by rationably »

hairyplanter wrote: post #51
TacticalCoder wrote: post #14

> Postgres 17.0 has been out for a bit and ... No. It's been released in September 2024. That's not "quite a bit". Now as to why people aren't all on 17 and not even on 16 yet, here's an acronym for you: LTS [1] Debian 11 Bullseye is the current LTS. It came out in 2021. [1] https://en.wikipedia.org/wiki/Long-term_support

Debian doesn't have LTS and non-LTS. Debian has Stable. That's it.

https://wiki.debian.org/LTS

Re: Why does everyone run ancient Postgres versions?

#89

Post by cvalka »

stickfigure wrote: post #10
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.

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

TiDB

Re: Why does everyone run ancient Postgres versions?

#90

Post by FearNotDaniel »

codetrotter wrote: post #20
nathanaldensr wrote: post #7

Weird that the maybe-AI-generated image of a column says "Postgres 13" on it when the article talks about Postgres 17.

Seems perfectly reasonable to me. The article is about people not upgrading from older versions. One could imagine that PostgreSQL 13.0 is the “pillar” of some company, that their whole system relies upon. The article then goes into detail on what they are missing out on by not upgrading PostgreSQL to a more recent major version, and why it might be that so many stay on ancient versions, and also how you can actually…

Yeah, I mean it's not hugely imaginative, but it kind of makes sense, if you want to describe pg 13 as "ancient" that you use some kind of Greek/Roman temple as a visual analogy for that. Not particularly well executed - a serif font might have helped to drive the "joke" home - but nobody's trying to win an art contest here.
Post reply on HN