Another point I'd like to make. I've run into (and seen others run into) show stopper bugs with both MySQL and PostgreSQL. I would say that I am happier with the responses and response times from PostgreSQL than from MySQL.
You can read the PostgreSQL mailing list archives for example of example of the lively debate and fast response from the development team. some example threads:
PostgreSQL not ACID compliant? Wherein Heikki of InnoDB fame steps into the discussion. It's a great thread, by the way, as it describes how very differently both innodb and postgresql handle their MVCC models. In fact, you can learn a lot about InnoDB from the Postgresql archives by searching for Heikki's posts
A creepy story about dates. How to prevent it? dicsusses the old way postgresql silently munged dates to fit into a date field if you swapped month / day.
That is good; The problems I've had basically revolved around the fact that some older versions of MySQL handle running out of disc space very ungracefully (in some cases messing up indexes).
My experience has been that if I have a lot of parallel updaters, eventually myisam tables show up broken. I haven't used MySQL / MyIsam in a heavy update environment in a long time since I've learned to use PostgreSQL or innodb for heavy updates.
I'm glad about that. I was obviously confusing it with Firebird, which is still extremely experimental in 64bit.
Sadly, whlie Firebird has some great code under the hood, they really just don't have as big of a development community as either PostgreSQL or MySQL does. I think this slows them down a lot in terms of this kind of thing.
I don't think that using a different table handler makes MySQL a different DB - we still have the same syntax etc. True, they have different performance characteristics.
Well, having the same syntax is actually kind of a bug. If you declare a table like this (myisam):
create table test (a int references master_table(id), col1 text, col2 int) engine=myisam;
MySQL right ignores the references. but, if you do this:
create table test (a int references master_table(id), col1 text, col2 int) engine=innodb;
guess what? Yep, MySQL ignores the syntax, and you get no foreign key. No warning, no error. just silently dropped FKs.
However, what I really meant by "a different database" was what you said, that they have different performance characteristics. People often make the mistake of assuming that queries that ran fast for isam tables should be just as fast for innodb. Often, they're not as fast, but maybe you can run 20 times as many, and the speed stays about the same, while myisam would slow down. So, my real point was that you need to think of it in the same terms as you would of switching to PostgreSQL or Firebird from the point of view of query profiling and performance tuning. Hence, you're pretty much using another database.
I too am hoping Falcon proves a good table handler. But a fast, reliable transaction table handler takes time to mature, so it will likely be a good year or two before it's "done" in the sense of ready for production deployment.
I disagree. You could achieve the same with stored procedures in a more visible and maintainable way.
Triggers are bad because they "do things behind your back" i.e. an application developer can easily not notice that they're there, and they change the inherent behaviour of the DB (e.g. inserting a row might do something else to another table too).
Not necessarily. For instance, in pgsql, FK relations are checked with Triggers. You could use triggers to maintain materialized views. You could use a trigger to email you when a table gets over a certain size. You could use a trigger / rule in pgsql to insert a date automatically when a row is updated, and to make it so the user cannot overwrite that value since they don't have the ability to override the trigger. There are some jobs a trigger is a great match for. I'm not sure how a stored procedure could do the date things I listed there, and you'd have to remember to call it for the email, but that was a supervisory function. Same thing with the mat view. Why would I want to even have to know to maintain the materialized view?
Initiate 1000 long running transactions, pull the plug on the server (or crash your kernel, or whatever) and the system comes up stable and all the committed transactions are there and complete, and no pieces of the failed transactions remain.
If that makes you happier; I believe that InnoDB should generally offer the same level of durability.
Of course that makes me happy. Let me tell you a story. Last company i worked at had about a hundred different db servers, running Ingress, PostgreSQL, Oracle, DB2, VSAM on a DGUX machine, MySQL, Foxpro and probably more I can't remember.
I built the intranet server on php / postgresql. It was basically a catch all quick development system that had a lot of useful things like the company directory, and other simple db based apps that were either auto-updated by cron jobs or allowed users to edit the data with a simple form system. It was under constant use by about 1500 people. To test it, I did exactly what I listed up there, running 1,000s of writes to the db and pulling the plug from the back. I learned that with Linux back then, IDE drives lied about fsync and got corrupted. A good RAID controller with batter back up and U160SCSI drives didn't and that's what we went with.
I bragged to many people around the company that our machine was bullet proof, and I'd tested it to be sure. Telling the Oracle DBAs about pulling the plug got me looks of horror. Most people just laughed, thinking that since we had three power conditioners, two very very large UPSes and a Diesel generator with near instant cut in that came on the second the UPSes came to life, that I'd never need that.
About 3 months later, we had an electrician drop a small piece of wire in one of the power conditioners. It fed back, blew the other two conditioners, and both UPSes. It also arc-welded the switch for the diesel generator in the off position, so we couldn't get it up either. It took about 2 hours to restore power, and we were running 200' electrical cables from dirty power wall sockets to get the hosting center up and running.
There was one database in the whole company that came up with no corruption and no lost data. And it was mine. So yes, that makes me very happy.
I don't need IPv6, but I'm struggling to see why I would.
Because you're in a hosting center that's switching over?
MySQL tends to be a "whatever you say boss" database.
This is true if you are using < 5.0 or you have the relaxed SQL modes on.
1: MySQL 5.0 has SQL modes relaxed on by default
2: Users can turn off ansi sql mode at will
3: Some things still don't pay attention to ansi sql mode yet.
4: See my point about the foreign keys above.
Silent failure was the standard for so long that there are still lots of silent failure modes that bug me in MySQL.
The difficulty is, if you've got an existing application built against MySQL in relaxed mode, it's not simply a matter of turning on strict SQL mode - this could result in the application breaking in a million tiny undetectable (ahead of time) ways.
so how come I can't turn it on for one db in a MySQL system? That would be really useful. I can turn it on in a session, but I can't make it sticky. Things like that can all be made sticky with "alter database ..." in postgresql, and I've gotten real used to it.
MySQL has a lot of very convenient syntax which lets you do things that you often need to do with less code. Examples of this are REPLACE INTO and CREATE TABLE IF NOT EXISTS.
True. Keep in mind a lot of that kind of stuff was done originally because MySQL didn't support multi-statement transactions back then.
So stuff like DROP VIEW IF EXISTS ... is just supremely handy and few if any others have it.
I don't see the use of drop view if exists so much as "create or replace" PostgreSQL supports create or replace view, function and rule. The other creates don't have or replace syntax just yet.
Another much more useful feature, in my opinion, is CREATE INDEX CONCURRENTLY which lets you create indexes without locking readers OR writers from your databas while the index is being created. That's a feature that's VERY handy on large, heavily accessed databases in a production environment.
