Databases with V

What all happened in the world of databases in 2023? Andy has a written an excellent article covering: - The new wave of vector databases and vector extensions - New changes in SQL 2023 spec, they added graph queries and arrays - MariaDB drama - NOTAM system outage - New databases and funding news (link submitted by community member Himanshu) https://ottertune.com/blog/2023-databases-retrospective

swieros - A tiny hand crafted CPU emulator, C compiler, and Operating System This is such a fun project! The compiler is 2k LOC and emulator is 600 readable C. It also comes with a small manual explaining the repository and code organisation https://github.com/rswier/swieros

Two years ago, I was doing a fun project where I was trying to insert 1B rows under a minute in SQLite. The best I could do was 100M in 33s. People optimised it further and contributed, which brought it down to 22s! Overall it was a great learning experience https://avi.im/blag/2021/fast-sqlite-inserts

If you are looking for a challege involving systems programming and optimisations, try The One Billion Row Challenge. It was created for Java, but they are accepting non-Java solutions too. Last date is Jan 31. > Your mission, should you decide to accept it, is deceptively simple: write a Java program for retrieving temperature measurement values from a text file and calculating the min, mean, and max temperature per weather station. There’s just one caveat: the file has 1,000,000,000 rows! > The goal of the 1BRC challenge is to create the fastest implementation for this task, and while doing so, explore the benefits of modern Java and find out how far you can push this platform. So grab all your (virtual) threads, reach out to the Vector API and SIMD, optimize your GC, leverage AOT compilation, or pull any other trick you can think of. There are a few rules to the challenge, like you should not use external dependencies. Current fastest implementation takes 12 seconds. https://www.morling.dev/blog/one-billion-row-challenge/

The draft version of A Science of Concurrent Programs by Leslie Lamport is released. Who is this book for? You probably belong to one of two classes of people who I will call scientists and engineers. Scientists are computer scientists who are interested in concurrent computing. If you are a scientist, you should be well-prepared to decide if this book interests you and to read it if it does. Engineers are people involved in building concurrent programs. If you are an engineer, you might have a job title such as programmer, software engineer, or hardware designer. I need to warn you that this book is about a science, not about its practical application. Practice is discussed only to explain the motivation for the science. If you are interested just in using the science, you should read about the language TLA+ and its tools, which are the practical embodiment of the science. But if you want to understand the underlying science, then this book may be for you https://lamport.azurewebsites.net/tla/science.pdf

Someone ran this query (simplified) on a prod DB with DBeaver which deleted all the records

UPDATE orders
SET is_deleted = true

WHERE id in (1, 2, 3)

Seeing the third line empty, it ignored the fourth line and ran the query - https://zaidesanton.substack.com/p/how-i-destroyed-the-companys-db --- The post has ideas on how to mitigate this. Two ways I follow: 1. Use transactions! They are like dry run. Initiate a transaction, make changes, verify and if it is all good, commit. If you intended to modify a couple of rows but if it is taking huge amount of time, then you know something has horribly gone wrong. 2. If I don't want to use transactions, then I usually skip the first token which is either UPDATE or DELETE, type it all out, verify and then complete the query. This works when you are updating 1-2 records and no need to verify the modifications.

Hello! I got a feedback (someone from this community) to setup a link to send messages and ask me questions. Use this form to send me messages / questions. I will check them once them in a while and publish the relevant ones. I have added the link in the channel description. https://forms.gle/YHKpTBvVooNmtJQcA

What is Jepsen? It is a tool, written in Clojure, to test databases, message queues, and distributed systems. It is open source and anyone can write a test suite and run against a distributed system. You inject all sorts of faults like network partition, disk corruption, machine failure etc and then check if the invariants hold true. Consider watching this talk by the author, Kyle Kingsbury - https://www.youtube.com/watch?v=tRc0O9VgzB0

Jepsen analysis on MySQL v8.0.34 is out. They have found issues in the MySQL's transactions. The post has recommendations on how this can be fixed. https://jepsen.io/analyses/mysql-8.0.34

Found this Ruby gem called litestack which replaces many of the components with SQLite. Sounds perfect for a side project and some production use cases too. > a full-fledged SQL database, a fast cache , a robust job queue, a reliable message broker, a full text search engine and a metrics platform all in a single package. Also, read the why section - https://github.com/oldmoe/litestack/blob/master/WHYLITESTACK.md https://github.com/oldmoe/litestack

BurntSushi explains why is Rust’s stdlib search is slower (than memchr)?

Weekend read recommendation: Database Fundamentals This is an excellent post which explains the fundamentals and what goes behind building a database. Covers ACID, BTree vs LSM Tree and making the DB distributed: CAP theorom, Raft, Consistent Hashing -- it has all! (Shared for the community by Karan. If you find anything worth sharing, send them to me!) https://tontinton.com/posts/database-fundementals

TIL SQLite has an authorizer callback > An authorizer is used when preparing SQL statements from an untrusted source, to ensure that the SQL statements do not try to access data they are not allowed to see, or that they do not try to execute malicious statements that damage the database. For example, an application may allow a user to enter arbitrary SQL queries for evaluation by a database. But the application does not want the user to be able to make arbitrary changes to the database. An authorizer could then be put in place while the user-entered SQL is being prepared that disallows everything except SELECT statements. documentaion: https://www.sqlite.org/c3ref/set_authorizer.html here is a nice example usage in Python - https://charlesleifer.com/blog/sqlite-database-authorization-and-access-control-with-python/

Implementing an upgradable RWLock in Go from scratch Go stdlib has Mutex and RWLock. The RWLock can be either read lock or write lock, but you cannot upgrade the read lock to write. Here is a post explaning why you need such lock and how to build one: https://upstash.com/blog/upgradable-rwlock-for-go

The architecture of today’s LLM applications https://github.blog/2023-10-30-the-architecture-of-todays-llm-applications/

This is an excellent short intro to TLA+ - https://blog.fponzi.me/2023-10-26-tla-plus-is-easy-if-you-know-how-to-abstract.html

A captivating article about OpenAI's history and the recent drama. It covers the background story, how they formed the company, how Github Copilot was built and the people behind it. Possibly biased and may not be accurate about the drama, but sure it is a great read 🍿 One interesting thing I liked was how they tested the LLMs before releasing them to the general public https://www.newyorker.com/magazine/2023/12/11/the-inside-story-of-microsofts-partnership-with-openai

Things to keep in mind if you are writing something keeping WASM as a target: