Apples and Oranges, Part III: A Linux DBMS Comparison

Evaluating a Wide Range of Factors

We're going to change direction in midstream. In this part, I'll cover non-performance-related topics. In the next and final part, I'll cover with benchmarking and then close with the final conclusion.

General Differences
Unlike PostgreSQL, MySQL and mSQL are not really relational database management systems. I've seen people on newsgroups call MySQL "just a fast storage tool," and mSQL has even been called a toy--not very flattering. At least mSQL implements part of what a full SQL DBMS should provide.

If one needs a real RDBMS, the only viable choice of the three here is PostgreSQL. If raw performance counts, one of the smaller systems may well be better, especially if the accesses made to the database are uncomplicated and mostly automatic. mSQL and MySQL are advertised as Web database systems for a reason.

Licensing
PostgreSQL is distributed with a BSD-style licence, free in all relevant respects (maybe too free for some fanatics): one can basically do anything with the software, provided the copyright notice is preserved.

MySQL is free and modification of the source code under certain conditions is allowed, but redistribution for commercial purposes is forbidden.

mSQL is free for use by noncommercial organizations; otherwise purchase of a licence after a 14-day evaluation period is necessary.

Because of these differences, people in the enterprise using one of them need to consider licensing conditions carefully.

Implementation of the ANSI Standard
These three systems are all a far cry from completely implementing the ANSI SQL standard, which, to be fair, looks a bit monstrous to me. While MySQL implements a subset that the developers have well-defined for themselves, mSQL does not even try to be very ANSI-compliant. PostgreSQL eventually aims at full ANSI compliance, but it still has a long way to go.

PostgreSQL does not yet fully support referential integrity, but it is the only DBMS tested that does transactions. Other new SQL features like the SQLSTATE variable aren't implemented either.

MySQL does neither transactions nor does it preserve referential integrity; tables can be explicitly locked and unlocked for transactional access.

mSQL lacks most of the features of ANSI SQL. It implements only a minimalistic API, with no transactions and no referential integrity.

APIs
All of the three tested APIs are mostly transparent to handle. Any problems that occur are usually due to inadequate documentation, not the APIs themselves.

Neither mSQL nor MySQL feature Embedded SQL (ESQL) preprocessors. I now like ESQL quite a bit, as it turns out, but it's not really hard to use the native C APIs that mSQL and MySQL provide. There is the same cursor metaphor, just implemented in a different way, and passing strings to C functions is only slightly harder than using embedding SQL statements in the code.

In addition to the ESQL API mentioned, PostgreSQL comes with a native C API, C++ bindings, JDBC, ODBC, Perl bindings, Python bindings, and Tcl bindings.

MySQL comes with additional ODBC support for Win32 platforms; language bindings are available for at least C++, Eiffel, Java, Perl, Python, PHP and Tcl.

mSQL is tightly integrated with Lite, a C-like scripting language shipping with the distribution. A Web integration package called W3-mSQL is available, as are JDBC, ODBC, Perl and PHP APIs.

Note that I haven't tested any of those additional bindings and features; their quality and the state of their documentation are not necessarily good. Many third-party extensions to all three systems can be obtained; this overview is not exhaustive.