Penguin
Note: You are viewing an old revision of this page. View the current version.

Someone wanted a page of features that PostgreSQL supported that MySQL didn't, so here we go:

|Feature|MySQL 3.x|MySQL 4.0.x|MySQL 4.1.x|PostgreSQL |SubSelects?| No | Limited | Yes | Yes |Views | No | No | No | Yes |Foreign Key relationships | No | Yes | Yes | Yes |Foreign Key constraints | No | No | No | Yes |Triggers | No | No | No | Yes |Indexing on non trivial types | No | No | No | Yes |Sequences | Partial (auto_increment) | Partial (auto_increment) | Partial (auto_increment) | Yes |Transactions | No | Yes | Yes | Yes |OO (Inheritance of tables) | No | No | No | Yes |Async Notifications | No | No | No | Yes |Constraints | No | No | No | Yes |SELECT INTO | No | Yes (INSERT INTO ... SELECT) | Yes (INSERT INTO ... SELECT) | Yes |Stored Procedures | No | No | No | Yes |Row level locking | Yes (with Innodb) | Yes (with Innodb) | Yes (with Innodb) | Yes |Table level locking | Yes | Yes | Yes | Yes |Multiversion Concurrency Control (MVCC) | No | No | No | Yes

Wow, so is there anything that MySQL can do that PostgreSQL can't? -- AlastairPorter

MySQL's big advantage is that it's stupidly simple. If you don't know much about SQL, you can get in and use MySQL and some of the great tools it has (phpMyAdmin!) to do pretty much what you want.

MySQL's big disadvantage is that it's stupidly simple. If you have learnt about SQL, you find out that in MySQL it is very hard to do complicated things, and, if you do try and do complicated things, it bogs down and goes very slow.

In recent studies I've seen (Posted by !UniForum?) MySQL is slightly faster per query, but PostgreSQL can handle more concurrent connections, not that you really care, because if you have that kind of load you really want to be looking at having redundancy and/or a bigger/better database (Oracle?).

MySQL has some nice features like the ability to modify tables schema easily (PostgreSQL won't let you add a new NOT NULL row in one step for example), and doesn't require regular maintenance that PostgreSQL does (you have to regularly VACUUM tables on PostgreSQL if they have a high modification rate), but MySQL is let down by the complete lack of advanced features that you end up needing.

(Actually one just has to do "alter table alter column" to change a column's NULL or DEFAULT attributes)

-- PerryLorier

Look what I found...

http://www-css.fnal.gov/dsg/external/freeware/Repl_mysql_vs_psql.html

Would someone please comment on this one?

-- MatthiasDallmeier

I'll give a whirl.

SPEED
Postgres in most instances is nearly the same speed as mysql. In many more complicated instances mysql cannot index on fields where postgres can, leading to order of magnitude speedups for postgres.
STABILITY
I can't say I know anything about java's JDBC interface, so I won't comment here.
DATA INTEGRITY
Postgres takes a hit because it wants your data to be correct by enforcing constraints. mysql has no constraints and in the 3.x tree (which is the most widely used) has no provisions for transactions or rollbacks. postgres exits gracefully on diskfull, mysql corrupts your tables. If you're storing data in a database surely you want your data to be correct?
SPECIAL SERVER SIDE FEATURES
Postgres by default doesn't listen on the network as a security feature, postgres's security is far more advanced than mysql's allowing things like IDENT auth etc.
LOCKING AND CONCURRENCY SUPPORT
postgres has subrow locking of data and Multiversion Concurrency Control (MVCC); mysql still usually (under what conditions, which versions?) locks the entire table.
LARGE OBJECTS
postgres's interface for large objects allows for flexible retrieval of data without having to transmit the entire large object from the database when it is not needed, speeding up many queries.
ALTER TABLE
postgres's alter table is lacking compared to mysql, however, if you have designed your schema well then you wouldn't normally be altering types of columns, except that requirements usually change during the life of a database. Changing types of columns can have wide ranging effects as the rest of your application may need to be changed on the fly somehow, so one needs to make such changes carefully. AndreGauthier? : Postgres can add columns with alter table and then use alter table set not null in the later releases (this forces you to put proper data into the new column before the not null constraint is added).

The major reason why mysql is a PITA is that it doesn't support many standard features such as "views" and "subselects". People may scoff that these are "syntactic sugar" but they seriously improve the maintainability of your database, when combined with well thought out constraints you can trust your data to be correct. Mysql is a database that if you do any SQL in for a reasonable length of time you'll quickly reach the limitations of.

There are also plenty of easy to use admin tools for postgres these days including psql, pgAccess, pgAdmin, phpPgAdmin, Tora. With the limitations of MySQL I see no reason to use it except for the future technical chalenge of migrating all the data into a more extendable database.


See also: http://sql-info.de/mysql/gotchas.html for issues to be aware of when using mysql.


MySql? has a page where it will unit test the DBMSs of your choice and give a breakdown on whether

the feature was available or not.

http://www.mysql.com/information/features.html

This site has an indepth article on real world performance testing of Mysql and !PostGres?.

http://www.phpbuilder.com/columns/tim20000705.php3?page=1