Hacker News Reader

Comment by chrisacky | Hacker News Reader

I've been 99% MySQL user, project reasons.. everytime I see a release announcement I'm amazed and have feature envy. This isn't a troll comment, but what does mysql do better than postgres? Is it just syntax-favour. I actually Googleéd and ask colleague 10-15 days ago if postgres had json support for indexing root attributes...

View on HN

MySQL can handle many more connections per instance than postgres. Often when you have a high-transaction database in prod with postgres, you need something like pgbouncer to handle connection pooling or you'll have a bad time.

I agree though that postgres has some fantastic features.

edit: I think they may have addressed some of this in a recent version? I'm basing my knowledge on postgres 12

Connection scaling in postgres works better with the more recent versions. Still, you might indeed want to implement a connection pool for your PG instance if you have large swathes of mostly idle connections due to the single-backend-per-connection constraint of postgresql.

mysql notably has index-oriented tables / persistently clustered tables. For some workloads, that is a blessing (it saves you from needing another copy of your data in unique join tables for the unique constraint), but for others its a curse (the PK columns are duplicated in all indexes, so multi-column PK + multiple indexes is quite inefficient).

Other than that, MySQL (since 8.0) has no stable feature releases. As in, after 8.0.0 new features were added in 8.0.x-releases, making it difficult to reason about what you can and cannot do in a given database cluster based on the major version number alone. This too is a blessing and a curse: you get new features faster, but you don't get bugfixes for a database engine with stable and well-defined featureset.

>but what does mysql do better than postgres

Not MySQL itself but something like Vitess [1]. I believe MySQL 9.0 should be close.

[1] https://vitess.io

MySQL doesn't need the auto-vacuumer to be tuned, as it doesn't have one. I've had Postgres servers crash due to excessive stale rows...

PostgreSQL is vulnerable to certain workloads. For example, if you keep appending something to a text column, you'll use up a lot of disc space since PG will copy the row each time and keep deleted copies around until autovacuum comes along.

Of course, if you are doing this then you are using your database wrong -- but it's still something that's easy to run into.

PostgreSQL 14 made improvements to this area around indexing:

https://www.postgresql.org/docs/14/btree-implementation.html...

Yes, but that doesn't cover the problem of bloat in the TOAST tables due to excessive churn of large attributes that was mentioned in the parent post.

I always thought it was cool that MySQL has different table engines. Postgres doesn't have this (unless I missed something). Sure, there are products based on Postgres that have this, but vanilla Postgres itself doesn't have this option.

Postgres added pluggable storage a couple years ago, but it takes time to develop new storage engines: https://news.ycombinator.com/item?id=19578562

Reply on news.ycombinator.com