8

I know the question sounds provocative, but it really isn't. I'm lately finding MySQL limiting in a lot of areas and liking PostgreSQL more and more. It scales a lot better and it respects the SQL standards a lot more than MySQL.

I'm still new to the PostgreSQL world though and since I'm willing to move away from MySQL for all my future projects, what I want to know is: is there any particular feature of MySQL that it is done better (as in more high-performing or more user friendly, etc..) than in PostgreSQL?

I'm wondering what I'm going to miss from MySQL. I already found that the AUTO_INCREMENT fields in MySQL are more handy than SEQUENCES in PostgreSQL and the deployment in Windows was problematic in the past (not a problem anymore. Never a problem for me).

What else?

Massimiliano Torromeo
  • 291
  • 4
  • 12

7 Answers7

3

COUNT(*) is also much slower with PSQL. You're supposed to create triggers for this kind of functionality.

aardbol
  • 1,473
  • 4
  • 17
  • 26
  • 1
    Interesting, but as far as I've understood it, this only applies to COUNT(*) queries WITHOUT the where clause on the MyISAM engine. Other than that, it performs similarly. Or is postgresql still slower even compared to InnoDB and MyISAM with a where clause? – Massimiliano Torromeo Feb 11 '10 at 13:45
  • What do you mean by that ? select count(*) from foo; does not not work in pgsql ? While it is true that count(*) is slower in postgres compared to mysql, there is a reason for that and a workaround. A good overview can be found at http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL – Sibster Feb 11 '10 at 13:45
  • 1
    It's slower for the same reason as MySQL's InnoDB, read more here: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#COUNT.28.2A.29 – aardbol Feb 11 '10 at 19:03
  • 3
    COUNT(*) is slower due to the MVCC implementation. You gain in terms of read/write performance but because transactions can be in various states, it becomes difficult to determine what the "true" count is. – Avery Payne May 07 '10 at 17:29
3

You're clearly approaching this from a developer perspective, so you may find more useful answers over at SO.

From an administrative perspective:
- Replication (HA)
- Replication (Scaling*)
- Replication (Backups)
- Application support
- Size and Depth of community (Documentation, Support)
- Existing install base / available jobs

*Note you mentioned postgres scaling better. scaling means something different to everybody, but as a general rule things that have a path to spreading the load across multiple servers scale better than things that don't.

Nawaz
  • 103
  • 4
cagenut
  • 4,848
  • 2
  • 24
  • 29
  • I'm also interested in the administrative aspects. I was not sure where to post this. BTW you are right about scaling. By scales better I really meant, on a single machine, not on clusters. MySQL has very little performance gain on multicore systems and this days multicore are the norm. Also I've seen MySQL go from good performance to almost useless when the workload increased, and the impression was that postgresql would have handled the load better, but I understand this is debateable and should be inquired better. BTW, the replication arguments are worth being considered. – Massimiliano Torromeo Feb 11 '10 at 14:10
  • 1
    MySQL used to have very little perf gain on multicore, but if you're running a well patched 5.0 build, nearly any 5.1 build, an innodb-plugin(/xtradb) build, or a 5.4 build thats not true anymore. Not trying to be confrontational but your anecdotal mysql slowdown could almost certainly have been addressed, and your "impression" postgres would have handled it better is a naked assumption. The key point here is, postgres vs. mysql is something you can blabber a lot about from a development-sophistry perspective, but from an operational/business perspective, the case was closed years ago. – cagenut Feb 11 '10 at 17:17
  • Of course, I specified it was just an impression because if I don't actually try serving the same data under the same load I cannot be sure, and I know I'm not really proving anything. Thanks for clarifying the latest performace gains of MySQL. – Massimiliano Torromeo Feb 12 '10 at 10:13
  • I just always use the type "serial" in postgres, I find it handier than "integer auto_increment". Or did you perhaps mean something else? – ptman Sep 29 '10 at 12:21
2

Because Postgres effectively does a copy-on-write for every update (so that it can deal with transactions), if you don't need transactions, and do a lot of writes relative to reads, MySQL won't have the overhead that PostgreS will. (every updated record needs to write more, update indexes, etc.)

Joe H.
  • 1,917
  • 12
  • 13
  • 1
    I think you should qualify that just a tad bit. Yeah, it will be faster, but there will be no guarantee of consistency in your data. This is perfectly fine for some situations (write-outs of user comments from a website) but might pose problems in others (taking a "shopping cart" order online). – Avery Payne May 07 '10 at 17:37
1

I can think of one thing missing from PostgreSQL:

I think there was something also with blocking, insert or update. But I can't seem to remember what it actually was. :S

Egon
  • 133
  • 6
1

The auto_increment field being 'handy' is not very relevant is it? Oracle has sequences and is undoubtedly the most widely used commercial database in the world.

The is a huge amount of information to be found about how PostgreSQL if more crash safe and so on. I'm just going to point you to this page on the PostgreSQL wiki (no doubt a coloured opinion, but that's what you are looking for anyway).

A lot of people are mentioning on this page that MySQL is more friendly to newbies. So? Explain to me how that matters in a mission-critical environment.

wzzrd
  • 10,409
  • 2
  • 35
  • 47
  • Being sequences the most widely used approach does not make auto_increment fields any less 'handy', and it is relevant to me because I'm asking for differences between the 2 dbms in question. But this is not stopping me from switching to pgsql so I agree that it's not a real problem. – Massimiliano Torromeo Feb 11 '10 at 14:41
1

MySQL is often called as SQL'ed grep. As to me (now at time of the writing) it looks pretty much as PHP — quite awkward, but popular.

I'm wondering what I'm going to miss from MySQL.

Try renaming a database for the very beginning. ;) Then add (missing) conditional indices.

P. S. I can point at least one non-disputable good feature MySQL has — it supports rather space conserving data types so small as (unsigned) byte for e. g.. This could save lots of RAM and disk space when used accordingly. But there're rather few goodies to mention in addition, alas. And comparison on the link you've been given is really nifty.

Community
  • 1
poige
  • 9,448
  • 2
  • 25
  • 52
0

auto_increment is handy until you realize that they suck for tables with extremely high insert concurrency, at which point they become a bottle neck and you're forced to implement a sequence using a MyISAM table.

Anyways, to answer your question, the biggest thing MySQL has had going for it has been easy to set up replication that allows for replication chaining and read slaves. However, Postgres will have that in the next release, 9.0 (which is already out in alpha status).

mage2k
  • 509
  • 3
  • 3