For most workloads, 5,000 inserts per second is a modest target for SQLite. What decides whether you hit it is how you batch rows into transactions, which durability setting you choose, and how your threads share connections. It is rarely the pool size. SQLite’s own FAQ says it can handle “50,000 or more” INSERT statements per second on an average desktop. A 2024-11-19 update to that answer says modern SQLite does far more. That is an official statement, not a benchmark of your schema or disk, so treat 5,000/sec as a target to measure rather than a guarantee.
This guide shows a design that gets there safely: one serialized write path, a few reader connections, WAL mode, batched transactions, and a clear view of what each setting costs you.
The short answer: what actually produces the throughput
- Batch inserts in transactions. The SQLite FAQ says: “Putting multiple operations inside a single transaction can improve performance dramatically by avoiding the overhead of transaction control after each individual operation.” Committing once per row is the usual reason SQLite looks “really slow.”
- Use WAL mode so readers and the writer overlap.
- Funnel writes through one connection (or one at a time) and keep each write transaction short.
- Pick a
synchronouslevel on purpose, knowing what failure it protects against.
A pool does not make simultaneous writes run in parallel. SQLite allows one writer at a time per database. A pool controls how connections are used and how much they contend.
Threading modes: what “thread-safe” means here
SQLite’s threading documentation (last updated 2023-12-05) describes three modes:
#1 Best Overall
- Single-thread: mutexes are disabled. Only safe if the whole process uses SQLite from one thread.
- Multi-thread: safe across threads as long as no connection, or statement derived from it, is used by two threads at the same time.
- Serialized: safe to share connections, because access is serialized with mutexes. The documentation states, “The default mode is serialized.”
Practical consequences:
- Check that your build or language binding has not selected single-thread mode. Many bindings set this for you, so read your library’s documentation.
- In multi-thread mode, give each worker its own connection. Never share a connection or prepared statement between threads at the same moment.
- Serialized mode makes sharing safe, but it does not make it fast. Threads sharing one connection take turns.
These are design recommendations based on SQLite’s connection rules. They are not an official prescription for any particular language’s pool library.
Recommended pool design
One writer, several readers
Because WAL still permits only one writer, the simplest dependable design is:
Rank #2
- A dedicated write connection owned by one thread or task, fed by a queue. Producers push rows; the writer drains the queue and commits in batches.
- A small read pool of separate connections, one per concurrent reader.
This removes writer-versus-writer lock contention from the hot path. If you must allow several writer connections, route them deliberately, keep their transactions short, and handle SQLITE_BUSY.
Batching sketch (Python)
The pattern below is illustrative. Adapt it to your language and binding.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Rank #3
import sqlite3, queue
def open_db(path):
con = sqlite3.connect(path, timeout=5.0, isolation_level=None)
mode = con.execute("PRAGMA journal_mode=WAL").fetchone()[0]
assert mode.lower() == "wal", mode
con.execute("PRAGMA synchronous=NORMAL") # see durability section
return con
def writer(path, q, batch_size=1000):
con = open_db(path)
while True:
rows = [q.get()]
while len(rows) < batch_size:
try: rows.append(q.get_nowait())
except queue.Empty: break
con.execute("BEGIN IMMEDIATE")
con.executemany("INSERT INTO events(ts, payload) VALUES (?, ?)", rows)
con.execute("COMMIT")Notes on this sketch: BEGIN IMMEDIATE takes the write lock up front, so a busy condition shows up at the start of the batch rather than midway. The connection timeout acts as a busy timeout. Batch size is a tuning knob, not a magic number: larger batches amortize commit cost but hold the write lock longer and raise latency for each row.
WAL mode: what it gives you and what it doesn’t
Enable it with PRAGMA journal_mode=WAL and confirm the statement returns wal. The setting is persistent in the database file, so you don’t need to repeat it on every open, though checking is cheap.
SQLite’s WAL documentation says: “The second advantage of WAL-mode is that writers do not block readers and readers do not block writers. This is mostly true.” The “mostly” matters. The documentation lists exceptions, including recovery and cleanup situations, where SQLITE_BUSY can still occur. Code every write path to handle it with a busy timeout and a bounded retry.
Checkpoints and WAL growth
- Automatic checkpoints normally trigger at around 1000 pages of WAL.
- A long-running reader, or a very large write transaction, can prevent a checkpoint from completing, and the WAL file then keeps growing. Keep read transactions short and don’t leave cursors open.
- Monitor WAL file size during load tests. A steadily growing WAL means checkpoints aren’t keeping up.
Keep the files together
When copying or moving a live database, keep the WAL file (and its shared-memory file) with the main database file. Separating them can lose committed transactions or corrupt the database. Use SQLite’s backup mechanisms instead of copying files from under a running process.
Best Value
Durability settings change what “fast” means
From SQLite’s pragma documentation, for WAL mode:
| synchronous | Behavior in WAL mode | Risk |
|---|---|---|
| FULL | Syncs the WAL on every commit | Strongest power-loss durability; slowest commits |
| NORMAL | Database stays consistent | A recent committed transaction may be lost after a system crash or power loss |
| OFF | No syncing | Added risk of corruption after an OS crash or power loss |
NORMAL is a common choice for high-ingest workloads where losing the last few moments of data is tolerable. If each acknowledged insert must survive power loss, use FULL and rely on larger batches to recover throughput. Do not present OFF as a free speedup: it trades integrity protection for speed.
How to measure your own 5,000/sec
No published benchmark matches an arbitrary setup, so measure yours. Record these variables, because each changes the result:
- Rows and bytes inserted, schema, and number of indexes (every index adds work per row)
- Single-row versus multi-row statements, and transaction batch size
- Writer connections and threads; concurrent reader load
- SQLite version and compile options
journal_modeandsynchronoussettings- Storage device and filesystem; cache state; warm-up and run length
- Whether the rate counts committed rows or attempted statements
Report rows/sec and transactions/sec separately, and look at tail latency, not just the average. Don’t compare an in-memory or unsynced run against a durable on-disk run without labeling the difference. Fast local storage such as an NVMe SSD can help, because commit syncs hit the disk, but the drive alone does not guarantee a target. Batching and durability settings usually matter more.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteCheck your SQLite version
SQLite documents a WAL-reset bug fixed in 3.51.3 and later, with backports in 3.44.6 and 3.50.7. It needs multiple connections to one WAL database and tightly timed concurrent writes and checkpoints, which is exactly what a multi-connection pool can produce. Verify the library version you actually ship (for example SELECT sqlite_version();), since language runtimes often bundle their own copy.
Quick Recap
Troubleshooting
- Under target, one commit per row: wrap rows in transactions and batch them.
- Frequent SQLITE_BUSY: too many writer connections, or long transactions. Move to a single writer queue and set a busy timeout.
- WAL file keeps growing: a long-lived reader is blocking checkpoints, or write transactions are too large.
- journal_mode did not return wal: the database may be on a filesystem or configuration where WAL is unavailable; check what the pragma returns rather than assuming.
- Corruption or missing data after a copy: the WAL file was separated from the database.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
