Which Moodle Database Should You Use?

The open source, eLearning LMS Moodle has recently been getting a lot of attention due to the COVID-19 Pandemic. The strain on the resources as educators move their students to eLearning platforms has caused many institutions to have to scramble to keep the system highly available and able to handle an ever increasing amount of concurrent users.  

What is Moodle?

What’s unique about Moodle is that it is an open source software for online learning management system, or LMS (also known as Virtual Learning Environment or VLE) under a GPL licence. It’s backend is built in PHP as the scripting language. The great thing with Moodle, it can run in Linux/Unix environment as well as a Windows environment. 

Moodle enables educators to create their own private website filled with dynamic courses that extend learning, any time, anywhere. Moodle has an all support with easy access and features for teachers, students, or administrators. 

Since it’s an open source and a free software platform, you can use this software free of charge just like other open source software. The costs only incurs when this software is hosted on a paid hosting platform, or if you are hosting it with their Moodle Cloud. Moodle is also available for handheld devices.

Moodle Supported Databases

SInce it’s an open source software, it is easy to gain traction within the open source community. In fact, it does enjoy the blessing of being open source software and users in their community forums reveal that users are always active in supporting and keeping the software always moving forward and up-to-date. While it could take some time for the world to return to normal as vaccines are rolling out over some countries, it will still be worthwhile for some universities and school organizations to take a hybrid type of educational environment. In that sense, databases supported by Moodle might or will improve as the following sections below are currently supported.

Microsoft SQL Server

Microsoft SQL Server (MSSQL) is one of the databases natively supported by Moodle. There’s some confusion from their documentation upon using MSSQL as it states that it has been stabilized using the minimum version MSSQL 2005 (v.9) but it also states that it has been stabilized in version SQL Server 2008. Regardless of the minimum version, better if you can use or try Moodle with at least the latest version or at least later version than the minimum version required.

When using MSSQL, there can be challenges such as using PHP 7 as there’s no native support for MSSQL driver for PHP 7. While that can be a challenge, Microsoft offers Microsoft Drivers for PHP for MSSQL. There has been a considerable discussion about dropping MSSQL with Moodle since PHP 7, but It looks like it is not anymore.

Furtheron, if you want to know how to set up Moodle with MSSQL, you can look into their documentation on Using the Microsoft SQL Server Driver for PHP or Installing MSSQL for PHP.

Oracle Databases

As their documentation has stated, Moodle will technically work on Oracle and it is supported by their database framework and it passes their basic unit testing. Some very large institutions use it successfully. It is reported that it’s not recommended as it is generally slow and hardly any core Moodle developers use it for development. Its Moodle support for Oracle relies 100% on volunteer fixes. In that case, you’ll have to wait otherwise to spend money for developers to enhance or work around to make fixes if you push Oracle database for your Moodle.

On the other hand, an Oracle database could be too expensive and a run for money and an overkill if you are starting to build your platform from scratch. Otherwise, if your company and organization has acquired an Oracle database license, then it can be a consideration to try it. Moodle provided documentation for installing Oracle database for PHP.

SQLite

SQLite is a popular choice as embedded database software for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others. SQLite is not a client->server database engine. Its most usage is mainly for local/client storage. In that case, Moodle has experimental support for it. It is not recommended for production Moodle sites.

Moodle’s SQLite driver requires SQLite PDO driver to be present in PHP. It won’t work with a native (non-PDO) SQL driver in PHP. According to Moodle, it contains bugs and it just doesn’t work. Following documents some attempts to use it and what needs to be investigated.

MySQL

MySQL is supported by Moodle. Either MySQL/MariaDB/Percona Server, these databases shall work as expected regardless of its MySQL supported as Percona Server is a drop-in replacement for MySQL. Alternatively, when speaking of MariaDB, it’s entirely different since 10.2 but the native approach by Moodle shall work as is for MariaDB than with MySQL. There can be differences such as variables supported that are no longer supported or are considered as deprecated depending on the MySQL/MariaDB version you are using. This is applicable to Percona Server as well.

MySQL/MariaDB is mostly one of the most recommended and popular databases to deploy when installing and using Moodle. MySQL is an open source Relational Database Management System which is a client-server database, an ACID compliant, fast, reliable, and easy to use database. It is deployed by large and big organizations and companies for managing millions of users and is capable of serving thousands or multiple concurrent users for a single database server.

In addition to what Moodle has for MySQL databases, a lot of developers especially plugin developers or providers are focusing on support for open source databases and more particularly on MySQL/MariaDB databases. In that case, it’s advantageous especially when using the available plugins you might want to utilize for your Moodle platform.

PostgreSQL

Another open source database and is not controlled by any corporation that Moodle supports. PostgreSQL is one of the two databases that is fully supported by Moodle, aside from MySQL. PostgreSQL is considered as the most advanced open-source ORDBMS software that is also comparable with MSSQL and Oracle when it comes to features and compliance to ANSI SQL standards.

PostgreSQL is also easy to install but it can be a diverse database unlike MySQL/MariaDB. In that sense, deploying a PostgreSQL as your database choice for Moodle requires skills and expertise of the said technology.

Which Moodle Database Should You Choose?

Only two of the supported databases are the most recommended choice when deploying your Moodle setup and environment. These are MySQL (and it’s forks) or PostgreSQL.

However, let’s not limit that support and retrospective for which Moodle has been at least tested and installed.

Choosing Oracle for Moodle

Oracle is not a recommended database choice, but they are supporting it but have not yet tried in a production environment. I haven’t read any post nor active and aggressive support by the community when it comes to Oracle. If your company enforces you to choose Oracle and has acquired a license, then why not? The problem is, there can be a number of expected issues which you might end up with your team to fix or develop plugins that are not yet compatible with Oracle. Choosing Oracle with Moodle might end up expensive, not on the license itself but the engineering and development time to be used especially when optimizing or tuning up your Moodle system.

Choosing MSSQL for Moodle

Might end up with the same dilemma as with using Oracle. Though it has a slightly different advantage when it comes to effort and time to set up. Same thoughts apply here when using MSSQL as with Oracle. If your company is tied up and wants you to use MSSQL as the backend database, then just follow their documentation and recommendations upon setting up and installing with MSSQL server. Of course cost is covered here but again as pointed out, only it’s a choice if it’s enforced and your organization or company is ready to cover any anticipated issues to fix or plugins to develop or tweak in order to make it work for your database environment setup.

Best Database Choice for Moodle: MySQL or PostgreSQL

We’ve excluded SQLite here as it’s totally not recommended and I don’t see a setup where Moodle is applicable. It can be for demo purposes or R&D or toying Moodle to make it work. Whilst, we’re talking of using Moodle as the educational platform that is ready to manage tons of users and a number of features or courses you want to push available to your platform.

So why MySQL or PostgreSQL? It’s because it’s free and the community are tailored to work on these database deployments. If you want agile and a fast phase development for your platform, using MySQL or PostgreSQL is a recommendable choice to deploy especially if you are not yet familiar with handling of any of these supported databases. Not only that, these databases are supported by a number of organizations and companies. Solutions that also provide load balancing, high availability, failover, redundancy are available for these database servers. 

MySQL Over PostgreSQL? Or PostgreSQL Over MySQL?

Now this might be the next question you might have to ask. Which of the two is more advantageous and more recommendable to choose from? Let’s provide you a brief guide to choose where to start.

Choosing MySQL

Most recommendable choice is MySQL if you are neither familiar with MySQL nor with PostgreSQL. Shall I say, MySQL on top of MariaDB and even on top of Percona Server. The MariaDB and Percona Server requires at least you have experience and understanding of how MySQL works. How to tune up MySQL or even how to run MySQL with high availability, i.e. setting up replication, using GTID’s, or using Galera. Doing research and asking for community help, MySQL community is big and has a large number of people that are able to help you. While MariaDB and Percona Server can be your alternative choice, it is preferred when you have further understanding how MySQL works. MariaDB is also recommendable or preferred over MySQL by Moodle for which I would guess can be due to performance differences and support that MariaDB offers especially when handling large deployments and enterprise ready (even for the community version).

MySQL/MariaDB/Percona Server deployments can be also favorable as tons of solutions are available for you to choose from. For load balancing, high availability, redundancy, backup solutions, or auto-failover solutions are available. Either open source or enterprise solutions, it comes cheaper compared to proprietary databases.

Alternatively, you can take advantage of fully-managed databases for MySQL/MariaDB which mostly equip with high availability solutions, backup solutions, load balancing, scalability, and a lot more. This as well can be cheaper than managing your own database as you have to deal with your engineers to manage and handle your database along with your server maintenance. It can be advantageous to just pay a third party to manage it for you and just deal with your Moodle platform

Choosing PostgreSQL

If you are aiming for an enterprise-level and expecting to handle very large data in the future, PostgreSQL can be a great choice here. It’s not that MySQL cannot hold this capability, but PostgreSQL makes these easy. It’s native SQL support is far richer than MySQL/MariaDB which manages a large database is very advantageous. It does also support query parallelism which allows you to take full advantage of these multi-core CPUs to improve query performance. Asking for help in the PostgreSQL Community isn’t that hard. However, as you expect that this database is more advanced than MySQL/MariaDB, then there can be some cases you do not understand how its feature or the problem you are dealing with. In that case, choosing this database as your deployment or your de facto standard to run Moodle has to be planned and re-think if you are ready to manage your PostgreSQL as data grows. Anyway, learning comes with patience, time, and dedication before it pays off. 

Although for high availability solutions, load balancing, redundancy, and auto-failover solutions aren’t as many choices than MySQL/MariaDB. But there are a lot of stable and open source software in the market that you can choose from.

Same as with MySQL/MariaDB, alternatively, you can take advantage of fully-managed databases and these third parties also provide or equip with high availability solutions, backup solutions, load balancing, scalability, and a lot more.

Choosing Your Database, Choose The Right Solution

Now that you come up with answers on what database to choose. Choose the right platform or solutions to manage your databases. ClusterControl is one of these solutions that manages both MySQL and PostgreSQL. With ClusterControl, it allows you to deploy MySQL/MariaDB/Percona Server and PostgreSQL from scratch or you can import your existing MySQL/MariaDB/Percona Server and PostgreSQL. 

Once your database server(s) are deployed/imported, these are registered to be monitored by ClusterControl. ClusterControl offers you the option to either choose if you want an asynchronous replication to be setup for your MySQL/MariaDB/Percona Server deployment, or choose a synchronous replication which you can use for MariaDB/Percona Server using Galera. While PostgreSQL, it allows you to deploy replication for a primary and standby environment but also supports asynchronous and synchronous replication setup.

If you want to manage your database internally, then ClusterControl can be your best choice here. It also allows you to manage your database backups which support to run or setup your backup manually and create right away, or schedule it in a different period of time. Scheduling your backup can be done daily, weekly, monthly, yearly, or at any predefined period of time you like. Once backup is ready, ClusterControl allows you also to verify your backup, or restore it on a target node in case catastrophic failure occurs.

When it comes to high availability, redundancy, and load balancing, ClusterControl enables you to deploy it easily and as well monitor these once registered or deployed. Deployment options depend on the type of database. For example, in MySQL/MariaDB/Percona Server, the following are available:

ClusterControl also provides basic to advance recovery operations which helps your database protect from catastrophic and human errors such that server was terminated unintentionally or server was corrupted due to hardware failure. ClusterControl will automatically failover once auto recovery and node recovery are enabled.

There’s more to this once you try and download ClusterControl for free.

Conclusion

Choosing the right database and right solution will ease your development process when deploying Moodle. As what we have discussed here, you shall be able to identify and pinpoint the right database and what you shall choose. Do not spend too much time if it costs more money from the engineering process that can affect your Moodle platform. Spend more on the business side so that you can focus on the feature of what your Moodle platform has to focus and provide.