SQLite is known for fast reads, but naive insert patterns can be surprisingly slow — sometimes by orders of magnitude. A few configuration changes can improve insert performance by 50x or more, and most of them come down to reducing how often SQLite touches the disk.
Wrap Inserts in a Transaction
This is the single biggest lever available. By default, each individual INSERT is wrapped in its own transaction, and every transaction forces a disk sync — a slow operation, so inserting 10,000 rows as 10,000 separate statements means 10,000 disk syncs. Wrapping those same inserts in one explicit transaction changes that math entirely: 10,000 disk syncs become just one, and in benchmarks this alone can push insert speed from roughly 85 inserts per second to around 50,000. Testing with simple and real-world table structures alike showed that wrapping calls to insert() within a transaction vastly improves performance compared to doing the inserts without one.
Switch to WAL Journal Mode
Journal mode has a direct effect on how SQLite handles concurrent access and disk writes. Write-Ahead Logging (WAL) improves concurrent access and can internally batch inserts, often doubling insert speed compared to the default. A typical configuration pairs it with a few other PRAGMA settings:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -20000;
PRAGMA mmap_size = 268435456;
For older SQLite versions without WAL support, a less paranoid journal mode — NORMAL, or even OFF — can significantly increase insert speed if you’re not too worried about database corruption in the event of a crash.
Chunk Very Large Transactions
Bundling everything into one giant transaction isn’t always the safest move. For extremely large datasets, a single massive transaction can exhaust system memory or lead to long rollback times if something fails, so splitting inserts into smaller chunks — for example every 10,000 records — mitigates that risk while keeping most of the performance benefit.
Create Indexes After Inserting, Not Before
Index maintenance during inserts adds overhead you don’t need while loading data in bulk. Performing all INSERT operations first and creating indexes afterward is considerably faster than creating indexes incrementally as data comes in.
Consider WITHOUT ROWID Tables
For certain table shapes, SQLite offers a storage option that combines data and index into one structure. Tables defined with WITHOUT ROWID use a single B-Tree for both data and indexes, which can be beneficial for combined storage efficiency.
Tune Page Size for Your Workload
Page size is a smaller lever, but still worth knowing about. Beyond page_size, there isn’t much left to tune — the default is 4KB pages, and moving to 8KB or 16KB can gain a little at insertion time, though at the cost of more I/O later during updates and deletes, so it’s a balance specific to your workload.
Use Bulk Insert Tools When Available
If you’re moving large volumes of data regularly, purpose-built tooling can save you from reinventing the batching logic yourself. External libraries and frameworks designed for bulk data loading across systems like MySQL, SQLite, and PostgreSQL can provide substantial speed improvements, with some reporting a 10x increase, since they often handle chunking and optimization automatically.
Join The Discussion
SQLite’s insert performance can vary wildly — from under a hundred inserts per second to tens of thousands — depending entirely on how it’s configured. Have you run into a bottleneck that transactions or WAL mode didn’t fully solve, or found a PRAGMA setting or batching strategy that made a bigger difference than expected for your specific workload?
