7 ms·

I would like to read through this when I'm not so tired. Thank you! From what I can tell, PostgreSQL is sensibly structured. At least, it seems better than MyS

I would like to read through this when I'm not so tired. Thank you! From what I can tell, PostgreSQL is sensibly structured. At least, it seems better than MySQL. The hackiest thing I have heard so far about MySQL is that if you run the exact same SQL text more than once, it will fetch the result from cache. If you run a differently worded query, it will skip the cache, even if the query were to bring back the exact same data. This is different than what I remember about PostgreSQL (correct me if I'm wrong). I remember reading in some book that PostgreSQL just let its rows become memory pages, and if a query resulted in data already in memory, then it got it from memory, otherwise it got it from disk. No need to make sure you didn't insert a space or something into your query the second time.

znep 10y ago
While that is true and the mysql query cache definitely became a scaling issue as machines got bigger with lock contention etc., postgres doesn't even have a similar feature period. Suppose you have a query that is doing a group by on a low cardinality field (eg. state) on a hundred million row table. Large amount of data in, small amount out. postgres has to actually look at all the rows to re-run the query. The mysql query cache only has to pull the tiny number of results out of the cache for that specific query. This is a dangerous feature because there is a super slippery slope as traffic increases... any change to any of the tables invalidates the cache when you need caching most, and hits all queries against those tables at the same time. But if used well in an environment with many copies of the data (replicated or otherwise) where you could manage when updates happened, it can be a lifesaver. InnoDB also has its own buffer pool that caches rows and is in general terms similar to the postgres buffer cache. It is fair to say that mysql has a lot more features like this that are "sometimes life-saving hacks that can bite you hard if you don't understand them" and postgres is much more restrained in ensuring the features are a little more general and thought through.
- brianwawok 10y ago
  MySQL needs the cache more often though with a limit of one index per query. Postgres seems like it more often can combine a few indexes and turn what scans 100k rows in MySQL into bitmasking two indexes and pulling 10 rows. Seems sensible, each has features to support the base design.
  - znep 10y ago
    True point (disclaimer: don't know how mysql has evolved in the past 5 years). Fits with my general feeling of "if you want to optimize for a super specific use case, sometimes mysql can provide some cool tricks for that use case that can be amazing" but if you want a more general solution if you need more flexibility or don't have the knowledge or don't know your future needs, postgres shines.
  - morgo 10y ago
    The limit of one index per query was lifted in 2005, with mysql 5.0.
    - brianwawok 10y ago
      It's now one per table. Poor word choice by me, sorry.
      - morgo 10y ago
        https://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html https://dev.mysql.com/doc/refman/5.5/en/index-merge-optimiza...
- jorgeleo 10y ago
  "While that is true and the mysql query cache definitely became a scaling issue as machines got bigger with lock contention etc., postgres doesn't even have a similar feature period." I am sorry, but I rather not have a feature than having a feature that is questionably implemented.
- maxlybbert 10y ago
  A few years ago, PostgreSQL added a feature where if (1) you ran a query (and there was no "order by" clause); and (2) before it finished, you started an identical query, the second query would simply piggyback off of the first -- until the first finished. After the original query finishes, the second query will retrieve any rows it missed. When they added this optimization, they issued a warning about it, because identical queries without "order by" clauses will return the same results, but without a guarantee of how they're sorted.
sqldba 10y ago
SQL Server does all of these things (for comparison). It compiles queries and stores them in a cache. In many cases it can parameterise simple ones so even if the exact text changes it will pull back the matching plan (and it stores all this data so you can analyse later what plans were compiled, what was reused, which ones were similar, which were recompiled later - though hardly anyone deep dives into it because it's a tar pit of info and why bother instead of just focusing on worst cases). Also when the query pulls data into memory it stays there in the buffer. If the rows change the buffers aren't discarded, they're flushed to disk (along with the log records). So if your query pulls back the exact same data then it's from memory sure but it's not stale or anything. I don't consider any of these problems so I'm surprised how they could be interpreted as bad in other products. I don't understand enough about them to say but it's fun to discus.
- znep 10y ago
  From reading what you wrote, what I think you are talking about is statement caching where it can cache the "how do I run this" but still has to read each row (or indexed row) from the buffer cache. The mysql query cache actually caches the results of the query, see https://dev.mysql.com/doc/refman/8.0/en/query-cache.html https://dev.mysql.com/doc/refman/8.0/en/query-cache.html so it doesn't even need to look at the rows at all. SQL Server may have a similar thing now, I'm not up to date on it, but wanted to call out the distinction based on how you described it. No question, SQL Server has a lot of awesome qualities for the right use case and has come a long long way since the Sybase days.
- brightball 10y ago
  SQL Server does some great things for the data that's in it but if you ever want to get the data out (Elastic Search / Hadoop / Kafka / etc) you are in for a world of pain (and bills). The lock in alone is reason enough to steer clear of it for virtually all use cases. As you start running into scaling issues and need to search for solutions that are faster to implement you'll reach for Enterprise and then for more cores and before you know it you're stuck with a $16,000 / core scaling cost that you can't avoid because you can't get your data out to offload things easily. Dealing with SQL Server on a customer facing system is the stuff of nightmares.
morgo 10y ago
This is only true for one specific kind of cache (query cache) which has been disabled by default for 4 years now. It works well at cheating benchmarks, and masking un-optimized queries. It is not something that I would recommend.

znep 10y ago

While that is true and the mysql query cache definitely became a scaling issue as machines got bigger with lock contention etc., postgres doesn't even have a similar feature period. Suppose you have a query that is doing a group by on a low cardinality field (eg. state) on a hundred million row table. Large amount of data in, small amount out. postgres has to actually look at all the rows to re-run the query. The mysql query cache only has to pull the tiny number of results out of the cache for that specific query. This is a dangerous feature because there is a super slippery slope as traffic increases... any change to any of the tables invalidates the cache when you need caching most, and hits all queries against those tables at the same time. But if used well in an environment with many copies of the data (replicated or otherwise) where you could manage when updates happened, it can be a lifesaver. InnoDB also has its own buffer pool that caches rows and is in general terms similar to the postgres buffer cache. It is fair to say that mysql has a lot more features like this that are "sometimes life-saving hacks that can bite you hard if you don't understand them" and postgres is much more restrained in ensuring the features are a little more general and thought through.

brianwawok 10y ago
MySQL needs the cache more often though with a limit of one index per query. Postgres seems like it more often can combine a few indexes and turn what scans 100k rows in MySQL into bitmasking two indexes and pulling 10 rows. Seems sensible, each has features to support the base design.
- znep 10y ago
  True point (disclaimer: don't know how mysql has evolved in the past 5 years). Fits with my general feeling of "if you want to optimize for a super specific use case, sometimes mysql can provide some cool tricks for that use case that can be amazing" but if you want a more general solution if you need more flexibility or don't have the knowledge or don't know your future needs, postgres shines.
- morgo 10y ago
  The limit of one index per query was lifted in 2005, with mysql 5.0.
  - brianwawok 10y ago
    It's now one per table. Poor word choice by me, sorry.
    - morgo 10y ago
      https://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html https://dev.mysql.com/doc/refman/5.5/en/index-merge-optimiza...
jorgeleo 10y ago
"While that is true and the mysql query cache definitely became a scaling issue as machines got bigger with lock contention etc., postgres doesn't even have a similar feature period." I am sorry, but I rather not have a feature than having a feature that is questionably implemented.
maxlybbert 10y ago
A few years ago, PostgreSQL added a feature where if (1) you ran a query (and there was no "order by" clause); and (2) before it finished, you started an identical query, the second query would simply piggyback off of the first -- until the first finished. After the original query finishes, the second query will retrieve any rows it missed. When they added this optimization, they issued a warning about it, because identical queries without "order by" clauses will return the same results, but without a guarantee of how they're sorted.

sqldba 10y ago

SQL Server does all of these things (for comparison). It compiles queries and stores them in a cache. In many cases it can parameterise simple ones so even if the exact text changes it will pull back the matching plan (and it stores all this data so you can analyse later what plans were compiled, what was reused, which ones were similar, which were recompiled later - though hardly anyone deep dives into it because it's a tar pit of info and why bother instead of just focusing on worst cases). Also when the query pulls data into memory it stays there in the buffer. If the rows change the buffers aren't discarded, they're flushed to disk (along with the log records). So if your query pulls back the exact same data then it's from memory sure but it's not stale or anything. I don't consider any of these problems so I'm surprised how they could be interpreted as bad in other products. I don't understand enough about them to say but it's fun to discus.

znep 10y ago
From reading what you wrote, what I think you are talking about is statement caching where it can cache the "how do I run this" but still has to read each row (or indexed row) from the buffer cache. The mysql query cache actually caches the results of the query, see https://dev.mysql.com/doc/refman/8.0/en/query-cache.html https://dev.mysql.com/doc/refman/8.0/en/query-cache.html so it doesn't even need to look at the rows at all. SQL Server may have a similar thing now, I'm not up to date on it, but wanted to call out the distinction based on how you described it. No question, SQL Server has a lot of awesome qualities for the right use case and has come a long long way since the Sybase days.
brightball 10y ago
SQL Server does some great things for the data that's in it but if you ever want to get the data out (Elastic Search / Hadoop / Kafka / etc) you are in for a world of pain (and bills). The lock in alone is reason enough to steer clear of it for virtually all use cases. As you start running into scaling issues and need to search for solutions that are faster to implement you'll reach for Enterprise and then for more cores and before you know it you're stuck with a $16,000 / core scaling cost that you can't avoid because you can't get your data out to offload things easily. Dealing with SQL Server on a customer facing system is the stuff of nightmares.

morgo 10y ago

This is only true for one specific kind of cache (query cache) which has been disabled by default for 4 years now. It works well at cheating benchmarks, and masking un-optimized queries. It is not something that I would recommend.