Home / server / Questions / 111863
Accepted
Massimiliano Torromeo
Asked: 2010-02-12 03:07:22 +0800 CST

Is MySQL better than PostgreSQL in something?

  • 772

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?

mysql sql postgresql

7 Answers

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

    • 3
  2. Best Answer

    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.

    • 3

  3. 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.)

    • 2

  4. 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

    • 1

  5. 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.

    • 1

  6. 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.

    • 1

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

    • 0