Penguin
Note: You are viewing an old revision of this page. View the current version.
 Feature   MySQL 3.x   MySQL 4.0.x   MySQL 4.1.x   MySQL 5.x]   PostgreSQL 
 SubSelect  No   Some   Yes   Yes   Yes 
 Views   No   No   No   Yes   Yes 
 Foreign Key relationships   No   Yes   Yes   Yes   Yes 
 Foreign Key constraints   No   No   No   Yes   Yes 
 Triggers   No   No   No   Yes   Yes 
 Indexing on non trivial types   No   No   No   Yes   Yes 
 Sequences   Some1   Some1   Some1   Some1   Yes 
 Transactions   No   Yes   Yes   Yes   Yes 
 OO (Inheritance of tables)   No   No   No   No   Yes 
 Async Notifications   No   No   No   No   Yes 
 Constraints   No   No   No   Yes   Yes 
 SELECT INTO   No   Yes2   Yes2   Yes2   Yes 
 Stored Procedures   No   No   No   Yes   Yes 
 Row level locking   Yes3   Yes3   Yes3   Yes3   Yes 
 Table level locking   Yes   Yes   Yes   Yes   Yes 
 Geospatial Datatypes (ie WKT)   No   Yes   Yes   Yes   No4 
 Multiversion Concurrency Control   No   No   No   No   Yes5 
 Native replication (M/M, M/S, Ring)   Yes   Yes   Yes   Yes   No6 
  1. Only auto_increment is supported
  2. But it's spelled INSERT INTO ... SELECT ("SELECT INTO" is actually a Sybase SQL extension, "INSERT INTO ... SELECT" is actually the SQL standard)
  3. Only with InnoDB tables
  4. Unless you use PostGIS which is a patch against PostgreSQL, but often lags behind the latest version
  5. Beware! As a result of MVCC, PostgreSQL cannot optimize a simple SELECT count(*) FROM table query. It will always use a sequential scan, this becomes very slow even on a table with only a few hundred thousand rows. This type of query is very common in webapps, as a result you have to design your database and app around it. People often assume this type of query is free in terms of processing. In MySQL it is optimised away.
  6. There are several projects out there with implement heavy modifications to PostgreSQL to implement replication, some rely on adding triggers to your database

UniForum posted studies that concluded that MySQL is slightly faster per query, but PostgreSQL can handle more concurrent connections. If you have that kind of load you really want to be looking at having redundancy and/or a bigger/better DataBase (Oracle?), though.


Some comments on a comparison of exporting data to MySQL and PostgreSQL:

SPEED
PostgreSQL in most instances is nearly the same speed as MySQL. In many more complicated instances MySQL cannot index on fields where PostgreSQL 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
PostgreSQL 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. PostgreSQL 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
PostgreSQL by default doesn't listen on the network as a security feature. PostgreSQL's security is far more advanced than MySQL's allowing things like IDENT auth etc.
LOCKING AND CONCURRENCY SUPPORT
PostgreSQL has subrow locking of data and Multiversion Concurrency Control; MySQL still usually (under what conditions, which versions?) locks the entire table.
LARGE OBJECTS
PostgreSQL'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
PostgreSQL's ALTER TABLE is lacking compared to MySQL. However, a carefully designed schema should preclude any need for altering types of columns, though in practice requirements 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 changes need to be made carefully. PostgreSQL requires to ALTER TABLE SET NOT NULL in an extra step after adding a column to allow you to put proper data into the new column before the NOT NULL constraint is added.

I hope that I am being consistent with proper usage of this Wiki.

I have read many postings saying that postgres was slow and is getting faster and mysql is basic but is getting more sophisticated features.

Several years ago I used a postgres database and became quite familar and comfortable with it. Later I switched to MySQL because at the time postgres had a limit of 8k per row and I want to store free text in HTML format. I also lost my linux server and mysql would run quite well on windows.

I stayed with MySQL because I could add free text index to the text fields. When postgres 8.0 came out for windows I thought I would switch back to postgres and try their Tsearch2 index, because I still missed lots of things about Postgres. During the switch I found still postgres familiar and I loved the stored procedures. Sadly however I found postgres incredibly slow and my users refused to accept the postgres text indexing as it way too slow. The interface with proper boolean structures and stemming was much nicer on postgres than mysql. PgAdmin is a very nice tool.

With some regret I am going back to MySQL, mainly due to speed. For instance I have a fully index table with about 250,000 rows with full text that I need to create "foreign keys" on another table. Updating the foreign key from another table takes maybe 10 minutes on postgres and less than a minute on mysql. (These are not "real" foreing keys because I think the triggering mechanism would slow updates even more. I tried dropping indexs and it didnt make much diffference. Creating a new table and re-indexing it was faster but this is clumsy. Why not just go back to mysql.


See Also