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

71–80 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#71

Post by sgarland »

fhdsgbbcaA wrote: post #27
tpmoney wrote: post #24

Earlier quoted context omitted.

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

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.

Re: Why does everyone run ancient Postgres versions?

#72

Post by sgarland »

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…

MySQL does have ON UPDATE for its DATETIME, though; something that Postgres inexplicably still lacks.

Re: Why does everyone run ancient Postgres versions?

#73

Post by erik_seaberg »

bastawhiz wrote: post #54
erik_seaberg wrote: post #18

If PostgreSQL has replication, why are they talking about "minimal" downtime? Is there no quorum strategy that delivers high availability? I don't know as much as I should.

Writes happen on your primary. At some point, you need to stop accepting writes, wait for the replica to fully catch up, reverse the replication so the replica is the new primary, then direct writes to the new primary. That's hard to do without any downtime. There's no option where the nodes all accept writes.

https://www.postgresql.org/docs/current/warm-standby.html#SY... mentions quorum-based synchronous replication, which sounds encouraging.

Re: Why does everyone run ancient Postgres versions?

#74

Post by sgarland »

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.

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

Re: Why does everyone run ancient Postgres versions?

#76

Post by fzeindl »

api wrote: post #9
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…

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.

* 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 and standardized approach to development: if a feature is included it generally works well in interaction with everything else

* syntax, semantics and performance are all very predictable

* great documentation

Regarding MySQL / MariaDB: MySQL optimized for performance first. Until 2010 the standard-storage-engine MyISAM didn't even support transactions.

PostgreSQL always focused on correctness and stability and then made sure everything performed.

Re: Why does everyone run ancient Postgres versions?

#77

Post by sgarland »

bastawhiz wrote: post #56
evanelias wrote: post #50

Earlier quoted context omitted.

Postgres limits btree keys to 2704 bytes, which is actually slightly smaller than MySQL's limit of 3072 bytes, assuming the default InnoDB storage engine. That said, when using utf8mb4 in an index key, MySQL uses the "worst case" of each character being 4 bytes. So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. For practical purposes, this doesn't…

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

You should not be indexing 768 characters in any circumstance I can imagine. Go ahead and try it. Spin up two tables, fill them with a few million rows, and slap and index on them. Give one a reasonable prefix limit, and let the other go wild. Make sure you ANALYZE each, then run queries in a loop and check the times.

Spoiler: I literally did this a couple of days ago. The index size bloat means that any possible savings you might have gained from collisions are obliterated from page fetches. I tested with a measly 128 characters vs. a prefix of 16, and that was enough for the average query time to be equal, with the smaller index winning for the minimum.

Re: Why does everyone run ancient Postgres versions?

#78

Post by bastawhiz »

erik_seaberg wrote: post #73
bastawhiz wrote: post #54

Earlier quoted context omitted.

Writes happen on your primary. At some point, you need to stop accepting writes, wait for the replica to fully catch up, reverse the replication so the replica is the new primary, then direct writes to the new primary. That's hard to do without any downtime. There's no option where the nodes all accept writes.

https://www.postgresql.org/docs/current/warm-standby.html#SY... mentions quorum-based synchronous replication, which sounds encouraging.

That still only has one primary: you can't just start pointing writes at the upgraded standbys. Synchronous replication (besides having its own downsides) just shortens the time needed for the replicas to catch up. You still need to perform a cutover.

Re: Why does everyone run ancient Postgres versions?

#79

Post by magicalhippo »

jart wrote: post #29
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.

Have postgres updates actually been requiring users do migrations? Or is this just a fear that something will go wrong?

From what I can gather, yes[1]:

A dump/reload of the database or use of the pg_upgrade application is required for major upgrades.

Now, seems one can mostly use pg_upgrade, which only rewrites the system tables[2] so is fairly quick. But if on-disk format has changed it seems you're forced to dump and reload.

At work we've mainly been using SQLAnywhere, which would just disable new functionality for databases using old on-disk format. So upgrading major versions has usually been fairly painless and quick.

[1]: https://www.postgresql.org/support/versioning/

[2]: https://www.postgresql.org/docs/current/pgupgrade.html

Re: Why does everyone run ancient Postgres versions?

#80

Post by aorloff »

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…

A shudder went through my body hearing that, I had forgotten all about Slony.
Post reply on HN