The Moodle Database – using PostgreSQL part 2

19th May 201917th May 2019 ElearningWorld Admin

Thinking of choosing PostgreSQL for your Moodle Database?

There are several reasons to go with Postgres..

On the performance side, Postgres requires a bit more up-front configuration than MySQL. A well tuned Postgres is pretty close on SELECT performance to MySQL with small databases. With large tables MySQL has some performance problems, and Postgres performs much better.

Write performance can also an issue with MySQL – with a lot of traffic, it has problems with concurrent writes. Under heavy load, Postgres performs much better.

But the real reason for choosing Postgres is reliability. Postgres is rock-solid reliable and has a focus on ACID-correctness: when it returns from a commit, the data is safely on disk and won’t be lost – barring actual disk problems (which can offset using RAID).

Sometimes MySQL databases with a lot of usage have index corruption issues. If you look at the startup scripts for MySQL on most Linux distributions, they check for data corruption on every startup – this is to address the fact that it is a frequent occurrence.

And while this is passable with small installations where the data isn’t mission critical, you have to consider how much you can trust such approach. And with large datasets running checkers can take hours.

And you also have to consider the performance boost of using async writes: if you tell a standalone Postgres or MySQL to use async writes, it’ll scale much better (should be able to handle up to 3-4 times more simultaneous writes). Once you do that, the performance advantage of a MySQL cluster mostly vanishes. It still has semi-hot takeover in case the master goes down, but Postgres can do that using Slony, and with better guarantees of consistency of the data in the slave.

In a nutshell, MySQL isn’t normally very solid when it comes to ensuring data is safely stored on-the-disk, even if it theoretically guarantees that it’s been saved.

If a process has a problem storing the data, the right thing is to tell that back to the user. With async writes, you end up with a queue of data that hasn’t been stored yet, but you already told the user it was.

That’s not what a database is supposed to do.

When it gets to the big sites, PostgreSQL seems the best option, and that’s why the UK Open University uses PostgreSQL.

Reproduced and annotated from:

https://docs.moodle.org/36/en/Arguments_in_favour_of_PostgreSQL