Some opinionated thoughts on SQL databases(blog.nelhage.com)
blog.nelhage.com
Some opinionated thoughts on SQL databases
https://blog.nelhage.com/post/some-opinionated-sql-takes/
19 comments
Yes having worked with MS SQL Server for many years then taking a look at PG the first thing that really surprised me was lack of hints. Having been through similar situations with MSSQL but being able to force it to do the right thing was a life saver.
This is about repeatability and stability. I would rather the DB run slower so long as it does so consistently and repeatably until a human can profile the issue and make a source code change to speed it up. Having the optimizer just decide in the middle of the night on one of a number of different production databases is a nightmare.
MSSQL now days lets you save and load full plans and freeze plans, but the reality is just some simple hints go a long way, like which index to use and what type of join to use.
Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. The client can manually prepare a plan but only reuse on the same connection.
This is about repeatability and stability. I would rather the DB run slower so long as it does so consistently and repeatably until a human can profile the issue and make a source code change to speed it up. Having the optimizer just decide in the middle of the night on one of a number of different production databases is a nightmare.
MSSQL now days lets you save and load full plans and freeze plans, but the reality is just some simple hints go a long way, like which index to use and what type of join to use.
Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. The client can manually prepare a plan but only reuse on the same connection.
Lack of hint is definitely a controversial topic. There are situations where there is only one obvious way to execute the query, in which case a query hint would help to prevent the database engine from going berserk. However, there are also situations where different query plans should be used according to the parameters, in which case a query hint would harm.
We now also have some empirical evidence on how the query planner evolve/devolve based on this decision. InnoDB always relies heavily on query hint, and recently it has given up on trying to get the planner to support some simple pagination queries correctly, after killing too many production systems that didn't add a hint: https://dev.mysql.com/worklog/task/?id=13929. It basically declares that "we know our query planner sucks for this class of queries, here's a flag to disable that portion of the query planner"
Meanwhile, in recent releases, Postgres started to allow statistics to be created for correlated columns, so that the query planner can have the necessary information to come up with the correct plan: https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correla...
> Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in.
This is actually the thing I hate the most about MSSQL. If I allow plans to be cached, then some queries would end up using some bad plans intended for parameters with very different data distribution. If I disallow plans to be cached, then the query planner can take 1~2 second just to come up with a plan for a rather simple query with 2 levels of nesting. A rock and a hard place.
We now also have some empirical evidence on how the query planner evolve/devolve based on this decision. InnoDB always relies heavily on query hint, and recently it has given up on trying to get the planner to support some simple pagination queries correctly, after killing too many production systems that didn't add a hint: https://dev.mysql.com/worklog/task/?id=13929. It basically declares that "we know our query planner sucks for this class of queries, here's a flag to disable that portion of the query planner"
Meanwhile, in recent releases, Postgres started to allow statistics to be created for correlated columns, so that the query planner can have the necessary information to come up with the correct plan: https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correla...
> Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in.
This is actually the thing I hate the most about MSSQL. If I allow plans to be cached, then some queries would end up using some bad plans intended for parameters with very different data distribution. If I disallow plans to be cached, then the query planner can take 1~2 second just to come up with a plan for a rather simple query with 2 levels of nesting. A rock and a hard place.
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.
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.
This is our case. A tiny staging environment will cause two tables to have billions of rows. This kills the Postgres.
We are looking at moving this data out of the database and query it using Drill or similar. But if anyone has tips for handling huge tables in Postgres (yes, we partition already, this is a singe partition) I’m all ears.
We are looking at moving this data out of the database and query it using Drill or similar. But if anyone has tips for handling huge tables in Postgres (yes, we partition already, this is a singe partition) I’m all ears.
If it's time series data check out the timescaledb extension.
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.
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.
> 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.)
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.)
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-...
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
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
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%2Cpostg...
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%2Cpostg...
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.
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.
> 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.
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.
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.
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.
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
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/
[2] https://www.citusdata.com/
[3] https://vitess.io/docs/overview/history/
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/
[2] https://www.citusdata.com/
[3] https://vitess.io/docs/overview/history/
Thank you very much!
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.