On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > Here are some other things we have v. mysql: > *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems It's also useful for lots and lots of other things. For instance, let's say you had an index on a good sized table, and that index took three hours to build. You look at the stats and it's not getting used much. You want to see if you can get rid of it, but don't want to drop it and then have to spend three hours rebuilding it. You have other indexes you want to still be used while testing, so set enable_indexscan off is out of the question. You can do this: begin; drop humongous index; explain analyze complexquerygoeshere; -- run it a few more times to see how caching affects it explain analyze complexquerygoeshere; explain analyze complexquerygoeshere; -- all done, got my numbers... rollback; While it's not safe to do in production 24/7 servers because of locking issues, it's quite a sane to do on test or reporting servers. > *) Better HA: PostgreSQL wal shipping/warm standby is much more robust > than mysql binary log shipping This can't be stressed enough. MySQL's replication is of the "gee, I sure hope it's working" variety, and there are LOTS of horror stories of it just quitting with no notice. > *) MVCC: (I'm sure this is up there, but it's worth > repeating)...smarter locking/concurrency for high write activity > (important) applications. There is more to databases than phpbb > backend. MySLQ has MVCC, if you're willing to give up full text indexing, and use an engine now owned by Oracle, which may or may not be available in the future. (i.e. InnoDB). And there are a few things that innodb has that postgresql doesn't, like predicate locking, which can be really useful (allows you to refer to things like count(id) in the net query and be guaranteed that it's right), and really aweful (to do that you lock EVERY row referenced in the previous count(id)), depending on your perspective. > *) Much better shell I'd disagree. I'd say it's the best damned shell period. :) Having used a lot of command line db interfaces, nothing touches psql. Learning it is easy, it's self documenting, and starting with 8.2 the line buffer goes by query not by line. And I totally agree with everything you said Merlin. Especially about the community.
