
How does a relational database work? (2017) - PeterZaitsev
http://coding-geek.com/how-databases-work/
======
averros
"Nowadays, many developers don’t care about time complexity … and they’re
right!"

Massive facepalm.

A developer who doesn't care about complexity (both structural complexity of
the code, and execution-time complexity of algorithms he uses) is simply
incompetent. No ifs and no buts about it.

~~~
kjeetgill
I mean, the very next sentences are:

> But when you deal with a large amount of data (I’m not talking about
> thousands) or if you’re fighting for milliseconds, it becomes critical to
> understand this concept. And guess what, databases have to deal with both
> situations!

I think they're just anticipating an audience who only deal with fragments of
data in frontend or "backend" services. That statement holds when you're never
really dealing with more than tens of elements.

~~~
craz8
I fixed a performance bug today that was caused by too many queries across
tens of rows of data. Hey, why keep it in memory when the database can get it
for you in only 5ms! 80 times per call

Admittedly, this was a bit of an outlier case, but you get enough of those and
you’re spending real money on extra hardware to keep up

~~~
quietbritishjim
The original quote is not about things generally taking a long time, but about
time _complexity_ i.e. differences between things like O(n), O(n log n) and
O(n^2). For example, if the way you fixed that performance bug reduced the
time by a fixed multiple then you didn't affect the time complexity, even if
it's now much faster.

Maybe you already understood that but your bug description makes it sound like
complexity wasn't necessarily the issue.

~~~
craz8
My case was about time complexity from calling the DB 10x times more than
necessary. Even though these things are all fast now, as an engineer, you
still need to know the techniques to avoid the slow bits

Although my example was only O(n^2), the constant of each n being over the
wire to the DB blew the runtime up to a significant number. So even an
n-squared algorithm with small data sets can kill your app. We changed it to
be O(n) over the wire to make it reasonable

~~~
rsanek
If you're making the same db call for n items, 10 calls and 1 call are both
O(n).

------
szafranek
All I see is: "Error establishing a database connection".

Is it some terribly ironic post?

~~~
lpill
Try the cached version:
[http://webcache.googleusercontent.com/search?q=cache:z-c0s5-...](http://webcache.googleusercontent.com/search?q=cache:z-c0s5-AdZ8J:coding-
geek.com/how-databases-work/+&cd=12&hl=en&ct=clnk)

------
craz8
Recently, a book was published about implementing Git in Ruby as a way of
learning Git internals. There was a long thread here about that, and about
others porting the code to their language of choice

It occurred to me that a book about building a database system in an easy to
read language would be a great way to get hands on with the various algorithms
used by modern database technologies - and may be more broadly useful too

~~~
samrat
I haven't been able to get my hands on the book, but Edward Sciore's "Database
Design and Implementation" is supposed to be such a book.

~~~
voltagex_
There is no logical reason this book should be as expensive as it is:
[https://www.abebooks.com/servlet/SearchResults?cm_sp=SearchF...](https://www.abebooks.com/servlet/SearchResults?cm_sp=SearchFwi-
_-SRP-_-
Results&kn=sciore&sortby=17&tn=Database%20Design%20and%20Implementation)

Worldcat shows some libraries have it:
[https://www.worldcat.org/title/database-design-and-
implement...](https://www.worldcat.org/title/database-design-and-
implementation/oclc/680582801)

------
jeffdo
...thanks for reminding me of my advanced relational db exam I just had today!

But in all seriousness, the fundamentals of how relational DB's work and
physically execute queries should be at least in the back of the mind of any
engineer who writes SQL for a living. Great article!

------
kinard
"Error establishing a database connection" \- Not very well apparently :)

------
dddw
"Error establishing a database connection" hn hug of death?

------
ironchest
I get "Error establishing a database connection" :_D

------
Luctst
On of the best article i could read on database.

------
Golfkid2Gadfly
Very good indeed, Quite the explanation, the storage part would be an
interesting thing to see, for instance in SQLite, what is this binary file
created in the filesystem, or how is it’s data organized.

~~~
bionoid
SQLite documentation covers this quite well, see "Database File Format" [0] or
have a look at pager.c source code [1]. SQLite database is actually
recommended by Library of Congress for archiving [2]

0:
[https://www.sqlite.org/fileformat.html](https://www.sqlite.org/fileformat.html)

1:
[https://sqlite.org/src/artifact/422fd8cfa59fb917](https://sqlite.org/src/artifact/422fd8cfa59fb917)

2:
[https://www.loc.gov/preservation/resources/rfs/data.html](https://www.loc.gov/preservation/resources/rfs/data.html)

