4 ms·

Some opinionated thoughts on SQL databases

72 points by xyzzy_plugh 6y ago

xyzzy_plugh 6y ago
> In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times. This is opposite my experience. While I do suspect MySQL is more widely deployed, I've seen MySQL more frequently among less-mature teams, and Postgres more frequently among very-mature teams. I've also seen MySQL burn folks more than Postgres. I do think, however, that both have enjoyed periods of popularity and stability. Right now, I think they're both quite stable, so it's really a coin toss. I find MySQL's behavior to be pretty weird compared to Postgres, and Postgres knowledge tends to translate pretty well to other databases, which are frequently derived from Postgres (Vertica, Redshift, Snowflake, Cockroach, etc.)
- throwdbaaway 6y ago
  It sounds like the author's conclusion, that he would pick MySQL over Postgres, was heavily influenced by his recent encounter with a Postgres performance cliff where some django ORM code inadvertently uses subtransactions: https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-b316-31a86f737acc https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-... For that, I would like to counter with the 10+ years old quadratic locking bug that remains unsolved in InnoDB: https://bugs.mysql.com/bug.php?id=53825 https://bugs.mysql.com/bug.php?id=53825
- closed 6y ago
  What you described is pretty similar to my experience. I wonder if part of the author's sentiment about it being more widely deployed can be explained in part by stack overflow trends data. Basically, MySQL used to make up a much larger % of questions on the site (compared to postgres). But postgres had been growing, and MySQL shrinking, so now they're about the same on the site. https://insights.stackoverflow.com/trends?tags=mysql%2Cpostgresql https://insights.stackoverflow.com/trends?tags=mysql%2Cpostg...
Friesen36 6y ago
Thanks to this article I can learn more. Expand my knowledge and abilities. Actually the article is very real. https://alaskasworld.run/ https://alaskasworld.run/
slt2021 6y ago
is it true that MySQL scales much better than postgres? for example ServiceNow is using customized mysql engine to store and search all data for all its clients. Doing something like that in pg is probably extremely hard. Would love to hear people's thoughts on this
- skunkworker 6y ago
  This depends what you mean by "scaling". Postgres can easily handle 10s of Terabytes on a single server (though this is dependent upon your use case), but when it comes to multiple host sharded databases at the moment MySQL has some more mature options like Vitess [1], where your options in Postgres are things like Citus [2] which just recently open sourced their shard mover, but still have the HA and replication feature behind expensive enterprise pricing. For most people not dealing with the multiple terabyte database size, Postgres is what I would definitely recommend. But once you get above that, then there are no easy answers and you would need to look at what YouTube did with Vitess [3] [1] https://vitess.io/ https://vitess.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ [3] https://vitess.io/docs/overview/history/ https://vitess.io/docs/overview/history/
  - slt2021 6y ago
    Thank you very much!
- redis_mlc 6y ago
  > is it true that MySQL scales much better than postgres? It's a strange question since "scales" depends on your site, but the short answer is yes: 1) Most of the FAANG companies run on MySQL, and they're approaching a trillion dollars in value each. 2) MySQL doesn't need a vacuum process, and isn't sensitive to number of indexes per table (write amplification) like Postgres is. 3) MySQL has more built-in replication methods than Postgres, which is very important for enterprises. 4) Postgres has a complex grant syntax that most devs don't understand. 5) Postgres has compliance certification issues around COPY FROM/COPY TO. There needs to be a grant to disable that per user. Both MySQL and Postgres are great Open Source databases. I use both, but prefer MySQL for the above reasons. I consider Postgres to be substantially weaker for enterprise use cases though. Source: DBA.
lihaoyi 6y ago
I maintain a medium scale/complexity service, backed by a postgres database, and this post resonates. While postgres has a great storage engine, the application interface going through the query planner is awful. We've had outages not just due to a small increase in table size causing 10000x slowdowns when the query planner crosses a threshold, but also similar outages when a small decrease in table size did the same thing! Who would have thought that deleting old/unused data would make your website go down due to 10000x slower database queries? Performance is a feature, and unpredictable 10000x slowdowns when the database decides to suddenly start doing billion-row table scans in production is just as bad as any other system crash About a decade ago I interviewed with a (at the time) hot new startup, and during process they told me "we don't do joins here". I thought that was crazy, but here I am a decade later sometimes telling my colleagues "we can't do joins here" as we break up queries, paying the added round trips to help the query planner do the right thing. Oh how the turn tables.
- skunkworker 6y ago
  I'm curious at what threshold you're seeing that kind of query planner disparity. I use postgres professionally at work and when testing a feature I'll load up a local database with 100k to 500k rows in order to simulate a production environment, normally this is more than enough to get an almost identical query planner result (after running explain (analyze, buffers) etc). But I haven't seen cases where a 10000x slowdown occurs after deleting data, unless you're hitting data where it spills over an in-memory sort, or is no longer efficient to do a heap/sequential scan. The hardest thing with postgres and query planning IMO is understanding what kind of index would need to be used, and ordering composite indexes accordingly. I've extensively used a sortable date/id column as the last entry in an index as postgres will end up only doing an index scan. On the other side there are times where the indexes for a table are greater as whole then the data contained therein. Though if you're running in the multiple billions of rows in a single table and haven't paritioned/sharded it in some way, YMMV.
  - xyzzy_plugh 6y ago
    Yes, I agree with this entirely. While not perfect, if you treat your data as real data, instead of magical database rows, you tend to get predictable results. Pretend the database doesn't exist and these are data structures in your process space -- where do the access patterns degenerate, etc.
zeroimpl 6y ago
The note that savepoints in postgresql don’t perform well is news to me. They are used extensively in different applications. Even in plpgsql, any time you catch an exception, it’s using savepoints under the hood. Perhaps there is room for more optimization there.
aargh_aargh 6y ago
> why isn’t data definition — which is a much better fit, in my opinion, for a declarative model — also declarative? My opinion on this one point (apart from DDL being ossified by the ISO standards) is that DDL being imperative is useful because it's used in migrations and imperative approach gives you more control over protecting existing data (i.e. not just the schema). Some contrived examples: 1) shortening a varchar - are you sure you want to just trim the values or do you want to have the option of doing something with them? 2) renaming a column - would have to be a explicit, a special case in declarative DDL 3) dropping a column - maybe you want to transform the existing values instead of just throwing them away Sure, it could all be probably handled even in a declarative DDL. My point is that in declarative DDL you would have to think to explicitly protect your data. In imperative DDL you see what you are doing.
- erik_seaberg 6y ago
  What I'd like to see is: here is the desired schema, please compare it to the old schema and propose what to do that doesn't irreversibly destroy any information unless we say to.
Annatar 6y ago
For years I worked one week on rotation doing database support for "developers" while I worked in the database engineering department. For every one legitimate bug, there were 99 cases where a familiar pattern consistently emerged: - a "developer" did not have sufficient knowledge of a relational database management system: "what is an index and where and how do I create one?" - the "developer" was using object-oriented programming, and writes to stable storage would end up being deserialization of objects; - the "developer" always used an ORM because he either did not know SQL, or his SQL programming skills were poor; - code generated by an ORM was huge: 32 KB of SQL querying every literal value, which of course would cause a huge, completely unnecessary slowdown.

72 points by xyzzy_plugh 6y ago

> In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times. This is opposite my experience. While I do suspect MySQL is more widely deployed, I've seen MySQL more frequently among less-mature teams, and Postgres more frequently among very-mature teams. I've also seen MySQL burn folks more than Postgres. I do think, however, that both have enjoyed periods of popularity and stability. Right now, I think they're both quite stable, so it's really a coin toss. I find MySQL's behavior to be pretty weird compared to Postgres, and Postgres knowledge tends to translate pretty well to other databases, which are frequently derived from Postgres (Vertica, Redshift, Snowflake, Cockroach, etc.)

throwdbaaway 6y ago
It sounds like the author's conclusion, that he would pick MySQL over Postgres, was heavily influenced by his recent encounter with a Postgres performance cliff where some django ORM code inadvertently uses subtransactions: https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-b316-31a86f737acc https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-... For that, I would like to counter with the 10+ years old quadratic locking bug that remains unsolved in InnoDB: https://bugs.mysql.com/bug.php?id=53825 https://bugs.mysql.com/bug.php?id=53825
closed 6y ago
What you described is pretty similar to my experience. I wonder if part of the author's sentiment about it being more widely deployed can be explained in part by stack overflow trends data. Basically, MySQL used to make up a much larger % of questions on the site (compared to postgres). But postgres had been growing, and MySQL shrinking, so now they're about the same on the site. https://insights.stackoverflow.com/trends?tags=mysql%2Cpostgresql https://insights.stackoverflow.com/trends?tags=mysql%2Cpostg...

Friesen36 6y ago

Thanks to this article I can learn more. Expand my knowledge and abilities. Actually the article is very real. https://alaskasworld.run/ https://alaskasworld.run/

slt2021 6y ago

is it true that MySQL scales much better than postgres? for example ServiceNow is using customized mysql engine to store and search all data for all its clients. Doing something like that in pg is probably extremely hard. Would love to hear people's thoughts on this

skunkworker 6y ago
This depends what you mean by "scaling". Postgres can easily handle 10s of Terabytes on a single server (though this is dependent upon your use case), but when it comes to multiple host sharded databases at the moment MySQL has some more mature options like Vitess [1], where your options in Postgres are things like Citus [2] which just recently open sourced their shard mover, but still have the HA and replication feature behind expensive enterprise pricing. For most people not dealing with the multiple terabyte database size, Postgres is what I would definitely recommend. But once you get above that, then there are no easy answers and you would need to look at what YouTube did with Vitess [3] [1] https://vitess.io/ https://vitess.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ [3] https://vitess.io/docs/overview/history/ https://vitess.io/docs/overview/history/
- slt2021 6y ago
  Thank you very much!
redis_mlc 6y ago
> is it true that MySQL scales much better than postgres? It's a strange question since "scales" depends on your site, but the short answer is yes: 1) Most of the FAANG companies run on MySQL, and they're approaching a trillion dollars in value each. 2) MySQL doesn't need a vacuum process, and isn't sensitive to number of indexes per table (write amplification) like Postgres is. 3) MySQL has more built-in replication methods than Postgres, which is very important for enterprises. 4) Postgres has a complex grant syntax that most devs don't understand. 5) Postgres has compliance certification issues around COPY FROM/COPY TO. There needs to be a grant to disable that per user. Both MySQL and Postgres are great Open Source databases. I use both, but prefer MySQL for the above reasons. I consider Postgres to be substantially weaker for enterprise use cases though. Source: DBA.

lihaoyi 6y ago

I maintain a medium scale/complexity service, backed by a postgres database, and this post resonates. While postgres has a great storage engine, the application interface going through the query planner is awful. We've had outages not just due to a small increase in table size causing 10000x slowdowns when the query planner crosses a threshold, but also similar outages when a small decrease in table size did the same thing! Who would have thought that deleting old/unused data would make your website go down due to 10000x slower database queries? Performance is a feature, and unpredictable 10000x slowdowns when the database decides to suddenly start doing billion-row table scans in production is just as bad as any other system crash About a decade ago I interviewed with a (at the time) hot new startup, and during process they told me "we don't do joins here". I thought that was crazy, but here I am a decade later sometimes telling my colleagues "we can't do joins here" as we break up queries, paying the added round trips to help the query planner do the right thing. Oh how the turn tables.

skunkworker 6y ago
I'm curious at what threshold you're seeing that kind of query planner disparity. I use postgres professionally at work and when testing a feature I'll load up a local database with 100k to 500k rows in order to simulate a production environment, normally this is more than enough to get an almost identical query planner result (after running explain (analyze, buffers) etc). But I haven't seen cases where a 10000x slowdown occurs after deleting data, unless you're hitting data where it spills over an in-memory sort, or is no longer efficient to do a heap/sequential scan. The hardest thing with postgres and query planning IMO is understanding what kind of index would need to be used, and ordering composite indexes accordingly. I've extensively used a sortable date/id column as the last entry in an index as postgres will end up only doing an index scan. On the other side there are times where the indexes for a table are greater as whole then the data contained therein. Though if you're running in the multiple billions of rows in a single table and haven't paritioned/sharded it in some way, YMMV.
- xyzzy_plugh 6y ago
  Yes, I agree with this entirely. While not perfect, if you treat your data as real data, instead of magical database rows, you tend to get predictable results. Pretend the database doesn't exist and these are data structures in your process space -- where do the access patterns degenerate, etc.

zeroimpl 6y ago

The note that savepoints in postgresql don’t perform well is news to me. They are used extensively in different applications. Even in plpgsql, any time you catch an exception, it’s using savepoints under the hood. Perhaps there is room for more optimization there.

aargh_aargh 6y ago

> why isn’t data definition — which is a much better fit, in my opinion, for a declarative model — also declarative? My opinion on this one point (apart from DDL being ossified by the ISO standards) is that DDL being imperative is useful because it's used in migrations and imperative approach gives you more control over protecting existing data (i.e. not just the schema). Some contrived examples: 1) shortening a varchar - are you sure you want to just trim the values or do you want to have the option of doing something with them? 2) renaming a column - would have to be a explicit, a special case in declarative DDL 3) dropping a column - maybe you want to transform the existing values instead of just throwing them away Sure, it could all be probably handled even in a declarative DDL. My point is that in declarative DDL you would have to think to explicitly protect your data. In imperative DDL you see what you are doing.

erik_seaberg 6y ago
What I'd like to see is: here is the desired schema, please compare it to the old schema and propose what to do that doesn't irreversibly destroy any information unless we say to.

Annatar 6y ago

For years I worked one week on rotation doing database support for "developers" while I worked in the database engineering department. For every one legitimate bug, there were 99 cases where a familiar pattern consistently emerged: - a "developer" did not have sufficient knowledge of a relational database management system: "what is an index and where and how do I create one?" - the "developer" was using object-oriented programming, and writes to stable storage would end up being deserialization of objects; - the "developer" always used an ORM because he either did not know SQL, or his SQL programming skills were poor; - code generated by an ORM was huge: 32 KB of SQL querying every literal value, which of course would cause a huge, completely unnecessary slowdown.