This is a question I get asked a lot. Actually, I doubt anyone has asked me. But that has never stopped me from giving my opinion.
Let’s just get my “pedigree” clear up front. I had 3 database courses (all Oracle). I’d used MySQL before I came to my current job, where I have been using MySQL daily for the 9 months or so I’ve been here.
I know Oracle is big and powerful, but in my experience it was clunky. I haven’t used it much but at school and on slow computers. I know it is capable of way more than I’ve used it for.
MySQL is what I am the most familiar with by far. I’ve got to say it’s a great little product. It’s ease of use is light years ahead of the other two (what tables are in your DB? It’s just SHOW TABLES. In Postgres the command is “\d”). That said, it’s got it’s…. oddities. Feed it a bad date and it will probably save it as ‘0000-00-00′. It will issue a warning (which is easy to ignore). But when you go to read that record using JDBC… it will complain that it can’t load the date ‘0000-00-00′. Yeah. It has weird habits choosing indexes so that it can often choose the wrong one. It likes to lock tables when you add columns and indexes which on large tables is killer. Also, the JDBC connector (or possibly the database, I’m not sure which) likes to try to load a full result set into memory. You can guess how well that works if your result set is 6GB, Java is given 1GB max, and your machine has 2GB total (plus swap).
So I’ve been looking into Postgres. The tools aren’t nearly as nice as the MySQL tools (especially pgAdmin III vs. MySQL Query Browser). I’m starting to get used to it (and the idea of having to run VACUUM is a little weird). That said, it acts much more proper, more like Oracle, complaining on bad data and not letting you do weird little things.
Of course, the point to all this is to see how it performs compared to MySQL for the main application I work with. So I’ve been spending time trying to copy the DB (complete with terrible records, bad data, and all sorts of other fun from before things were cleaned up) into Postgres so I can start running queries against it and see how it performs.
