Can you trust the leading open-source database engines, PostgreSQL and MySQL, to deliver the performance and features that the Oracles, SQL Servers, and DB2s of the world do? Not just yet, but they could offer enough to meet your needs. Find out how they stack up against each other, as well as against the commercial alternatives, writes Tim Conrad.
PostgreSQL vs MySQL vs Commercial DBs: Depends On Your Needs
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
Not bad, but a big thing missed was schema support.. last time I checked mysql doesn’t have schema support, ie: database.schema.table lookups, which pg does, however mysql ‘fakes’ it with cross database select. The SQL standard says that a user:schema is 1:1, this is how oracle runs, however pg allows arbitary schemas, which I think is much better than Oracle’s system. Oracle doesn’t allow cross database selects via normal methods, but special sceneros can be setup. Schema support is quite essential especially when migrating an oracle database over to pg/mysql. If oracle is setup for cross database selects with schemas, then migrating to mysql isn’t possible. Microsoft sql server doesn’t have schemas, but the syntax seems to be schema aware (..), so Yukon probably will have schemas, where I don’t think mysql doesn’t.
Postgres data partitioning (ie tablespaces) are one of the key things holding it back, (along with native multi-master replication and a PROPER blob type, bytea just doesn’t cut it), MySQL is much further behind than Pg feature-wise, but I didn’t really get that impression from reading this article.
Was a nice article and easy to read
Didn’t really touch on MySQL’s lack of ANSI compliance however….
I’m sure this will improve in time however
wow im impressed. Finely a rational and easy to read DB comparison. Ive been flip flopping on MySQL vs PostgreSQL for a while.. and settled on PG.. guess I’ll stick to it
Does any one know the differences between the SQL used in PG versus the stuff in centura/gupta? and any links to docs comparing the 2?
Both me and marhall have some very good point. As a long time pg user who have been forced into MySQL (new job) one of the things I really miss is the ability to add my own datatypes to the system.
The author doesn’t mention this just like he forgets that PG isn’t an RDBMS but an ORDBMS (Object Relational DBMS).
I do agree with author about his concerns about REALLY large databases though, but it will come sooner or later.
ORDBMS vs RDBMS?
You want to go over the difference there?
In what ways does BYTEA not ‘cut it’? Just curious for your perspective.
Anyway, PostgreSQL has a Large Object type, which is different from the BYTEA type. (http://www.postgresql.org/docs/7.4/interactive/largeobjects.html) The only difficulty with large objects is the need to invoke the import and export functions.
The author didn’t mention the other free DBMS: Firebird …
Firebird is not as popular as PostgreSQL and MySQL and so the author probably never used it, and so he could not write anything about it.
>> Didn’t really touch on MySQL’s lack of ANSI compliance however….
>>I’m sure this will improve in time however
Heh, yeah… and the improvement is called SAPDB (now MAXDB). The other thing he didn’t mention was that MySQL’s enforcement of basic datatypes is so bad that for all practical purposes, it should be called a typeless DBMS. For example, it helpfully truncates or rearranges values to fit, without returning errors. Try inserting 1024 into a TINYINT, for example, or see if you can create a nonexistent date, such as the 31st of February.
Sure, this quote is sort of at short introduction:
“ORDBMS extend the standard relational systems by introducing the features of object references, object collection, aggregate functions including user-defined set aggregates, nested row types and nested relations. The motivation for the development of ORDBMS has been to incorporate the features of object-oriented systems which facilitate the manipulation of complex data while at the same time being compatible with relational systems and maintaining the high level, declarative features of relational systems.”
http://www.acrc.unisa.edu.au/groups/datawebeng/dbdesign.html
A couple of more resources can be found on Google
bytea requires odd backslash escaping, and is HORRIDLY slow… try 1mb escaped bytea sizes in pg compared to 1mb BLOB types in oracle. oracle’s at least 100x faster
Look here http://sql-info.de/mysql/gotchas.html for some MySQL Gotchas. Interesting read. PostgreSQL simply rocks. Woud you trust your credit card to an ecommerce store that didn’t support transactions?
“Slashdot, a widely read online publication, uses MySQL to store all of the information related to its site.”
Anyone have a pointer to the hardware used by slashdot, how large their database is, dbhits per second and so on?
Yours truly,
Jeffrey Boulier
It really find these database comparison articles to miss the point completely. The answer should always be “It depends” 😉
/. can run off of mysql because it doesn’t have to worry about insane amount of INSERT statements, and can cache most the the SELECT statements. The active dataset over the course of a day is a tiny fraction of their entire database. Note, I’m assuming a lot with those previous statements…
Imagine the level of activity going on in a flight reservation system or in a bank (yeah, I know, classic DB class examples). I just find it funny when some write throws in that some company is using such and such software, mostly because of the fact that the company is large.
Hmm… I don’t mean to sound condesending at all. I loved the article and hope to see more DB related stuff here! Just wanted to get that off my fingertips!
I would like to see hard facts on the 100gig issue. I have been told on the postgresql mailing list that it can handle terabytes with the proper hardware. And do you see users dropping off postgresql at 100g only because they think it can’t handle it or does it really have issues with that size of data.
hi
“http://sql-info.de/mysql/gotchas.html“
this is hopeless outdated.
If I had a few terabytes of storage, I’d be happy to create a postgres db just to see how it would handle it… to bad my 40g caviar isn’t that
*sigh*
I think the “odd” escaping rule applies to string/text in general, not just BYTEA. That’s just the way Pg expects one to escape character (‘\000’). The double backslash does seem unusual compared to C, and I guess the reason is probably historical.
As for LOB’s in Oracle, I think it compares more closely to the other LOB that Pg has. The Pg manual specifically states that BYTEA/TEXT is _not meant_ for large strings as they are always processed in entirety (doesn’t support chunked processing) and thus eat lots of memory. I do wish BYTEA/TEXT will be more efficient for storing large data in the future though, as they are very convenient compared to the other LOB.
Everytime OS DBMS’es are discussed, this complaint about Firebird not getting enough mention always comes up. The article explicitly says just MySQL and PostgreSQL, so please calm down… 🙂
What does the author mean by ‘partial rollback of a transaction’. Nested transaction? I don’t recall Pg or MySQL has that at the moment.
As to indexing, I think Pg is already quite advanced. It supports functional indexes (index on expression) as well as partial indexes (indexes on only some rows in the table; I don’t think even Oracle has this). Pg also has R-tree/GiST for spatial queries. Pg can also index [very] long BYTEA/TEXT (thousands of bytes and more) where Oracle/DB2/SQL Server/MySQL/etc can’t (though one wonders why someone would index huge blobs).
The one thing Pg lacks is physical table partitioning (splitting a [big] table into several partitions for speed).
I would like to see comparisions between
PostGreSql and Firebird 1.5 databases. MySql is not in same
league as postGreSql and Firebird. The feature set of postGreSql and Firebird are more similar as compared to MySql.
Even the licenses are better than MySql.
Wish Firebird had similar _free replication tools as postGreSql has.
I have been using MySql for last 2+ yrs and considering to converting to Firebird 1.5 or postGreSql.
The limited number of developers working on MySql is the main reason for its lack of features and bug fixes. Funny that they discourage people to use Foreign keys.
If you think between those two, just drop MySQL. In my opinion this is not a real database. There are still no constraints possible. You still cannot do subselects. The authentication management is quite horrible. I am very happy that PostgreSQL exists. And since the 7.x release the vacuum problems are gone (one of the view downsides in older postgres releases).
Here’s a good list (IMO): http://brainscraps.com/faq/pg_my.html
Then again, if MySQL is perfectly filling all your needs and you don’t plan to use the fancy features of PgSQL, there’s no point in switching. You should always consider your needs before choosing a tool. No need to use a sledgehammer to drive a 1″ wood nail.
It’s strange that it is a second time some author insists on comparing stable, production Postgres features with those of MySQL version that’s release is years ahead in the future.
This is no more than comparing some company’s roadmap with existing, released software.
How is the MySQL Gotchas page “hopelessly outdated” ? The site claims that “All gotchas have been tested in current MySQL versions (3.23.5x, 4.0x and 4.1alpha).” I’ve submitted several gotchas to the site, which were current as of 4.0.16 (and looking at the .17 and .18 changelogs, they haven’t been fixed).
with this article. It glossed over a lot. For instance, he said that indexing is essentially comparable in MySQL and PostgreSQL. It’s not. PG goes functional indexes and partial indexes. These are incredibly flexible, powerful tools (I’m eagerly awaiting PG 7.5, which will make better use of functional indexes).
Another thing he glossed over is DATE/TIME handling. MySQL’s calendar types are little more than strings – you can put just about anything into a MySQL DATE column, including invalid dates. The MySQL API is defined in terms of year 0, which never occured (so its results are meaningless). The PostgreSQL calendar types are incredibly flexible and perform much more rigid integrity checking.
He also should have covered MySQL’s attrocious NULL handling.
… Continues with the articles : “Blondes or brunettes : depends on your taste” and “Toyota or Ferari : depends on your income”
Also, I think Firebird should change its name to FireSQL, maybe then people wouldn’t keep forgetting it in articles like this
What does the author mean by ‘partial rollback of a transaction’. Nested transaction? I don’t recall Pg or MySQL has that at the moment.
He might be refering to transaction savepoints (which MySQL/Innodb supports, dunno for PgSQL)
Until mysql expands its feature set, I really can’t see mysql and postgres filling the same niche. I use mysql for some rather large content mangement systems and it’s been a real champ, but anything that involves any amount of real complexity (read as:things involving money) postgres makes life a LOT easier. I’ve been trying to find some comparisons between maxdb and postgres, but haven’t turned up much yet. any clues?
(and yes, I’m too lazy to do it myself)