Thread: Why is PGSQL better than MySQL
I've been reading the "AP2" thread and take it as a matter of faith that PGSQL is better than MySQL. It seems more complete for one thing. I have tried to explain my reasoning to several people when convincing them to use one versus the other. But does anyone know if there is a quasi-categorical comparison of the two so that I could speak with more conviction other than "I know it's better".
On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote: > But does anyone know if there is a quasi-categorical comparison of the > two so that I could speak with more conviction other than "I know it's > better". I've been involved in that comparison at work, and after some boring theoretical discussions, i once turned to my CTO and said: "Do a test. On the same machine, do a script that executes 50.000 inserts, OR any other 50.000 SQL operations you want, and benchmark the results". He did, and Postgres immediately got another die-hard fan. The facts spoke more clearly than i could ever do, with just words. Nowadays, he is even more "defensive" of PGSQL over MySQL than i am. \\pb
Agreed. You can't argue with a well executed benchmark. All I'd say is be careful not just the same simple select statement over and over again - Do a variety of both simple and complex queries. And always use InnoDB as compared to MyISAM (otherwise you're not comparing apples with apples). The biggest pro (imho) that PostgreSQL has over MySQL is its locking mechanism. PostgreSQL uses MVCC, which greatly improves concurrency. For a list of "Gotchas" take a look at these: http://sql-info.de/mysql/gotchas.html http://sql-info.de/postgresql/postgres-gotchas.html Also stumbled across this: http://www.vitavoom.com/postgresql.html On 12/13/05, Pedro B. <pedro.borracha@netcabo.pt> wrote: > On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote: > > But does anyone know if there is a quasi-categorical comparison of the > > two so that I could speak with more conviction other than "I know it's > > better". > > I've been involved in that comparison at work, and after some boring > theoretical discussions, i once turned to my CTO and said: > > "Do a test. On the same machine, do a script that executes 50.000 > inserts, OR any other 50.000 SQL operations you want, and benchmark the > results". > > He did, and Postgres immediately got another die-hard fan. > The facts spoke more clearly than i could ever do, with just words. > Nowadays, he is even more "defensive" of PGSQL over MySQL than i am. > > \\pb > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote: > I've been reading the "AP2" thread and take it as a matter of faith > that PGSQL is better than MySQL. It seems more complete for one thing. > I have tried to explain my reasoning to several people when convincing > them to use one versus the other. But does anyone know if there is a > quasi-categorical comparison of the two so that I could speak with > more conviction other than "I know it's better". I have been a serious MySQL user for several years and was looking forward to the 5.0 release so that I could employ triggers and stored procedures in some of our applications. While attempting to convert from MySQL 4.1 to 5.0 this past summer we uncovered numerous serious bugs in MySQL all of which were reported. We subsequently discovered many of these same bugs in version 4.1. None of the reported bugs have yet been fixed in any version. Additionally the implementation of stored procedures and triggers is so useless that it is laughable that they even bothered to mention it as a feature. Needless to say, this fall we began the conversion to PostgreSQL 8 and we couldn't be more pleased. As a part of the conversion I found several new bugs in MySQL due to PostgreSQL complaining about some of my old queries. The most egregious of these are MySQL silently truncating var chars that are too long for the column and silently allowing one to insert a quoted string into an integer column. Ouch! We won't be using MySQL for any new work here and will eventually migrate all of our applications to PostgreSQL. My heartiest congratulations to the entire PostgreSQL team for an absolutely outstanding product! dave -- David Rogers vnet 777-6522 MCI Network Security Operations Center
--- John Mohr <jtmohr@healthconresources.com> wrote: > I've been reading the "AP2" thread and take it as a > matter of faith that > PGSQL is better than MySQL. It seems more complete > for one thing. I have > tried to explain my reasoning to several people when > convincing them to use > one versus the other. But does anyone know if there > is a quasi-categorical > comparison of the two so that I could speak with > more conviction other than > "I know it's better". > John, when i first started developing in open source, i did a lot of research on dbs. mysql had a HUGE, HUGE, HUGE advantage in documentation at the time - and that was a big factor since i'd be starting from scratch. i chose postgrsql inspite of this. native foreign key constraints was a big reason. i came to like transactions and it is more sql compliant than mysql. it is also less buggy. the license is also much more application developer friendly - and this is HUGE if you ever want to develop a product and market it. having said that, mysql is an INCREDIBLY popular database. those people that know it inside and out will not be inclined to change easily - the devil you know is better than the devil you don't know... while i'd choose pgsql 10/10 times, a 5 year veteran of mysql will likely choose mysql 10/10 times - and that developer may well be an excellent developer and that may be the right choice for him/her. now, if you are going to be developing apps as part of a team of folks who want to use mysql, that would be tough. i'd want to use postgresql, too. do your research regarding sql compliance, bug issues and hacked "features" in mysql that were designed into postgresql natively. this isn't a run down on mysql, either. it is a fact they hacked in features after the fact. a good mysql developer may be able to tweak the system so it works fine, but i prefer to avoid the jumping and the hoops. mysql has a reputation for being blazing fast... but i have to say my postgresql db on our lan is blink of the eye fast, too - although my records are currently in the 100s, not 100,000s. best of luck. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On 12/12/05, John Mohr <jtmohr@healthconresources.com> wrote: > > I've been reading the "AP2" thread and take it as a matter of faith that > PGSQL is better than MySQL. It seems more complete for one thing. I have > tried to explain my reasoning to several people when convincing them to use > one versus the other. But does anyone know if there is a quasi-categorical > comparison of the two so that I could speak with more conviction other than > "I know it's better". Here is my response: In the beginning, PostgreSQL focused on implementing features and being as standards-compliant as possible. MySQL focused on raw speed, and keeping the codebase to the smallest footprint possible. The end result is that the PostgreSQL folks implemented a ton of features, and adhered to a lot of standards, and have now had plenty of time to refactor and refine the software to provide significant advances in speed and stability. MySQL is only now at the point where they are starting to implement features and consider standards. To me, this puts MySQL at the same point of PostgreSQL, just three or four years ago. Why wait? ;-) -- Mitch Pirtle Joomla! Core Developer Open Source Matters
On Tuesday 13 December 2005 12:03, Mitch Pirtle wrote:
> Here is my response: In the beginning, PostgreSQL focused on
> implementing features and being as standards-compliant as possible.
> MySQL focused on raw speed, and keeping the codebase to the smallest
> footprint possible.
If PostgreSQL is like that old saying "Make it right, then make it fast", I
guess my$ql is "Make it fast, then make it right". Seems like a no brainer
to me :-)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Tue, Dec 13, 2005 at 08:36:12AM -0600, David Rogers wrote: > Needless to say, this fall we began the conversion to PostgreSQL 8 and > we couldn't be more pleased. As a part of the conversion I found several > new bugs in MySQL due to PostgreSQL complaining about some of my old > queries. The most egregious of these are MySQL silently truncating var > chars that are too long for the column and silently allowing one to > insert a quoted string into an integer column. Ouch! We won't be using > MySQL for any new work here and will eventually migrate all of our > applications to PostgreSQL. You should make sure all that stuff is reported in the gotchas: http://sql-info.de/mysql/gotchas.html -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Dec 13, 2005 at 02:24:48AM +0000, Pedro B. wrote: > On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote: > > But does anyone know if there is a quasi-categorical comparison of the > > two so that I could speak with more conviction other than "I know it's > > better". > > I've been involved in that comparison at work, and after some boring > theoretical discussions, i once turned to my CTO and said: > > "Do a test. On the same machine, do a script that executes 50.000 > inserts, OR any other 50.000 SQL operations you want, and benchmark the > results". My personal favorite: CREATE TABLE s(s smallint); INSERT INTO s VALUES(99999); SELECT * FROM t; Seeing what those 3 lines produce is MySQL is enough to convert 50% of people on the spot (after you tell them that *every* type in non-strict MySQL operates that way). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461