What is The Most Deployed Software in the World?

If you had asked me the same question a few years before, I might have told, Linux, Wordpress or something of that sort. But the reality is that the most deployed software isn’t a single application or platform—it’s a database engine. It is none other than SQLite. And here is the story of SQLite.

The Origin Story

It is early 2000s and the internet was beginning to explode with new applications, services and devices. Computer became one of the normal consumer goods and had wide applications in almost every industry. Bath Iron Works is a shipyard located in Bath, Maine, USA and Dr. Richard Hipp was working as a software contractor there. He was working on a battleship called USS Oscar Austin (DDG-79). His work mainly focused on a software that help sailors isolate any pipe works that is damaged during a battle. They used Informix, a notable commercial RDBMS that time.

Informix was notorious for its complex architecture and operational overhead. It required a separate server process, a complex configuration, and significant hardware resources. This posed a significant challenge - what if the software loses the connection to the server during a mission? That Informix server process became a single point of failure for the whole application. Server goes down -> Sailors cannot quickly isolate the pipes with damage -> Ship sinks.

They needed a new type of database—one that was self-contained, didn’t require a separate server process, and could operate independently of the underlying operating system. But it was a time of political stalemate in the US, where no new contracts or procurement were happening. So, Dr. Hipp decided to take matters into his own hands and create a solution from scratch.

He wrote a little C library. There was no need for a server process. The application talks to this library, and writes a self-contained single file database. Unbeknownst at that time, it was a landmark moment in software engineering. Thus, SQLite came into existence.

The Journey Thereon

SQLite was first released in 2000 by Richard Hipp, as an independent software developer. The idea was simple yet revolutionary: create a self-contained, serverless, zero-configuration database engine that could be embedded directly into applications. No need for a separate database server process or system to install—just drop the library into your app and start using it immediately.

This design philosophy led to SQLite’s incredible ubiquity. Today, SQLite powers everything from mobile operating systems (Android and iOS both use it for local data storage), to web browsers (Chrome, Firefox, and Safari all use SQLite for storing bookmarks, history, and preferences), to IoT devices and embedded systems. As a matter of fact, there are more instances of SQLite than the population of this planet!

But how did this tiny, lightweight database become the most deployed software on the planet? The answer lies in its perfect blend of simplicity, power, zero overhead, and the ingenious design.

Android Opened a Can of Worms

In mid-2000s, Google announced their mobile OS, Android. And SQLite was the default database engine used in the OS for local data storage. This decision was a game-changer. It meant every device running Android would come with SQLite pre-installed. With the large number of users and different usecases, some limitations of SQLite started to come to light.

Again Dr. Hipp found himself distilling the codebase to make it even lighter and more efficient for mobile devices. He spent over 60 weeks strengthening the core and test suite of the library. He achieved 100% MC/DC (Modified Condition/Decision Coverage) for SQLite and theoretically eliminated all critical bugs. Initially the library was just a few thousands of lines of code - not millions unlike others. After the MC/DC coverage and rewrite of the library, it had a ratio of 1:600 -> for every line of application code, there was at least 600 lines of test script.

The Design

Basically a sqlite file contains a signature in the first 16 bytes. This magic header identifies the file as a valid SQLite database and ensures compatibility across different versions and platforms. The rest of the file consists of a series of pages that store the database’s tables, indexes, and data in a structured format.

Each of this page is 4096 bytes in size, by default. Yet, it can be configured to different page sizes to the power of 512. This means the page size can be 512, 1024, 2048, 4096, 8192, 16384, or 32768 bytes. The choice of page size impacts performance: larger pages reduce the number of disk I/O operations needed to read large datasets, while smaller pages minimize wasted space when storing small records.

Each page works as a B-Tree. You can imagine these pages like shards. They are organized hierarchically with a root page at the top, branching out to child pages that contain pointers to the leaves. The leaf pages hold the actual rows.

Another factor is indices. Indices also use B-Trees to provide fast lookups with index names as the key and hold respective row IDs in them. So, a read head will land on the leaf node containing the matching index entry and retrieve the corresponding row from the main table.

One of the brilliant innovations in SQLite’s design is the use of Write-Ahead Logging (WAL) for transaction management. And yes, this is what empowers SQLite to become ACID compliant. Instead of modifying the database file directly, SQLite writes changes to a separate log file first. This means, all the reads happen in the main database. And all writes are “appended” to the WAL. and when it comes to I/O operations, appending is cheaper than writing data in the middle of the file. Also, multiple writers never block each other - all will just append their deltas to the WAL, and the library handles the rest. This is exactly why, sqlite DBs almost seldom go corrupted evenif the application crashed mid-write. Writes are never on the actual table. And whenever the application/library see the file again, the WAL will be merged back into the main database file.

Trivia!

SQLite is “Open Source” - meaning, you can copy the code, distribute, monetize and whatever. But it is not “Open Contribution”. The entire codebase is owned and maintained by Dr. Richard Hipp and his team at the SQLite Consortium. While the software is free to use, the development process is controlled, and contributions from outside are not accepted.

Road Ahead

Unlike many other FoSS, SQLite don’t publish their roadmap publicly. This means that the future direction of SQLite is guided by the needs of its existing user base and the strategic decisions of the SQLite Consortium, rather than a public-facing development plan.

Said that, there are many spin-offs being built and becoming popular these days. By spin-off I mean, there are Rust rewrites, putting Sqlite on edge etc. You can look out for Turso, LibSQL, LiteFS, rqlite, Litestream, ElectricSQL and similar projects.

Here is the link to Dr. Hipp’s personal web page: https://www.hwaci.com/drh/index.html

HWACI (Hipp, Wyrick & Company Inc.) is his company.