Thread: PG -v- MySQL
Hi:
I work for a large corporation that uses many different databases. I used to use Oracle but then moved away, mainly because of the expense and complexity. I wanted to stay on Linux, so I shy’ed away from SQL-Server. Many others around here use MySQL, but I decided to go with Postgres because at the time, it had more features and seemed more stable and robust. I’ve been very pleased with PG so far.
I’ve been invited to participate in a forum which discusses technical issues for relational DBs used in the corporation. The 3 DBs they are discussing are Oracle, SQL-Server and MySQL. I’d like to introduce PG, but want to be able to address the “Why not use MySQL” questions when they arise. I know in the past there have been many comparisons between the 2 DBs but would like to know if there are any good, recent ones. Also, any “off-the-cuff” comments would be welcome, especially if you have used both DBs.
Thanks in Advance
-dave
Gauthier, Dave wrote: > Hi: > I’ve been invited to participate in a forum which discusses technical > issues for relational DBs used in the corporation. The 3 DBs they are > discussing are Oracle, SQL-Server and MySQL. I’d like to introduce PG, > but want to be able to address the “Why not use MySQL” questions when > they arise. I know in the past there have been many comparisons between > the 2 DBs but would like to know if there are any good, recent ones. > Also, any “off-the-cuff” comments would be welcome, especially if you > have used both DBs. I suggest you take a look at: http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project http://sql-info.de/mysql/gotchas.html http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql http://www.commandprompt.com/files/mysql_learn.pdf http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php Sincerely, Joshua D. Drake P.S. In short, we rock.
On Tue, May 13, 2008 at 10:39 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > > I've been invited to participate in a forum which discusses technical > issues for relational DBs used in the corporation. The 3 DBs they are > discussing are Oracle, SQL-Server and MySQL. I'd like to introduce PG, but > want to be able to address the "Why not use MySQL" questions when they > arise. I know in the past there have been many comparisons between the 2 > DBs but would like to know if there are any good, recent ones. Also, any > "off-the-cuff" comments would be welcome, especially if you have used both > DBs. > > > > I suggest you take a look at: > > http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project > http://sql-info.de/mysql/gotchas.html > > http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql > http://www.commandprompt.com/files/mysql_learn.pdf > http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html > http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php Here are some other things we have v. mysql: *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems *) Better HA: PostgreSQL wal shipping/warm standby is much more robust than mysql binary log shipping *) 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. *) Much better shell *) Much smarter query planner (lame rule based planner is easily outsmarted) *) Stored procedure support/SPI interface is years beyond anything they offer...compare pl/pgsql to their lame psm implementation. (procedure support is much more nuanced than "procedure support, checkmark!" *) A stronger, albeit smaller, community. merlin
Le mardi 13 mai 2008, Joshua D. Drake a écrit : > I suggest you take a look at: > > http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project > http://sql-info.de/mysql/gotchas.html > http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-p >ostgresql http://www.commandprompt.com/files/mysql_learn.pdf > http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html > http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php I'm suprised not to see following link in previous list: http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 > P.S. In short, we rock. I like to say that although using MySQL provides problems, using PostgreSQL provides solutions. -- dim
Attachment
At 10:39a -0400 on Tue, 13 May 2008, Dave Gauthier wrote: > I’d like to introduce PG, > but want to be able to address the "Why not use MySQL" questions when > they arise. I know in the past there have been many comparisons between > the 2 DBs but would like to know if there are any good, recent ones. In addition to Josh's links, you may find Greg Smith's (et al) article useful. It is well-written, thorough, and recent (less than a year old). http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 > Also, any "off-the-cuff" comments would be welcome, especially if you > have used both DBs. I use both on a daily basis, and as developer, my opinion is that Postgres is much nicer: * The error messages are much more informative: typo something in your SQL and compare for yourself the error messages b/w the two DBs. * If you work with the command line client, tab-completion is much nicer in psql. (I daresay, it's more complete. :-) ) You can complete almost anything. (It even understands the context of where the cursor is in the statement you're writing.) * I am a member of both MySQL and Postgres mailing lists. In my opinion, the community help from Postgres is (in general) much more ... complete. You will regularly get answers straight from the devs, with a definitive answer on how to fix your problem. And, the community is more generally concerned with helping your larger problem, than just the problem at hand. * #postgres on irc.freenode.net follows the same path as the mailing lists. * The documentation on the website for Postgres is by far the better of the two DBs. MySQL's is decent and very useful, but Postgres' documentation just plain rocks. In fact, the Postgres documentation is one of the very best examples of documentation for any project with which I've had to deal, OpenSource or otherwise. Head and shoulders. * The \* commands are much easier for me to work with, although that may just be my preference. \d a_table, for instance is easier for me than typing SHOW CREATE TABLE a_table. (And tab-completion works there too...) As a proponent of data-correctness: * The default for PG is ACID compliance. As opposed to MySQL's default table type of MyISAM, which, although fast for selects, is not ACID compliant. This is especially hazardous since MySQL's internal data structure is all MyISAM. * The community in general is oriented towards doing it "the right way" as opposed to just getting it done. As a proponent of speed and concurrency: * Postgres may not be as fast for certain boundary cases like SELECT COUNT(*) FROM a_table; -- (read link above) but it far outstrips MySQL if you have much concurrent access. Someone else will hopefully serve out some links on this soon since I don't have them handy at the moment. As a DBA for different clients, I /really/ like the fact that Postgres has transactional DDL statements. Even Oracle doesn't have transactional DDL statements. Just DML. HTH, Kevin
On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> 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.
Scott Marlowe wrote: > On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >> Here are some other things we have v. mysql: >> *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems > And I totally agree with everything you said Merlin. Especially about > the community. > In short, we rock. :P Joshua D. Drake
On Tuesday 13 May 2008 12:09:28 Kevin Hunter wrote: > * The documentation on the website for Postgres is by far the better of > the two DBs. MySQL's is decent and very useful, but Postgres' > documentation just plain rocks. In fact, the Postgres documentation is > one of the very best examples of documentation for any project with > which I've had to deal, OpenSource or otherwise. Head and shoulders. > I've always thought that the biggest problem with the MySQL documentation is that it has a very hard time organizing all the little caveats that come into play between the different storage engines. Sometime an item might be documented in a storage specific area rather than a specific command (so it gets overlooked), other times I find that the command pages themselves are too cluttered with storage specific issues (making it hard to find information specific to your environment). It's an understandably difficult issue to work around, since ever storage engine you use means that you're basically learning the intricacies of a separate database, so it doesn't surprise me that things end up a little schizophrenic at times. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
jd@commandprompt.com ("Joshua D. Drake") writes: > Gauthier, Dave wrote: >> I�ve been invited to participate in a forum which discusses >> technical issues for relational DBs used in the corporation. The 3 >> DBs they are discussing are Oracle, SQL-Server and MySQL. I�d like >> to introduce PG, but want to be able to address the �Why not use >> MySQL� questions when they arise. I know in the past there have >> been many comparisons between the 2 DBs but would like to know if >> there are any good, recent ones. Also, any �off-the-cuff� comments >> would be welcome, especially if you have used both DBs. > > I suggest you take a look at: > > http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project > http://sql-info.de/mysql/gotchas.html > http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql > http://www.commandprompt.com/files/mysql_learn.pdf > http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html > http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php I would add to that document list the following... http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 -- let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/internet.html If anyone ever markets a really well-documented Unix that doesn't require babysitting by a phalanx of provincial Unix clones, there'll be a lot of unemployable, twinky-braindamaged misfits out deservedly pounding the pavements.
It surely rocks. We switched from MySql to PostgreSQL about a year ago and have never looked back, we are happy as Larry. A bit more information on our web site. http://www.alerce.com.au Regards, Mario Joshua D. Drake wrote: > Scott Marlowe wrote: >> On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> >> wrote: >>> Here are some other things we have v. mysql: >>> *) Transactional DDL: much safer and easier to roll out changes to >>> 24/7 systems > >> And I totally agree with everything you said Merlin. Especially about >> the community. >> > > In short, we rock. :P > > Joshua D. Drake > > >
Attachment
On May 13, 2008, at 11:42 AM, Merlin Moncure wrote: > Here are some other things we have v. mysql: > .... > *) Much better shell I tend to agree based on my limited experience. However, being a GUI-oriented person I haven't noticed management tools comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). Perhaps someone can enlighten me? (Yes, I've tried pgAdmin, but it's not quite ... right. I can't say why at the moment, I should probably take another look at it.) -- Andy
Andy Anderson wrote:
On May 13, 2008, at 11:42 AM, Merlin Moncure wrote:Here are some other things we have v. mysql:
....
*) Much better shell
I tend to agree based on my limited experience.
However, being a GUI-oriented person I haven't noticed management tools comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). Perhaps someone can enlighten me?
(Yes, I've tried pgAdmin, but it's not quite ... right. I can't say why at the moment, I should probably take another look at it.)
-- Andy
Andy Anderson wrote: > However, being a GUI-oriented person I haven't noticed management tools > comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). Perhaps > someone can enlighten me? > > (Yes, I've tried pgAdmin, but it's not quite ... right. I can't say why > at the moment, I should probably take another look at it.) There is phpPGAdmin which I believe was forked from phpMyAdmin several years ago, but still maintained. Also, pgAdminIII is a great tool, I think you should take a second look. Matt