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

91–100 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#91

Post by fhdsgbbcaA »

sgarland wrote: post #71
fhdsgbbcaA wrote: post #27

Earlier quoted context omitted.

UTF-8 is what made me switch. It’s insane MySQL has something called UTF-8 that isn't really UTF-8, but do have a type UTF8MB4 that actually is correct. This means if you use UFT-8 in MySQL, you can’t use emoji for example.

> This means if you use UFT-8 in MySQL, you can’t use emoji for example. I for one have always viewed this as a perk.

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.

Re: Why does everyone run ancient Postgres versions?

#92

Post by SavageBeast »

wbl wrote: post #82
sgarland wrote: post #74

Earlier quoted context omitted.

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.

I recall this being the case A LOOOONG time ago but I haven't heard of, read about, been warned to look out for or personally seen such a thing in forever. Have you?

* I'm running a lot of MySQL stuff and such a topic might be of interest to me

Re: Why does everyone run ancient Postgres versions?

#93

Post by aitchnyu »

polishdude20 wrote: post #45
yen223 wrote: post #25

Databases tend to be "stickier" than other parts of any large software system. Largely because database migrations are costly. You can't just tear down an old database and rebuild a new one, you have to figure out how to move all that data across too. The consequence is that things in database-land tends to move slower than other types of software. This I think is the major reason why we still use SQL.

What's the SQL alternative?

Hopefully Edgedb. It could make most ORMs and heavyweight web frameworks redundant, maybe some BI tools and jobs too.

https://www.edgedb.com/

Re: Why does everyone run ancient Postgres versions?

#94

Post by webprofusion »

Lol, try upgrading old MongoDB stuff.

Database engines (every single one) are notorious for incompatibilities between major versions, upgrading mission critical stuff means updating and re-testing entire applications, which in some cases can be a multi-million dollar process, before going into production.

Even if you deeply know/think that there's no problem upgrading, if something does fail in production after an upgrade and it's mission critical..

Re: Why does everyone run ancient Postgres versions?

#95

Post by andrenotgiant »

redslazer wrote: post #75

Funnily enough neon does not offer an easy way to upgrade database compared to other managed database hosting.

(Neon employee) We auto-upgrade minor versions as long as they can be done autonomously. For major versions, you're right it's still manual but we're working on improving that. Here is our version policy: https://neon.tech/docs/postgresql/postgres-version-policy

Re: Why does everyone run ancient Postgres versions?

#96

Post by lelanthran »

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

My experience of both is that MySQL is easier for developers, PostgreSQL is easier for sysads.

That was true in 2012; dunno if it still applies though.

Re: Why does everyone run ancient Postgres versions?

#97

Post by crazygringo »

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…

To be honest, indexes aren't designed for that. They're meant for fast lookup of short identifiers. Things like people's names and product ID's. Not long URL's. It's not performant.

If you need to keep a million long URL's in a defined sort order, my first recommendation would be, don't -- see if there's another way to achieve your end result. But if you absolutely have to, then create a new integer column to be your sort key, and use a little bit of extra code to give it values that produce the same sort order.

Creating short numerical primary keys for long strings is a common database technique.

Re: Why does everyone run ancient Postgres versions?

#98

Post by mnahkies »

yobert wrote: post #36

I have a large production deployment that is still on 9.6 because the software depends on table inheritance. (Oh man!)

Unless I'm mistaken table inheritance is still a thing in current PG versions, in terms of partitioning at least it's just less commonly used in favour of declarative partitioning since it's easier to manage.

It's been a long time since I worked with v9.x in anger, so I could well be forgetting things though

https://www.postgresql.org/docs/current/ddl-partitioning.htm...

Re: Why does everyone run ancient Postgres versions?

#99

Post by occz »

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

Re: Why does everyone run ancient Postgres versions?

#100

Post by o11c »

SavageBeast wrote: post #92
wbl wrote: post #82

Earlier quoted context omitted.

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.

I recall this being the case A LOOOONG time ago but I haven't heard of, read about, been warned to look out for or personally seen such a thing in forever. Have you? * I'm running a lot of MySQL stuff and such a topic might be of interest to me

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.

Post reply on HN