|Feature||MySQL 3.x||MySQL 4.0.x||MySQL 4.1.x||MySQL 5.x||PostgreSQL|
|Foreign Key relationships||No||Yes||Yes||Yes||Yes|
|Foreign Key constraints||No||No||No||Yes||Yes|
|Indexing on non trivial types||No||No||No||Yes||Yes|
|Transactional DDL statements||No||No||No||No||Yes|
|OO (Inheritance of tables)||No||No||No||No||Yes|
|INSERT ... SELECT||No||Yes||Yes||Yes||Yes|
|Row level locking||Yes2||Yes2||Yes2||Yes2||Yes|
|Table level locking||Yes||Yes||Yes||Yes||Yes|
|Geospatial Datatypes (ie WKT)||No||Yes||Yes||Yes||No3|
|Multiversion Concurrency Control||No||No||No||No||Yes4|
|Native replication (M/M, M/S, Ring)||Yes||Yes||Yes||Yes||No5|
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.
lib/main.php:944: Notice: PageInfo: Cannot find action page