3 ms·

The problem with shallow technical knowledge and, even worse, talking with confidence like the author does, is that it can propagate misinformation and loses a

The problem with shallow technical knowledge and, even worse, talking with confidence like the author does, is that it can propagate misinformation and loses a ton of nuance. For example, the author talks very confidently about indexes, and makes a few conclusions, but they aren't as correct as his confidence suggests. > That an index is only useful if it matches the actual query your application is making. If you index on “name plus email” and then start querying on “name plus address”, your index won’t be used and you’re back to the full table scan described in (1) Not true. If you have single column indexes on both name and email, it could use the two indexes, though not as efficient as a single two-column index. If you query "name plus email" and the index is "name, email, age" then it could use the index. > That indexes massively speed up reads, but must slow down database writes and updates, because each change must also be made again in the index “dictionary” Must? No. The performance might be imperceptible and not material at all. If you have a ton of indexes, sure but not if you have a reasonable amount. Shallow technical knowledge is fine but you should also have the humility to acknowledge that when you're dispensing said shallow knowledge. It can also lead to pretty bad engineering decisions if you think your shallow knowledge is enough.

sgarland 1y ago
For that matter, if you index `(name, email)` and query with `name` and `address` as the predicates, unless there's a better option, or the table is tiny, there's an excellent chance the planner will use that index to narrow down the initial result set to filter.
tibbar 1y ago
> Not true. If you have single column indexes on both name and email, it could use the two indexes, though not as efficient as a single two-column index. If you query "name plus email" and the index is "name, email, age" then it could use the index. See, there are databases that implement clever optimizations like this, but those are going to vary widely by database and you would need some domain expertise with that system to know if such optimizations are working. By contrast, this mental model does help you ensure that you can create indexes that are actually helpful in the vast majority of databases. So I think the author's mental model is working out pretty well for him here, honestly.
- rileymat2 1y ago
  You have me curious which databases would not make this optimization, it seems like table stakes?
  - tibbar 1y ago
    For example, many NoSQL databases are less sophisticated in query planning.
- blindriver 1y ago
  The point is that there's misinformation in the things that he's saying. There's a level of confidence that overexceeds the value of the information he is disseminating. If he were the lead engineer of a project, would he make bad decisions because of stuff like this? My guess is yes.
  - tibbar 1y ago
    It's definitely an overly-broad generalization. But these mental models would still improve how many product engineers work with databases, at the expense of a very simple explanation. I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits?
    - sgarland 1y ago
      > I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits? You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well, and as an extension, their RDBMS vendor's implementation of the spec. You have to know your main programming language to get the job; why should the part of it that stores and retrieves all information for your application somehow be lesser? If you don't want to, then you don't get to write queries and design schemas, period. Access them via an API that's been designed by domain experts, otherwise you're putting everyone at risk.

sgarland 1y ago

For that matter, if you index `(name, email)` and query with `name` and `address` as the predicates, unless there's a better option, or the table is tiny, there's an excellent chance the planner will use that index to narrow down the initial result set to filter.

tibbar 1y ago

> Not true. If you have single column indexes on both name and email, it could use the two indexes, though not as efficient as a single two-column index. If you query "name plus email" and the index is "name, email, age" then it could use the index. See, there are databases that implement clever optimizations like this, but those are going to vary widely by database and you would need some domain expertise with that system to know if such optimizations are working. By contrast, this mental model does help you ensure that you can create indexes that are actually helpful in the vast majority of databases. So I think the author's mental model is working out pretty well for him here, honestly.

rileymat2 1y ago
You have me curious which databases would not make this optimization, it seems like table stakes?
- tibbar 1y ago
  For example, many NoSQL databases are less sophisticated in query planning.
blindriver 1y ago
The point is that there's misinformation in the things that he's saying. There's a level of confidence that overexceeds the value of the information he is disseminating. If he were the lead engineer of a project, would he make bad decisions because of stuff like this? My guess is yes.
- tibbar 1y ago
  It's definitely an overly-broad generalization. But these mental models would still improve how many product engineers work with databases, at the expense of a very simple explanation. I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits?
  - sgarland 1y ago
    > I think the interesting question is like, if I have X amount of time and mental bandwidth to learn about a technology, what's the most helpful lossy compression of concepts that fits? You cannot possibly know if your condensed version is accurate or sufficient if you can't point to the author of it and definitively state that they knew the original material well enough to summarize it. I also continue to push back on the idea that [backend] devs shouldn't need to know SQL extremely well, and as an extension, their RDBMS vendor's implementation of the spec. You have to know your main programming language to get the job; why should the part of it that stores and retrieves all information for your application somehow be lesser? If you don't want to, then you don't get to write queries and design schemas, period. Access them via an API that's been designed by domain experts, otherwise you're putting everyone at risk.