sqliteai / sqlite-multiwriter

# sqlite-multiwriter
**With 16 threads writing one database, 49,277 transactions per second against 8,630 for SQLite (5.7x), and the slowest 1 in 1000 commits takes 2.08 ms instead of 157 ms.**
**With 16 processes, 25,987 against 8,636 (3.0x), and 1.54 ms against 233 ms.**
SQLite is the most widely used database in the world, and it has one well-known limit: a database accepts only one writer at a time. With many threads, or many processes (several agents working on the
same database, for example), the writers queue up behind one lock, or fail with `SQLITE_BUSY` and have to try again.
sqlite-multiwriter removes that limit without touching SQLite. It is a VFS that you load as an extension (or link in): your SQLite, your SQL and your database file stay the same, and many connections
write the same database at once. Every commit is durable, and the file remains an ordinary SQLite database.
- **More writes per second.** Writers that touch different rows (or different pages) do not wait for each other.
- **Almost no `SQLITE_BUSY`.** With writers on their own rows the application retries about 2 in 1000 transactions.
- **Threads or processes.** One process with many connections, or many processes on one file.
- **Nothing to change in the schema or in the SQL.** No triggers, no new syntax, no special tables.
## In numbers
16 writers on one database against stock SQLite in WAL mode, `synchronous=FULL` (every commit is on disk when it returns). Latency is the time from the start of a transaction to its commit, retries included. Best result in bold; how it was measured is under the tables below.
| 16 writers | SQLite tx/s | multiwriter tx/s | Gain | Retries per 100 tx | Slowest 1 in 1000 commits |
|---|---:|---:|---:|---:|---:|
| Threads, each inserting its own rows | 8,630 | **49,277** | 5.7x | 189 → **0.2** | 157 → **2.08** ms |
| Threads, own rows on shared pages (with rebase) | 13,562 | **55,098** | 4.1x | 35 → **0** | 584 → **0.93** ms |
| Processes, each inserting its own rows | 8,636 | **25,987** | 3.0x | 189 → **0.1** | 233 → **1.54** ms |
| Processes, own rows on shared pages (with rebase) | 13,225 | **51,472** | 3.9x | 30 → **0** | 664 → **16.6** ms |
| Threads, all on the same 4 rows | 13,277 | **29,976** | 2.3x | 24 → **17** | 609 → **122** ms |
One writer is not slower than SQLite (15,959 against 13,746 tx/s). When writers change the same rows they really conflict: the gain is smaller and the retries stay.
## How it works
Each writer has its own WAL. Its transactions run there on a snapshot of the database, and at commit they are checked, ordered and published; the log is merged into the database file in the background:
 writer 1 --> private WAL --+
 writer 2 --> private WAL --+--> check and publish --> commit log --> database file
 writer 3 --> private WAL --+ (first committer wins) (compaction)
Each transaction runs on a snapshot of the database. At commit, its pages are checked against the commits published in the meantime. If nobody change