Penguin
Recent Changes
Blame: PostgresVsMysql
EditPageHistoryDiffInfoLikePages
Annotated edit history of PostgresVsMysql version 38, including all changes. View license author blame.
Rev Author # Line
28 AristotlePagaltzis 1 <?plugin OldStyleTable
37 AristotlePagaltzis 2 |^ __Feature__ | __[MySQL] 3.x__ | __[MySQL] 4.0.x__ | __[MySQL] 4.1.x__ | __[MySQL] 5.x__ | __[PostgreSQL]__
3 | SubSelect~s | No | Some | Yes | Yes | Yes
4 | Views | No | No | No | Yes | Yes
5 | Foreign Key relationships | No | Yes | Yes | Yes | Yes
6 | Foreign Key constraints | No | No | No | Yes | Yes
7 | Triggers | No | No | No | Yes | Yes
8 | Indexing on non trivial types | No | No | No | Yes | Yes
30 PhilMurray 9 | Sequences | Some<sup>1</sup> | Some<sup>1</sup> | Some<sup>1</sup> | Some<sup>1</sup> | Yes
10 | Transactions | No | Yes | Yes | Yes | Yes
37 AristotlePagaltzis 11 | Transactional DDL statements | No | No | No | No | Yes
12 | OO (Inheritance of tables) | No | No | No | No | Yes
13 | Async Notifications | No | No | No | No | Yes
30 PhilMurray 14 | Constraints | No | No | No | Yes | Yes
37 AristotlePagaltzis 15 | INSERT ... SELECT | No | Yes | Yes | Yes | Yes
16 | Stored Procedures | No | No | No | Yes | Yes
17 | Row level locking | Yes<sup>2</sup> | Yes<sup>2</sup> | Yes<sup>2</sup> | Yes<sup>2</sup> | Yes
30 PhilMurray 18 | Table level locking | Yes | Yes | Yes | Yes | Yes
37 AristotlePagaltzis 19 | Geospatial Datatypes (ie WKT) | No | Yes | Yes | Yes | No<sup>3</sup>
20 | [Multiversion Concurrency Control | http://www.postgresql.org/docs/7.4/interactive/mvcc.html] | No | No | No | No | Yes<sup>4</sup>
21 | Native replication (M/M, M/S, Ring) | Yes | Yes | Yes | Yes | No<sup>5</sup>
30 PhilMurray 22
28 AristotlePagaltzis 23 ?>
24
25 # Only __auto_increment__ is supported
30 PhilMurray 26 # Only with InnoDB tables
27 # Unless you use PostGIS which is a patch against PostgreSQL, but often lags behind the latest version
37 AristotlePagaltzis 28 # __Beware!__ As a result of MVCC, PostgreSQL cannot optimize a simple <tt>SELECT COUNT(*) FROM table</tt> 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 somewhat common in webapps (though rarely necessary), as a result you have to design your database and app around it. Alternately, this can be emulated fairly trivially with a trigger that updates a counter on create/deletion of a new row. People often assume this type of query is free in terms of processing. In MySQL it is optimised away.
30 PhilMurray 29 # There are several projects out there with implement heavy modifications to PostgreSQL to implement replication, some rely on adding triggers to your database
30
37 AristotlePagaltzis 31 ~UniForum posted studies that concluded that [MySQL] is slightly faster per query, but [PostgreSQL] can handle more concurrent connections.
28 AristotlePagaltzis 32
33 ----
34
35 !! 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]:
36
37 AristotlePagaltzis 37 __Speed__:
28 AristotlePagaltzis 38
39 [PostgreSQL] in most instances is nearly the same speed as [MySQL].
40 In many more complicated instances [MySQL] cannot index on fields where [PostgreSQL] can,
41 leading to order of magnitude speedups for postgres.
42
37 AristotlePagaltzis 43 __Stability__:
28 AristotlePagaltzis 44
45 I can't say I know anything about [Java]'s [JDBC] interface, so I won't comment here.
46
37 AristotlePagaltzis 47 __Data Integrity__:
28 AristotlePagaltzis 48
49 [PostgreSQL] takes a hit because it wants your data to be correct by enforcing constraints.
36 LawrenceDoliveiro 50 [MySQL] [has limited referential constraints|http://dev.mysql.com/doc/refman/5.0/en/create-table.html].
28 AristotlePagaltzis 51
37 AristotlePagaltzis 52 __Special Server Side Features__:
28 AristotlePagaltzis 53
36 LawrenceDoliveiro 54 Both by default don't listen on the network as a security feature.
28 AristotlePagaltzis 55 [PostgreSQL]'s security is far more advanced than [MySQL]'s
56 allowing things like IDENT auth etc.
57
37 AristotlePagaltzis 58 __Locking and Concurrency Support__:
28 AristotlePagaltzis 59
60 [PostgreSQL] has subrow locking of data and
61 [Multiversion Concurrency Control | http://www.postgresql.org/docs/7.4/interactive/mvcc.html];
62 [MySQL] still usually (under what conditions, which versions?) locks the entire table.
63
37 AristotlePagaltzis 64 __Large Objects__:
28 AristotlePagaltzis 65
66 [PostgreSQL]'s interface for large objects allows for flexible retrieval of data without having
67 to transmit the entire large object from the DataBase when it is not needed, speeding up many queries.
68
37 AristotlePagaltzis 69 __Alter Table__:
28 AristotlePagaltzis 70
71 [PostgreSQL]'s ALTER TABLE is lacking compared to [MySQL].
36 LawrenceDoliveiro 72 In theory, a carefully designed schema should preclude any need for altering
73 types of columns, but in practice requirements change during the life of a DataBase.
28 AristotlePagaltzis 74 [PostgreSQL] requires to ALTER TABLE SET NOT NULL in an extra step after adding a
75 column to allow you to put proper data into the new column before the <tt>NOT NULL</tt>
76 constraint is added.
77
78 ----
79
80 I hope that I am being consistent with proper usage of this Wiki.
81
82 I have read many postings saying that postgres was slow and is getting faster and mysql is basic but is getting more sophisticated features.
83
84 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.
85
86 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.
87
88 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.
37 AristotlePagaltzis 89
90 —~StuartGalloway
28 AristotlePagaltzis 91
92 ----
93
94 !! See Also
95
35 MatthiasDallmeier 96 * [PostgreSQL or MySQL?|http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html]
38 AristotlePagaltzis 97 * http://howfuckedismydatabase.com/

PHP Warning

lib/blame.php:177: Warning: Invalid argument supplied for foreach() (...repeated 3 times)