Talk:MySQL vs PostgreSQL

From WikiVS, the open comparison website

[edit] Discussion Page suggestion

use the ':' syntax to indent replies to others.

[edit] Entire Page

This comparison is clearly biased towards PostgreSQL.

This is because PostgreSQL users have been more actively updating the wiki. MySQL users, please contribute so we can have a fair comparison! It's a wiki; you can fix any biased statements to make it more accurate.

I'm not even sure the perceived bias is solely because of PostgreSQL users updating the wiki. There's also the simple fact that PostgreSQL has made significant advances with the 8.x versions that make any benefits to MySQL over PostgreSQL less clear. Furthermore, I'm not sure it's going to be very easy to get more MySQL users to contribute more meaningful information, because a greater percentage of the PostgreSQL community seems to know more about DBMS implementation than of the MySQL community. There are a number of reasons for this, like the fact that PostgreSQL tends to attract more professional DBAs due to greater feature parity between PostgreSQL and commercial counterparts like Oracle, Informix, et cetera, than between MySQL and those same commercial DBMSes. In other words, regardless of technical matters, social factors lend themselves to more PostgreSQL users being able to contribute meaningfully. Of course, there are knowledgeable MySQL users out there -- and I'd like to see more of them contribute so that more information can be added to this page, rather than leaving us with one-line statements from people who won't or can't contribute accusing some nebulous sense of others being biased. - apotheon 16:45, 30 April 2008 (UTC)

[edit] Incomplete

There are several more things to keep in consideration. I'm very experienced with MySQL, but I only have a general idea about Postgres, so I'm not qualified to add them to the wiki. Please help out.

Roles (PostgreSQL has them, MySQL will have them but currently does not; you can more-or-less add them with clever stored procedures).
User management and permissions (I have a hunch PostgreSQL's will be better).
Backups (PostgreSQL: no idea; MySQL: nice hot logical backup on InnoDB; commercial hot logical backup on InnoDB; locking hot logical or physical backup on non-transactional engines).
Scaling: details on PostgreSQL's replication and clustering options, whether they can scale reads and/or writes, whether they provide HA or not, whether they provide automatic failover or not. MySQL's stuff could also be expanded, and replication hacks explained. Also, MySQL's replication is quite good for scaling reads all you want and backups.
Details on MySQL's NDBCluster engine, which is pretty fast and great for some niches, but has lots of limitations.
Advanced SQL features, such as custom operators, sequences, objects, etc. which AFAIK PosgreSQL has but MySQL is nowhere near supporting them.
Non-standard extensions: MySQL supports multi-table UPDATE and DELETE. Don't know about PostgreSQL, guess not.
Database management, statistics, profiling (MySQL is nice and simple, maybe too simple; a tad better with Google's version; no idea about PostgreSQL).

Also, maybe we could discuss if MySQL's development is slow or not. Personally, I think MySQL's development is being slow. Relational databases scale horizontally with difficulty. MySQL has a good replication solution, but not a full scaling solution (except NDB, to some extent). The second PostgreSQL is proven to scale horizontally better than MySQL does, and manages to gain popularity with managers, it'll be game over for MySQL because they don't offer a feature set nearly as rich as PostgreSQL's.

22:39, 9 October 2008 (UTC) Anonymous

[edit] Raw Speed

On MyISAM, INSERTs can be lock-free all the time if you want, which leaves holes in the table as rows are appended, until the table is optimized. 22:05, 9 October 2008 (UTC) Anonymous

[edit] Configured Speed

Disclaimer: I'm generally a Postgres guy ...

...that can lead to faster performance... is weak. I'm no Postgres guru, but perhaps someone with more knowledge can flesh that out. "can lead" leaves a LOT of room for interpretation. Reference? Flesh out?

I gave it a shot. Some more "fleshing out" could be done, of course, and it might be a good idea for someone to add some performance benefits for MySQL to provide a little balance. apotheon 05:44, 1 April 2008 (UTC)

The partial indexes thing is completely wrong.

MySQL does not support partial indexes in the way Postgres does at all. You can't create an index that only indexes rows within given ranges with a WHERE clause. It only supports leftmost prefix indexes. And it does support them in MyISAM; prefix indexes work anywhere indexes work. What you can't do in MyISAM is create a leftmox prefix FULLTEXT index.

Also, system tables being MyISAM doesn't matter at all.

21:55, 9 October 2008 (UTC) Anonymous

[edit] COUNT(*)

COUNT(*) is O(1) in MyISAM, that's correct. The quote from Bad CTK is just silly though, because it thinks MySQL == MyISAM.

COUNT(*) in Postgres and InnoDB is O(n). However, InnoDB counts on the primary index, which is theoretically faster. I have no idea whether this is actually faster or not, but it's at least smarter.

22:05, 9 October 2008 (UTC) Anonymous

[edit] ACID Compliance

What is the licencing thing doing here? Also, I want to point out that licensing InnoDB doesn't matter at all unless you're planning on acquiring commercial licences to bundle or distribute MySQL with a non-GPLv2 licence. InnoDB is GPLv2. No matter what Oracle does, MySQL can always use InnoDB, bundle it with MySQL, improve it, and continue to support it. (InnoDB's tech support was provided by MySQL even before Oracle bought InnoBase. InnoBase is a three-person company who took care of development alone.)

[edit] Stored Procedures

MySQL's implementation of SPs is a WIP; even MySQL says so (at least in their courses). It's a toy programming language when compared to PL/SQL, and PL/SQL is a toy programming language when compared to a decent programming language such as <insert your favourite dynamic language here>.

[edit] Triggers

What's being said from MySQL on NDB is true, though that doesn't matter nearly as much as this: triggers aren't triggered on foreign key actions such as cascading. This will get fixed once MySQL implements server-wide, engine-agnostic FKs.

[edit] Partitioning

I consider MySQL's partitioning support a toy or, at best, a WIP, until global indexes are implemented. Currently, it doesn't support global indexes, so you cannot guarantee uniqueness on partitioned tables unless you partition by the unique key. Also, you don't reduce MyISAM's table-level locking with partitioning, which is disappointing.

22:23, 9 October 2008 (UTC) Anonymous

Why it says PostgreSQL doesn't support hash and combined partitionig? It supports hash via immutable functions. And combined partitioning works like expected.

[edit] Popularity

The "Many web developers use MySQL" link is seven years old. Is there some recent research?

I don't have any recent research, but I see MySQL everywhere, and I bump onto Postgres nearly as often. I don't think you'll achieve anything by questioning this. 22:23, 9 October 2008 (UTC) Anonymous

Questioning what? The 7 year old, completely unsupported guesstimate? The developers of SQLite believe, and present a detailed case, that they have 200-300 million deployments, compared to ~100 million deployments of all other SQL database engines combined ( http://sqlite.org/mostdeployed.html ). The MySQL claim should be deleted from the article, since it has no supporting facts, and is contradicted by the above. (PS. I refer to the statement: 'MySQL justifiably calls itself "The world's most popular open source database." ') 58.174.242.29 05:11, 10 October 2008 (UTC)

Fixed. - apotheon 17:34, 10 October 2008 (UTC)

[edit] Cluster / Replication

MySQL provides far better replication and cluster abilities than PostgreSQL. If you plan to scale with multiple backends definetely use MySQL. MySQL supports multi master replication and real HA clustering.

It doesn't support multi-master replication. It does support bi-directional and circular asynchronous replication (somewhat synchronous with Google's version), but this scheme is pretty dangerous in most circumstances, and it doesn't scale writes. There's no single way to scale writes with MySQL. 21:52, 9 October 2008 (UTC) Anonymous