How Turso made connections to SQLite databases 575x faster

Turso is the next evolution of SQLite. A full rewrite in Rust with a new, modern architecture, it is designed to respond to the demands of modern applications. Some of the work that Turso aims to do requires rethinking architectural foundations of SQLite, like improving write concurrency and getting rid of the notorious “database is locked” problem.

But there are a lot of other opportunities to improve SQLite even without deep architectural changes by revisiting some of its assumptions.

In this post we will discuss recent developments in Turso that made opening a connection to SQLite up many orders of magnitude faster - more than 500x in our example, for databases with a large amount of tables (10,000 in our example).

#Opening a connection in SQLite

Opening a connection in SQLite is straightforward. Every SDK is slightly different, but in a nutshell, you have a method called “open” that takes a filename. After the connection is established, it is ready to receive queries.

Opening a connection to a SQLite file is usually very fast. For example, in our benchmark, the median time to open a connection to a simple SQLite file with one table is just 130 microseconds.

But in order to receive queries, there is a lot of information the connection needs to have. In particular, it needs to have access to the current schema of the database so it knows which tables are present, and the definition of each table. And that means that every time a new connection is opened, the internal table containing all table definitions (sqlite_schema) has to be read, parsed, and brought into memory.

As the number of tables grows, opening a connection becomes increasingly slower.

As we can see, for a complex database with 10,000 tables, it takes 23ms to open a new connection.

#Modern applications, modern infrastructure

It is not every day one sees databases with 10,000 tables, but they do exist. In fact, this work was driven by one of our partners who uses SQLite in production to run a large unicorn.

SQLite’s architecture was devised when single-threaded applications were the norm. SQLite supports threads just fine, but it wasn’t designed with threads in mind, and it goes as far as claiming they are evil in its FAQ.

While I don’t doubt the real presence of evil lurking in our world, threads are not really the place where it hides. Parallelism and multithreading has now become a reality and it are ubiquitous. It is now common for applications to open many connections in the same process to drive high levels of concurrency. Yesterday’s corner case is today’s norm.

Turso fully embraces parallelism. Opening the first connection parses the schema. That need doesn’t go away. But subsequent connections don’t have to redo that work and can just share that state.

Implementing this is slightly harder than just making sure all connections hold a reference to the same data structure: being a transactional database, it is possible that a connection is making a schema change inside a specific transaction. To protect against that, we use Arc::make_mut to safely mutate the schema without interfering with other references, using a Copy-on-Write pattern.

This guarantees that a new connection only needs to acquire a reference count until the moment it tries to modify the schema. Only when it does, it acquires its own independent copy. If the schema change is committed, then the change is propagated and all existing connections will see it.

Faster connections

With that change, the time it takes to open a new connection to an existing SQLite database is independent of the number of tables in the schema. In our benchmark, it took only 40 microseconds to open a new connection to an existing database, regardless of whether the database had 10, 5,000 or 10,000 tables. Notice that 40 microseconds is also faster than SQLite’s 130 microseconds for the one-table scenario.

#Try it out!

Do you have SQLite files with complex schemas and want to see your connections go faster? Try Turso today by downloading the shell with curl -sSL tur.so/install | sh, or in your favorite SDK

Turso is rewriting SQLite to comply with the demands of the modern world. But the most important part for us is its Open Contribution nature. While SQLite is a closed community without external contributors. Turso already has over 130 contributors.

Want to be a part of what the future of embedded databases looks like ? Check us out on Github! You can star the Turso repository, and look at some of the issues marked as “Good First Issue”