Logical Replication is the big one. MySQL has had logical (and "mixed") replication for a decade (or more?). PostgreSQL has gotten built-in logical replication only recently, and it's still very annoying. It doesn't replicate schema change statements, so those have to be applied independently by some external system, and of course it's not going to be timed exactly right so replication will get stuck. And somehow for very lightly active applications generating just a few MB of writes per day, the stuck replication log can back up 10s of GBs per day. And does logical replication across versions for zero-downtime upgrades work in Postres yet? I dunno.
Connections you mentioned already, Postgres needs an external connection pooler/proxy, MySQL not nearly so soon. Vacuuming is a problem I never had with MySQL/MariaDB of course.
I'm an infrastructure dev, not an DBA or an advanced SQL user, I never used window functions, or wrote a query that filled half the screen, or have a thick web of foreign keys ... If you keep things damn simple so you have confidence in efficiency and performance, then MySQL works pretty damn well in my experience, and Postgres, for all its academic correctness and advanced query feature set, has been pretty damn annoying.
And, you can tell MySQL to use a particular index for a query!
I'm usually gonna reach for Wordpress first along with MySQL if I need to set up a basic blog for non-technical people to contribute to. Maybe the DB isn't as cool as it could be, but it works fine, and I hardly ever have need to touch the database itself directly.
When the client count and the business complexity explode, things start to get hairy fast, but by that time the investment in MySQL is hard to walk away from. You will start to hit all sort of edge cases in other DBs - which MySQL did not exhibit simply because your application grew around its feature set and limitations.
I'd like to point out that this seems to happen whatever db you move from and to.
Sometime over a decade ago I was part of a team moving from an ancient version of Sybase Adaptive Server Anywhere to the newest Microsoft SQL server running on a beefier server.
We ported everything carefully and tested it out. We also felt good since both where dialects of T-SQL.
...and then we ran right into so large performance problems that we had fall back to the old system.
The reason was that at least older models of had Sybase ASA had implicit indexes.
Feels like there is always something.
Then again, depending on your scenario, introducing a bunch of extra complexity into your app tier code may be less annoying than introducing a bunch of extra complexity (connection poolers etc.) into your infrastructure.
So it ends up that there's a bunch of workloads where mysql is obviously the better option, and a bunch of workloads where postgresql is obviously the better option, and also a bunch where they're mostly differently annoying and the correct choice depends on a combination of your team composition and where your workload is likely to go in the next N years.
Just as an example, there are so many people who assume they need a 3rd party search engine simply because what’s built into their database is so bad compared to the options you get with PG.
You’re welcome to only use your database as a crud data store while you manage a mountain of other tools to work around all of the limitations…but you don’t have to.
At that point you're effectively using MySQL as a key/value store with an SQL interface, rather than a full RDBMS.
Which is, in fact, pretty much exactly what MySQL was originally designed to be and it's extremely good at it.
> If you keep things damn simple so you have confidence in efficiency and performance
I can have that level of confidence on a minimally tuned (albeit sensibly indexed, you still have to make sure the indices you need -exist- on anything, realistically) postgres at levels of schema/query complexity where getting mysql to handle it -at all- would be ... an experience.
Having used both extensively, I'd say that there's a lot of knowledge when using mysql that's basically "introducing complexity into your application and query pattern to work around the optimiser being terrible at doing the sensible thing" and then there's lots of knowledge when administrating postgresql that's "having to pick and choose lots of details about your topology and configuration because you get a giant bag of parts rather than something that's built to handle the common cases out of the box".
So ... on the one hand, I'm sufficiently spoiled by being able to use multi column keys, complex joins, subqueries, group by etc. and rarely have to even think about it because the query will perform just fine that I vastly prefer postgres by default as an apps developer these days.
On the other hand, I do very much remember the learning curve in terms of getting a deployment I was happy with and if I hadn't had a project that flat out required those things to be performant I might never have gotten around to trying it.
So, in summary: They've chosen very different trade-offs, and which set of downsides is more annoying is very dependent on the situation at and.
This completely maps to my experience as well. I still believe Postgres' defaults being set to use an absolute minimum footprint, especially prior to PostgreSQL 9, significantly impacted it's adoption. It's better now, although IMO it could still do with an easier configuration where you could flag the install as dev/shared or prod/standalone to provide more sensible settings for the most common situations. Like, gosh, I'd like to be able to stand up an instance and easily tell it to use 90% of the system memory instead of 100 MB or whatever it was.
But the MySQL issues were much worse. Outer join and subquery problems, the lack of functions like ROW_NUMBER(), a a default storage engine that lacked transactions and fsync(), foreign keys that don't do anything, etc. I've met so many people who think MySQL limitations are RDBMS limitations, or don't understand database fundamentals because MySQL just didn't implement them properly. Then again, I also remember when the best reason to use MySQL was the existence of GROUP_CONCAT(), which everything else lacked for religious reasons.
I also vividly remember prior to MySQL 5.5 when new users of PostgreSQL, Oracle, or MS SQL Server would discover that, instead of making a guess for what you wanted, the RDBMS would actually return error messages and expect you to understand and solve them deterministically. And somehow they would be angry about it! Old MySQL (mostly 3.3, but 4.0 and 4.1, too) used to silently truncate data, or allow things like February 31 in a date field, or silently allow non-deterministic GROUP BY, or only reporting warnings when you explicitly asked for them really undermined the perception of MySQL to database folks. This wasn't that long ago, either.
I think Postgres is really cool and has a lot of good reliability, but I've always wondered what people do for high availability. And yeah, pgbouncer being a thing bugs,
It feels like we're getting closer and closer to "vanilla pg works perfectly for the common worklfows" though, so tough to complain too much.
See if you can understand enough of it and consider doing it again for next upgrade. (I have done that for 12 > 13)
I trust the pg people that tools do what are advertised, but it's a very high risk proposition
I forgot to post the article link!
Thanks for posting it.
You do need to use an external connection pooler (pgbouncer etc), though Postgres 14 has made some large improvements in gracefully handling large numbers of connections, though you can still run into problems.
As for the query planner/optimizer, so far in all of the optimizations and improvements I have worked on, I've only run into 1 or 2 that had a query plan that made my head scratch. There are some extreme edge cases that will prompt the cost function to prioritize the wrong index, but in production I have found that 99%+ of slow queries can be easily improved by a rather simple composite index. One thing I do love about postgres is using partial indexes, which can significantly reduce the amount of space required and also make it extremely easy to create indexes to match predicates of a function, while indexing other columns.
Other than one or two slow queries I've tracked down and worked on, I've never wished that I could "hint" to use a particular index over another.
Now some of the things that people like for Postgres over Mysql, in practice aren't that great at the moment. People like doing atomic DDL operations, when in reality locking the table schema can cause lots of problems, and in production you only add indexes etc concurrently.
You still get the issue of dead tuples in mysql and need to periodically clean them up using OPTIMZE TABLE et al, though postgres and innodb have different designs, but ultimately it needs to happen sometime. Its just that postgres IMO requires more tuning to get the right balance.
... then SQLite might be the right choice for you?
In all seriousness, so many use cases are met by SQLite on the low end, PostgreSQL on the high end, and dedicated KV stores like Elastic off to the side, that what is left for MySQL? When you have a write-heavy workload on a multi-TB dataset that needs to be sharded, with a schema that is "simple" but not so simple that it works with an actual KV store?
I didn't know this was a thing or even common until this year and it opened my eyes to some major issues when the PG optimizer doesn't get a query right you have no escape hatch.
That being said, and me being weak in MySQL, it wasn't obvious to me if this is very common and how often you _have_ to tell MySQL what index to use because the optimizer misses it...
For me the biggest issue with that is that the optimizer decision can change basically at any time. If the stats start preferring one index over the other at 2am, you can't just say "no, trust me, use this one instead" - you have to find out exactly which value crossed some threshold and tweak the stats to restore the previous behaviour.
> it wasn't obvious to me if this is very common and how often you _have_ to tell MySQL what index to use because the optimizer misses it...
I'm not sure how representative my sample is, but in the last 20 years, I've seen a hint used two times and one of them was an app bug (the forced index was not better, the order of columns in it should've been reversed and the hint removed). But I haven't worked with super-complicated reporting queries.
I’ve been using PostgreSQL built-in replication since 9.1 (when it was introduced) and this is definitely not my experience....
In fact it wouldn’t be possible to apply out of replication changes because (unlike MySQL) Postgres replication forces immutability (the replica nodes cannot become r/w).
The "issue" could potentially be that it's async? But I use postgresql a _lot_ and I've never experienced this.
The original mode of replication merely ships the WAL, which describes binary changes to data files. Every change comes in the form "set page 32435 in <file> to <bytes>", with no distinction between the operations that the changes represent.
Logical replication describes the changes logically. A change may be "insert <column values> into <table>" or "add <column name> of <type> to <table>". Since this format isn't tied to the underlying database file format, it offers greater compatibility between PostgreSQL versions. It also allows other applications to process the log and do things like listen for data changes and ingest them into other databases, queues, etc.
I seriously trialled and compared Postgres vs MySQL at the start of a major major project, and MySQL had a few clear wins for us (mainly replication) while the features Postgres had weren't in our current roadmap's requirements (the biggest regret that caused me was not having stored procedures). That was in 1998/1999. I now seem to be stuck on a career path where everything is MySQL and switching to Postgres anywhere seems to have huge back pressure from everyone I work with - even though at least half of them know damned well (like I do) that MySQL hasn't been the right choice for a couple of decades almost.
:shrug:
And I feel seen on the devs with no database skills thing. Every single database problem I can recall having to step in and help fix in the last ~5 years has been Java devs leaving everything up the the ORM to deal with, resulting in unindexed full table scans and similar brain damage. I "fixed" a production problem with a query that'd crept up to over 30 seconds without anybody noticing, by adding a single index to a single table. (And then sent the devs to work out how to give Hibernate the hints it needed to create the schema properly and set up more monitoring so once-weekly reporting SQL queries taking 10+ seconds wouldn't be hidden in 95 and 99 percentile alerting...)
The one thing that I've learned recently which surprised me- if you want to cluster MySQL (via partitioned tables or PlanetScale, etc) you cannot use foreign keys.
Some of the Postgres compatible tooling (Citus, Cockroach, etc) appears to support foreign keys across partitions / shards.
I was quite surprised to see this. Makes sense that not allowing FKs makes the data easier to shard but I never imagined that would be a thing you would have to contend with.
For reference, we're at about 250 million rows, so not even proper "scale" scale!
> Yes Postgres ... has worse connection handling that usually requires a pooler
This is the issue. The vast majority of PHP (Wordpress runs ~30% of the internet or something) and Python (Django) devs are not going to write a database connection pooler. I happen to like PHP and love Python, but the low bar to entry on those two (PHP especially) means that the vast majority of devs would be completely lost without the default tooling provided by e.g. php-mysqlng or other prepackaged database connection modules in their default settings with the most trivial of use cases.A slow database can be ignored for a while. The need to add additional code in which the database is no longer a black box can not be ignored.
It sure is one more step that for MySQL, but we're talking production level DB, and in my experience it's a small task compared to the rest of what you will be doing to have your architecture properly running.
Just look at the changelog for the bugs fixed (and yes, there are unfixed bugs as well): https://www.pgbouncer.org/
And the FAQs for the unusual behaviours to be aware of and find workarounds for: https://www.pgbouncer.org/faq.html
I would love nothing more than to be convinced. I find MySQL to be adequate, but far from great. Postgres is described as great by everyone who uses it, however I prefer adequate but reliable over great but leads to downtime.
I'm with on how much it would help if it had a magic mode without these quirks, but in my experience you're still ahead of MySQL and its per session configuration quirks for instance. On a day to day basis, you'll rarely be scratching your head about how to deal with the pgbouncer pooling behavior you chose, an you'll still have the option to directly hit the DB on anything you don't trust the pooler to deal with appropriately.
For the bugs, those are mostly on the security and configuration side. For the few years I've used it, I never hit a behavioral bug TBH.
Is it though? I read this post earlier this year and it mirrors some similar tests I did back in 2020 when I was just checking basic perf between PostgreSQL / SQL Server / MySQL on my laptop.
https://itnext.io/benchmark-databases-in-docker-mysql-postgr...
> A simple conclusion: Postgres engine family is about twice as fast as MySQL engine family, except MariaDB.
Also:
> Postgres has worse connection handling
Is this addressed in PG14? Or is it still to come in PG15? I haven't kept up with the latest Postgres stuff but I thought they were fixing the connection stuff.
pg14 improves the performance for large numbers (thousands) of connections. IIRC there is some work to add a primitive connection pooler as an option, but I can't find anything references on that ATM.
So the situation is improving, but the fundamental connection=process model is so baked into Postgres that it's unlikely to ever change.
People like to criticize MySQL for some bad engineering decisions made decades ago. For whatever reason, often these folks have a more positive view of MariaDB, but without stopping to consider where all those original MySQL engineers went. FWIW one of the footnotes in the original article touches on this too.
This is by design, postgresql doesn't come with a connection pooler. For each connection a separate process is started. If you have a lot of mostly idle connections then you should use use pooler in your application, server side (e.g. pgbouncer) or both (there are trade offs).
Optimistic mode for parallel in-order replication
https://mariadb.com/kb/en/parallel-replication/#optimistic-m...
I was at the Perl conference in maybe 1999 or 2000 where there was some guy giving a MySQL talk. It wasn't Monty, but he was in the audience. I asked a question during question time about enabling ssl for encrypted replication, and the guy giving the talk said "Sorry, I don't know if/when that'll happen". Monty stood up and said "I've just done that. I'll check it in tonight, come grab me out in the corridor if you'd like me to mail you the patch."
I then spent the next 7-8 years running replicated MySQL databases in SF/Dallas/London/Singapore/Sydney. It worked really well for us.
Fear of a db not coming back up in a restart and backups being corrupt as well.
We pull our backups into new instances daily and I think that would catch it.
Yeah, I am one of those people. Needed a database in 2005, and MySQL was the de facto choice. Got used to it and never ran into problems that couldn’t be solved by getting better at schema design and indexing.
I never felt limited by MySQL and I am very comfortable with it, so never felt the need to try anything else. I might be missing something, but there is an opportunity cost in switching without a real motivating reason.
It's not really that hard to change if you started with OFF, it just requires a logical dump and reload (typically on a replica, followed by some re-cloning and promotion). Companies with sizable data tend to have database engineers who can handle this fairly easily.
In any case, it's not an urgent maintenance operation like a VACUUM problem...
I look around now and you a right. It's really no big deal to move a few terabytes of data around but it sure was a mess back then and I'm once bitten, twice shy.
I was fortunate that the startup was willing to devote sufficient time and resources to help me get our DB problems under control -- including getting expert consulting services from Percona, attending conferences, etc. However I've definitely seen some other startups that didn't take DB tech debt seriously enough, and just kicked the can down the road until a major disaster occurred.
Anyway though, even aside from improvements in hardware and tooling, managing large DBs just gets less stressful once you've spent some time on the problem. Assuming you successfully solved this 6 years ago, why shy away from it in the future? You almost certainly learned a lot in the process, and the good news is it only gets easier from there!
Do you have an up to date source for that?
1. Change Buffering - If your benchmark result shows that MySQL is faster than PostgreSQL, it is likely due to change buffering. For random read/write workload that doesn't fit into the memory, this feature is fantastic. This is available since the very first commit of InnoDB, and of course also has been causing random corruption during crash recovery throughout the years: https://jira.mariadb.org/browse/MDEV-24449 (fixed in MariaDB, still exists in MySQL)
2. Page-level Compression - PostgreSQL still doesn't support this, but I am fine with relying on ZFS filesystem compression when running either MySQL or PostgreSQL. Arguably it should be better for the database engine to do its own compression, but see this blocker level bug that impacted FreeBSD: https://jira.mariadb.org/browse/MDEV-26537 (MariaDB only, but feel free to search bugs.mysql.com for other compression related bugs)
3. Clustered Index - The main topic in the Uber drama. I think InnoDB has the better design, since scaling write is harder for a RDBMS. I also think that InnoDB implementation causes a severe performance cliff, when it comes to optimistic update versus pessimistic update.
There are compromises there. With page level compression the pages usually remain compressed in memory as well as on-disk and get unpacked on access (and repacked on change). This eats CPU time, but saves RAM as well as disk space which for some workloads is a beneficial trade-off as a larger common working set fits into the same amount of active memory (particularly when IO is slow, such as traditional spinning disks, cloud providers when you aren't paying a fortune, or when competing with a lot of IO or network contention on a busy LAN/SAN).
With filesystem compression you don't get the same CPU hit every time the page is read, and depending on your data you may get better compression for a couple of reasons, but you don't get the RAM saving.
When running PostgreSQL, I would probably go with `primarycache=all` for the data dir, a largish ARC cache, and a smallish shared buffers, to take advantage of the filesystem compression.
When running MySQL, I would probably go with `primarycache=metadata` for the data dir, a smallish ARC cache, and a largish buffer pool, to still benefit (slightly) from the filesystem compression.
Of course, that particular example really only matters if you're actually hitting the 4MB limit in your queries, but I think there were some other settings with similar too-low defaults.
So the first thing you do after setting up Postgres on a reasonable hardware is tune the config file. There's a lot of advice on the web, and there's pgtune service that makes it very easy to start in the right direction (https://pgtune.leopard.in.ua). Still, you need to know about it to do it, so there's definitely quite a lot of suboptimal Postgres installations out there.
I've not used either for a long time so my knowledge may be out of date, but mysql definitely used to be the easiest to get started out of the box which was part of the reason it won out in the shared hosting space. Of course the main reason it won out in that area was because it was faster than pg in many artificial benchmarks because at the time it didn't bother with silly things like transactions (as MyISAM was the default table type), and foreign key constraints, and other necessities…
Maybe its all better now?
Wow, right on the chin with that one.
> More jarring were the people who insisted everything was OK (it seems most MySQL users and developers don't really use other databases)
I only have anecdata of my usages of MySQL and Postgres but I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing.
Yes Postgres can be slower out of the box and yes Postgres has worse connection handling that usually requires a pooler but the actual engine and it's performance makes it worth it in my opinion.