Collection of insane and fun facts about SQLite. Let's go!

SQLite is the most deployed and most used database. There are over one trillion (1000000000000 or a million million) SQLite databases in active use.

It is maintained by three people. They don't allow outside contributions. SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. It's everywhere!

Did you know that if SQLite performs better, then the lifetime of your mobile increases?

But how?

Notes on my paper: SQL Statement Logging for Making SQLite Truly Lite This paper appeared in VLDB, 2017 and I am reposting my old notes. I will also explain some necessary database internals.

tl;dr is instead of physical logging, they propose using logical logging to reduce write amplification

vldb.org/pvldb/vol11/p5…

Some notes on how SQLite implements MVCC using WAL and provides Snapshot Isolation

Let's dive in First, let's understand the transaction model of SQLite, which is quite different (and more limited) than other databases.

What SQLite offers:
- Concurrent transactions
- Multiple readers AND a writer simultaneously
- But no multiple concurrent writers

Here's a fascinating story of how researchers used Bloom filters cleverly to make SQLite 10x faster.

I'll also explain some database internals and how databases implement joins.

Let's dive in! I have posted about this paper earlier and also about filesystem-blob benchmarks. But let's discuss again for our new friends.

The results of this research have been applied to SQLite already and were released in v3.38.0.

This is going to be a long thread ☕️

Link to the paper: vldb.org/pvldb/vol15/p3…

I watched @penberg 's talk "Patterns of Low Latency" at the P99 conference.

This talk is dense with practical insights and to the point. I was wondering why watch a talk on latency, but Pekka's background as a Linux Kernel contributor and his work on OSv and ScyllaDB made it worth hearing what he got to say.

Here are my notes. The highest ROI tips from the talk:

- Disable Nagle's algorithm (stops TCP from buffering small packets, reduces cold start)
- Eliminate dynamic memory allocation (use static/pool allocation instead)

Here is the link to the talk:

if you discover a bug after shipping, would you leave it to maintain backward compatibility? how far would you go?

SQLite allows NULL values in primary key columns. This was an oversight by the developers, but since it was already shipped, they kept it to maintain backward compatibility. If you want standard behaviour, you need to explicitly add a NOT NULL constraint to the primary key column. In contrast, PostgreSQL automatically adds NOT NULL constraints to primary keys.

The bug lives on happily. read more: sqlite.org/withoutrowid.h…

SQLite is cracked text phrases stolen from:

sqlite.org/mostdeployed.h…
dl.acm.org/doi/abs/10.147…

fun fact: SQLite is the most deployed and most used database. There are over one trillion (1e12) SQLite databases in active use.

It is maintained by three people. They don't allow outside contributions. It's pretty much everywhere - sqlite.org/mostdeployed.h…

I always wondered how adblockers for YouTube worked, this post nicely explains it. Also, the cat-and-mouse chase between YouTube and adblockers. A fascinating read link to the post - andadinosaur.com/youtube-s-anti…

Here is a fascinating story of how researchers teamed up with SQLite core developers to make it faster using Bloom filters!

Let's also dive into database internals and understand how databases implement joins.

📄Paper: SQLite - Past, Present, and Future (2022) link -

I presented this paper earlier at Papers We Love, Bangalore and I had been promising people to post the summary. So, here it is!

This is going to be a long thread ☕️☕️vldb.org/pvldb/vol15/p3…

For the past few months, I have been learning about internals of databases. I found many excellent articles on writing compilers, but I could not find many practical resources for databases. So I wrote one. CaskDB is the project I wish I had started with.

github.com/avinassh/py-ca… CaskDB is based on Riak's Bitcask paper. The idea of Bitcask is brilliant yet straightforward, which makes it attractive for newbies to learn about key-value store internals and implement one.