How to Fix Slow SQLite Queries

Most "slow SQLite" reports aren't about queries. They're about PRAGMAs that should have been set the first time the database was opened. A SQLite database with default settings, hammered by a multi-threaded app, will spend most of its time blocked on SQLITE_BUSY or fsyncing every write to disk. The same database with journal_mode = WAL, synchronous = NORMAL, and a sensible cache size will fly. The difference is half a dozen lines of configuration that most teams discover after their first incident.

This is what makes SQLite performance different from server databases. SQLite isn't a process you tune. It's a library you embed, and most of the tuning happens in the connection setup code. Once you've done that part right, the query optimizer is straightforward, the concurrency model is predictable, and the file is a million times faster than its reputation suggests.

This guide is the long version of "what every SQLite-backed app should be doing": the PRAGMAs that matter, the concurrency model and why apps deadlock on it, EXPLAIN QUERY PLAN, the index types and patterns, FTS5 and JSON1 for text and document workloads, the application-side tools (better-sqlite3, sqlite-utils, Datasette, litestream), and the patterns that turn SQLite into a serious production database.

Why SQLite queries get slow

Four patterns drive almost every slow-SQLite report:

1. Wrong PRAGMAs. Default journal_mode = DELETE serializes readers and writers. Default synchronous = FULL fsyncs on every commit. Default 2MB cache forces every working-set query to hit disk. Fix these and most "slow SQLite" disappears.

2. Concurrency model misunderstood. SQLite has one writer at a time, full stop. Long writes block every other writer. Apps that try to "scale concurrency" by opening more connections often make things worse.

3. Missing index, no statistics. Like Postgres or MySQL. EXPLAIN QUERY PLAN shows SCAN, add the right index, gain three orders of magnitude. Don't forget ANALYZE.

4. The query is doing something that prevents index use. Function on indexed column, leading wildcard LIKE, OR across different columns. The mechanical query-rewrite section of every database performance post.

This post leads with the PRAGMA section because that's the fix that helps the most apps. If you've ever shipped a SQLite-backed app to production without setting WAL mode, you've probably already met the failure mode.

Step 1: The PRAGMAs you should set first

These are the settings worth applying to every production SQLite database, in roughly priority order.

journal_mode = WAL

The single most important setting. The default journal_mode = DELETE writes a rollback journal that serializes readers and writers: while someone writes, nobody can read, and vice versa. WAL (Write-Ahead Logging) lets readers proceed during writes:

PRAGMA journal_mode = WAL;

This is set per database file, not per connection. Set it once after creating the database. WAL persists across connections.

WAL changes the file layout: you'll see a db.wal sidecar file and a db.shm shared memory file. They're part of the database. Don't delete them. Some old SQLite tools don't understand them; verify before using third-party utilities.

There's a small downside: WAL mode requires the storage to support shared memory. On NFS or some restricted filesystems, you might need journal_mode = TRUNCATE instead. On any local disk or modern cloud volume, WAL is fine.

In WAL mode, periodically run PRAGMA wal_checkpoint(TRUNCATE) to flush the WAL back to the main file. SQLite does automatic checkpoints, but on bursty write workloads the WAL can grow before checkpoint catches up. Most apps don't need to do this manually; if the WAL is consistently large, set wal_autocheckpoint lower.

synchronous = NORMAL

The default synchronous = FULL fsyncs on every commit. Safe but slow. NORMAL is safe enough for most apps in combination with WAL and is 2-10x faster:

PRAGMA synchronous = NORMAL;

The difference: FULL survives any kind of crash, including OS-level. NORMAL survives application crashes and clean shutdowns but a power loss at exactly the wrong moment could lose the last committed transaction. For most application workloads, NORMAL is the right setting.

OFF removes fsync entirely. Dramatically faster, and corrupts the database if anything goes wrong. Use for ephemeral databases (test fixtures, scratch space) and never for anything you care about.

cache_size

The default page cache is 2000 pages (about 8MB with the default 4KB page size). For a database hot path, this is tiny. Bump it to something reasonable:

PRAGMA cache_size = -200000;

The negative value is in KB (so 200MB here). A positive value is in pages. The negative form is easier to reason about; the positive form depends on page_size. The cache is per-connection.

For an application database, 100-500MB per connection is reasonable. For an embedded database in a phone app, smaller (10-50MB). The cache should be big enough to hold the indexes and hot pages of frequently-accessed tables.

mmap_size

Memory-mapped I/O lets SQLite read pages directly from the OS page cache without copying into its own cache. For read-heavy workloads on large databases, this is a big win:

PRAGMA mmap_size = 268435456;

256MB shown. On 64-bit systems, you can set this to gigabytes without much downside; the OS only pages in what's needed.

mmap helps reads, not writes. Writes still go through the regular page cache.

temp_store = MEMORY

Forces temporary B-trees (used by ORDER BY without an index, GROUP BY, DISTINCT) into memory instead of a temp file:

PRAGMA temp_store = MEMORY;

For most workloads, the temp data is small and fits in RAM. For huge sorts you might want disk-backed temp; in that case, leave the default.

busy_timeout

When the database is locked (because another connection is writing), the default behavior is to fail immediately with SQLITE_BUSY. That's almost never what an application wants:

PRAGMA busy_timeout = 5000;

SQLite will retry for 5 seconds before giving up. This is the difference between an app that handles concurrency well and one that pelts the developer with SQLITE_BUSY errors under load. Set it on every connection.

foreign_keys = ON

Foreign keys are not enforced by default. They're declared in the schema but only enforced if you turn them on per connection:

PRAGMA foreign_keys = ON;

This isn't a performance setting, but it's the one most people forget to set in production. Set it in your connection initialization.

Putting it together

A reasonable production setup, applied to every new connection:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -200000;
PRAGMA mmap_size = 268435456;
PRAGMA temp_store = MEMORY;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

Some of these (like journal_mode) are file-level and persist. Most (like cache_size, busy_timeout) are connection-level and need to be set every time. better-sqlite3 (Node), sqlite3 (Python), rusqlite (Rust), and most other drivers let you set them in a connection-init callback. Use it.

Step 2: Understand the concurrency model

The single most consequential thing about SQLite for production apps is the concurrency model. It is different from every server database in this comparison.

One writer at a time

SQLite serializes all writes. There's no way around this. Two connections cannot simultaneously execute write transactions. The second one waits. With busy_timeout, it waits politely; without, it errors immediately.

This is fine if your write transactions are short. It's catastrophic if they're long. A 10-second write transaction blocks every other write for 10 seconds.

Practical implications:

• Don't do network I/O inside a write transaction. A BEGIN; ... external API call; COMMIT; is a recipe for serialization hell.
• Batch writes. Many small writes scattered through application code become many short transactions. Group them.
• Move slow logic out of the transaction. Compute the value you want to write, then write it.

WAL allows concurrent reads

With WAL mode, multiple readers can read concurrently, even while a writer is writing. The reader sees the database as of the moment the read transaction started; the writer's changes aren't visible until commit.

This is the killer feature of WAL. The default journal_mode = DELETE serializes everything. WAL gives you Postgres-like multi-version concurrency for reads.

Transaction types: DEFERRED, IMMEDIATE, EXCLUSIVE

When you BEGIN, the default is DEFERRED. The transaction doesn't acquire a lock until the first read or write operation. This sounds nice but creates a deadlock risk: two BEGIN DEFERRED transactions both promote to writer simultaneously, both block, both eventually error.

For transactions that you know will write, use BEGIN IMMEDIATE:

BEGIN IMMEDIATE;
INSERT INTO orders ...;
UPDATE customers ...;
COMMIT;

IMMEDIATE acquires the write lock at BEGIN. If another writer is already going, this transaction waits at BEGIN instead of mid-transaction. Cleaner failure mode.

For most apps, the pattern is:

• Read-only transactions: BEGIN DEFERRED (or the default).
• Write transactions: BEGIN IMMEDIATE.

If you don't separate them, you'll get SQLITE_BUSY errors mid-transaction that are hard to diagnose.

Connection pooling

SQLite doesn't need traditional connection pooling, because connections are cheap and there's only one writer slot. But you do need to think about how many connections you keep open.

Patterns that work:

• One reader pool, one dedicated writer. Reads scale; writes are serialized through one connection.
• Per-thread connection. Each application thread has its own connection, with appropriate locking around writes.

What doesn't work: opening hundreds of connections expecting them to all write concurrently. They won't.

For Node.js, better-sqlite3 is synchronous and single-threaded; it's a natural fit for the model. For Python, the standard sqlite3 library is async-unfriendly; use it carefully with threading.Lock or move to aiosqlite with discipline about transactions.

Long readers can block writes too

Less obvious gotcha: in WAL mode, a read transaction holds a snapshot. If a read takes an hour, the WAL can't be checkpointed during that hour (because the snapshot might still need old pages). The WAL grows; writes get slower as it does.

For long-running reads, either use a separate read replica (yes, you can), or chunk the work into shorter transactions.

Step 3: Diagnose with EXPLAIN QUERY PLAN

SQLite doesn't have pg_stat_statements or a slow query log built in. The application is the profiler. Most drivers have hooks for this.

Time queries from the CLI

For interactive debugging:

.timer on
SELECT * FROM orders WHERE customer_id = 12345;
-- Run Time: real 2.834 user 0.012 sys 0.085

real is wall-clock time, which is what you usually care about.

Profile from the application

// better-sqlite3 (Node.js)
const start = process.hrtime.bigint();
const result = db.prepare(sql).all(...params);
const ms = Number(process.hrtime.bigint() - start) / 1_000_000;
if (ms > 100) {
  console.warn(`slow query: ${ms.toFixed(1)}ms ${sql}`);
}

import sqlite3, time

class TimingCursor(sqlite3.Cursor):
    def execute(self, sql, params=()):
        start = time.perf_counter()
        try:
            return super().execute(sql, params)
        finally:
            ms = (time.perf_counter() - start) * 1000
            if ms > 100:
                print(f"slow query: {ms:.1f}ms {sql}")

For richer tracing, set a profiler callback. better-sqlite3 has db.function; Python's sqlite3 has set_trace_callback; rusqlite has Connection::trace. Use them in development; ship without them in production.

EXPLAIN QUERY PLAN

Once you have a slow query, run EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id = 12345
AND status = 'pending';

QUERY PLAN
`--SCAN orders

SCAN means a full table scan. On 500,000 rows, that's the problem.

The other words to know:

A healthy plan is full of SEARCH ... USING INDEX lines. A single SCAN on a table larger than a few thousand rows is suspect.

ANALYZE and sqlite_stat1

SQLite's query planner uses statistics from the sqlite_stat1 table. ANALYZE populates these:

ANALYZE;

Or for a specific table:

ANALYZE orders;

Without statistics, SQLite uses heuristics. They're often wrong on large tables, leading to the wrong index choice. Run ANALYZE after a bulk load, after a migration, or on any table where data distribution has shifted.

Inspect what the optimizer knows:

SELECT * FROM sqlite_stat1 WHERE tbl = 'orders';

orders  idx_orders_customer  500000 50

That row says: the table has 500,000 rows, and the index averages 50 rows per distinct customer_id. If your real distribution is wildly different (one customer has 100,000 orders, the rest have a handful), the optimizer might pick the wrong index because it assumes a uniform distribution. The fix is more advanced statistics:

PRAGMA analysis_limit = 1000;
PRAGMA optimize;

PRAGMA optimize (added in 3.18) lets SQLite update stats incrementally based on its tracking of which tables changed. Worth running periodically.

sqlite3_analyzer

For deeper investigation of how space is used:

sqlite3_analyzer mydb.sqlite

You get a long report with per-table page counts, fragmentation, B-tree statistics, and more. Useful for understanding why a file is huge or why a query is doing more I/O than expected.

The .expert command

The sqlite3 CLI has an .expert mode that suggests indexes for a query:

sqlite> .expert
sqlite> SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';

CREATE INDEX orders_idx_xxxxxxxx ON orders(customer_id, status);

(no records returned)

Treat as a starting point, not an answer. The suggestion doesn't know your overall workload or partial-index opportunities.

Step 4: Index design

SQLite is B-tree-only at the core (with FTS5 and R-Tree as extensions). The strategy is similar to other relational databases, but a few SQLite-specific notes matter.

Single-column indexes

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Verify with EXPLAIN QUERY PLAN that the planner picks the new index. If it doesn't, run ANALYZE.

Composite indexes

For queries that filter on multiple columns:

CREATE INDEX idx_orders_customer_status
  ON orders(customer_id, status);

Leftmost-column rule applies. The index helps:

WHERE customer_id = 12345 AND status = 'pending'
WHERE customer_id = 12345

But not:

WHERE status = 'pending'  -- missing leftmost column

Put the most selective equality column first.

Covering indexes

If the index contains every column the query reads, SQLite never touches the table:

SELECT customer_id, status, total FROM orders
WHERE customer_id = 12345;

CREATE INDEX idx_orders_covering
  ON orders(customer_id, status, total);

EXPLAIN QUERY PLAN shows USING COVERING INDEX. For dashboard queries with a small projection, this is the fastest possible read.

Partial indexes

Index only the rows that match a predicate:

CREATE INDEX idx_orders_pending
  ON orders(customer_id)
  WHERE status = 'pending';

The query must include the predicate for the planner to use the partial index. Partial indexes are dramatically smaller and faster on workloads with skewed distributions (most orders are settled, only the pending ones are queried).

Expression indexes

For queries that wrap columns in functions:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Uses the index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

This is also the standard pattern for indexing inside JSON columns (more below).

WITHOUT ROWID tables

SQLite tables normally have an internal rowid, a 64-bit integer that's the actual storage key. The primary key is a regular index on top.

For tables with a non-integer primary key, WITHOUT ROWID makes the primary key the B-tree itself, eliminating a level of indirection:

CREATE TABLE sessions (
  token TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  created_at INTEGER NOT NULL
) WITHOUT ROWID;

Wins: smaller disk usage, faster primary-key lookups. Losses: secondary indexes are a bit bigger because they have to store the full primary key as the rowid equivalent.

The rule of thumb: use WITHOUT ROWID when the primary key is small (under ~32 bytes) and you don't have many secondary indexes.

Indexing JSON columns

SQLite has good JSON support via the built-in JSON1 functions. Index specific paths with expression indexes:

CREATE INDEX idx_events_user_id
  ON events(json_extract(data, '$.user_id'));

SELECT * FROM events
WHERE json_extract(data, '$.user_id') = '12345';

The query must use the same json_extract expression as the index for the planner to use it.

For more sophisticated patterns (multiple JSON paths, generated columns), see our post on SQLite JSON virtual columns and indexing.

When not to add an index

Indexes aren't free. SQLite uses copy-on-write internally, so an index update on a write-heavy table adds I/O. Avoid indexing:

• Boolean flags or other low-cardinality columns.
• Tables under a few thousand rows. A scan is faster than two B-tree lookups.
• Columns you never filter or sort on.

To find unused indexes, you can't do it from SQL directly (no pg_stat_user_indexes equivalent). Profile your queries with EXPLAIN QUERY PLAN over a representative workload.

Concurrent index builds

SQLite's CREATE INDEX locks the database. There's no online concurrent build like Postgres has. For a large table on a production database, the lock duration matters.

The pragmatic approach: do schema changes during maintenance windows, use small batched migrations where possible, or copy the data to a new table with the new index and swap atomically.

DB Pro

Work With Your Databases

Like A Pro

Query, explore, and manage your databases with a beautiful desktop app, collaborative web platform, and built-in AI.

Download Now

Step 5: Rewrite the query

The same mechanical patterns as other databases.

Avoid SELECT *

Reads every column, including big ones, defeats covering indexes:

-- Reads everything
SELECT * FROM orders WHERE customer_id = 12345;

-- Faster, and may use a covering index
SELECT id, status, total FROM orders WHERE customer_id = 12345;

Keyset pagination over OFFSET

-- Slow on page 500: walks every skipped row
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Fast: jumps to a known position
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;

Functions on indexed columns

-- Disables the index
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-18';

-- Index-friendly range
SELECT * FROM orders
WHERE created_at >= '2026-06-18 00:00:00'
  AND created_at <  '2026-06-19 00:00:00';

UNION ALL over OR across columns

When OR spans different columns, SQLite often falls back to a scan:

-- May SCAN
SELECT * FROM orders
WHERE customer_id = 12345 OR external_ref = 'abc-123';

-- Each side uses its own index
SELECT * FROM orders WHERE customer_id = 12345
UNION ALL
SELECT * FROM orders
WHERE external_ref = 'abc-123' AND customer_id <> 12345;

Avoid LIKE with leading wildcards

-- Cannot use a B-tree index
SELECT * FROM products WHERE name LIKE '%widget%';

For substring search, use FTS5 (below). For "starts with" matches, regular indexes work:

-- Uses the index
SELECT * FROM products WHERE name LIKE 'widget%';

Batch inserts in a transaction

SQLite auto-commits each statement by default. For bulk inserts, wrap them:

BEGIN;
INSERT INTO orders VALUES (...);
INSERT INTO orders VALUES (...);
-- thousands more
COMMIT;

This can be 100x faster than auto-commit because each commit otherwise fsyncs (depending on synchronous setting).

For very-bulk inserts (millions of rows), use prepared statements with INSERT ... VALUES (?, ?, ?) and reuse the prepared statement.

Step 6: FTS5 for text search

The built-in LIKE '%foo%' is a sequential scan with no index help. For any substantive text search, use FTS5: SQLite's built-in full-text search extension.

CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content=posts, content_rowid=id
);

-- Populate
INSERT INTO posts_fts(rowid, title, body)
SELECT id, title, body FROM posts;

-- Query
SELECT p.*
FROM posts_fts f
JOIN posts p ON p.id = f.rowid
WHERE posts_fts MATCH 'sqlite AND performance'
ORDER BY rank;

FTS5 supports prefix matching (MATCH 'sql*'), phrase queries (MATCH '"slow queries"'), boolean operators, and column filters (MATCH 'title: sqlite').

For more depth, see our SQLite FTS5 guide.

A few production notes:

• Use the contentless mode (content='') when you don't need to retrieve the original text from FTS, only search it. Saves substantial disk.
• The external content mode (content=posts) keeps the main table as the source of truth and only indexes it. Requires triggers to keep in sync.
• rebuild rebuilds the index from scratch: INSERT INTO posts_fts(posts_fts) VALUES('rebuild');
• For ranked queries, FTS5's BM25 ranking is exposed via the rank column on selects.

Step 7: JSON1 for document workloads

For semi-structured data, SQLite's JSON1 extension is on by default in modern versions:

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL CHECK (json_valid(data))
);

INSERT INTO events(data) VALUES('{"type": "signup", "user_id": 12345}');

SELECT * FROM events
WHERE json_extract(data, '$.user_id') = 12345;

The pattern that scales:

1. Store JSON as TEXT with a CHECK (json_valid(data)) constraint.
2. Index specific paths with expression indexes.
3. Use JSON_TYPE, json_each, json_tree for traversal.

For complex patterns including generated columns and array indexing, our post on SQLite JSON virtual columns and indexing covers the patterns. The short version: a generated column plus an index on it is the cleanest way to index a JSON path.

SQLite 3.45 added JSONB (binary JSON) for faster operations, similar to Postgres jsonb. If you're on a recent version, it's worth using.

Step 8: Configuration beyond PRAGMAs

Most SQLite "configuration" is the PRAGMA block from Step 1. A few additional knobs.

page_size

Set once, at database creation time. Must be a power of 2 between 512 and 65536. Default is 4096, which matches most filesystems. Common reasons to override:

• 8192 or 16384 for SSDs and modern workloads. Slight performance win at the cost of small databases.
• Larger pages for read-mostly databases with big rows.

Once a database is created, changing page_size requires a VACUUM. Pick at create time.

auto_vacuum

PRAGMA auto_vacuum = INCREMENTAL;

Default is NONE: the file only grows. Deletions leave empty pages in the file. To reclaim, run VACUUM manually.

FULL: rebalances after every delete. Expensive.

INCREMENTAL: tracks free pages, lets you reclaim them on demand with PRAGMA incremental_vacuum. The middle ground.

For most apps, NONE is fine; databases grow but stay efficient. For apps with significant churn, INCREMENTAL with a periodic PRAGMA incremental_vacuum(N) is the right pattern.

wal_autocheckpoint

Defaults to 1000 pages. The WAL gets checkpointed automatically when it crosses this threshold. For workloads with sustained write throughput, you may want a larger threshold to amortize the checkpoint cost:

PRAGMA wal_autocheckpoint = 10000;

For workloads where you want to keep the WAL small (perhaps to reduce reader-blocked-on-writer scenarios), lower it.

Threading mode

SQLite has three threading modes:

• Single-thread: not safe at all to share connections.
• Multi-thread: each connection used by one thread at a time (default).
• Serialized: connections can be used by multiple threads (with internal locking).

Most drivers expose this as a compile-time or runtime option. The default multi-thread is right for most apps; each thread gets its own connection.

Step 9: Common slow query patterns

Writer contention without WAL

In default DELETE journal mode, every reader blocks every writer and vice versa. Symptom: writes occasionally take seconds for no clear reason. Fix: WAL.

Long-running writers

Even in WAL mode, only one writer runs at a time. A transaction that takes a second blocks every other write for that second. Keep write transactions short. Move slow logic out of the transaction boundary.

Missing index on foreign keys

SQLite doesn't auto-index foreign key columns. They're a common cause of slow joins:

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

This is so common that some app frameworks add it by default; if yours doesn't, add it explicitly.

Sorting without an index

USE TEMP B-TREE FOR ORDER BY in the plan. The sort runs in memory or temp file:

-- Forced sort
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

-- Index produces the rows in order
CREATE INDEX idx_orders_status_created
  ON orders(status, created_at DESC);

auto_vacuum surprises

auto_vacuum = FULL is sometimes set thinking it'll save disk space. It rebalances on every delete and is slow. Change to INCREMENTAL or NONE.

Schema changes locking the database

ALTER TABLE ADD COLUMN is fast in modern SQLite. ALTER TABLE DROP COLUMN (since 3.35) is fast for simple cases. But some DDL still rewrites the entire table, holding an exclusive lock:

• Changing a column type.
• Adding a FOREIGN KEY constraint.
• Reordering columns.

For these, use the INSERT INTO new_table SELECT ... FROM old_table; DROP TABLE old_table; ALTER TABLE new_table RENAME TO old_table; pattern, or accept the maintenance window.

N+1 queries from ORMs

Same problem as in any other database. SQLite makes it worse in some ways (every query has prepare overhead even though it's in-process), better in others (no network round-trip). Use eager loading or batch with IN:

SELECT * FROM order_items WHERE order_id IN (1, 2, 3, /* ... */);

Big TEXT/BLOB columns

A row with one giant column is read in full unless you project around it:

-- Reads the giant column too
SELECT * FROM articles WHERE id = 12345;

-- Reads only what you need
SELECT id, title, summary FROM articles WHERE id = 12345;

For large blobs, consider storing them outside the database (in object storage) and keeping only the reference in SQLite.

Cross-database joins via ATTACH

ATTACH DATABASE 'other.sqlite' AS other lets you query across databases. Joins across attached databases work but with less optimization than within a single file. For high-traffic cross-database queries, denormalize into the primary database.

Stale statistics

After a bulk load or migration, ANALYZE may be stale. If a query that used to be fast turned slow after a data change, try ANALYZE before debugging deeper.

Step 10: When tuning isn't enough

SQLite handles surprisingly large workloads. The official docs note SQLite handles 281 TB databases. Most apps will hit operational limits long before they hit any technical SQLite limit. But here are the escape hatches when you need them.

Litestream for backup and replication

Litestream is a single-binary tool that continuously replicates SQLite to S3 (or any S3-compatible store). It works by tailing the WAL and shipping it to object storage in real-time:

litestream replicate -config litestream.yml

Recovery is litestream restore. For production SQLite, this is the standard backup approach. Don't run a production SQLite database without it or an equivalent.

Read replicas

You can run "read replicas" by replicating with Litestream to another machine and restoring continuously. There's some lag, but for read-heavy workloads it gives you Postgres-like read scaling.

LiteFS (also from the maker of Litestream) takes this further: it presents a SQLite database as a distributed filesystem that replicates over the network. Worth investigating for serverless and edge use cases.

Embedded vs network access

SQLite is in-process. If you want it accessed over the network, you'll need a wrapper. The common patterns:

• rqlite: a distributed SQLite via Raft.
• Turso: edge-distributed managed SQLite (libSQL fork).
• Cloudflare D1: SQLite-backed serverless database.

These trade SQLite's "library, not a server" simplicity for network access. Each has a different set of trade-offs around durability, replication, and cost.

Sharding by file

For pure horizontal scale: many SQLite files, one per tenant or per shard. Many production apps run this way; one database file per customer, queried per request. Disk is cheap, SQLite is fast, the file boundary is a natural shard boundary.

The tools worth installing

For most projects, the right starting set is: better-sqlite3 (or your language's equivalent), Litestream for backups, sqlite-utils for ad-hoc manipulation, and Datasette for serving the database as a quick API. That covers 95% of real-world SQLite operations.

sqlite-utils

sqlite-utils deserves special mention. It's a CLI and Python library for working with SQLite databases. The CLI is especially good for:

• Importing CSV, JSON, or stdin into a table with auto-detected schema.
• Adding columns, indexes, foreign keys.
• Bulk operations.
• Quick analysis.

If you do any data work on SQLite, install it.

Datasette

Datasette is the "publish a SQLite database as a web API" tool. Point it at a file and get a UI, a JSON API, faceted browsing, full-text search, and visualizations:

datasette serve mydb.sqlite

Built by Simon Willison; widely used for journalism, internal tools, and rapid prototyping. Worth knowing even if you don't use it daily.

Quick checklist

When you encounter a slow SQLite query or workload:

1. Set the basic PRAGMAs. WAL, synchronous=NORMAL, cache_size, mmap, busy_timeout. If you haven't, do this before anything else.
2. Run EXPLAIN QUERY PLAN. Look for SCAN on a large table. Add the missing index.
3. Run ANALYZE. Stale statistics cause wrong plan choices.
4. **Check for SELECT *** and OFFSET pagination. Both have mechanical fixes.
5. Check write-transaction shape. Long transactions block every other writer.
6. Use BEGIN IMMEDIATE for writes. Deferred transactions create subtle deadlocks.
7. For text search, switch to FTS5. LIKE '%foo%' doesn't scale.
8. For JSON data, expression-index the paths you query. JSON1 plus an index is fast.
9. Confirm with timing. Reproduce the slow query, apply the fix, re-time.

Most slow SQLite issues come down to PRAGMA defaults, missing indexes, or a misunderstanding of the concurrency model. Once you've gotten those three right, SQLite is a remarkable production database. The reputation for "toy database" comes from the easy mistakes; the reality is one of the most production-ready embedded engines in the world.