16 Jul, 2008
MySQL vs Postgres
Posted by Bhavin Turakhia
In my perpetual comparison between MySQL and Postgres I am beginning to lean towards MySQL offlate. There are many reasons, but a short list that is currently relevant to us is here -
- MySQL supports multiple backend storage engines providing more flexibility of choice. For instance one can choose MyISAM for tables where transactions and ACID compliance does not matter, and gain a performance advantage. Or one can use a Memory storage engine for temporary in-memory tables
- InnoDB supports optional MVCC, thus providing best of both worlds
- MySQL supports native replication and shared nothing clusters
- MySQL has better integration with memcached
- MySQL uses multi-threading as opposed to process-forking, making it less heavy
- More people are using MySQL than Postgres – eg Facebook, Youtube etc
- MySQL is now owned by Sun, and despite their recent lay-offs they are a company I respect
There are many other reasons, but currently these are the ones that are relevant to the products we are working on.
http://spyced.blogspot.com/2006/12/benchmark-postgresql-beats-stuffing.html
If you don’t need ACID compliance, don’t use the SQL hammer. There are better alternatives (memcached, BerkeleyDB for example).
PostgreSQL supports “in memory” tables (they are termed as TEMP tables there).
WRT replication: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php
Sun employs a fair number of PostgreSQL developers, as does RedHat.
Yahoo! doesn’t use MySQL for important stuff, just long term caching. Note that NTT is slightly more interesting than Facebook for our use case(s).
i don’t know much about this discussion in detail. but as per my information regarding “in memory” tables.
mysql have also a MEMORY (HEAP) storage engine.
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
Postgres is in a different league to MySQL. Let me state up front that I like both. I think that MySQL has done an awesome job of getting market share and has really good 3rd party tools around it.
But it’s a lightweight database for very lightweight tasks.
Postgresql is what I call a “real” database with proper transactions, a real procedure language that is nearly an exact copy of Oracle’s and you can add many other lanugages such as Java, Ruby, Perl, Python if you don’t want to use the PL/PgSQL
You have triggers, rules and database procedures. You have proper standard SQL. You have very advanced replication, a write ahead logger.
You have proper schemas and proper roles to which users can be assigned. You have table spaces as well. The combination of schemas and table spaces is essential for a serious, production, high availability database. This is big iron stuff, not a lightweight database for a 1U rackmount web server. This is for serious big iron usage.
But all this doesn’t come at a performance cost. Postgres is as easy to install (and I would argue easier to manage) on a 1U rack mount web sever as MySQL. It plays very well in the areas that MySQL plays in. It’s just that while MySQL is great for a lake, Postgres is great for an entire ocean too. It’s great for both.
But best of all, Postgres is both a relational database AND and object database. It makes life so much easier have objects and inheritance. That combined with the roles, schemas, table spaces, triggers, rules and database procedures make this a replacement for Oracle.
MySQL has a very long way to go before it can replace Oracle. It can replace SQL Server, for sure, and I would argue that it SHOULD replace SQL Server. It’s a darned sight better than SQL Server. But it ain’t in Oracle’s playing field yet, whereas Postgres in many ways bets Oracle on there are only a few things where Oracle beats it, mostly esoteric stuff like it doesn’t have an implementation of queues (which don’t work too well in Oracle anyway) and more cumbersome SQL extensions.
@kieser: with the innoDB engine MySQL does offer the transactional features. Also check the new Falcon storage engine (http://dev.mysql.com/doc/refman/6.0/en/se-falcon.html) which even supports true MVCC. I do like the MySQL storage engine model which permits one to plugin the most relevant storage engine for the task and gain performance benefits.
Believe me – this is not a rant against Postgres. We are heavy postgres users. But there are needs that postgres cannot fulfill – for instance when we have to run a simple select on a table with gazillion rows, no transactions, no MVCC, no locking, no referential integrity, no foreign keys required
Hi,
I’m also in the dilemma of which one is better. While PostgreSQL is catching on SPEED, MySQL is adding features after features. I believe that will ’somewhat’ equate in the near/far future. For now, it turned out that I’m using both DB… depending on client’s CURRENT requirements (and probably 2-3 years from now).
I personally believe that building up speed is far more “risk-less” than building up “features”.
MySQL having multiple database engine is a good and bad thing.
The goods are stated above. The bad thing is that InnoDB is not used in Clustered environment (correct me if I’m wrong). We have no choice but to use the less-feature-rich MyISAM. It means that most “features” in 5.1 will not work in CLUSTERED ENVIRONMENT.
For the meantime, let’s all enjoy the goodness of Open Source. (I hope MySQL will have only 1 license in the future).
BTW Bradley, nice review…
@arnold: we too use both in our dev environments. As a techie I have always remained platform-agnostic and endeavored to apply the best platform/tool to the task at hand
1) What does “MySQL has better integration with memcached” mean?
2) Agree with Devdas: Don’t bother with a database if you don’t need ACID compliance – In this instance there are lots of other options that are faster than MySQL. If you do need ACID compliance and thus use the InnoDB table engine let’s *please* dispell the arguments that MySQL is faster than PostgreSQL – It’s not.
3) “MySQL is now owned by Sun…..”
….and Sun are now owned by Oracle, so lets see where it goes from here.
I’ve developed a number of sites using MySQL but spent quite a bit of time a while ago converting them all to PostgreSQL – and haven’t looked back since.
MySQL cluster has own specific data engine NDB
MySQL has UDF to integrate with memcached, so it can be done in triggers
http://tangent.org/586/Memcached_Functions_for_MySQL.html
I’m also currently choosing between MySQL and PostgeSQL. Whereas PostgreSQL seems like full-blown DB, MySQL has very nice features I was able to take advantage of and planning to use in current ptoject:
* blackhole data storage – for dumping logs and metrics (lots and lots of them)
* memcached UDF
* new cluster 7.0 with shared nothing architecture, partitioning, ability to store data on disk (it was all in memory before), OpenLDAP frontend (instead of SQL), or other frontends like mod_memcached for lighttpd or similar for Apache; those would access NDB data nodes directly
* mixing different data engines InnoDB, MyISAM, blackhole, etc. for different scenarios of data usage (e.g. masted DB is InnoDB and replicated read-only is MyISAM)
* flexible replication that allows implement different scenarios (save to binary log and replay later…)
PostgeSQL as DB looks more attractive (ACID, data types, PL/pgSQL), but I’m not sure if flexibility of MySQL is achievable with PostgreSQL.
How is PostgreSQL server cluster comparing to MySQL one?
Are there UDF to integrate with memcached?
Replications, are they more or less robust then MySQL replication?
Read-only tables? I assume since it MVCC read-only access should be very fast. Is it true?
Massive log/metrics dumping into DB? Would PosgreSQL survive?
Support? Would it be good enough for time critical applications?