Blog of OtterTune, a database tuning startup from Carnegie Mellon research, with Andy Pavlo's yearly database reviews and a post on PostgreSQL MVCC.
HN commenters link it to explain who Andy Pavlo is and what OtterTune does, an ML-based tuning service for MySQL and Postgres.
These are just a few random ones off the top of my head!
* Handling several thousand connections per second without needing a proxy or pool (MySQL's connection model is thread/conn vs Postgres using process/conn)
* Handle extremely high volume of primary key range scan queries (MySQL's InnoDB uses a clustered index, see https://news.ycombinator.com/item?id=35909053)
* Handle workloads that lean heavily on UPDATE or DELETE, without major MVCC pain (see https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most for example)
* Semi-synchronous logical replication, for environments which cannot tolerate any data loss during failover; this ensures the statements have reached at least one replica but without the huge latency of synchronous replication
* Use index hints, to ensure random index stats changes don't cause unexpectedly negative query plan adjustments (see discussion in subthread https://news.ycombinator.com/item?id=35909340)
* Handle very high-volume OLTP workloads using direct I/O, since InnoDB's buffer pool design is completely independent of filesystem/OS caching
* Achieve best-in-industry compression by using the MyRocks storage engine (MySQL's pluggable storage engine design has a lot of tradeoffs but it is inherently what makes this even possible)
* Use UNSIGNED int types, to store twice as many values in the same number of bytes, if you know negative numbers are not going to be present
* Use case-insensitive or accent-insensitive collations…
Most of the answers here are choosing Postgres over MySQL, But there are cases where MySQL is far better than Postgres.
1. MySQL's Replication is simple, more reliable and takes up less disk space than Postgres.
2. Ability to control the optimization of queries with Optimizer hints. At scale, to lower tail latencies you will definitely need to help the query planner since you have some domain knowledge about your app and queries that the planner doesn't have.
Yes, In PG you can use pg_hint_plan, but still it is not an official solution.
3. MySQL has better connection scaling without a separate component like pgBouncer.
Also, cost of connection creation is lower due to MySQL's thread per connection model vs Postgres' process per connection model
4. Collations suck in Postgres since it decided to depend on the OS for collation support.
Version updates of other packages (glibc) or the OS can corrupt your database due to this collation mess.
The fix for this is to use ICU collations but even they have multiple limitations (You can't specify a non-deterministic collation at the database level etc)
5. Postgres's MVCC implementation is the most inefficient among modern databases [1]
Not only is it inefficient, it causes maintenance headaches with managing and tuning auto_vaccuum.
It also causes increased disk usage, write amplification (entire row is rewritten on each update and index is updated even if the column being updated is not indexed) and increased bloat due to…
I am going to be controversial for a hot second, and say that in many ways MySQL is a more advanced and better implemented database at its core. Disclaimer: as a developer I love and prefer Postgres. But I've been on many projects where MySQL won for ops-related reasons.
Postgres has a MVCC implementation that is recognized as inferior[0] to what MySQL and Oracle do, and requires dealing with vacuuming and all of its related problems.
Postgres has a process-based connection model that is recognized as less optimal than the thread-based one that MySQL has. There are ongoing efforts[1] to move Postgres to a thread-based model but it's recognized as a large and uncertain undertaking.
Other commenters have also explained the still very noticeable difference in replication support, the lack of query planner hints, the less intuitive local tooling.
One thing to keep in mind is that both databases keep evolving, and old prejudices won't take us far. Postgres is improving its performance and replication support with each release. MySQL 8.0 added atomic DDL and a new query planner (MariaDB did their own query planner rework in 11.0, widening their differences). Both are improving their observability. So the race is far from over. But I definitely wouldn't count MySQL out.
[0] https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most
[1] https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi
OtterTune | Frontend/FullStack Engineers (all experience levels) & more | Remote | https://ottertune.com
OtterTune is a database optimization-as-a-service company. We use ML to automatically improve the performance of MySQL/PostgreSQL databases running on Amazon. We are a remote-first company. We are backed by Accel and the co-founders of Snowflake, DataBricks, SingleStore, CockroachDB, and Duolingo.
* Senior Front-End Engineer: https://jobs.lever.co/ottertune/ad9948ce-a542-4189-bd49-0bf3bb1e6690
* Senior Full-Stack Engineer: https://jobs.lever.co/ottertune/762d0365-f00b-43de-a3a1-37f7257ba11f
* Front-End Engineer: https://jobs.lever.co/ottertune/7ce5d9b0-c54f-41e1-9367-b7b35bc8c318
* Full-Stack Engineer: https://jobs.lever.co/ottertune/512a101c-9a4c-4f18-b6cd-21751bd68a4c
* Product Manager: https://jobs.lever.co/ottertune/a7fcb8f8-4bd1-45a4-a29b-07236886a034
We are also hiring for other roles: https://jobs.lever.co/ottertune
Email me if you have questions: nappelson@ottertune.com
Actually, it was a combination of three things:
1. OtterTune Start-up (https://ottertune.com)
2. Biological Daughter (https://twitter.com/andy_pavlo/status/1187841279260004355)
3. Pandemic
When the pandemic first started, I had a bunch of CMU students reach out to me saying that their summer internships were rescinded and that they were looking for a project to work on so that they wouldn't have a gap in their CV. I ended up taking on any student that could program C++ even if they hadn't taken my DB class before. It as my way of trying to help. But our research group grew to about 35 people. That was not sustainable and the code quality suffered greatly.
We ended killing the project and now all our self-driving work is done in the context of Postgres (https://db.cs.cmu.edu/papers/2023/p27-lim.pdf).
I also now realize that building the DBMS engine first then building the query optimizer second is the wrong order. Our future project is going to start with the optimizer first.
Expanding on the the above,
> The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most]
Postgres deprecated support for time-travel in ~1997 and the more general notion of "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/
Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consistency