link by Lukas Kahwe Smith @ 2008-06-03 11:06 CEST

This is going to be an unusual blog post, because I will continuously update it with features that MySQL still has on top of PostgreSQL, which is generally considered to be more feature rich. Some of these missing features can however hurt a lot. I am including MySQL 5.1 in here, since eventhough its not yet released as GA, more and more people have started to use it in production. At the same time I am also including 8.4. So in a way I am talking about what MySQL has on top of PostgreSQL by the end of the year. I am not going to include stuff like auto increment if there is something that is more or less equivalent with SERIAL. I am also not including features I consider unwise (like REPLACE). Finally I am skipping XML support, because I know both are working on improving the support, but I have not yet looked at it so closely. So here it goes: Multiple charsets/collations in the same DB (all the way down to the column level)

Choice of Row/Statement based replication to read able slaves (PostgreSQL 8.4 is only going to offer log file shipping based cold slaves)

Event Scheduler (PostgreSQL requires the external pgAdmin tool for this)

Hash/Key Partitioning (PostgreSQL only supports Range/List, though I hear that 8.4 might catch up here ..)

Portable data files across OS

Pluggable storage engines (even without a server restart)

Better 24/7 operations support due to less reliance on cleanup tasks (aka VACUUM)

More control over the strictness via SQL_MODEs (to some extend this is a disadvantage, because the global setting can be overridden in each session, making the life of DBA's miserable, but generally I consider flexibility a good thing) Some "softer" facts: More readily available, at hosters and more importantly in "Enterprise IT" departments (though MySQL is still usually not support, but at least its not forbidden)

More available OSS apps (or at the very least more tested, even if the app relies on some abstraction layer to also provide PostgreSQL support)

A larger community (everybody knows a bit of MySQL and its gotchas, there are more books and best practices blog posts etc. .. though PostgreSQL has less quirks to begin with) Feel free to correct me, expand my list or provide any other relevant feedback in a comment. Like I said I will update this post accordingly. Update [03/06/2008]:

PostgreSQL also supports pluggable full text indexes. Clarified point about VACUUM a bit. Update [04/06/2008]:

Added a "soft facts" section and note about SQL_MODEs.

Comments







link by hartmut @ 2008-06-03 12:06 CEST

Less reliance on cleanup tasks (aka VACCUM) to be fair this one depends on the storage engine, and any MVCC implementation has to clean up stale old version records somehow InnoDB seems to have found a more transparent way with its purge thread that always runs in parallel in the background. No need to schedule vacuum tasks in any way, stale rows are usually cleaned up "as soon as possible" in the background, but even this approach may lead to "interesting" situations if the purge thread is not able to keep up with version changes which can be triggered by high concurrency parallel updates or during *large* DML operations ...

link by Magnus @ 2008-06-03 13:06 CEST

You can certainly reconfigure the full text search in PostgreSQL without restart. As for VACUUM, with the capabilities of autovacuum in 8.3, most installations will not need to deal with manual VACUUM at all. Finally, I assume you're only counting in-core (which doesn't really work with the PostgreSQL development model, but that's a different discussion) - but if not, pgAdmin contains an event scheduling service. (same goes for replication, of course, with several different external options)

link by Gregory Haase @ 2008-06-03 14:06 CEST

I have seen a lot of chatter about a replication solution in PostgreSQL, but to me that is only one of the two deal killers. To the best of my knowledge, PostgreSQL still does NOT have the ability to upgrade in place. If you have upwards of 100GB of data, it not feasible to dump and re-import your entire database while sustaining only a minimal amount of downtime.

link by Lukas Kahwe Smith @ 2008-06-03 16:06 CEST

Autovacuum does solve a lot of the issues associated to PostgreSQL's MVCC approach. Of course this approach does have its benefits (for example a ROLLBACK should be less costly), but in terms of providing constant reliable 24/7 performance, the rollback log approach still seems like a plus in many situations. That being said, there is limited support for inplace UPDATES using a feature called HOT since 8.3 (released earlier this year):

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00353.php

link by FACORAT Fabrice @ 2008-06-03 17:06 CEST

FULLTEXT has been integrated inside postgreSQL core.

http://www.postgresql.org/docs/8.3/static/release-8-3.html

link by Lukas Kahwe Smith @ 2008-06-03 17:06 CEST

Right of course, the question was just if you can add/swap fulltext search algo's at runtime like you can do with the pluggable API in MySQL 5.1. According to Magnus this is possible so I will update the above accordingly.

link by Bill @ 2008-06-03 18:06 CEST

VACUUM sounds like it might have some advantages. Innodb tables can be fragmented with a lot of inserts and deletes. From looking at the docs, it sounds like Vacuum also reclaims the space and can be done with out locking anything. I'd like to see an inverse of this list showing the great things postgres does that won't be done in the next year for mysql.

link by Steffen @ 2008-06-03 20:06 CEST

Why do you think that REPLACE is unwise?

link by Bill @ 2008-06-03 20:06 CEST

Replace does a Delete then an Insert. It causes innodb table space fragmentation, because it always deletes. Also some people sometimes forget that its not an insert or update and forget to populate fields that they aren't updating.