Databases with V

#AMA Time 1. Two similar questions [paraphrased]: why not enable threads in channel instead of google sheet? Threads are applicable only in Telegram, not possible in WhatsApp. I will probably think about Discord or similar which have community and moderation tooling. 2. I want to write my own database to have a much better understanding of how things work! Where do I start? [another one]...I want to know where should I start to really understand and build a database for myself to really grasp the theory. If you prefer courses, CMU DB 15-445 lectures are the best out there. For books, I recommend Database Internals and Architecture of a Database System by Stonebraker. This is how I did: - Wrote an in-memory B Tree - Converted that to store on disk - Then I found about Bitcask, wrote CaskDB 3. Can you write more about your database project caskdb so that other people can try to make it in different languages other than python like golang. Definitely! Some people have done this too. I have seen implementations in Rust, C, Ruby, and OCaml. Right now, I am going through the project again to write it in C. It will take a bigger post to explain the internals of CaskDB, so I will link to the explanation by fren Mr Karan - https://mrkaran.dev/posts/barreldb However, Bitcask is simple, start with the paper so that you will also develop the skill of reading and implementing research papers. The paper is like 3 pages - https://riak.com/assets/bitcask-intro.pdf There is an ordered tasks list in the repo, do the same in any language - https://github.com/avinassh/py-caskdb?#tasks Then there are hints on writing tests, but they just explain the requirements - https://github.com/avinassh/py-caskdb/blob/master/hints.md The repo also has distinct challenges, split in levels, L1 to L3. Implementing all of them is challenging, but it should be very rewarding. You implement advanced features like crash safety to garbage collector. Do note that CaskDB is an open source project and contributions are welcome. A tremendous shout out to PaulisMatrix who recently contributed some level 1 features - https://github.com/avinassh/go-caskdb/pulls?q=is%3Apr+is%3Amerged+author%3A%22PaulisMatrix%22+ 4. Any other WhatsApp community you would recommend for content like this? Unfortunately, I know none. Outside WhatsApp: FOSS United - https://t.me/joinchat/RUh3Qb2fb1k0pQbO Multiprocess Discord - https://discord.gg/PmmShtF7WS The Engineering Org - https://discord.gg/WtyYEctdGF TigerBeetle’s Slack. You may also consider joining project specific communities like RocksDB, Postgres etc. WhatsApp (or even Telegram) channels have huge discoverability problem. How do you find similar content? It affects mine too, how do you grow these channels? Only time I have seen people join is when I have shared the links on Twitter and other platforms. So, share the links in your social networks too! 5. Why cant we use low/2 + high/2 (assuming it wont round off low/2 if we are dealing with float div and we can floor the final result [This is context to the previous binary search post] I think you would have to adjust the algo a bit, as you have rightly mentioned in the question. That simply complicates it. e.g. (3 + 5) / 2 = 4 3/2 + 5/2 = ?

Stream SQLite Python function to extract all the rows from a SQLite database file concurrently with iterating over its bytes, without needing random access to the file. https://github.com/uktrade/stream-sqlite

Cthulhu on the cover of the C book. So apt :)

I asked my fren Mr. xyz, a C veteran, worked in battery controllers to databases, on learning C. The advice I got: Follow these books (in order): 1. K&R C 2. Programming in the UNIX Environment 3. Programming Abstractions in C An alternate path: 1. Effective C 2. Modern C K&R Style and Modern Style are really different, both are prevalent in different areas. Start with one, completely understand the philosophy, idioms and patterns and move to the other. Do the K&R and PAC exercises in the modern style. These exercises are invaluable. They look simple but are a pretty good test of understanding C. The depth comes only when you work with simple code and see how small change has big effect.

One more :) This was quite challenging but fun! I learned about SQLite inserts, internals of VDBE (https://www.sqlite.org/opcode.html), optimisations SQLite does while doing bulk inserts https://github.com/tursodatabase/libsql/pull/1048

A visual, interactive guide to bloom filters: https://samwho.dev/bloom-filters/

Two new SQLite tools I found this week: 1. SQLite-web - https://github.com/coleifer/sqlite-web Web-based SQLite database browser written in Python. You do:

$ sqlite_web /path/to/database.db

and then you can access your db at localhost:8080 or over the web 2. wddbfs - https://github.com/adamobeng/wddbfs webdavfs provider which can read the contents of sqlite databases i.e. it lets you mount the sqlite database as a filesystem. How cool is that!

wddbfs --anonymous --db-path=/path/to/database.db

read more here: https://adamobeng.com/wddbfs-mount-a-sqlite-database-as-a-filesystem/

> The INGRES relational database management system (DBMS) was implemented during 1975-1977 at the Univerisity of California. Since 1978 various prototype extensions have been made to support distributed databases [STON83a], ordered relations [STON83b], abstract data types [STON83c], and QUEL as a data type [STON84a]. In addition, we proposed but never prototyped a new application program interface [STON84b]. The University of California version of INGRES has been ‘‘hacked up enough’’ to make the inclusion of substantial new function extremely difficult. Another problem with continuing to extend the existing system is that many of our proposed ideas would be difficult to integrate into that system because of earlier design decisions. Consequently, we are building a new database system, called POSTGRES (POST inGRES) The design of Postgres by Michael Stonebraker - https://dsf.berkeley.edu/papers/ERL-M85-95.pdf

I am beginning to learn C and I asked for resources on the same. Here is what I got: 1. K&R C book - this is the OG book. Short and concise. But it is old now and has some minor bad practice / incorrect code apparently. 2. Modern C by Jens Gustedt available for free but might feel advanced 3. Effective C by Robert C Seacord - as another introductory alternative

How easy it is to make changes and compile sqlite3? 1. Get the source code from fossil or github mirror. Or from their official downloads page.

wget https://www.sqlite.org/2024/sqlite-src-3450100.zip

2. To build: ./configure; make sqlite3.c 3. You may need the shell to access:

gcc shell.c sqlite3.c -lpthread -ldl -lm -o sqlite3

That's it!

My PR is now merged! This is my first contribution to the SQLite code :) https://github.com/tursodatabase/libsql/pull/1027

Some info on the C debugging story: https://fixupx.com/glcst/status/1757766622113153264?s=46&t=DpkP6pa

I haven't posted here in a while, I got busy with some stuff. I will resume from now. Some updates and follow ups: 1. I had previously talked about read-your-writes consistency briefly. My PR is now merged and I have better understanding of it (though not fully. Consistency levels are too complex and read your writes seems simple, but it isn't really). You may check the fix here - https://github.com/tursodatabase/libsql-client-go/pull/103 I will post an explainer soon 2. I am trying to contribute to libsql, so fighting with C and friends! This is the bug - https://github.com/tursodatabase/libsql/issues/865 I had read about git bisect but never had the opportunity to use it myself... untill now. git bisect found the bad commit under a few mins. Also I realise the value of atomic commits. Since the patch is small, it was somewhat easy to figure out the problematic line. Hoping to send a patch soon! 3. There are couple of #AMA questions and I will get to them this week. If you have any questions to submit, do them and I will try my best to answer.

Here is a non trivial example (in Go), a producer which takes a io.Writer (could be a file, network connection etc.) and a channel notifier for stop signal. That is, unless someone sends a stop signal, it will keep on writing to the writer on every second

func producer(writer io.Writer, done <-chan struct{}) {
 ticker := time.NewTicker(1 * time.Second)
 defer ticker.Stop()

 i := 1
 for {
  select {
  case <-ticker.C:
   fmt.Fprintf(writer, "%d\n", i)
   i++
  case <-done:
   return
  }
 }
}

Testing such code under DST would be fun! But I have no idea how to test it... yet.

Can you elaborate on DST or show us the advantage with a simple example? Here is one example. Using DST, you control every aspect, that includes time. I mean the time gets simulated too. I was so blown away when I first learned about this possibility. Quick example:

func testTen():
 time.Sleep(10 * time.Second)
 fmt.Println(10)

Say if you want to test this, the test suite has to wait 10s to complete. That is slow! But with DST, you can sort of fast forward the time and make the function finish instantly. I am not fully sure how this is done, but my first guess is to abstract the time module. If this takes 1ms to finish, then in 10ms you would have tested 100s of CPU time! Here is what FoundationDB authors say: > ...over the years we have run the equivalent of a trillion CPU-hours of simulated stress testing. https://apple.github.io/foundationdb/engineering.html (#ama this was a submitted question and I will go through other questions soon)

I'm working on understanding Deterministic Simulation Testing (DST) and how to write deterministic software. What does that mean? You write your code in such a fashion that it always runs the same way. It’s not about getting the same output or the same build. Using DST, you test your code with different parameters and you always get the same test result every time for the same parameters. Let's assume that the code creates and runs two threads. Most languages do not guarantee a specific order of thread execution. But if you can control the way threads run, then you can run the threads the way you want. Then it becomes trivial to reproduce the bugs involving concurrency. I mostly code in Go, which is a garbage collected language. How do you control the GC runs? If the GC can run anytime it wants, how does it affect the testing? I am wondering about these questions and working on a prototype. I asked the similar question on Twitter and it has some excellent discussions - https://twitter.com/iavins/status/1742928854065496415 --- I recommend watching FoundationDB’s testing video, who pioneered the DST -  https://www.youtube.com/watch?v=4fFDFbi3toc

On Binary Search, a funny thing is most implementations are broken. This is the problematic line:

mid =(low + high) / 2;

low + high can overlfow causing chaos. Here is a blog post from Google Research (from 2006!) - https://blog.research.google/2006/06/extra-extra-read-all-about-it-nearly.html Is this still relevant today? Yes! Here is a recent bug fixed in QuestDB, just a week ago - https://github.com/questdb/questdb/issues/4087

A computer scientist explains the Binary Search to police xD https://archive.is/RyZI0

Distributed Systems are fun pain. I am working on a bug related to read-your-write consistency, I have written a fix too, but it took me so much time to reproduce the issue consistently that I could not test my patch till now. I will post in detail if all goes well and my patch is accepted.

I recently learnt about a tool called Goose. It’s a language / ORM agnostic migration tool It’s simple and works perfectly. I liked it so much that I ended up adding Turso support (https://github.com/pressly/goose/pull/658) Contributing to it was a delightful experience. It comes with a full end to end test suite and I added same for libsql as well. I plan to use this in my Go project with gorm / sqlc. Also, in one Python project https://github.com/pressly/goose