Apples and Oranges, Part III: A Linux DBMS Comparison

By: Matthias Warkus
Monday, November 29, 1999 12:38:18 AM EST
URL: http://www.linuxplanet.com/linuxplanet/tutorials/1282/1/

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.

Documentation and More

PostgreSQL is documented in DocBook SGML. The manual is subdivided into an administrator's guide, programmer's guide, user's guide and a tutorial. Additionally, FAQs and various README files cover some topics. Several areas of the software lack adequate documentation.

MySQL is documented in GNU Texinfo; the manual looks complete.

mSQL comes with a single-document manual (no hypertext), which is available in PostScript and in HTML form. As can be expected from a commercial software product, it covers all the features.

Authentication and General Security
A topic I have not addressed yet, but which needs to be mentioned in this comparison, is access authentication. ANSI SQL offers a very sophisticated and fine-grained mechanism for access control, namely the GRANT and REVOKE statements.

PostgreSQL and MySQL, while internally handling access control differently, understand these standardized statements. An authentication add-on for mSQL's Web integration package (W3-mSQL) is available, but in its most basic form this database system seems not to have any access control support built in. Unlike safe_mysqld and postmaster, the mSQL database daemon is supposed to be run as root, which may possibly pose a security risk.

Any larger database needs a security concept as much as it needs a thorough database design. It is impossible to say which of the systems that support authentication (i.e. PostgreSQL or MySQL) is more secure; here, everything depends on the design.