Differences between version 31 and predecessor to the previous major change of PostgresVsMysql.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 31 | Last edited on Wednesday, December 14, 2005 10:26:46 am | by PhilMurray | Revert |
| Older page: | version 29 | Last edited on Tuesday, September 6, 2005 11:55:26 am | by PerryLorier | Revert |
@@ -1,33 +1,37 @@
<?plugin OldStyleTable
-|^ __Feature__ | __[MySQL] 3.x__ | __[MySQL] 4..x__ | __[MySQL] 4.1.x__ | __[PostgreSQL]__
-| SubSelect~s | No | Some | 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 | Some<sup>1</sup> | Some<sup>1</sup> | Some<sup>1</sup> | 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<sup>2</sup> | Yes<sup>2</sup> | Yes
-| Stored Procedures | No | No | No | Yes
-| Row level locking | Yes<sup>3</sup> | Yes<sup>3</sup> | Yes<sup>3</sup> | Yes
-| Table level locking | Yes | Yes | Yes | Yes
-| [Multiversion Concurrency Control | http://www.postgresql.org/docs/7.4/interactive/mvcc.html] | No | No | No | Yes
+|^ __Feature__ | __[MySQL] 3.x__ | __[MySQL] 4..x__ | __[MySQL] 4.1.x__ | __[MySQL] 5.x]
__ | __[PostgreSQL]__
+| SubSelect~s | 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 | Some<sup>1</sup>
| Some<sup>1</sup> | Some<sup>1</sup> | Some<sup>1</sup> | 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 | Yes<sup>2</sup>
| Yes<sup>2</sup> | Yes<sup>2</sup> | Yes
+| Stored Procedures | No | No | No | Yes
| Yes
+| Row level locking | Yes<sup>3</sup>
| Yes<sup>3</sup> | Yes<sup>3</sup> | Yes<sup>3</sup> | Yes
+| Table level locking | Yes | Yes | Yes | Yes | Yes
+| Geospatial Datatypes (ie WKT) | No | Yes | Yes | Yes | No<sup>4</sup>
+| [Multiversion Concurrency Control | http://www.postgresql.org/docs/7.4/interactive/mvcc.html] | No
| No | No | No | Yes<sup>5</sup>
+| Native replication (M/M, M/S, Ring) | Yes | Yes | Yes | Yes | No<sup>6</sup>
+
?>
# Only __auto_increment__ is supported
-# But it's spelled __INSERT INTO ... SELECT__
+# But it's spelled __INSERT INTO ... SELECT__ ("SELECT INTO" is actually a Sybase SQL extension, "INSERT INTO ... SELECT" is actually the SQL standard)
# Only with InnoDB tables
+# Unless you use PostGIS which is a patch against PostgreSQL, but often lags behind the latest version
+# __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.
+# There are several projects out there with implement heavy modifications to PostgreSQL to implement replication, some rely on adding triggers to your database
-[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 (like phpMyAdmin) to do pretty much what you want. These days however there are plenty of easy to use admin tools for [PostgreSQL] including psql, pgAccess, pgAdmin, phpPgAdmin, Tora.
-
-[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. People may scoff that features such as "views" and "subselects" are "syntactic sugar" but they seriously improve the maintainability of your database, and when combined with well thought out constraints you can trust your data to be correct.
~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.
+
[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 a single step for example), and doesn't require the 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.
----
