Open source database vendor MySQL AB will release version 5.0 of its eponymous database management system in the first quarter of 2005, a year after it first went in to alpha testing. Also, after 4 weeks of work, involving a lot of bug fixes, and documentation improvements to the source tree, PostgreSQL.org have released the 5th Beta of 8.0.0.
MySQL prepares for version 5.0; PostgreSQL 8.0-b5 Available
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSAlert.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
I think that no matter which database you use, the creators of both these projects should be given thanks for the work that they do.
Thanks!
Given that both are starting to meet in terms of functionality and speed, specifically most of the reasons often used to /not/ use one or the other have been surpassed, what reasons are there to choose one over the other?
Damien
Can not agree more with both previous comments!
MySQL is still playing catch-up with the big boys. PostgreSQL has a lot of nice features, but isn’t well known.
The only advantages both databases have is: free, and for MySQL: the integration with PHP.
But anyway: good luck devs; give me a better database!
I hope MySQL improves performance under heavy load.
We had our web sites sereral times down because there were too many users accessing the MySQL database.
We migrated to PostgreSQL and we never had the problem again. We’re currently developping all our web sites and software using FreeBSD, PHP and PostgreSQL. It’s been working like a charm.
Rant_mode_on {
PostgreSQL is a “real” database, MySQL is a filesystem with a SQL interface (superficially speaking).
}
I worked with Oracle(v.8i) for about a year in my previous job, and frankly, there is not much I miss in PostgreSQL. It has SQL-92 schemas, tablespaces, stored procedures (that can return cursors), triggers, cluster support, …
I just love it.
“PostgreSQL is a “real” database, MySQL is a filesystem with a SQL interface (superficially speaking). ”
thats total crap even superficially speaking. you dont think Yahoo PAID for MySQL to buy a filesystem interface do you?. oh you think you know better. err… ok.
First I have to say that the article’s use of the word “Eponymous” is incorrect. I think the author meant to use the word “Ubiquitous”, which definitely describes MySQL.
Anyway, I like PostgreSQL a bit better than MySQL because it conforms to the SQL standard better and has more features. However, MySQL is fast, stable, and available with nearly every hosting provider.
Before I go, thanks to both the MySQL and PostgreSQL developers for all your hard work.
“thats total crap even superficially speaking. you dont think Yahoo PAID for MySQL to buy a filesystem interface do you?. oh you think you know better. err… ok.”
I don’t agree. For me, MS SQL and PostgreSQL are “on the same level” of performance and quality. You can use true and advanced SQL queries. Now, use PostgreSQL or MS SQL for 2-3 years and go back to MySQL. Then we talk.
I always wonder why OSAlert never talks about Firebird. Firebird has all of the features of both databases in this article…and much more…and it has zero licensing issues, it is truely free.
Eugenia, how about some Firebird coverage? Developers might finally discover how valuable this little-known gem really is!
http://www.firebirdsql.org
” Now, use PostgreSQL or MS SQL for 2-3 years and go back to MySQL. Then we talk.”
you mean migrate everything thats is working to pay loads of cash to MS-SQL and then try to challenge a OSAlert commenter. oh that makes perfect sense
you mean migrate everything thats is working to pay loads of cash to MS-SQL
Then try PostgreSQL instead (I’m not a big fan of Microsoft either BTW).
“Then try PostgreSQL instead (I’m not a big fan of Microsoft either BTW).”
again the pain of migration and the relatively slower speed negates any percieved advantages. I dont see why I should migrate. I have 18000 databases working perfectly fine for around 2 years in MySQL
Then try PostgreSQL instead (I’m not a big fan of Microsoft either BTW).
Migrating “for fun” still cost a lot of money. Speaking from my own experience, I had to redo my database when I migrated from MySQL to PgSQL as some datatypes were not supported (you can be sure I will use standard ones next time).
—
I did a project in MySQL 3.23 and 4.0… At first, it was cool but it becomes a tad more complicated once I needed some serious stuff. InnoDB might have foreign keys but they didn’t behave like I expected. Furthermore, I do not like some MySQL’s quirks (like the ability to enter February 31 0000). Of course, these issues are not critical when data safety/sanity is not really important (like on a website) but I still prefer PgSQL’s safety to MySQL’s speed. That said, perhaps they did change the behaviour of MySQL in the version 5… I don’t think I will adopt it as I am quite satisfied by PgSQL but I will try it once again.
Zambizzi, in my opinion, the main issue with Firebird is the complete lack of documentation. That might have changed since I have switched to PgSQL (as I did considered Firebird) but I did not found any reference document on it, just some old document on InterBase or whatever was its previous name…
Don’t be an idiot. You know perfectly well what he meant.
Because someone pays a lot for something doesn’t mean that something is worth paying that much or that it’s the best that amount of money can buy.
I have used mysql and postgresql quite a lot at work and I most certainly prefer postgresql. We used to use mysql before, but we switched to postgresql around v. 7.x. We also used db2 for certain things (because of politics), but it was a real PITA, it needed constant monitoring and loving to keep it running.
Listen: they are two very different products. It is almost pointless to argue the differences between them. Nutshell:
MySQL — it is fast and simple, and not too uptight with your data. You should put most of your business logic and constraints in the application layer. Can one do serious applications with MySQL? TO a certain extent, but to do so you simply must realize that you are recreating in your application a good percentage of what the more standard SQL systems provide. And just be aware of the gotchas — http://sql-info.de/mysql/gotchas.html
PostgreSQL — it is very close to the capability of the major commercial systems, and thus you can successfully make the database layer the major business logic repository, which allows for multiple application environments to connect with the DB without worrying about data corruption. In general you have very sophisticated mechanisms and capabilities for dealing with just about any kind of data you can imagine.
I know people who do good work with both systems, although I would say that PostgreSQL is a much better candidate for a banking application, while MySQL is better suited to online product catalogs and forum systems.
thats total crap even superficially speaking. you dont think Yahoo PAID for MySQL to buy a filesystem interface do you?. oh you think you know better. err… ok.
Yahoo probably paid to get official support… and perhaps the non-GPL licence.
I doubt that Yahoo runs completely on MySQL. The web interface, maybe, as it’s well known that MySQL is FAST for frequent SELECTs and few UPDATEs but probably not their whole search database.
Anyway, you have to evaluate your needs… Personally, I wouldn’t trust a banking system or anything that need perfect data sanity to MySQL but it’s suitable enough for websites (probably even more than PgSQL), forum software or pretty much anything… It’s also more accessible than PgSQL, since there are better doc (IMO) and more books available for it.
”
I doubt that Yahoo runs completely on MySQL. The web interface, maybe, as it’s well known that MySQL is FAST for frequent SELECTs and few UPDATEs but probably not their whole search database.”
wrong. ask jeremy who works on yahoo. yahoo specialised and standardised on mysql
It baffels me to read these comments. All the mysql bashing going on here focuses on myisam tables which are blazingly fast but with their limitations.
If your needs grow to a real db with transactions and all the bells and whistles all you need to do is alter your table to type InnoDB. Inno is modeled after oracle, uses table spaces and supports all the latest features ( some not yet exposed by mysql, like views or triggers but we all know that if you rely on views you got design issues anyways ).
A 100% innodb mysql db is basically the same as postgres but you get all the mysql advantages like the querycache which does wonders for webapplications or the amazing query analzyer plus all the third party tools like phpmyadmin, cocoasql and so on.
Postgres, firebird and mysql are all amazing databases. Postgres and firebird concentrate on big business while mysql has additional support for small webapps and its constantly bashed over this.
All big mysql companies use mainly innodb nowadays, slashdot, yahoo, matrix, Phaistos and so on. Read more about it at http://www.innodb.com. you will be suprised how many 20+ gb innodb databases exist and perform amazingly well.
Last but not least: Mysql is damn fast.
I used to use Firebird, but I became disallusioned and frustrated with the lack of docs, try to find docs on what the system tables do.. And the developers are mostly very rude and cranky i.e. Jim Starky and Helen Borie.
The development of Firebird is also like pond water when compared to PostgresQL.
I will never use Firebird again for a major client server application because of the reasons listed above.
And after I started to use Postgres I was pleasantly suprised to find that it was superior to Firbird in almost every way, and Version 8 even more so.
I found that I could use any SQL I wanted in postgres stored functions where in firebird I was limited to PSQL. Try creating a table or adding a user from a Firebird stored proc, ha ha you can’t. Firebird has several layers/version of SQL, i.e DSQL for use from the client side and PSQL which is another subset that only can be used in procs.
Stay away from Firebird and use Postgres, also the version 8 NATIVE win32 version is excellent.
I think you are a bit unfair to the Firebird team, the firebird support mailing list is one of the best support forums I have seen. Helen Borrie and others answers lots and lots of questions every day, so its quite understandable if they get a bit cranky when asked questions that would better fit on a SQL beginners list. If you ask firebird related questions you usually get very good answers amazingly fast.
True the documentation can be a bit hard to find, they really shouldn’t rely that much on old interbase docs. But as I undertstand it there is an ongoing project to get a Firebird specific documentation.
Anyway, I would say that Firebird is far superior to MySQL it has stored procedures, views, triggers etc. The main advangtage with firebird is that it is crossplatform as it runs windows, MacOS-X, Linux and perhaps some other unixes.
Even though it is hard to find what system tables do in one place of the documentation, stored procedures and triggers is very well documented. Much better than for Postgresql who seam to more occupied with telling us how many languages you can use then to tell you how you actually create your code.
Great to see that Postgresql gets ported to windows though.
Postgres have been one of my long term favorites.
Its a bit strange to compare Postgresql 8.0 beta to MySQL 5.
Postgresql is likely to be in final state before the end of this year, while MySQL 5 will be alfa state for a forseable future. When it finally gets released we probably can compare it to the final versions of postgresql 9 or 10.
The current production grade version of MySQL is 4.1.
And if we compared that to the current version of postgres you would find that MySQL is far far behind in features.
If we were to compare MySQL products to postgresql it would be much more interesting to compare with MaxDB. MaxDB is the former SAPdb now sold by MySQL AB. Just like Postgresql MaxDB is a full featured database with triggers, stored procedures, views,.. while production ready versions of MySQL lacks such things.
You could argue that the beauty of MySQL is that it lacks so many features that databasedevelopers normally take for granted. This means tha people that are not database developers can use it without much training. Even your generic computer illiterate 70 years old mother would be able to install it and make a few selects statements. But if you are looking for a real database that compares to MS-SQL Server, Oracle or DB2 you should look at Postgresql, MaxDB or Firebird if you want free alternatives.
once finish to copy coding to 5.0… then MaxDB bye…….
If your needs grow to a real db with transactions and all the bells and whistles all you need to do is alter your table to type InnoDB. Inno is modeled after oracle, uses table spaces and supports all the latest features ( some not yet exposed by mysql, like views or triggers but we all know that if you rely on views you got design issues anyways ).
Sorry, but if innodb have nice features that is good for innodb. However, if you can’t reach them in MySQL that is of little use in the real world.
I would say that using views is not necessarily a sign of design issues. Views is a way of creating logical data independence. This is very important in large systems where many applications rely on common data. It is also a very useful tool to enforce security. If you need more info on why views can be a good idea google for ANSI/SPARC model which is used most large systems, where security is important, such as banks.
A 100% innodb mysql db is basically the same as postgres but you get all the mysql advantages like the querycache which does wonders for webapplications or the amazing query analzyer plus all the third party tools like phpmyadmin, cocoasql and so on.
Most databases use this design, I would be very surprised if postgresql didn’t do something similar. Anyway modern version of postgresql is quite fast, so I really don’t care how they do it.
Postgres, firebird and mysql are all amazing databases. Postgres and firebird concentrate on big business while mysql has additional support for small webapps and its constantly bashed over this.
I think it gets bashed, becasuse MySQL users have a tendency
to market it as a solution to all database problems which is clearly not the case. But as you say it is quite suitable for small web apps (well, even large ones for that matter). The reason for this is that they seldom use much consistency checking as often is needed in business or banking applications.
This support for small web apps is something new to me. Sounds interesting. Is it some kind of .net interface of what? Please elaborate.
wrong. ask jeremy who works on yahoo. yahoo specialised and standardised on mysql
Ah, yes, Jeremy, that’s my good ol’ friend… I mean, we went to high school together.
Translation: who the hell are you talking about?
Anyway, according to InnoDB’s website, Yahoo do use MySQL for many applications (Finance, JAKE…) but not for everything.
once finish to copy coding to 5.0… then MaxDB bye…….
Only a pity that this copying is so slow. And I really like that MySQL in the copying proceccs copy the functionality not the code. It seams that MySQL 5.0 will be much more ANSI 99 compliant than MaxDB.
If your needs grow to a real db with transactions and all the bells and whistles all you need to do is alter your table to type InnoDB. Inno is modeled after oracle, uses table spaces and supports all the latest features ( some not yet exposed by mysql, like views or triggers but we all know that if you rely on views you got design issues anyways ).
Most bad designers probably don’t even know what views are. They can be quite useful and they can avoid you from making ye olde big SELECT of Death(tm) with multiple sub-selects. And like Uno said, most large designs use them because of security, etc. Like everything, they can make your design/life more simple as long as you know when to use them.
As for InnoDB, it’s not exactly like the internal format from PgSQL & co because of some limitations in MySQL, especially with foreign keys/constraints. Note that I didn’t use versions past 4.0.16 so they might have corrected some quirks but I must say that they gave me a bit of trouble. Nothing critical, but a bit annoying.
Nevertheless, I am quite openminded and I hope MySQL 5 will be good. If they fix (or at least document) the quirks, they will probably “win” me back as I *personally* find it faster for what I am doing.
Does anybody know what the Inno part of InnoDB (and Inno Installer for that matter) means? I know in Japanese an inno is a scrotum. I doubt that’s what the developers had in mind though.
Firebird has all of the features of both databases in this article…and much more…and it has zero licensing issues, it is truely free.PostgreSQL is released under the BSD license. What licensing issues were you referring to?
Do you know something similar like mysqladmin for psql ?
I’m running Debian.
http://www.pgadmin.org/pgadmin3/index.php
Thanks, I will try it when 1.2 will be released, rc at this stage.
p.s.
I saw that as GUI framework they are using: http://www.wxwidgets.org/
Nice, never heard about it.
Let’s not forget that Postgres is FREE (as in speech), unlike MySQL.
I saw someone diss (well, not quite, but almost) selects with multiple subselects here: There is nothing wrong with this. So what if it takes half a milisec more to run the query, and even if you write the query in a fairly unoptimised way, it will almost guaranteed be faster than selecting * and then using your application to filter/sort the data … After all, that’s exactly what the DB is for.
If you don’t like fancy SQL queries then why are you bothering with a database in the first place?
Let’s not forget that Postgres is FREE (as in speech), unlike MySQL.
That remind me flamewar between GTK+ and Qt. GTK+ is like PostgreSql. Free for prop software. Qt is like MySQL. Not free for prop software. Qt buddy always said it is understandable if you have to pay for developing prop or commercial software.
Maybe you should not forget about that.
Btw, I never like Qt and MySql license.
Hi,
just to clue some guys up a little bit.
MySQL is not only used by major websites like Yahoo, Slashdot or Ebay
MySQL is used by some major banks as well.
Cheers
Gunnar
phpPgAdmin:
http://phppgadmin.sourceforge.net/
More tools here:
http://techdocs.postgresql.org/guides/GUITools
Wait, I did not dissed them. I simply said that views can make your life more simple as they can avoid you from making very long queries. There is _nothing_ wrong with multiple subselects but they can be a bit difficult to craft, especially for the less experienced. Anyway, I must say that I am not a DB specialist as I am in computer enginnering.
I know this forum is a day-old post, but I just wanted to chime in to say “way to go” to both teams. I’ve really enjoyed my limited experience with MySQL, and openly thank everyone involved.
So what if it takes half a milisec more to run the query, and even if you write the query in a fairly unoptimised way, it will almost guaranteed be faster than selecting * and then using your application to filter/sort the data
Using views is just an abstraction. Technically it’s not different from using complicated queries. The query from the view is run each time. But very large queries does not necessarily degrade performance. It’s up to the query compiler to decide exactly how the data is looked up and accessed from disk (or memory).
The query compiler and transaction manager is at the heart of any serious DBMS, and my experience with PostgreSQL is that they’re doing a great job. Performance for lightweight web applications lies just a bit behind MySQL, but it doesn’t seem to get any slower no matter how many transaction you throw at it. And there is no comparison between the two when it comes to features. PostgreSQL is in another league, even compared to MySQL 5.1 features.
But back to the performance issue: If you have an extremely complicated database with 150+ tables and 20+ nested queries and/or joined tables, you can always use PL/Perl or some other technique to program derived tables, “physical views” or some other kind of cache. Whoups, … where is MySQL now?
just to clue some guys up a little bit.
MySQL is not only used by major websites like Yahoo, Slashdot or Ebay
MySQL is used by some major banks as well.
To see MySQL in large sites like Yahoo or Slashodot is no surprise. These sites have many visitors and very little
need for data integrety and transaction control. MySQL would fit very well in that setting.
To see it in banking or on Ebay is more surprising. Even though MySQL can do transactions there is no way to make business logic part of these transactions as MySQL doesn’t support stored procedures.
This means that you will have to use some external transaction management system e.g. a J2EE application server. By doing so you usually get a slower system than
if you use a database that support stored procedures and put your application logic directly in the database.
All in all, if MySQL is suitable or not doesn’t depend so much on how many users you have, but rather the integrity and transactional requirements of your application.