Turso 0.8: Concurrent writes without SQLite's single-writer bottleneck

Today we are announcing the release of Turso 0.8. We are continuing work from the previous release to improve the engine under real-world workloads. So far, we've focused on SQLite features, compatibility, and correctness. However, performance is a big part of why people love SQLite. It runs like a bat out of hell for read-intensive workloads, and it also performs well for single-threaded writes. However, SQLite's single-writer transaction model means higher transaction tail latency and limited write-throughput scalability under concurrent writes. Turso's architecture is designed for high concurrency with asynchronous I/O, but we haven't fully taken advantage of it yet. This release therefore focuses on concurrent write performance with BEGIN CONCURRENT.

For this release, we focused on two specific performance areas:

• Transaction latency: 99.9th percentile latency of 2.4 ms at 32 connections (vs. 1.2 s for SQLite, 500x lower)
• Concurrent write throughput: 9,500 TPS at 64 connections (vs. 1,370 TPS for SQLite, 7x higher)

Let's go through them one by one.

#Transaction latency: up to 500x lower tail latency than SQLite

Transaction latency is how long a transaction takes to start and commit while other transactions are running concurrently. SQLite has high transaction latency under concurrent transactions because it allows only a single write transaction at a time. Turso addresses this limitation with BEGIN CONCURRENT; transactions can start and commit even when other transactions are running.

To measure transaction latency, we implemented a microbenchmark that schedules 1000 transactions per second using a Poisson arrival distribution and measures the time from when a transaction is scheduled until it commits. Transaction latency in this benchmark therefore also includes the time a transaction waits to start after it is scheduled.

Connections

181632

Y axis

LinearTail

TursoSQLite

Engine	p50	p90	p99	p99.9	max
Turso	0.87 ms	1.33 ms	1.67 ms	5.85 ms	46.3 ms
SQLite	1.35 ms	5.37 ms	129 ms	730 ms	2.53 s

As shown in Figure 1, Turso's transaction 99.9th percentile latency stays under 6 ms from 8 to 32 connections, going from 5.9 ms at 8 connections down to 2.4 ms at 32 connections, while SQLite's grows from 730 ms at 8 connections to 1.2 s at 32 connections. With a single connection the two engines are on par up to the 99th percentile, at 5.8 ms for SQLite and 5.1 ms for Turso, but SQLite's 99.9th percentile is already 73 ms against Turso's 14 ms. In this benchmark, SQLite is limited by its single-writer transaction model, while Turso's MVCC allows transactions to start independently, and commit in groups, which is an optimization we added in this release.

To illustrate why Turso has such a big advantage here, let's use a Ben Dicken-inspired balls animation. In this animation, transactions originate on the left connections and execute in the database on the right. In SQLite, a transaction must take the single write lock before it can execute, and if it finds the lock held, it sleeps for an increasingly long time before trying again, so the lock can sit idle while writers are still asleep. Those sleeps are what push SQLite's tail latency into the hundreds of milliseconds and beyond one second. With BEGIN CONCURRENT, transactions in Turso execute immediately and commit together in groups, so latency stays close to the cost of a single commit.

#Concurrent write throughput: up to 7x higher than SQLite

Concurrent write throughput is how many transactions per second an engine can commit when multiple connections are writing at the same time. SQLite is extremely efficient for single-writer workloads, but it does not scale well with multiple writers. Turso's MVCC engine allows multiple writers to commit concurrently, which improves throughput under concurrent writes.

To measure concurrent write throughput, we implemented a microbenchmark that schedules 100 rows per transaction on disjoint keys, with a Poisson arrival distribution, and measures the number of transactions committed per second. The benchmark is run with 1, 2, 4, 8, 16, 32 and 64 connections.

TursoSQLitemean of 3 runs ± 1 sd

As shown in Figure 2, Turso's write throughput scales with the number of connections, while SQLite's does not. SQLite's throughput hovers around 1,370 transactions per second regardless of the number of connections, and is faster than Turso with a single connection. Turso overtakes it at 2 connections and reaches about 9,500 transactions per second at 64 connections, which is about 7x SQLite's throughput.

#Other highlights: SQL features, FTS with MVCC, and query optimizer improvements

Performance is the theme of this release, but a few other improvements are worth calling out.

#SQL language features

Turso now has complete support for window functions, and supports recursive queries:

• Window functions rank(), dense_rank(), first_value(), last_value(), nth_value(), lag(), lead(), ntile(), cume_dist() and percent_rank() (#7388, #7922, #7923, #7940, #7985, #8049, Jussi Saurio)
• Every window frame SQLite supports, with all ROWS, RANGE and GROUPS bounds and all EXCLUDE options (#8130, Jussi Saurio)
• Recursive common table expressions with WITH RECURSIVE (#8050, Jussi Saurio)

#Full-text search with MVCC

Full-text search now works with MVCC, and its indexes got faster:

• Transactional indexes that work with BEGIN CONCURRENT (#8425, Preston Thorpe)
• Faster and leaner indexes (#8085, Preston Thorpe)
• Index segments merged on the write path once there are enough of them, instead of piling up (#8611, Preston Thorpe)

#Query optimizer improvements

The query optimizer handles more queries efficiently, and shows more of what it decided:

• Unnesting of more correlated subqueries (#8184, Jussi Saurio), including ones that correlate on non-equality comparisons (#9154, Jussi Saurio)
• Hash joins when an index cannot seek the join keys (#9107, Jussi Saurio)
• Covering indexes with virtual generated columns (#8261, Mikaël Francoeur) and partial index predicates (#8429, Mikaël Francoeur)
• NULLS FIRST and NULLS LAST in indexes (#8134, Mikaël Francoeur)
• JSON output for EXPLAIN QUERY PLAN (#8400, Mikaël Francoeur), including the optimizer's join estimates (#8831, Jussi Saurio)

#Try Turso 0.8

Install the engine locally and try BEGIN CONCURRENT on your own workload:

Want to check our numbers? The benchmarks are open source, and the results in this post were measured at commit fd41c07dc against SQLite 3.50.2. Run them on your hardware and tell us what you find on Discord.