3 ms·

A little bit unrelated, but as of today, what would be the advantages of choosing pgsql over mysql for someone who already has decent knowledge of mysql? Is it

by cmer 15y ago

A little bit unrelated, but as of today, what would be the advantages of choosing pgsql over mysql for someone who already has decent knowledge of mysql? Is it still a matter of preference (and being able to deal with the mysql license) or did pgsql progress so much over the last 3-4 years that it became superior? What are the pros over mysql? I'd consider switching to pgsql for a future project but throwing my mysql expertise out the window for nothing seems like a bit waste of time.

alexg0 15y ago
This has a pretty decent, if dated list: http://sql-info.de/mysql/gotchas.html http://sql-info.de/mysql/gotchas.html Few of the items on that list bit me personally.
- cmer 15y ago
  I'm more interested in knowing what PgSQL does better than MySQL today, in 2011. MySQL definitely has a few caveats, my question is mostly, is PgSQL now significantly better than MySQL or they're both more or less the same, with different pros and cons.
  - vegai 15y ago
    The enemy of better is good enough ...
  - leandrod 15y ago
    No, PostgreSQL is way better. It has a mostly compliant SQL dialect. It is more compatible with other DBMSs, specially DB2 but also Oracle. It implements a host of procedural programming languages and extensions, including types and operators. It is way faster, and scales way more. It is totally free (no paying Oracle to do InnoDB hot backups). It is way more consistent. It does geographic data second to none. Its development is totally open and way faster. Reading the PostgreSQL TODO wiki page is a joy. And so on and so on.
  - jacques_chester 15y ago
    For me personally: 1. MySQL's "feature set" is usually described as a union of storage engine features; whereas in fact you only ever get some of them at a time. I find that extremely annoying. 2. PostgreSQL has a decent, smart query planner. In cases where I have multiply-layered views, I've seen MySQL throw up its hands and manually churn through each view in turn, while PostgreSQL did the smart thing and combined all the views into a single execution plan. 3. It's much more featuresome for in-database programming. The web world tends to look on RDBMSes as flat files with a funny accent, so this doesn't matter for a lot of programmers. But sometimes you absolutely must either a) protect the data or b) place computation as close to the data as possible. Featuresome databases like PostgreSQL, Oracle, DB2 etc let you do this. MySQL not as well.
  - pvh 15y ago
    Looking over the list here, I'd call out a few specific things that are important to me: * HStore, the key-value store column type * PGXN, still new, but going to change the world * Replication that Actually Works For Real No Kidding. * Window functions, which are a total brainf*ck but once you internalize them, indispensible.
  - jeffdavis 15y ago
    "I'm more interested in knowing what PgSQL does better than MySQL today, in 2011." The most striking difference[1] is probably the cost-based optimizer and the availability of a lot more execution plans. Most often cited is that MySQL has no hash join, but there are lots of plans that make a huge difference in execution time, and (for the most part), mysql just doesn't have those execution plans available. And these are algorithmic differences. Typically, you think of optimization as something that does your same algorithm, just a little faster. In a SQL DBMS, optimization means choosing a better algorithm. And it's hard for MySQL to add new algorithms because it doesn't have a cost-based optimizer, so it would have no idea which ones to choose. [1] There are lots of other differences, but sometimes differences aren't easily represented as convincing "check-mark" features. So I really think you are asking the wrong question. The right question is: if I were making non-trivial application XYZ in postgres, how would it be done (from both dev and operational standpoint)? And is it easier to develop and smoother to operate if I do it that way?
samstokes 15y ago
A few from my personal experience: * microsecond granularity on timestamps, rather than second * transactional DDL (if a migration fails halfway through, you don't end up in a broken or inconsistent state) * really superb documentation - clear and detailed explanations of hard stuff like locking semantics * No silent coercion/truncation between data types * Functional indices (as far as I can tell from http://bugs.mysql.com/bug.php?id=4990 http://bugs.mysql.com/bug.php?id=4990, MySQL doesn't have these) * http://postgres.heroku.com http://postgres.heroku.com (yeah MySQL has RDS but I've not heard great things)
zzzeek 15y ago
by far the largest - joins and subqueries actually optimize correctly on Postgresql. In mysql, if you say (pseduosql) "select a_few_rows_from (select many_many_rows from some_table) where <some criterion>", mysql scans "many rows" no matter what. Postgresql can figure out the big picture.
jeffdavis 15y ago
"Is it still a matter of preference" The systems are quite different; I don't think it was ever really about preference -- it's more about the approach you take to development and operations. It's hard to point to any one thing that you might find useful in postgres without knowing more about the kind of projects you work on. But if you start to do things the postgresql way, then I think you'll find some things that make development much easier and operations much smoother. "Better" is a loaded word; but if developing a new application, I think it's a good idea to default to postgresql, and only use mysql if you have a specific reason (and there are a few good ones). I've been a postgresql community member for a long time and use postgresql much more than mysql, so take that opinion with a grain of salt.

by cmer 15y ago

alexg0 15y ago

This has a pretty decent, if dated list: http://sql-info.de/mysql/gotchas.html http://sql-info.de/mysql/gotchas.html Few of the items on that list bit me personally.

cmer 15y ago
I'm more interested in knowing what PgSQL does better than MySQL today, in 2011. MySQL definitely has a few caveats, my question is mostly, is PgSQL now significantly better than MySQL or they're both more or less the same, with different pros and cons.
- vegai 15y ago
  The enemy of better is good enough ...
- leandrod 15y ago
  No, PostgreSQL is way better. It has a mostly compliant SQL dialect. It is more compatible with other DBMSs, specially DB2 but also Oracle. It implements a host of procedural programming languages and extensions, including types and operators. It is way faster, and scales way more. It is totally free (no paying Oracle to do InnoDB hot backups). It is way more consistent. It does geographic data second to none. Its development is totally open and way faster. Reading the PostgreSQL TODO wiki page is a joy. And so on and so on.
- jacques_chester 15y ago
  For me personally: 1. MySQL's "feature set" is usually described as a union of storage engine features; whereas in fact you only ever get some of them at a time. I find that extremely annoying. 2. PostgreSQL has a decent, smart query planner. In cases where I have multiply-layered views, I've seen MySQL throw up its hands and manually churn through each view in turn, while PostgreSQL did the smart thing and combined all the views into a single execution plan. 3. It's much more featuresome for in-database programming. The web world tends to look on RDBMSes as flat files with a funny accent, so this doesn't matter for a lot of programmers. But sometimes you absolutely must either a) protect the data or b) place computation as close to the data as possible. Featuresome databases like PostgreSQL, Oracle, DB2 etc let you do this. MySQL not as well.
- pvh 15y ago
  Looking over the list here, I'd call out a few specific things that are important to me: * HStore, the key-value store column type * PGXN, still new, but going to change the world * Replication that Actually Works For Real No Kidding. * Window functions, which are a total brainf*ck but once you internalize them, indispensible.
- jeffdavis 15y ago
  "I'm more interested in knowing what PgSQL does better than MySQL today, in 2011." The most striking difference[1] is probably the cost-based optimizer and the availability of a lot more execution plans. Most often cited is that MySQL has no hash join, but there are lots of plans that make a huge difference in execution time, and (for the most part), mysql just doesn't have those execution plans available. And these are algorithmic differences. Typically, you think of optimization as something that does your same algorithm, just a little faster. In a SQL DBMS, optimization means choosing a better algorithm. And it's hard for MySQL to add new algorithms because it doesn't have a cost-based optimizer, so it would have no idea which ones to choose. [1] There are lots of other differences, but sometimes differences aren't easily represented as convincing "check-mark" features. So I really think you are asking the wrong question. The right question is: if I were making non-trivial application XYZ in postgres, how would it be done (from both dev and operational standpoint)? And is it easier to develop and smoother to operate if I do it that way?

samstokes 15y ago

A few from my personal experience: * microsecond granularity on timestamps, rather than second * transactional DDL (if a migration fails halfway through, you don't end up in a broken or inconsistent state) * really superb documentation - clear and detailed explanations of hard stuff like locking semantics * No silent coercion/truncation between data types * Functional indices (as far as I can tell from http://bugs.mysql.com/bug.php?id=4990 http://bugs.mysql.com/bug.php?id=4990, MySQL doesn't have these) * http://postgres.heroku.com http://postgres.heroku.com (yeah MySQL has RDS but I've not heard great things)

zzzeek 15y ago

by far the largest - joins and subqueries actually optimize correctly on Postgresql. In mysql, if you say (pseduosql) "select a_few_rows_from (select many_many_rows from some_table) where <some criterion>", mysql scans "many rows" no matter what. Postgresql can figure out the big picture.

jeffdavis 15y ago

"Is it still a matter of preference" The systems are quite different; I don't think it was ever really about preference -- it's more about the approach you take to development and operations. It's hard to point to any one thing that you might find useful in postgres without knowing more about the kind of projects you work on. But if you start to do things the postgresql way, then I think you'll find some things that make development much easier and operations much smoother. "Better" is a loaded word; but if developing a new application, I think it's a good idea to default to postgresql, and only use mysql if you have a specific reason (and there are a few good ones). I've been a postgresql community member for a long time and use postgresql much more than mysql, so take that opinion with a grain of salt.