5 ms·

This is particularly important if you are working on Heroku. I just made the switch from MySQL to Postgres and while they both run SQL, they have numerous synta

This is particularly important if you are working on Heroku. I just made the switch from MySQL to Postgres and while they both run SQL, they have numerous syntactical and performance considerations that need to be addressed so you can transition into the Heroku cloud easier. It's definitely worth considering for 9 and up, as at least when I was in grad school for databases (~2008), MySQL outperformed Postgres.

ominous_prime 16y ago
> MySQL outperformed Postgres. Although a statement like this is too general to mean much of anything, I doubt that MySQL with innodb (which should be used for most cases) ever bested Postgres in overall performance.
- wladimir 16y ago
  Indeed, (My)ISAM isn't really comparable to Postgres with regard to features anyway (transactions, for one), even if it is faster that's not a fair comparison. Only InnoDB is...
- jeffdavis 16y ago
  DBMSs are complex things. Any DBA proficient in both systems can show you a query (and table layout) in PostgreSQL that outperforms InnoDB and vice versa.
calloc 16y ago
And with MySQL you throw out ACID or any type of consistency ...
- mattyb 16y ago
  InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine. Key advantages of InnoDB include: Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine... Edit: swapped out Wikipedia for MySQL's official docs.
  - calloc 16y ago
    2008 ...
  - dstorrs 16y ago
    So, if I'm reading this right, a release of MySQL that came out only a year ago and is still of development quality finally has ACID compliance by default? Or has there been a production-grade release since then? EDIT: My mistake. Apparently I'm not sufficiently familiar with MySQL's revision number practices. According to this: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-x.html#news-5-5-x http://dev.mysql.com/doc/refman/5.5/en/news-5-5-x.html#news-... 5.5.x is a production release; I saw an odd minor version and assumed it was dev, since that's what I'm used to everywhere else. My other questions still stand, though.
    - whiskers 16y ago
      That's not really the point though - the different engines provided are for different tasks. MyISAM for example supports fulltext indexes by default which was/is probably one of the most useful features for web development which explains why MySQL is so popular in that arena. Whatever default was selected by MySQL is irrelevant - do your research and pick the right tool for the job.
      - jeffdavis 16y ago
        "the different engines provided are for different tasks" You should think of them more like separate DBMSs that are tied together in one system. The semantics change depending on the storage engine, so they aren't just drop-in replacements. "MyISAM for example supports fulltext indexes..." PostgreSQL has supported full text search for a long time.
        whiskers 16y ago
        I didn't claim they are drop in replacements, just like I wouldn't claim you can just jump from MySQL to PostgreSQL. What I said was you need to do your research and pick the right tool for the job. I haven't used PostgreSQL for quite a few years but I think it got built-in support for fulltext indexes in v8.3 (April 2008) whereas MySQL had them in v3.23 (April 2001). A lot of shared hosting providers/package management systems wouldn't have supported/installed the extra extensions to make PostgreSQL support fulltext indexing prior to it being built-in I suspect. To be clear, I don't care what you or anyone else uses - I was simply pointing out that just because it was the default engine it didn't mean you had to use it!
- Luyt 16y ago
  I guess "MySQL is not ACID-compliant", http://www.xtranormal.com/watch/7091415/ http://www.xtranormal.com/watch/7091415/ is not relevant anymore?
torial 16y ago
>MySQL outperformed Postgres. For my part, I thought Postgres was slow until 5 months ago (when I came from a port from SQL Server). Then I learned something! The default install of Postgres is slow (at least on Windows). And on benchmarks against the baseline SQL Server port I had, it was about 2x slower. BUT there were a number of things I could do to make it faster: a) Use the EnterpriseDB wizard for tuning Postgres --> resulted in a 2x to 3x performance improvement. b) Adjust the way I did updates (there is a way to get Postgres updates to be fast, it just takes some massaging) c) Get rid of views when possible -- I found that certain query compilation optimizations I had taken for granted on SQL Server weren't available. Doing all this resulted in a system that performed faster and w/ less RAM than SQL Server(and I had spent a bunch of time tuning the SQL Server perf in the first place!)
- chunkbot 16y ago
  I'm liking the new trend of making databases "fast" by default, with "safe" configurations. People who care about safety should take the time make it so; people who want it fast, well, give it to them faster! So maybe PostgreSQL should offer faster defaults?
  - dstorrs 16y ago
    It's a design decision. MySQL chose "fast defaults". PostgreSQL chose "safe defaults". If you want the other choice, use the other database.
  - hgimenez 16y ago
    Not sure what you mean by "safe" by default. Speed and performance have nothing to do with security, if that's what you mean. The reason that the default Postgres configuration does not perform well is that Postgres runs on a number of operating systems, and it is not possible to make assumptions about the hardware and configuration of the host. For example, increasing configuration parameters like shared_buffers to any useful value will require you to tweak the OS'es kernel resources. The Postgres documentation does a great job at describing the process on a number of operating systems: http://www.postgresql.org/docs/current/interactive/kernel-resources.html http://www.postgresql.org/docs/current/interactive/kernel-re...

ominous_prime 16y ago

> MySQL outperformed Postgres. Although a statement like this is too general to mean much of anything, I doubt that MySQL with innodb (which should be used for most cases) ever bested Postgres in overall performance.

wladimir 16y ago
Indeed, (My)ISAM isn't really comparable to Postgres with regard to features anyway (transactions, for one), even if it is faster that's not a fair comparison. Only InnoDB is...
jeffdavis 16y ago
DBMSs are complex things. Any DBA proficient in both systems can show you a query (and table layout) in PostgreSQL that outperforms InnoDB and vice versa.

calloc 16y ago

And with MySQL you throw out ACID or any type of consistency ...

mattyb 16y ago
InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine. Key advantages of InnoDB include: Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine... Edit: swapped out Wikipedia for MySQL's official docs.
- calloc 16y ago
  2008 ...
- dstorrs 16y ago
  So, if I'm reading this right, a release of MySQL that came out only a year ago and is still of development quality finally has ACID compliance by default? Or has there been a production-grade release since then? EDIT: My mistake. Apparently I'm not sufficiently familiar with MySQL's revision number practices. According to this: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-x.html#news-5-5-x http://dev.mysql.com/doc/refman/5.5/en/news-5-5-x.html#news-... 5.5.x is a production release; I saw an odd minor version and assumed it was dev, since that's what I'm used to everywhere else. My other questions still stand, though.
  - whiskers 16y ago
    That's not really the point though - the different engines provided are for different tasks. MyISAM for example supports fulltext indexes by default which was/is probably one of the most useful features for web development which explains why MySQL is so popular in that arena. Whatever default was selected by MySQL is irrelevant - do your research and pick the right tool for the job.
    - jeffdavis 16y ago
      "the different engines provided are for different tasks" You should think of them more like separate DBMSs that are tied together in one system. The semantics change depending on the storage engine, so they aren't just drop-in replacements. "MyISAM for example supports fulltext indexes..." PostgreSQL has supported full text search for a long time.
      - whiskers 16y ago
        I didn't claim they are drop in replacements, just like I wouldn't claim you can just jump from MySQL to PostgreSQL. What I said was you need to do your research and pick the right tool for the job. I haven't used PostgreSQL for quite a few years but I think it got built-in support for fulltext indexes in v8.3 (April 2008) whereas MySQL had them in v3.23 (April 2001). A lot of shared hosting providers/package management systems wouldn't have supported/installed the extra extensions to make PostgreSQL support fulltext indexing prior to it being built-in I suspect. To be clear, I don't care what you or anyone else uses - I was simply pointing out that just because it was the default engine it didn't mean you had to use it!
Luyt 16y ago
I guess "MySQL is not ACID-compliant", http://www.xtranormal.com/watch/7091415/ http://www.xtranormal.com/watch/7091415/ is not relevant anymore?

torial 16y ago

>MySQL outperformed Postgres. For my part, I thought Postgres was slow until 5 months ago (when I came from a port from SQL Server). Then I learned something! The default install of Postgres is slow (at least on Windows). And on benchmarks against the baseline SQL Server port I had, it was about 2x slower. BUT there were a number of things I could do to make it faster: a) Use the EnterpriseDB wizard for tuning Postgres --> resulted in a 2x to 3x performance improvement. b) Adjust the way I did updates (there is a way to get Postgres updates to be fast, it just takes some massaging) c) Get rid of views when possible -- I found that certain query compilation optimizations I had taken for granted on SQL Server weren't available. Doing all this resulted in a system that performed faster and w/ less RAM than SQL Server(and I had spent a bunch of time tuning the SQL Server perf in the first place!)

chunkbot 16y ago
I'm liking the new trend of making databases "fast" by default, with "safe" configurations. People who care about safety should take the time make it so; people who want it fast, well, give it to them faster! So maybe PostgreSQL should offer faster defaults?
- dstorrs 16y ago
  It's a design decision. MySQL chose "fast defaults". PostgreSQL chose "safe defaults". If you want the other choice, use the other database.
- hgimenez 16y ago
  Not sure what you mean by "safe" by default. Speed and performance have nothing to do with security, if that's what you mean. The reason that the default Postgres configuration does not perform well is that Postgres runs on a number of operating systems, and it is not possible to make assumptions about the hardware and configuration of the host. For example, increasing configuration parameters like shared_buffers to any useful value will require you to tweak the OS'es kernel resources. The Postgres documentation does a great job at describing the process on a number of operating systems: http://www.postgresql.org/docs/current/interactive/kernel-resources.html http://www.postgresql.org/docs/current/interactive/kernel-re...