mysql vs postgres
By Justin Silverton
postgres/mysql comparison chart
| Feature | Mysql 3.x | Mysql 4.0 | Mysql 5.x | Postgres 7.x |
| Subselects | No | Partial | Yes | Yes |
| Views | No | No | Yes | Yes |
| Foreign Key Relationships | No | Yes | Yes | Yes |
| Foreign Key Constraints | No | No | Yes | Yes |
| Triggers | No | No | Yes | Yes |
| Indexing (non-trivial types) | No | No | Yes | Yes |
| Sequences | Partial | Partial | Partial | Yes |
| Transactions | No | Yes | Yes | Yes |
| Table Inheritance | No | No | No | Yes |
| Async. Notifications | No | No | No | Yes |
| Constraints | No | No | Yes | Yes |
| Select Into | No | Yes | Yes | Yes |
| Stored Procedures | No | No | Yes | Yes |
| Row-Level Locking | Yes | Yes | Yes | Yes |
| Table-Level Locking | Yes | Yes | Yes | Yes |
| Geospatial datatypes | No | Yes | Yes | No |
| Native Replication | Yes | Yes | Yes | No |
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
11 Comments so far
Leave a reply
Wot’s this with PostgreSQL not having geospatial datatypes? That’s what PostGIS is for.
http://postgis.refractions.net/
thanks for the link. It was very informative.
I will update the original post.
Also, MySQL 5 has beginning support for horizontal partitioning, something that’s very useful for large databases.
maybe you should take into consideration the latest postgresql version, 8, not 7
Postgres has support for geospatial data through PostGIS.
With InnoDB as a backend, MySQL 4.0 (and later) supports foreign key constraints.
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.
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.
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
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.
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.
Postgres DOES have native geospatial datatypes. These are pretty useles IMHO, & PostGIS should be used instead for OGC SFS compliance. MySQL claims to have OGC compliant spatial datatypes, but any query on these is likely to return wrong answers as queries only test against the bounding box of features, thus asking for the points within in a circle will return all points in the MBR of the circle. I suggest such a dysfunctional implementation is worse than not having the capability at all.