Some opinionated thoughts on SQL databases
blog.nelhage.com
blog.nelhage.com
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.
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.
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.
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.
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.
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.
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.)
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
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 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.
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.
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]