SQLite is astonishingly capable for an embedded database — it powers phones, browsers, and plenty of production services. But out of the box it is tuned for safety and simplicity, not raw speed. A handful of deliberate choices around journaling, transactions and indexing make the difference between sluggish and instant.

1. Turn on WAL mode

The single biggest win for most applications is Write-Ahead Logging. It lets readers and a writer work at the same time instead of blocking each other.

PRAGMA journal_mode = WAL;

You only need to set it once per database; the setting persists. With WAL you can usually also relax durability slightly for a big speed boost:

PRAGMA synchronous = NORMAL;

2. Batch writes in a single transaction

By default every INSERT is its own transaction, which means a disk sync per row. Wrapping many writes in one transaction is often a 100x speedup.

BEGIN;
INSERT INTO events (ts, msg) VALUES (...);
INSERT INTO events (ts, msg) VALUES (...);
-- ... thousands more ...
COMMIT;

3. Index what you actually query

An index turns a full-table scan into a direct lookup. Create indexes that match the columns in your WHERE, JOIN and ORDER BY clauses.

CREATE INDEX idx_events_ts ON events(ts);

-- then confirm the planner uses it:
EXPLAIN QUERY PLAN
SELECT * FROM events WHERE ts > 1700000000;

EXPLAIN QUERY PLAN is your best friend: if you see SCAN TABLE where you expected SEARCH ... USING INDEX, you are missing an index. A covering index (one that includes every column the query needs) can skip the table entirely.

🔧 BackendSide Tool

SQLiteDesk — A Real Workbench for SQLite

Tuning a database is far easier when you can see it. SQLiteDesk lets you browse and edit data in a grid, write queries in an SQL workspace with autocomplete, design schemas and ER diagrams visually, run online backups and full maintenance — and it supports SQLCipher-encrypted databases out of the box.

Explore SQLiteDesk →

4. Tune a few key PRAGMAs

PRAGMA cache_size = -20000;   -- ~20 MB page cache (negative = KB)
PRAGMA temp_store = MEMORY;   -- keep temp tables/indexes in RAM
PRAGMA mmap_size = 268435456; -- memory-map up to 256 MB of the DB

These trade a little memory for noticeably less disk I/O on read-heavy workloads.

5. Keep the planner’s statistics fresh

SQLite’s query planner makes better decisions when it knows the shape of your data. Run ANALYZE after large data changes, or let SQLite manage it:

PRAGMA optimize;   -- run periodically / before closing a long-lived connection

6. Reclaim space and defragment

After lots of deletes and updates, a database can become fragmented and bloated. VACUUM rebuilds it into a compact, contiguous file:

VACUUM;

A note on schema design

The fastest query is the one your schema makes easy. Normalising sensibly, choosing the right column types, and defining clear relationships up front prevents a whole class of performance problems later.

🔧 BackendSide Tool

ERPDesk — Design Your Schema Before You Optimize It

A well-indexed query starts with a well-designed schema. ERPDesk is a visual designer for ER, class, process and sequence diagrams, with multi-dialect SQL generation (including SQLite), SQL reverse-engineering of existing databases, and XMI round-trip — ideal for getting your tables and relationships right from the start.

Explore ERPDesk →

Key takeaways

  • Enable WAL mode for concurrency; pair it with synchronous = NORMAL.
  • Wrap bulk writes in a single transaction.
  • Add indexes that match your queries, and verify them with EXPLAIN QUERY PLAN.
  • Tune cache_size, temp_store and mmap_size for your workload.
  • Run ANALYZE/PRAGMA optimize and VACUUM to stay fast over time.

Apply these and most “SQLite is slow” complaints simply disappear.