Really nice guide.
Things to know about databases
101–110 of 247 posts
Re: Things to know about databases
#102Earlier quoted context omitted.
You don't actually address the point. If your database engine is an old design or your data is small by modern standards, then a B+tree will be one of the few indexing algorithms available and if the data is small it will probably work. Modern database kernels targeting modern hardware and storage densities typically aren't using B+trees and the reasons why are well-understood. No one with any sense is using a B+tree…
> You don't actually address the point. You said that B-Trees "use in indexing has dwindled with time". This is demonstrably false. > Back then I used them ubiquitously but I honestly don't remember the last time I've seen one in a new design. Even if that was true (which it definitely isn't), why would anybody judge the commercial or scientific relevance of B-Trees by looking at what new systems do? There are very f…
Read this guy's past posts, you will save yourself a lot of time. He does a lot of this sort of thing.
Re: Things to know about databases
#103Earlier quoted context omitted.
> You don't actually address the point. You said that B-Trees "use in indexing has dwindled with time". This is demonstrably false. > Back then I used them ubiquitously but I honestly don't remember the last time I've seen one in a new design. Even if that was true (which it definitely isn't), why would anybody judge the commercial or scientific relevance of B-Trees by looking at what new systems do? There are very f…
Read this guy's past posts, you will save yourself a lot of time. He does a lot of this sort of thing.
Thanks for the tip
Re: Things to know about databases
#104This is great. Thanks for sharing!
Re: Things to know about databases
#105Earlier quoted context omitted.
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…
I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.
SQL has some annoying quirks but on balance I am not sure there is the distaste for SQL that you claim. It's fine, even good in many circumstances, but also too low level for some tasks which pushes common problems onto application developers to solve over and over again or build up layers of dependencies to utilize someone else's solution. That's not the fault of SQL but the fault of the RDBMS.
Our layers of abstractions atop SQL along with approaches taken by DBMSes outside of the common relational names have shown that there is room for improvement within those tasks, able to be done natively by the DBMS, and it does not have to come at the expense of losing SQL when you need to be able to describe lower level questions. Different tools for different jobs, but little will within the RDMBS space to explore those other tools.
Re: Things to know about databases
#106#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…
> Except the most important problem: A pleasant API
A pleasant API is clearly not the most important business problem a database is there to solve.
The data in it is presumably the life and blood of the business, whereas the API is something only developers need to deal with.
But that aside, the interface will be SQL which is quite powerful, long-lived (most important) and, fortunately, very pleasant.
Re: Things to know about databases
#107Earlier quoted context omitted.
What kinds of indexing structures are used instead, and how do they differ from B+trees? Do you have examples of which relational databases have replaced B+tree indexes?
I know Clickhouse uses MergeTrees which are different from B+trees. However it can't really be used as an RDBMS. It's especially bad at point reads. https://en.wikipedia.org/wiki/Log-structured_merge-tree
There are projects that use LSMTs as the storage engine for RDBMS' (like RocksDB); I'm not sure it's accurate to say "they can't be use as an RDMBS".
Re: Things to know about databases
#108Earlier quoted context omitted.
I think this is and will continue to be a common use case. I'm very thankful for these applications that the data was still stuck in a crusty old relational database for me to work on top of as I built a new application. It's going to be interesting when this same problem occurs years from now when people are trying to reverse schemas from NoSQL databases or if they become difficult to extract. The only sticking poin…
>business logic is put into stored procedures This is a double-edged sword. I have seen massive business logic baked into stored procedures...so much so, that the applications themselves are rather slim. If this stored procedure code is properly versioned and otherwise managed, this is not entirely bad. If the data model is sound, I don't worry that much...stored procs vs 100KLOC of Java code? I can tell you what is…
Also an old timer. I’ve gone from mostly complex app and no DB logic to mostly heavily optimised DB and lots of procs. They protect the DB, make carefully crafted interfaces available and allow changes to the implementation.
Except for eg infinitely scalable cloud data stores like Google’s, or for ML where it’s just massive data and you need a dumb-ish store of many GB of parquet.
Re: Things to know about databases
#109#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…
With SQL you kind of have two options/extremes that are unpleasant in their own way.
You either model things in a very domain specific and classic fashion. Here you get the benefit of being quite declarative and ad-hoc queries are natural. Also your schema is stronger, as in it can catch more misuse by default. But this kind of schema tends to have _logical_ repetition and is so specific that change/evolution is quite painful, because every new addition or use-case needs a migration.
Or you model things very generically, more data driven than schema driven. You lose schema strength and you definitely lose sensible ad-hoc queries. But you gain flexibility and generality and can cover much more ground.
You can kind of get around this dichotomy with views, perhaps triggers and such. In an ideal world you'd want the former to be your views and the latter to be your foundational schema.
But now you get into another problem, which is that homogeneous tables are just _super_ rigid as result sets. There are plenty of very common types you cannot cover. For example tagged unions, or any kind of even shallowly nested result (extremely common use case), or multiple result groups in one query. All of these things usually mean you want multiple queries (read transaction) or you use non-SQL stuff like building JSONs (super awkward).
If you can afford to use something like SQLite, then some of the concerns go away. The DB is right there so it's fine to query it repeatedly in small chunks.
I wonder if we're generally doing it wrong though, especially in web development. Shouldn't the backend code quite literally live on the database? I wish my backend language would be a data base query language first and a general purpose language second, so to speak. Clojure and its datalog flavors come close. But I'm thinking of something even more integrated and purpose built.
Re: Things to know about databases
#110Great post. Also highly recommend Designing Data-Intensive Applications by Martin Kleppmann ( https://www.amazon.com/Designing-Data-Intensive-Applications... ). The sections on "Storage and Retrieval", "Replication", "Partitioning" and "Transactions" really opened up my eyes!
Absolutely loved the book. Can someone recommend similar books?
I have not read it personally, but I've seen 'How Query Engines Work' highly recommended several times before. I have a procrasinatory tab open to check it out some day.