<?plugin OldStyleTable 

 | Sequences | Some¹ | Some¹ | Some¹ | Some¹ | Yes 

 | Transactions | No | Yes | Yes | Yes | Yes 

 | Constraints | No | No | No | Yes | Yes 

 | Table level locking | Yes | Yes | Yes | Yes | Yes 

  

 ?> 

  

 # Only __auto_increment__ is supported 

 # Only with InnoDB tables 

 # Unless you use PostGIS which is a patch against PostgreSQL, but often lags behind the latest version 

 # There are several projects out there with implement heavy modifications to PostgreSQL to implement replication, some rely on adding triggers to your database 

  

  

 ---- 

  

 !! Some comments on [a comparison of exporting data to MySQL and PostgreSQL | http://www-css.fnal.gov/dsg/external/freeware/Repl_mysql_vs_psql.html]: 

  

  

  [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. 

  

  

  I can't say I know anything about [Java]'s [JDBC] interface, so I won't comment here. 

  

  

  [PostgreSQL] takes a hit because it wants your data to be correct by enforcing constraints. 

  [MySQL] [has limited referential constraints|http://dev.mysql.com/doc/refman/5.0/en/create-table.html]. 

  

  

  Both by default don'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. 

  

  

  [PostgreSQL] has subrow locking of data and 

  [Multiversion Concurrency Control | http://www.postgresql.org/docs/7.4/interactive/mvcc.html]; 

  [MySQL] still usually (under what conditions, which versions?) locks the entire table. 

  

  

  [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. 

  

  

  [PostgreSQL]'s ALTER TABLE is lacking compared to [MySQL]. 

  In theory, a carefully designed schema should preclude any need for altering 

  types of columns, but in practice requirements change during the life of a DataBase. 

  

 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 

  

 * [PostgreSQL or MySQL?|http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html]