Talk:MySQL vs PostgreSQL

From WikiVS, the open comparison website

Contents 1 Discussion Page suggestion 2 Entire Page 3 Incomplete 3.1 Missing: evaluation of transaction capabilities 3.2 Missing: SQL conformity 4 Raw Speed 5 Configured Speed 6 COUNT(*) 7 Benchmarks 8 ACID Compliance 9 Stored Procedures 10 Triggers 11 Partitioning 12 Popularity 13 Cluster / Replication 14 Other -> not in article 15 Shared Hosting/Recovery/Permission 16 Authentication/security 17 JOIN 18 spammers 19 IMMUTABLE, STABLE flags on PostgreSQL 20 ALTER TABLE Speed comparison? 21 OpenGIS

[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)

Yeah, I'd rather see the "This article is biased" sentence taken out. I'm a programmer, not a DBA, and know just enough about each system to be dangerous. But after reading the article, there didn't seem to be a PostgreSQL bias at all. It reads to me as fairly even-handed, possibly tilted toward the MySQL side. My 2 cents.

[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 cccccccwiki. 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). (Definitely user management is consistent in postgresql while mysql leaves a confusion for new administrators/users).
• 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).

MySQL does not support hot back-up for MyISAM, PostgreSQL supports hot back-up without any problem.

• 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] Missing: evaluation of transaction capabilities

• PostgreSQL's idea of transaction isolation are different enough to deserve a mention. When doing certain transaction-supported processing models, you can find that changes in one transaction have "leaked" into another overlapping one despite using Serializable transactions. I'm not expert enough myself to address it in the main article and will leave it to experts.

75.140.243.0 02:14, 20 November 2008 (UTC)

News to me. If that's the case, I hope someone who knows something about it addresses the matter. -- apotheon 07:00, 20 November 2008 (UTC)

Maybe this is because PostgreSQL does not have true SERIALIZABLE isolation level, but SNAPSHOT instead.

91.192.173.242 10:49, 6 August 2009 (UTC)

[edit] Missing: SQL conformity

A very important thing to add is to which version of SQL the two are strictly conforming. --Fiable.biz 05:41, 20 March 2010 (UTC)

[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] Benchmarks

A new benchmark link http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html

13:42, 18 June 2008 (GMT) 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

[edit] Other -> not in article

What about to give some real world knowledge in using database with spatial informations? I only know http://postgis.refractions.net/ with postgre and Iam just about year using this. How it is on mysql? (luvar@plaintext.sk)

[edit] Shared Hosting/Recovery/Permission

I've a lot of experience with MySQL (also in bad times ;)) and just a little with PostgreSQL. First, MySQL seems to me much better for shared hosting infrastructure. Most of the time one customer database has just one query. Second, I've no idea about PostgreSQL recovery features after system crash but the ones from MyISAM seems very very good to me (I've had a lot of trouble with KVM-Virtualization). InnoDB's recovery features were, in short, not as good as MyISAM. Last thing I'd like to mention: I've lost a lot of nerves to change user permissions on PostgreSQL. The way MySQL implements them seems to me much more logical. PostgreSQL stores access (network layer) permissions in a file and other access (application/data layer) permissions in database.

what about memory footprint? ~~Anon

Toggling InnoDB makes a difference of about 100MB memory, that might be worth pointing out for shared hosting/virtual servers. 92.230.52.33

[edit] Authentication/security

I'm a bit surprised that this comparison does not mention the different ways in which MySQL vs. PostgreSQL manages things like authentication, authorization, GRANTs and so on.

IME it's quite a different task to set up users with specific database or table access, handle IP-address based authorization on the two. --Ajmas 16:55, 30 July 2010 (UTC)

[edit] JOIN

I have been working with MySQL and Oracle and I have noticed that MySQL simply can't handle well RIGHT/LEFT JOIN on large tables, especially if there are multiple such JOIN. The CPU goes through the roof - and SQL that runs fine on Oracle simply *cannot* run on MySQL - forcing me into writing an SQL much less optimal. After extensive searching on the internet it dawned on me that MySQL simply ain't doing this feature very well. Naturally I'm very interested to see if PostgreSQL is better. But I couldn't find any information about this issue on this page. I can refer to discussion for more information about this issue: http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables

[edit] spammers

Who do I need to bribe to give me the ability to ban the spammers whose edits I've been undoing here for the last week or so? - apotheon 03:27, 15 June 2009 (UTC)

Thanks! - apotheon 16:39, 4 July 2009 (UTC)

[edit] IMMUTABLE, STABLE flags on PostgreSQL

These flags are relevant to query optimiser and to functional indexes. There are not any constraint for using VOLATILE functions as columns defaults. Functional indexes are limited on IMMUTABLE functions.

Pavel

[edit] ALTER TABLE Speed comparison?

Hi Folks. The project im am workin in has some performance-issues with mysql because on every ALTER TABLE-statement a temporary copy of the whole table is created which takes a great amount of time on a 200GB-Table. I can't find anything but old articles about this with PostgreSQL 7.x. I think it would be worth pointing out that mysql has this issue. --78.51.115.210 10:36, 21 June 2010 (UTC)

[edit] OpenGIS

Another difference worth mentioning is support for OpenGIS. PostgreSQL has support via PostGIS, while MySQL has a limited implementation (see forum discussion). At this point I can't say how much things have evolved since. --Ajmas 16:55, 30 July 2010 (UTC)