From v0.7 to v0.8: How group commit changed Turso's tail latency

About a year after I joined, Pekka Enberg, Turso's CTO, handed me a barebones pull request for MVCC (multi-version concurrency control). It built on an open source MVCC experiment he had worked on with Piotr Sarna and Avinash Sajjanshetty, based on the research behind Hekaton, Microsoft's in-memory database engine.

I suspect Pekka was also trying to cure me of a few stereotypically Spanish habits, like long naps and late breakfasts. I neither confirm nor deny indulging in either. Fittingly, the project turned out to be about keeping writers from sleeping on the job.

Thankfully, I wasn't alone for long. Everyone on the Turso team and a lot of open source contributors helped bring MVCC to life, so much so that we've been able to shift our focus from reaching feature parity with SQLite to improving Turso's performance. With 0.8, Turso has much lower tail latency and higher throughput than SQLite under concurrent writes. You can see the full results in Turso 0.8: Concurrent writes without SQLite's single-writer bottleneck.

This post covers one piece of that work. In v0.7, BEGIN CONCURRENT already let Turso move past SQLite's single-writer limit, but concurrent writes didn't scale the way they should have. Group commit fixed that in v0.8.

#What a commit means in Turso's MVCC

To see where commit time goes, it helps to know what a commit does. Here's the map.

Turso's MVCC keeps recent row versions in memory, in a structure we call the MVStore. Rows live in a lock-free skip map that points each row ID to a list of that row's versions:

SkipMap<Rowid, Mutex<RowVersions>>

The skip map is lock-free, so transactions reading different rows never block each other. A naive design, such as a hash map behind one mutex, would make every reader wait for every other. Each row's version list does have its own lock, which matters later.

Every transaction moves through a few states. While a transaction is Active, its changes are visible only to itself. Other transactions see a consistent snapshot of committed data, which is how we implemented snapshot isolation. An Active transaction can also abort, on ROLLBACK or an error. When it commits, the transaction moves to Preparing. Turso checks whether another transaction already committed a change to the same rows. If so, it aborts with a write-write conflict. If not, its changes are written to disk.

Those changes go to a file called the logical log. It's similar to SQLite's write-ahead log (WAL), with one important difference. The WAL records whole pages, typically 4 KB each, so changing one row still writes an entire page. The logical log records only the rows a transaction changed: new rows, updates, and deletes (written as tombstones), with schema changes tracked separately. On restart, Turso replays the logical log to rebuild the in-memory state.

The logical log can't grow forever, and memory is limited. So past a threshold, a checkpoint moves data into the regular SQLite-format database file, and garbage collection frees row versions nobody can see anymore.

In short, committing a transaction takes two steps:

1. Write the transaction's rows to the logical log.
2. Fsync the logical log, so the data is actually on disk.

The second step is where I went down a performance rabbit hole. Unlike most rabbit holes, this one led somewhere good.

Figure 1 steps through that path in v0.7. A transaction does all its work in memory. The commit is the only place it touches the disk, and the disk is where the milliseconds are. Press play, or click details on any box.

#The v0.7 baseline: MVCC that didn't scale

The whole point of MVCC is that write throughput should grow as you add concurrent writers. SQLite allows one writer at a time, so its throughput stays flat no matter how many connections you open. With BEGIN CONCURRENT, Turso lets transactions work in parallel, so more connections should mean more commits per second.

In v0.7, that wasn't really happening. I noticed it while preparing for my upcoming P99 CONF talk, when I ran the write benchmarks Pekka had built. Adding connections didn't buy us nearly as much throughput as it should have.

Those benchmarks are the same ones in the 0.8 release post. One is a closed-loop throughput run: each connection inserts 100 rows on disjoint keys, then immediately starts the next transaction. The other is an open-loop latency run: 1,000 transactions per second with Poisson arrivals, spread across 1, 8, 16, and 32 connections, measured from the moment a transaction is scheduled until it commits. SQLite stays flat on the first and grows a long tail on the second, because it has one writer. Turso in v0.7 should have climbed. It didn't.

So I profiled it. The profiles showed where the time went: a large share of every commit was spent in fsync. You can see that in Figure 1 already. The fsync box is the only step measured in milliseconds. Everything else is microseconds. Figure 2 shows what that does when more than one connection reaches COMMIT at the same time.

#The bottleneck: every transaction paid for its own fsync

Writing data to a file doesn't mean the data is on disk. When a write call returns, the data may still be sitting in the operating system's page cache or in the disk's own cache. If the machine loses power at that moment, the data is gone. A database that promises durability has to call fsync, which forces everything written so far onto stable storage, and wait for it to finish.

That wait is expensive. Fsync is one of the slowest operations in the commit path, and it blocks the disk while it runs.

In v0.7, every transaction did both steps on its own. With N transactions committing, that meant:

1. N writes to the logical log
2. N fsyncs

The writes are genuinely per transaction: each one has its own rows to append. The fsyncs are different. Every fsync does the same thing, making the logical log durable up to that point. When many transactions commit at nearly the same moment, most of those fsyncs are redundant. One fsync after all their writes would make every one of them durable.

So with more concurrent writers, we were queueing more and more identical, slow disk flushes. That's why adding connections didn't scale the way MVCC should.

#The fix: group commit

The fix is a technique almost every serious database uses: group commit (or batch commit). Instead of each transaction flushing on its own, transactions that are committing at about the same time share one fsync.

Group commit wasn't a new idea for us. I had tried implementing it a long time ago, but at the time fsync wasn't what limited us, so it added complexity without a clear payoff. It only became the right thing to build once the profiles showed fsync blocking the database from scaling.

Here's how it works now in v0.8:

1. Transactions gather into a group. When a transaction reaches the Preparing state, it enqueues its log record and gets a ticket. There is no timer. Transactions that arrive while a flush is already running join the next group.
2. One transaction becomes the leader. The leader is not chosen. The first transaction to take the commit lock while the queue is non-empty becomes the leader and drives all writes and the fsync.
3. The leader does the disk work for everyone. It writes every queued transaction's rows to the logical log, then calls fsync once.
4. The others wait for the leader. The other transactions in the group park until the leader's fsync covers their ticket. Once it does, their data is durable, and each continues independently to finish its commit.

Figure 3 is the same run as Figure 1, with the durable phase rewritten. One leader writes every queued record and flushes once. The others sleep until that flush covers them.

The arithmetic is simple. For N transactions:

The writes don't go away, because every transaction still has its own rows to append. But the slowest, most redundant step now happens once per group instead of once per transaction.

Figure 4 puts the two commit paths on the same connections, with the same transaction durations. Only the commit path differs. Count the fsync blocks.

#The results: v0.7 versus v0.8

To measure the effect of group commit, I reran the transaction latency benchmark with three setups: SQLite, Turso v0.7 (without group commit), and Turso v0.8 (with group commit). The benchmark schedules 1,000 transactions per second with Poisson arrivals and measures each transaction from when it's scheduled until it commits, at 1, 8, 16 and 32 connections.

The honest headline is that without group commit, Turso's MVCC had a far worse tail than SQLite. At every connection count, the slowest transactions took seconds, and at 32 connections the p99.9 reached 157 seconds. Allowing concurrent writes didn't help, because every transaction still waited on its own fsync.

The numbers are this extreme because the benchmark offers a fixed load of 1,000 transactions per second. Paying one fsync per transaction, v0.7 couldn't keep up with that rate, so transactions queued, and each one's latency includes the time it spent waiting to start. You can see the backlog forming in the single-connection curve: about three-quarters of transactions finish quickly, then the rest stall for seconds. Adding connections made it worse, since more writers meant more fsyncs competing for the same disk.

With group commit, every curve moves left by three to five orders of magnitude. Median latency sits around 1 ms at every connection count, and the p99.9 is lower than SQLite's in every configuration, from 14 ms at one connection down to 2.4 ms at 32.

#Why more connections means lower tail latency

One result looks backwards at first: Turso's tail latency goes down as connections go up. At a single connection, p99.9 latency was 14 ms. At 32 connections, it was 2.4 ms.

Group commit explains this. With few concurrent writers, groups are small, and each transaction still pays for something close to a full fsync. With many writers, each group holds more transactions, so the cost of one fsync is shared across more of them. The busier the database, the better group commit amortizes its most expensive step.

The contrast with SQLite is even more impressive because, under the same load, SQLite's p99.9 grows from 73 ms at one connection to 1.2 s at 32, because writers back off and sleep while waiting for the single write lock.

#Where this works best

The throughput benchmark writes to different rows in each transaction and that's deliberate: it's the workload we expect to be most common, and the one where Turso's MVCC works best.

When transactions update the same row, two things go wrong. First, they contend on that row's lock in the MVStore, so they block each other. Second, at commit, all but the first fail with a write-write conflict and have to retry. We saw this with one user whose workload updated the same rows every few milliseconds, and MVCC wasn't a good fit for it (at least for now).

The sweet spot is writing a lot of data where transactions mostly touch different rows. If your writers constantly fight over the same few rows, SQLite's single-writer model may serve you just as well.

#Try it out

Install Turso locally and run BEGIN CONCURRENT on your own workload:

MVCC is also available in tech preview on Turso Cloud. The benchmarks are open source, so run them on your hardware and tell us what you find on Discord.

If you want the rest of the story, including how MVCC works end to end, how we handle conflicts, and the hard parts of checkpointing, come to my talk, "Breaking SQLite's Single-Writer Bottleneck," at P99 CONF on October 21–22.