Differences between version 26 and predecessor to the previous major change of PostgresVsMysql.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 26 | Last edited on Tuesday, February 22, 2005 5:43:24 pm | by StuartGalloway | Revert |
| Older page: | version 24 | Last edited on Monday, August 2, 2004 11:38:02 am | by PhilMurray | Revert |
@@ -1,75 +1,66 @@
-Someone wanted a page of features that
[PostgreSQL] supported that
[MySQL
] didn
't, so here we go:
+|^ __Feature__ | __[MySQL] 3.x__ | __[MySQL] 4..x__ | __[MySQL] 4.1.x__ | __
[PostgreSQL]__
+| SubSelects | 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[1] | Some[1] | Some[1] | 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[2] | Yes[2] | Yes
+| Stored Procedures | No | No | No | Yes
+| Row level locking | Yes[3] | Yes[3] | Yes[3] | 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
+[1] Only __auto_increment__ is
supported
+
[2
] But it
's spelled __INSERT INTO ... SELECT__
+[3] Only with InnoDB tables
-|__Feature__|__
[MySQL] 3
.x__|__
[MySQL
] 4..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|http://www
.postgresql.org/docs/7.4/interactive/mvcc.html]) | No | No | No | Yes
+[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
.
-''Wow
, so is there anything
that [MySQL] can
do that [PostgreSQL]
can't? -- AlastairPorter''
+[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.
-[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
.
+!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]'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
.
+[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
.
-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.
+!! 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
]:
-(Actually one just has
to do "alter table alter column" to change a column's NULL or DEFAULT attributes)
+; __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.
--- PerryLorier
+; __STABILITY__: I can't say I know anything about [Java]'s [JDBC] interface, so I won't comment here.
-Look what I found
...
+; __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?
-http://www-css.fnal.gov/dsg/external/freeware/Repl
_mysql
_vs
_psql
.html
+;
__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
.
-Would someone please comment on this one
?
+; __LOCKING AND CONCURRENCY SUPPORT__: [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.
--- MatthiasDallmeier
+; __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.
-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
.
+; __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
.
-;__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?
+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
.
-;__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
.
+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
.
-;__LOCKING AND CONCURRENCY SUPPORT__:
postgres has subrow locking
of data
and Multiversion Concurrency Control ([MVCC|http://www
.postgresql
.org/docs/7.4/interactive/mvcc.html]);
mysql still usually (under what conditions, which versions?) locks the entire table
.
+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
.
-;__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
.
+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
.
-;__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.
+!! See also
-http://www.phpbuilder.com/columns/tim20000705.php3?page=1
+* [MySQL] has a page where it will unit test the [DBMS]s of your choice and give [a breakdown on whether the feature was available | http://www.mysql.com/information/features.html].
+* An indepth article on [real world performance testing of MySQL and PostgreSQL |
http://www.phpbuilder.com/columns/tim20000705.php3?page=1].
+* [Issues to be aware of when using MySQL | http://sql-info.de/mysql/gotchas.html]
