Differences between version 28 and predecessor to the previous major change of PostgresVsMysql.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 28 | Last edited on Saturday, August 13, 2005 11:13:58 pm | by AristotlePagaltzis | Revert |
| Older page: | version 24 | Last edited on Monday, August 2, 2004 11:38:02 am | by PhilMurray | Revert |
@@ -1,75 +1,102 @@
-Someone wanted a page of features that
[PostgreSQL
] supported that
[MySQL] didn't, so here we go
:
+<?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__|__[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
+# Only
__auto
_increment
__ is supported
+# But it's spelled
__INSERT INTO ... SELECT__
+# Only
with InnoDB tables
-''Wow, so
is there anything
that [MySQL
] can do that
[PostgreSQL] can't? -- AlastairPorter''
+[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 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
.
+[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 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
.
+~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
.
-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 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
.
-[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.
+----
-(Actually one just has
to do "alter table alter column" to change a column's NULL or DEFAULT attributes)
+!! 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]:
--- PerryLorier
+__SPEED__:
-Look what I found.
..
+ [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
.
-http://www-css.fnal.gov/dsg/external/freeware/Repl
_mysql
_vs
_psql.html
+__STABILITY
__:
-Would someone please
comment on this one?
+ I can't say I know anything about [Java]'s [JDBC] interface, so I won't
comment here.
--- MatthiasDallmeier
+__DATA INTEGRITY__:
-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
.
+ [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?
-;
__STABILITY
__: I can't say I know anything about java's [JDBC] interface, so I won't comment here.
+__SPECIAL SERVER SIDE FEATURES
__:
-;__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?
+ [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
.
-;
__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.
+__LOCKING AND CONCURRENCY SUPPORT
__:
-;__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.
+ [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.
-;
__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.
+__LARGE OBJECTS__:
-;
__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)
.
+ [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 <tt>NOT NULL</tt>
+
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.
+I hope
that I am being consistent
with proper usage
of this Wiki
.
-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
.
+I have read many postings saying that postgres was slow and is getting faster and mysql is basic but is getting
more sophisticated features
.
-----
-See also: http://sql-info
.de/mysql/gotchas.html for issues
to be aware
of when using
mysql.
+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
.
-----
-!MySql has a page where it will unit test
the DBMSs
of your choice
and give a breakdown on whether
-feature
was available or not
.
+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
.
-http://www
.mysql.com/information/features
.html
+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
.
+
+----
-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
+* Bob Ippolito [hates MySQL | http://bob.pythonmac.org/archives/2005/08/12/mysql-hate/]
+* [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]
