whenpenguinsattack.com

Saturday, May 13, 2006

mysql vs postgres

By Justin Silverton


postgres/mysql comparison chart



FeatureMysql 3.xMysql 4.0Mysql 5.xPostgres 7.x
SubselectsNoPartialYesYes
ViewsNoNoYesYes
Foreign Key RelationshipsNoYesYesYes
Foreign Key ConstraintsNoNoYesYes
TriggersNoNoYesYes
Indexing (non-trivial types)NoNoYesYes
SequencesPartialPartialPartialYes
TransactionsNoYesYesYes
Table InheritanceNoNoNoYes
Async. NotificationsNoNoNoYes
ConstraintsNoNoYesYes
Select IntoNoYesYesYes
Stored ProceduresNoNoYesYes
Row-Level LockingYesYesYesYes
Table-Level LockingYesYesYesYes
Geospatial datatypesNoYesYesNo
Native ReplicationYesYesYesNo




Companies using postgres



These are links to case studies, showcasing the successful usage of postgres in an organization


BASF (PDF format)
Mohawk Software
Proximity (PDF format)
Radio Paradise (PDF format)
Shannon Medical Center
Spiros Louis Stadium (PDF format)
The Dravis Group OSS Report (PDF format)
Vanten Inc.


Companies using mysql




Patypoker.com (PDF format)
Greyhound bus - data website (PDF format)
Sandstorm (PDF format)
Leapfrog schoolhouse (PDF format)
NetQos
Sony (PDF format)
Dell (PDF format)
Friendster.com

So which one is better?

Both postgres and mysql have had success on large-scale websites and they each have their benefits:

Where postgres wins:

1) faster with more complex queries

2) ACID compliant

3) embedded language capibility (Perl, PHP, TCL, and PG/PLSQL)

4) on average, can handle more concurrent connections

where mysql wins:

1) more wide-spread support and usage with ISPs and the open source commuity

2) robust replication

3) easier for newcomers to use

4) on average, faster per query

10 Comments:

  • Wot's this with PostgreSQL not having geospatial datatypes? That's what PostGIS is for.

    http://postgis.refractions.net/

    By Anonymous Anonymous, at 5:04 PM  

  • thanks for the link. It was very informative.

    I will update the original post.

    By Blogger justin silverton, at 6:16 PM  

  • Also, MySQL 5 has beginning support for horizontal partitioning, something that's very useful for large databases.

    By Anonymous Anonymous, at 6:45 PM  

  • maybe you should take into consideration the latest postgresql version, 8, not 7

    By Blogger Lawrence, at 3:04 AM  

  • Postgres has support for geospatial data through PostGIS.

    With InnoDB as a backend, MySQL 4.0 (and later) supports foreign key constraints.

    By Anonymous seth, at 7:23 AM  

  • Postgres has had geospatial types for almost an eternity.

    Oh. And it would be good if you put up the details for postgres 8.x, which has been out longer than Mysql 5

    Also, Postgres 8.x supports partitioned indexes.

    Also, Postgres supports Pro*C, similar to Oracle.

    By Anonymous Anonymous, at 8:59 AM  

  • hey guys,

    I wish I could add all of your informative info to my article, but blogspot.com has marked my blog as spam (I don't know why). I think it has something to do with the number of people hitting this article per second. When this gets straightened out, I will update the article or post a new one with some more detailed information on the differences between the two databases.

    By Blogger justin silverton, at 9:11 AM  

  • if someone commenting is interested in additions to my article or there is someone that would like to be a poster, feel free to send me over an email here: justin@whenpenguinsattack.com

    By Blogger justin silverton, at 11:01 AM  

  • Some more comments...

    PostgreSQL has many replication options, Slony I being the most popular (and probably more robust than anything non-commercial for MySQL). Replication is not built in because it's next to impossible to build a replication solution that will satisfy all the needs of all the users. It has nothing to do with the quality of the replication options out there.

    On features, PostgreSQL supports many, many different languages inside the database. C, Java, Perl, Python, Ruby come to mind. Heck, even sh is supported.

    PostgreSQL enforces data integrity. MySQL didn't until 5.0, and 5.0 arguably makes things worse because even if you do turn strict mode on, any query may turn it back off. If you're not sure what I'm talking about, run the following with strict mode off (or on an older version):

    CREATE TABLE t(t tinyint);
    INSERT INTO t VALUES(300);
    SELECT * FROM t;

    You'll get 127 back. MySQL's handling of 'Feb 31' is also rather "interesting". See also http://sql-info.de/mysql/gotchas.html

    MySQL also has only partial support for ACID. How is it partial? If you don't compile with InnoDB support, it will silently create all your tables as MyISAM, which means no ACID (no refferential integrity, either). If you fat-finger InnoDB in your CREATE TABLE statement, you'll also silently get a MyISAM table.

    I would argue that PostgreSQL has much better support than MySQL. First, the community support for PostgreSQL is absolutely top-notch. And if that's not good enough there's a whole slew of companies you can buy support from: http://www.postgresql.org/support/ Googling for 'mysql support' on the other hand turns up MySQL itself and a web forum. I will grant that MySQL hosting is more common than PostgreSQL hosting, but PostgreSQL hosting is by no means hard to find.

    MySQL being faster per query is a highly questionable assertion. Every comparison of InnoDB tables and PostgreSQL shows the two to be either even or PostgreSQL comming out ahead. Every case I've seen where MySQL is substantially faster is either using MyISAM (which since it has no data integrity and isn't ACID isn't a valid comparison), or is a very simple single user test (which is rather meaningless since it's unlikely that performance will matter much at all), or both.

    Disclosure: I work for a company that provides PostgreSQL support. These views are my own, and I've had them since long before I was a PostgreSQL consultant.

    By Blogger Decibel, at 1:54 PM  

  • You would do well to include more advanced features such as distributed transactions, which become crucial when one moves beyond simple applications. You also did't consider pessimistic-locking-versus-MVCC, which is a big win for databases such as Firebird, PostgreSQL, Oracle, and MS SQL 2005 over the likes of MySQL.

    And what's up with comparing MySQL 5.x to PostgreSQL 7.x? PostgreSQL 8.x has been out for a lot longer than MySQL 5.x.

    By Anonymous Anonymous, at 4:14 PM  

Post a Comment

Links to this post:

Create a Link

<< Home