Support for Sqlite and PostgreSQL in MailerQ
We have added support for PostgreSQL and Sqlite to MailerQ, as alternatives for MySQL that was already supported. And by doing so, we learned something about the differences between those different engines.
But let me explain why MailerQ needs a database connection in the first place. As you probably know, we have created MailerQ with high delivery performance in mind. And because of that we only use technologies for their performance capabilities: RabbitMQ because it is very good at message queueing, and Couchbase because it is a very fast and reliable key value store. And although traditional databases have many advantages and can be very fast too, they do not offer the best thinkable solution when it comes down to speed. So why do we use it then?
A traditional database is a very good tool for storing data in a well structured manner, and to look up specific data based on certain fields. And because traditional databases are already in use for such a long time, there is support for it in almost every thinkable programming language. And that is exactly why we use a database in MailerQ: to lookup the configuration, and to keep track of statistics so that other programs can easily access that data too. For tasks that require high performance (loading and storing messages) we rely on other technologies, but for statistics and configuration settings we use a database to make this information easily accessible.
But even although we only use the database for non-critical tasks, we still did our best to keep the performance penalty for using a database as small as possible. To prevent that database queries form a bottleneck, MailerQ runs all queries in a non-blocking asynchronous way. This means that when a query is being executed, MailerQ still continues its other duties - it does for example not stop any SMTP communication while a query is running. And besides that, all query results are cached in memory, so that queries do not need to be executed too often. In most cases the required data is already loaded in memory and no query needs to be started. Update operations and insert operations are grouped so that a single query can update many counters at once. This reduces the number of necessary queries even further.
That is why we have chosen to create special "create table" queries for all different engines. But that did not come as a surprise, because the data definition languages for all database engines are known to have many differences. The regular "select", "insert" and "update" queries on the other hand are supposed to be standardized SQL and should thus be supported by all engines. Those types of queries have been rewritten in such a way that the same query can be used for accessing all different engines. For almost all queries that we already used we had to do some rewriting because we were using specific MySQL features.
But we have managed to make these changes. It will now probably also be easier to add other database engines too, as we have simplified all our queries so much that only the most common features from SQL are used.