Comparing MySQL and Postgresql 34
Mr Poet would like to start a discussion on the following: "I am an AVID user of Postgresql, over the past 6 months I have seen more and more about MySQL. I have done a little research and outside of MySQL being extremely fast I have not seen any reason why people are so excited about it. This is not a slam on MySQL. I just don't understand the reasons. Could we have a technical discussion as to why?"
That's a good question. What are the strengths and weaknesses of both programs. Are there areas of deployment where one is more appropriate than the other, or are these programs fairly interchangeable?
Re:Thread support (Score:1)
Remember, kids, the thread you seg fault may not be your own...
Re:MySQL vs PostgreSQL (Score:1)
Re:Thread support (Score:1)
Threads are merely a design issue. mySQL decided to execute mutliple queries over threads rather than processes. Oracle, Informix, DB2, and PostgresSQL use multiple processes in UNIX environments. This largely due to their age. When they were first written UNIX threads either didn't exist or weren't standard enough to work reliabily across platforms. IMHO, threads are better because they can be started and closed fater and require fewer resources, but then, processes are cool as well.
use HAVING (Score:1)
the use of aggregate comparisons.
Re:Me Too! (Score:1)
I kinda miss subselects, but not much; they're scheduled for a point release in the near future. I think the word from Monty is "September", but don't hold me to that.
I've never missed transactions. The one time they would have been nice I was able to catch the exception and delete the incomplete information.
MySQL's license is pretty simple -- as long as you don't sell a product that can only function with MySQL, you don't need a license. If you provide a MySQL adaptor, a PostgreSQL adaptor, and an Oracle adaptor, I don't think you need one.
Re:Disclaimer : DB newbie opinion (Score:1)
Disclaimer : DB newbie opinion (Score:1)
However, I had made some research on the issue. Here are my conclusion :
- MySQL is reputed faster (MySQL developper admit that speed is no. 1 priority), but PostgreSQL advocate point that the speed disadvantage of PostgreSQL is mostly due to fsync() call for every write request to the db, wich is a Good Thing (TM) but can be disabled if speed is an issue. My personnal opinion is that fast hardware is cheap, thus lowering the importance of this issue.
- MySQL has a lot of nice third-partie support and goodies and better doc. However, the doc for PostgreSQL 6.5 had been much improved and is quite helpful.
- As pointed out in previous post, support for transaction is a plus for PostgreSQL. Also, the PostgreSQL developpement team seem genuinely focused on SQL92 compliance, another Good Thing (TM) from my newbie point of view.
- Another nice aspect of PostgreSQL I don't know much about is native geometric data type (could be interesting in GIS application) and support for BLOB (don't know if MySQL support BLOB). I don't need these features, but it's nice to know there available.
Thus said, PostgreSQL has some shortcoming (at least in v.6.3.2, the one I use):
- Can't use aggregate function in WHERE clause. For example, the following statement is legal with MySQL :
SELECT * FROM sometable WHERE id = max(id);
In PostgreSQL, you need some hoopla to do this :
SELECT * FROM sometable WHERE id = (SELECT max(id) FROM sometable);
This might be fixed in Pg 6.5, I don't know.
- There is no AUTO_INCREMENT in PostgreSQL. You have to create a "sequence" (CREATE SEQUENCE seq;) and invoke nextval('seq') to extract a serial number. This is fixed in v6.5 : PostgreSQL now have a SERIAL data type that is auto-incrementing.
That being said, don't take my word for it (I *really* don't have much experience with database).
Just my 0.02 CDN$.
Re:MySQL is not free software (Score:1)
derived from or including mysql that is non
redistributable or non-free.
or summat like that. either way for practical
use, say on a website, even if you are a footsie
100 company, you will not have to pay.
It is true that it is not fully GPL'd though.
MySQL is excellent for a range of tasks. (Score:1)
I can say beyond a shadow of a doubt that for OLAP and dimensional data modeling MySQL is without peer. One of the applications I have written has an active concurrent userbase of nearly 10,000 clients. (Not 10,000 possible clients, 10000 concurrent clients!) And the MySQL engine has no problems cranking out the data that they need.
MySQL has excellent hooks into a plethora of programming languages. The first applications written at my corporation were written in Perl with the DBI interface. The interface between Perl and MySQL is flawless. I have never had a single problem in dealing with recordsets via the DBI interface. Once we shifted away from pure reporting applications and more into dynamic web-based analytical processing a shift was made to PHP3. The PHP hooks into MySQL are amazing, the speed is unmatched and the ease of use alowed for excellent rapid application development in our high pressure enviroment. Once you learn however, that until PHP4, PHP did not have a true garbage collector so if you didn't explicitly free your result sets, you ended up with Apache processes that took up 140Mbs. of ram
The current shift is away from PHP and into a more formalized OO model with Java Servlets. The MM Mysql Drivers (available for download from a link on the MySQL download page) are excellent and fast. I wrote a custom database connection pooling algorithm based on some nice circular skip list heuristics, and we have never had a problem with it. I am still not sure if I like it as much as PHP, but the ties into MySQL are amazing.
As for MySQL's limitations... well, it does NOT have subselects... yet. With MySQL 3.23.* we will soon have subselects. At first this was something that really bothered me, until I realized that 90% of all things I needed to do via subselects were easily and more efficiently implemented as joins. And the rest could easily be implemented with the use of temp-tables (after all, that's what a subselect does anyways
The lack of transaction and rollback capabilites can be a problem, but with the soon advent of atomic operations we will have most of the neccessary tools to emulate some of the functionality of transaction-rollback mechanisms. This, however, is a valid complaint, and if you are developing an OLTP instead of an OLAP application, it might be a good idea to go with an Oracle, Postgress, or Solid.
And finally, the MySQL mailing list has some of the most brilliant minds in the world of database development I have personally ever encountered. There is seldom a question asked that does not either receive a professional concise answer, or a pointer to the proper place too receive that answer. So, all that said, look at the subject of the message for a concise version of this posting, and if anyone has anything they would like to dicuss with me about MySQL development, feel free to get in touch with me.
ed [mailto]
Speed vs a "real" RDBMS (Score:1)
--
DB not an alternative to mysql (Score:1)
--
Re:Disclaimer : DB newbie opinion (Score:1)
>- Can't use aggregate function in WHERE clause. For example, the following statement is legal with MySQL :
>SELECT * FROM sometable WHERE id = max(id);
Hmm...IIRC, you can't do this in SQL92. So it's actually a symptom of PostgresQL's stricter adherence to SQL.
Of course, with my luck, I'm totally wrong about this, and deserve every flame I get because of it.
Re:Thread support (Score:1)
2) Is anyone aware of a current benchmark comparing PostgreSQL to other DBs? The standard line about PostgreSQL performance seems to be "PostgreSQL was pretty slow, but 6.5 is a huge improvement"... with no numbers to compare.
Re:MVCC documentation (Score:1)
Re:Me Too! (Score:1)
That's absurd! That is what you use transactions for -- ensuring concurrency and atomicity among updates. MySQL is barely a relational database, and you could do almost as well using serialized hashtable objects in your choice of programming languages. :-)
I have never really seen any speed problems with postgresql -- I built a web-based groupware and e-mail application for a liberal arts college and based it on postgresql; at any given time, the db has tens of thousands of rows (maintaining user preferences, info about postponed messages, addressbooks, and myriad other things in the email app alone), and performance is comparable to if not better than an Oracle namespace of similar magnitude on the development server at my current job doing db development for a major pharmaceuticals company.
The moral of the story: if you're backing a web site, your bottleneck is most likely not going to be the DB, unless you're opening the DBMS with every page you serve (i.e. use a connection pool OR ELSE). You're more likely to be network-I/O-bound most of the time, and a "lightning fast" DBMS won't help that at all. However, if you're writing an application that requires that more than one person access/update the data simultaneously, you'll want transactions, or else you can deal with the mangled db.
Good luck. I'd suggest you read Philip and Alex's Guide to Web Publishing [photo.net] for more info on why this is important. You should also check out Mr. Greenspun's clever ArsDigita Community System -- it does almost everything that a web/db application needs to do, and it caches db connections.
MySQL vs PostgreSQL (Score:1)
The big differences are:
1) Transaction management
2) Stored procedures
Take for example a "Slashdot" system. This system doesn't require very much of the database.
It doesn't need transactions. Most action is making selects on texts to view. After that the
"transaction" is complete. The second most use option is probably submitting a text (such as this). This can be done with a single insert command. The last option is updating user settings. This may require transaction handling: what happens if the user is "logged in" twice and changes his user settings at the same time? This situation is probably considered "unlikely" or an "acceptable bug". From this point of view, Slashdot is just like many other web-sites: all it requires is speed.
However, I you try to build a real world financial administration,you cannot live with such "acceptable bugs". This requires more, most important the two features mentioned above.
Now the question is whether many organisation would allow such critical data to live on PostgreSQL. System administrators are used to Oracle, Sybase, Informix etc. These names they trust. PostgreSQL only has become reasonably stable in the last 1.5 year and still has to build its name. Only a few months ago some critical features (e.g. row-locking) have been implemented to make it feasible to make such an administration system. The name still has to grow, but has a great potential. After it has done so, you will find more comments of people being enthousiastic about PostgreSQL.
Me Too! (Score:1)
I have done as much research as Google could point me to and as far as i can see MySQL screams but it is not feature rich. Here is the summary of an afternoon (last week) of research (Disclaimer: I still have to do benchmarks myself but this is a start.)
MySQL is fast but it lacks some important stuff that we decided we couldn't live with out.
Postgres on the other hand was developed in a very academic enviroment and is extremly feature rich. The major draw back to postgres I could see was that it doesn't scale very well and i am not sure if 6.5 addressed any of these issues. That being said if the db won't scale then it really doesn't matter how many features you have.
They guys who developed MySQL have a really cool and usfull tool on there web site that runs a script and lets you know exactly (It is VERY detailed) what features each db has.
I strongly suggest anyone insterested in benchmarking features of dbs check this out. db compare [mysql.com]
We decided that we would use postgres until we do our own benchmarks. It may scale enough for what we need, the load shouldn't be too high for our use.
more info would be great, also the timeline for MySQL to incorporate inner selects would also be handy cause i know they havbe it at the tops of the features they are going to implement.
Thread support (Score:1)
Speed (Score:1)
However, what are the real comparison numbers? Are we talking 3-4% on 1 million rows?
If PostgreSQL [postgresql.org] is only slightly slower that MySQL [mysql.com], I would consider switching.
A.
--
Adam Sherman
Re:Multi-version concurrency control (Score:1)
Re:Disclaimer : DB newbie opinion (Score:1)
MySQL supports them. I really like the ease with which one can throw around large blocks of text using MySQL - easier than with postgres or Oracle IMHO.
I looked long and hard at both of these when I started doing db backed web development (about 1 year ago). I started with postgres, but there were occasional dropped connections and even server crashes. I really liked the feature set of postgres, but the consensus at that time was that mySQL was more stable - so I switched. And it was true. Not one crash or problem of any kind yet with lean and mean MySQL. I would like to see a larger limit on varchar, and subselects (coming soon!).
Re:Thread support (Score:1)
Sybase and Oracle still use separate processes, but I think Informix uses threads.
Re:Me Too! (Score:1)
Re:Thread support (Score:1)
--
OS lover
MVCC documentation (Score:1)
Excerpt:
--
OS lover
Documentation! (Score:1)
this:
PostGresQL:
undocumented bugs, inconsistent behavior
from one release to the next. Not reliable.
MySQL: The opposite.