OtterTune Blog

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.

For reference, that "some guy" is Andy Pavlo, a professor of databases at Carnegie Mellon. He has a lot of neat writeups online, like this annual review of new database systems [0]. He puts up lectures on the internet for free too. Having been to Dr. Pavlo's office - or at least, one of them - I can attest to there being multiple cardboard boxes filled with gifted shirts from different companies. One of my favorite designs was actually from CMU's DB group itself - it has a little "this database kills fascists" tag on it, with a skull motif :D [0]: https://ottertune.com/blog/2022-databases-retrospective

OtterTune | REMOTE | Full-time | https://ottertune.com OtterTune is a database optimization-as-a-service company based on research out of Carnegie Mellon University. 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 VC's at IntelCap, RaceCap, Accel and the co-founders of Snowflake, DataBricks, SingleStore, CockroachDB, and Duolingo. * Senior Full-Stack Engineer: https://jobs.lever.co/ottertune/762d0365-f00b-43de-a3a1-37f7257ba11f Legit compensation + equity. 401k w/ matching. Unlimited PTO + sickdays + paid FMLA. Email me if you have questions: [email protected]

This is Andy Pavlo, so he probably got sidetracked with https://ottertune.com/ Not sure what op's intention with this was

It's not baked-in, but Andy Pavlo has an ML-based tuning tool called "Ottertune" for Postgres: I think it's free for a single DB https://ottertune.com/

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

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…

>We are also hiring for other roles: https://ottertune.com/careers/ None are listed :)

Something interesting that wasn't mentioned in this article, but was mentioned in the other Ottertune blog article [0] is that significant performance improvements can be achieved with the help of open-source tools like MySQLTuner [1]. [0] https://ottertune.com/blog/benchmark-using-machine-learning-to-optimiz… [1] https://github.com/major/MySQLTuner-perl

The link was incorrect. https://ottertune.com/blog/benchmark-using-machine-learning-to-optimiz…

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

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…

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: [email protected]