5 ms·
Many people learn databases by learning sql. I recommend people learn DBs by doing a class like this and understanding b-trees. Nearly Everything about the st
by tonymet 3y ago
Many people learn databases by learning sql.
I recommend people learn DBs by doing a class like this and understanding b-trees.
Nearly Everything about the strengths & weaknesses of RDBMS is understood through knowing btrees and how they affect key insertion, lookup & ordering.
Most people try to speed up a DB by adding indexes -- but when you realize you're just adding one tree on top of another you're just masking the problem.
Some problems are well suited toward b-trees, and many aren't.
SQL is just a query interface to a remote b-tree system.
- lichtenberger 3y agoWell, it may be a B-tree, or an LSM-tree, a trie or whatever index structure suits... Also, of course you may have covering indexes.
- ljm 3y agoI think this is too reductive. A b-tree isn't the only indexing strategy, and it's well understood that an index is intended to improve read performance at the expense of write performance because the common case is that your DB handles a lot more reads than writes. > but when you realize you're just adding one tree on top of another you're just masking the problem. What is the problem and how do you propose to solve it without touching indexes? They are a hard requirement for any decent sized table.
- tonymet 3y agoIf you are using oracle, mysql , mssql you are typically using b-tree index. mysql supports hash index but i rarely see it applied in prod (probably just habits or lack of familiarity).
- roetlich 3y agoYes, but a lot of the cool, new DBs use LSM trees. The post we're currently discussing also spends more time explaining LSM trees + WAL, than it does B-trees. But your point is still valid. :)
- tonymet 3y agoUsing data structures that match the performance profile. if the DB doesn't support that, using another solution.
- linl 3y agoI agree with this. B-trees vs hash indices, I/O hierarchy, process model, etc. Also in the modern day, it's valuable to learn common strategies behind column-oriented databases such as late tuple materialization, deferred execution, linear scans vs. binary search, instruction pipelining. Once you're familiar with all this, you'd find sometimes in the field what you really need is a simple flat file or an embeddable database like RocksDB, not a DBMS.
- bogomipz 3y ago>"I agree with this. B-trees vs hash indices, I/O hierarchy, process model, etc." I'm familiar with all of the concepts in the context of databases except "process model." Is this the same as data model? Could you elaborate if not?
- linl 3y agoThere are several types of process models that's suitable for a DBMS: 1 process per DBMS worker, 1 thread per DBMS worker, a lightweight thread pool, a process pool, etc. Hellerstein, Stonebraker and Hamilton (2007) is a great introduction on this.
- arendtio 3y ago> When you realize you're just adding one tree on top of another you're just masking the problem. Can you elaborate with a specific example, please?