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.<\/p>\n
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.<\/p>\n
PRAGMA journal_mode = WAL;<\/code><\/pre>\nYou 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:<\/p>\n
PRAGMA synchronous = NORMAL;<\/code><\/pre>\n2. Batch writes in a single transaction<\/h2>\n
By default every INSERT<\/code> is its own transaction, which means a disk sync per row. Wrapping many writes in one transaction is often a 100x<\/strong> speedup.<\/p>\nBEGIN;\nINSERT INTO events (ts, msg) VALUES (...);\nINSERT INTO events (ts, msg) VALUES (...);\n-- ... thousands more ...\nCOMMIT;<\/code><\/pre>\n3. Index what you actually query<\/h2>\n
An index turns a full-table scan into a direct lookup. Create indexes that match the columns in your WHERE<\/code>, JOIN<\/code> and ORDER BY<\/code> clauses.<\/p>\nCREATE INDEX idx_events_ts ON events(ts);\n\n-- then confirm the planner uses it:\nEXPLAIN QUERY PLAN\nSELECT * FROM events WHERE ts > 1700000000;<\/code><\/pre>\nEXPLAIN QUERY PLAN<\/code> is your best friend: if you see SCAN TABLE<\/code> where you expected SEARCH ... USING INDEX<\/code>, you are missing an index. A covering<\/em> index (one that includes every column the query needs) can skip the table entirely.<\/p>\n\n🔧 BackendSide Tool<\/div>\nSQLiteDesk — A Real Workbench for SQLite<\/h4>\n
Tuning a database is far easier when you can see it. SQLiteDesk<\/strong> 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.<\/p>\n