In contrast to my recent rant about database comparisons, I was pretty impressed with the Oracle 10g vs PostgreSQL 8 vs MySQL 5 article. The two articles don’t compare the same issues, but I’d like to point out how well this one done.

Right from the start the author states his bias upfront (he is a long time Oracle admin), along with what was being compared. In this case the focus was on installing on a lower end Windows 2000 system. All of the factors were spelled out and the scoring method was described in sufficient detail. Each factor had some discussion and and a score for each database.

Overall I’d consider this a good example of a database comparison, it has a well defined (and narrow) focus with an upfront scoring system. My only complaint with this review was in the “Documentation and Getting Started Support” section. Although PostgreSQL and MySQL basically have the same issues in the discussion area, the scores are different. Given how well the rest of the review was done I don’t think this issue completely ruins the review, just provides a low point.

I hope that the author, Lewis R Cunningham, continues to do additional reviews of these three databases using this same format.

Once again there is another attempt at comparing MySQL vs. PostgreSQL. Like so many others before it, this benchmark falls prey to a classic mistake, not enough details. So lets go through their review shall we?

Comparison
Operating System: All of the OSs listed for MySQL also apply to PostgreSQL. No explanation is given as to why they only listed two for PostgreSQL. There are even FAQ entries for some OSs, like AIX, HPUX, IRIX and Solaris.

Performance: I’ll go into more detail on this when later, suffice it say that this is a gross over simplification.

Other APIs: I’m not sure why they describe MySQL with “Most of languages” and mention specific languages for PostgreSQL. I suspect that virtually any language that has MySQL support also has PostgreSQL support. I can’t say this for sure of course because I have surveyed every single language that supports one or both of these databases, but you get the idea.

For the rest of the items they do a reasonable job, although they do leave out some additional features that might be of interest to some like domains, inheritance, sequences, etc.

Summary
There are some features that are extremely handy even for fairly small databases (like views). Look, MySQL does a pretty good job (aside from some issues) at what it is intended to do, stop trying to make excuses for though when you discover that it doesn’t fair well feature wise.

Benchmarks
There is no indication into how much tuning was done for either MySQL or PostgreSQL, if any was done at all.

Data Set & Results: There is no discussion about how many simultaneous connections/users there are. From the look of things all of these tests were done with a single connection/user. If that is the case then all of the inserts done in this should be thrown out and redone using MySQL’s LOAD DATA INFILE command and PostgreSQL’s COPY command. Doing bulk imports like this is always going to perform poorly using straight inserts. Not to mention that if you are only interested with single connection/user situations then you may well include things like MS Access for your benchmarks.

Another missing component is what type of table is being used in MySQL. Unless you specify what table type you want MySQL will use the default type, which is MyISAM. If the MySQL side of these tests were run using MyISAM tables then this whole test needs to be thrown out (inserts, queries and deletes) and redone using the InnoDB table type.

Until these sorts of issues are at least addressed all of the results should simply be ignored, there is simply not enough information to gain anything remotely useful from those numbers at this point.

Conclusion
Again, if you are interested in making bulk imports happen very quickly, at least use the right tool for the job.

Hardware & Software
Kudos for mentioning hardware and software details.

My Conclusion
Going good and meaningful benchmarks is hard work, taking a vastly over simplified approach like the one done here is not really helpful to anyone. There were no goals outlined as to what sort of usage they wanted to test against (although it looks like there were interested in single user heavy insert models) and insufficient discussion and details about how they were going to mimic that model as accurately as possible. Of the discussion that was provided, most it revolved around how to minimize the impact of rapid inserts wrapped in transactions for PostgreSQL. That discussion is waste because they weren’t using the right tool for the right job.

I was pointed to the review by a blog entry at SitePoint, which now has several comments. There is something of thread revolving around an issue with MySQL where if you try insert a 300 character long string into a field that only supports 250 characters, MySQL will simply truncate your data without throwing an error. This has been brought up before and it is simply wrong, the MySQL folks need to just fix this and move on instead of trying to find different ways to justify trashing your data when it is inserted. A counter point is brought up that good programmers always validate their data before attempting to do an insert, in this case making sure that your string is less than or equal to 250 characters. The sad thing about this stance is that there is some truth to it, but not in the sense that it is being used. It’s true that you should be checking for obvious problems in your data before you insert it so that you can give meaningful errors back to the user, however, that doesn’t change the fact that what MySQL is doing is corrupting data on insert.

This disagreement reminds me of the folks who simply add client side javascript error checking for form input, which allowed them to provide meaningful warnings and errors without having to process the form every time. The security folks were quick to point out that the same checks still had to be done on the server side because client side javascript checks were easy to by pass. Client side checks are a great thing for user, but they are no excuse to avoid those same checks on the server side. Checking the length of your strings is a good idea to provide good user feedback, but it is not excuse to allow your database to corrupt data.

Use the Microsoft test here, if MS SQL Server did this sort of thing, would you still be saying the same thing?

When you come across things like this it is hard to resist beating them up over it one more time. Andrew Oliver has an entry about dealing with BLOBs in MySQL. He points out that if your BLOB is too long “… then rather than like error or warn it just silently truncates the data”. I’d really prefer that my database throw an error rather than alter my data. Andrew went from Oracle to PostgreSQL to MySQL only to discover this, um, interesting feature.

It should be noted that this happens when the data is inserted, which is when it should be throwing an error instead of truncating data. This limitation is clearly spelled out in the BLOB documentation, fifth paragraph:

If you assign a value to a BLOB or TEXT column that exceeds the column type’s maximum length, the value is truncated to fit.

Because BLOBs can be very large it seems that you would be unlikely to run into this with LONGBLOBs. Unfortunately that doesn’t make my gut feel any better about how wrong it is to simply truncate data that is too big when giving an error would really be the right thing to do.

Daniel Lemire came across some funny math in MySQL. He ran ‘select 11/5;’ and had MySQL give him 2.20, where PostgreSQL gave him 2. There is nothing wrong with MySQL’s answer, but it might throw off most programmers who be more likely to expect the kind of result that PostgreSQL gives. The second query he ran was ‘select round(0.5);’ and MySQL returns 0 and PostgreSQL returns 1. As far as I can tell MySQL is just completely wrong here. Those were about all the details Daniel gave, so I thought I’d try this out for myself.

I was able to duplicate all of the results on MySQL 4.0.20 and PostgreSQL 7.4.3 systems. I thought I would take this one step further and try ‘select 11%5;’ on PostgreSQL, it gave me the correct answer of 1. This is nicely documented under Mathematical Functions and Operators in the PostgreSQL docs. Interestingly enough MySQL also returns 1 when running ‘select 11%5;’. MySQL does support integer division, but only by using the DIV() function. As for rounding, the MySQL docs for round() claim that:

… the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead.

This explanation seems very odd to me since both the PostgreSQL and MySQL tests I ran were on the same system. Perhaps PostgreSQL implements round() independently of the c libraries in order to achieve more consistent (and correct) results? If so MySQL should do the same and not simply throw its arms up in the air if the result of round() comes out incorrectly. If Microsoft had done something like this in Excel the screaming Slashdot hoards would be all over them.

Note to MySQL developers: just fix this and move on.

I’ve yet to actually use full-text indexing on a project, but I’m continually drawn to the idea. I found mysql a bit surprised by the fact that it isn’t being used nearly as much as (I thought) it should be. The two obvious places that I thought full-text indexing would surely just take off are blogs and wikis. More than anything these two systems are designed to hold lots and lots of words. If the rise of Google has taught us anything it is that being able to easily, accurately and quickly search through huge amounts of words is vital to how useful those words are. What’s the point of having wikis and blogs store their data forever (more or less) if their own built in search features can’t find what you’re looking for? Some of you will immediately raise your hand to tell me that simply letting Google index your wiki and blog is much better than trying to have a good built in search. That may or may not be true, but that only works for publicly available resources. What about all of those wikis and blogs that are for internal use only? So once again we are back to relying on the built in search features of these tools.

So if I were to write a wiki or blog system it seems like using full-text indexing would be the obvious choice when it comes searching. So I started looking around to see if something like WordPress or MediaWiki. WordPress doesn’t appear to make any use of full-text indexing, but MediaWiki does. I did some quick looking around and couldn’t find any other wiki or blog system that made use this. I thought this was very strange since most of these systems use MySQL as their back end database. MySQL has had support for full-text indexing for years and looks to be pretty easy to use, so why isn’t it seeing more use? I think the answer can be found in the MySQL Full-Text docs, full-text indexing only works for MyISAM table types. For some reason I had never come across this limitation before, I just assumed that it also worked with InnoDB tables. It is on their ToDo list, but there doesn’t appear to be an expected timeline for it.

So anyone who wants to make use of full-text indexing has to use MyISAM on those tables under MySQL. This seems like a huge limitation. One of the reasons MySQL has advanced so much (in my view) is because of the features that InnoDB has brought to the table.

This brings me PostgreSQL and full-text indexing, made possible by the tsearch2 extension. The immediate difference between MySQL and PostgreSQL that jumps out is that MySQL has it built in and PostgreSQL has as a third party module. The install instructions look pretty easy, but I haven’t tried them yet so I can’t say for sure. Both of these approaches have advantages, but the advantage is targeted towards different groups. For PostgreSQL, having a third party develop this extension makes things easier for the PostgreSQL developers and harder for the users of software that uses PostgreSQL for the backend. Now not only do I have to make sure that my web host supports PostgreSQL (that’s another story in itself), but I have to make sure that they’ve installed tsearch2. For MySQL there is likely more work for the developers of MySQL because now they have one more thing that they have to support and test for each release. The benefit of that work is that full-text indexing is available to users of any install of MySQL. This is a grossly over simplified look at things, but I believe it fits the mind set of those who want to run software X on some random web host.

Unlike MySQL, PostgreSQL doesn’t have different table types. This means all of the features and abilities are available to you wether you want use full-text indexing or not. This factor would almost completely dry up if full-text indexing was supported in InnoDB. There are also some implementation details that are different between the two. MySQL uses (at least for syntax) something that looks like what you would use to create a regular index. In tsearch2 you end up adding a field to you table and then creating an index on that field.

So which is better? I don’t know, I hope to try out of both of them in the future, just to see how they work. Based on what I’ve read I don’t think either of these solutions provide a simple and powerful full-text indexing that can be used widely. MySQL’s support for it excludes their most powerful table type, InnoDB and PostgreSQL requires the install of an additional module in order to work.

Perhaps full-text indexing is a sufficiently complex task that there will never be a true poor mans solution.

Update (3:16 pm 12 Aug 2004): I guess it really is all about timing. There’s an intro article on PostgreSQL full-text indexing by Joshua Drake over at DevX dated 10 Aug 2004.