Re: postgre vs MySQL

At 09:47 PM 3/11/2008, rrahul wrote:

>Hi,
>
>I am a database professional but have never used Postgre. My client was
>exploring the posiblity of using Postgre instead of Mysql and wnated to know
>the comments from the community.
>I waned you people you post your views on the following comparision points
>1] Performance
>2] Scalablity
>3] community support
>4] Speed
>5] ease of use
>6] robustness
>
>Any major clients of the two.
>You can add you own points too.

The "advantage" of MySQL is it looks good on paper with all the ticks
on the extensive feature list so it's easy to convince bosses to use it.

But the problem with MySQL is you often can't use all the "advertised
features" at the same time, some of them are mutually exclusive.

For example, with MySQL if you want fast single user selects or
insert speeds you use MyISAM tables, but if you start to need
transactions or high concurrency writes you have to use InnoDB which
is significantly slower.

Want to have a consistent backup of your MySQL database? For MyISAM
tables you have to lock all tables till the backup is done, and that
can affect performance a lot. OK so you use InnoDB. But when the time
comes to _restore_a multiGB innodb table, you might find innodb a bit
too slow. Worse, apparently "fun" things happen if someone halts the
restore process halfway ;).

Guess what happens if you have a mix of table types.

A "solution" of course is to have multiple database servers with a
master server replicating to a slave server that's used for backups,
and resort to backing up stuff in on-disk format - shutdown slave and
copy the files. This makes restoring faster. But after all this,
MySQL stops looking so easy right?

In fact such a multi database set up just to do proper backups and
restores resembles a bad implementation of Postgresql's MVCC :).

Basically with Postgresql, there's a lot less of this "If you want to
use Cool Feature A, you can't have Wonderful Feature B" thing.

I did a simple mass insert test (followed by backup with pg_dump or
mysqldump and restore ) and postgresql 8.1 is faster or as fast as
MySQL 5.0.26 with MyISAM tables, and faster than MySQL with innodb
tables- default packages from suse 10.2 with tuning done for MySQL
(increase of buffers etc) but postgresql is as per suse 10.2 defaults.

I use MySQL daily at my workplace, and it's not something I recommend
you use if you had a choice. In the old days (before version 6.x)
postgresql wasn't good, but postgresql is way ahead now.

Lastly, the other problem with MySQL is its Innodb and BDB stuff are
now owned by Oracle. While Oracle is not squeezing MySQL yet, who
knows what will happen a few years later.

Regards,
Link.