Anyone made the jump from MySQL to PostreSQL? It is worth it?

Anyone made the jump from MySQL to PostreSQL? It is worth it?
old.reddit.com

Don't leave MariaDB out of the conversation... that is basically the MySQL team that forked the project after oracle bought them out.

Job #1, we were a heavy PHP/MySQL shop with a smattering of Ruby. I had come from a PG/MySQL shop and knew the merits and pitfalls of both databases (PGs auto vacuum being a particular point of contention at my job before this one), and informed the CIO that going to PG instead of following the upgrade path to MySQL $latest had a number of minor improvements and since our SQL was /mostly/ standard on the frontend we could attempt the change.

I was in charge of testing and automating deployments of the test environments, so I set about massaging the data into PostgreSQL-types. However, we found that our data had been being silently corrupted over a long period of time. Dates were often very incorrect, values of not null columns were filled with the "empty" of whatever it was (0 for int, "" for string etc).

We managed to get the data into postgresql and a lot of our tools stopped working, the commerce site itself was working thanks to the efforts of two really committed developers, but the back office tooling was not ported. Due to the sheer amount of queries we had which were running silent failure modes (which postgresql does not allow to run) we ended up going back to mysql rather than fixing them.

I don't work there anymore.

Job #2, We were a heavy C++/MySQL shop, MySQL just because it's what everyone knows, and because everyone knows it, it becomes a standard that is hard to move away from. I was tasked with getting 50KiB~ binary data blobs to be saved with a high update frequency, strong consistency and ideally alongside its locks.

We tried to coerce mysql to do the right thing for a very long time, we rented this huge iron server which had 42 directly attached SSD's in RAID0 (to test the limits of the software as a control, not for prod-like environments), 60 CPU threads, one of the best raid cards you can buy etc;

What we found was that MySQL couldn't climb above 600qps for this workload, no matter what you did, we even ended up recompiling the kernel with a faster implementation of sockets and ipc, and changing the memory allocator mysql used (which was unsafe), but we couldn't get it to move much faster than that.

So, on profiling we found that it was spinning on an internal lock, something we couldn't engineer around.

600qps was well below our target, so we started looking at alternatives. It should be noted that one of the requirements was that the data be fsync'd before returning OK to the application. I say that because 99% of noSQL databases only ensure that their data is in VFS.

We tried postgresql as our only non-noSQL test, since it's similar enough to mysql's syntax and easy to test. The C++ developers fell in love, instantly with the C++ SDK for pgsql, instantly referring to the mysql equivalent as "braindead", and the database scaled linearly with I/O, even as we added a additional arrays of drives eventually saturating our 40GBit NIC.

Now we run postgresql as our only RDBMs.

I still work there.

If your project is already on MySQL and you have issues => understand the issues you are facing and make sure that moving to Postgres would fix them (99% chance it won't)

If you have a new project and have very precise informations about the constraint you will face (pretty rare) => Do your research and choose what's best for your use case

If you have a new project and have only got a vague idea of what you are going to need/face 5 years down the road => Postgres is a slightly better choice than MySQL due to the feature set. Unless you have some other reason to go for MySQL (expertise, ...)