The most used database in the world

[00:00:00]SQLite is the most widely deployed database in the world. You will find it inside cars, airplanes, and even orbiting satellites. It is literally everywhere. Even right now, your phone is running several SQLite instances just to manage daily things like your contact list and text messages. We are used to databases that need dedicated servers and constant administration, but SQLite doesn't need any of that, and it packs an entire SQL system into a single file.

[00:00:26]So today we're going to look at how it works under the hood and why almost every device is using it. Normally when you use Postgress or MySQL, they run as separate server processes. Your application has to talk to them over a network socket. So you need to open a connection, send a request across the network, and then you wait for the database to process it and send a response back. SQLite works completely differently. It is essentially a C library that lives right inside your app. So when you write a Python script or build a simple API in node, quering the database is just a plain function call. You don't have to manage connection pools and there is no background server that can crash or run out of memory on its own. Basically your schema, your tables and all the raw data are stored in a single file. Since it is literally just raw bytes managed by the library, you can treat it like any other document. You can copy your entire database to a USB drive or just email it to a colleague. When you write SQL, the database sees nothing more than a raw string of text. So before anything executes, SQLite has to compile that query. First, a tokenizer breaks your string down into individual symbols.

[00:01:33]Then a parser turns those symbols into a syntax tree to check your grammar, making sure your select comes before your from. Once that tree is valid, the code generator compiles it into bite code. Your SQL query literally becomes a tiny executable program. And you can actually see this on your own. If you put explain keyword at the beginning of your query, SQLite will show you the exact steps it's about to take. Instead of SQL keywords, you see real instructions like open read, rewind, column, and next. SQLite turns your declarative query into a strict step-by-step procedural program. To execute these bite code instructions, SQLite uses its own virtual database engine. This software interpreter has internal memory registers and cursors.

[00:02:16]It's like a tiny virtual machine similar to the one that runs Java programs, but purpose-built for database operations.

[00:02:23]The execution loop for a simple select statement follows a strict mechanical path. The virtual machine opens a cursor on the target table and rewinds it to the first row. It reads the requested columns from the disk into its temporary memory. It returns a single row to your host application, then jumps back to the read step until it hits the end of the table. Every complex join or sort operation is just a combination of these primitive instructions. Everything above this virtual machine thinks entirely in SQL. Everything below the virtual machine thinks entirely in B trees. The engine is the exact translation point between those two completely different data models. Basically the actual database file is divided into fixedsiz chunks called pages. The default size is exactly 4,96 bytes. The page is the atomic unit of all input and output operations. SQL light never reads a single row from your disk. It always reads the entire page that holds that row. Every table inside your file is structured as a B tree. Specifically, it is a B+ tree where each entry is identified by an internal row number.

[00:03:26]The engine stores the actual row data directly in the leaf nodes at the bottom of the tree. Every index you create is just a completely separate B tree. that index maps your specific column values back to those internal row identifiers.

[00:03:39]A standard binary tree would be way too deep. It would force you to read dozens of separate pages from the disk just to find one single row. A B tree solves this problem. It packs hundreds of keys into a single 4 kilob node. This creates a huge branching factor. So the tree stays incredibly flat. A table holding a billion rows is typically only four levels deep. So a complete lookup only costs a few page reads. The upper levels of that tree usually stay cached in memory anyway. Finding a specific record often takes exactly one physical read from the disk. If you query an index column, the engine just does two consecutive tree walks. It walks down the index tree to find the correct row identifier. Then it walks down the main table tree to fetch the actual row data using that exact identifier. The B tree layer never touches the disc directly.

[00:04:27]Instead, it asks a component called the pager for a specific page number. The pager serves that page from its in-memory cache if it can. If the page is not in memory, the pager issues a system call to read it from the physical file. The pager handles the hardest problem in database engineering. A single transaction often modifies dozens of different pages scattered across the file. If the power goes out halfway through, the file is left in a broken state. The operating system only offers a basic command to write a stream of bytes to a file. We have to guarantee that a multi-page change finishes completely or never starts. A half-written transaction corrupts the entire database structure. Think about a simple bank transfer. The system might write the page that takes money out of your account but lose power before it updates the receivers's balance. In that scenario, the money just disappears.

[00:05:15]SQLite solves this problem with a roll back journal. Before the engine modifies any page in the main file, it copies the original version into a separate journal file right next to your database on the disk. To make a safe change, the engine makes a system call named f-sync on the journal file. Normally, your operating system keeps file changes in RAM because writing to memory is much faster than writing to a physical drive. The f-sync command forces the operating system to flush that memory cache and push those pages straight down to the disk. Once that finishes, the engine writes your new pages into the main database file and calls f-sync again. When the data is safe on the hardware, the engine deletes the journal. If the power dies at any point during this process, SQLite wakes up on the next boot and finds that leftover journal file. It copies all the old pages back into the main database, completely undoing the broken transaction. That f-sync command is the most expensive part of the operation.

[00:06:09]The database engine literally stops and waits for the physical hardware to confirm the right. It is a slow blocking process, but that hardware delay is exactly what guarantees your data actually survives a crash. This roll back journal approach has one major problem. The database needs an exclusive lock on the entire file to safely write new pages. That means readers and writers block each other. If you have a background job writing data, your user interface freezes while it waits to read that same data. SQL light fixes this issue with write ahead logging. We usually just call it wall mode. Instead of modifying the main database, the engine appends your new page versions to a completely separate log file. Your main file stays untouched during the transaction. When readers want data, they check that new log file first. If they find an updated page there, they read it. If they don't find it, they just pull the older version from the main database. Because of this separation, readers and writers never block each other again. At some point, all those new pages have to go into the main database. This is called a checkpoint. SQLite runs it automatically once the log grows past a certain threshold, about a thousand pages. The engine takes all the accumulated pages from the log, moves them back into the main file in one batch, and then resets the log. Even with wall mode enabled, SQL light still only allows one write transaction at a time. When you write data, the engine allocates new pages and splits B tree nodes into separate branches. If two threads tried to restructure the exact same tree at the same time, they would overwrite each other and corrupt those internal page references. To stop this, SQL light enforces an exclusive write lock directly at the engine level. Concurrent writers have to wait for their turn. If a writer waits too long, your application throws a busy error. That's the real limit of SQLite. There can be only one writer at a time. If you have an active application with a few users hitting the database, SQLite handles the traffic easily. But if you have 50 servers hammering one shared file over a network, you are using the wrong tool.

[00:08:08]The original creators are very clear about this. SQL light doesn't compete with Postgres. It competes with writing to a standard local file. Let's summarize everything. Whenever you run a query, your SQL gets compiled into bite code and a small virtual machine executes those instructions against the B tree stored in your file. Underneath it all, the pager makes sure that a power cut can't destroy your data. And this entire system lives inside one single file on your disc. No server, no configuration, no network. That's exactly why SQL light ended up in pretty much every device you own.

[00:08:42]I get asked all the time about how I build my animations. So, if you want to learn how to do it yourself, check the link in the description.

[00:08:49]If you enjoyed this video, drop a like and subscribe. And please tell me in the comments what topic you want to see next. I'll pick the most liked one and make a video about it. Thanks for watching and see you in the next one.