sqlite-optimization

Set these PRAGMAs on every connection for most applications:

```sql

PRAGMA journal_mode = WAL; -- Non-blocking concurrent reads/writes

PRAGMA synchronous = NORMAL; -- Fast commits, safe for WAL mode

PRAGMA foreign_keys = ON; -- Data integrity

PRAGMA cache_size = -64000; -- 64MB cache (negative = KB)

PRAGMA temp_store = MEMORY; -- In-memory temp tables/sorting

PRAGMA busy_timeout = 5000; -- 5s retry on lock contention

PRAGMA mmap_size = 2147483648; -- 2GB memory-mapped I/O (benchmark first)

```

Run periodically:

```sql

PRAGMA optimize; -- Update query planner statistics

```

1. Diagnose with EXPLAIN QUERY PLAN

```sql

EXPLAIN QUERY PLAN SELECT ...;

```

Key indicators:

• SCAN TABLE → Full table scan (O(N)) — needs index or query rewrite
• SEARCH TABLE USING INDEX → Index lookup (O(log N)) — good
• COVERING INDEX → Index-only scan, no table lookup — optimal
• USE TEMP B-TREE → Sorting not covered by index — consider index with ORDER BY columns

2. Schema Design

Use INTEGER PRIMARY KEY for rowid alias:

```sql

CREATE TABLE users (

id INTEGER PRIMARY KEY, -- Alias for rowid, no extra index

email TEXT NOT NULL,

created_at TEXT

);

```

Choose appropriate types:

• INTEGER for IDs, counters, booleans
• REAL for floats
• TEXT for strings
• Avoid large BLOBs in hot tables; use separate table or external files

WITHOUT ROWID for junction tables:

```sql

CREATE TABLE user_roles (

user_id INTEGER,

role_id INTEGER,

PRIMARY KEY (user_id, role_id)

) WITHOUT ROWID; -- Clustered index, no rowid overhead

```

3. Indexing Strategy

Create indexes for real query patterns:

```sql

-- For: WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC

CREATE INDEX idx_orders_user_created

ON orders (user_id, created_at DESC);

```

Covering indexes eliminate table lookups:

```sql

-- For: SELECT user_id, total FROM orders WHERE user_id = ?

CREATE INDEX idx_orders_cover

ON orders (user_id, created_at DESC, total);

```

Partial indexes for subsets:

```sql

CREATE INDEX idx_active_users

ON users (email) WHERE status = 'active';

```

Expression indexes:

```sql

CREATE INDEX idx_users_lower_email

ON users (lower(email));

```

Avoid indexing:

• Small tables (< hundreds of rows)
• Low-cardinality columns alone (booleans)
• Columns rarely in WHERE/JOIN/ORDER BY

4. Write Optimization

Always batch writes in transactions:

```sql

BEGIN TRANSACTION;

INSERT INTO logs (...) VALUES (...);

-- ... many more inserts

COMMIT;

```

Throughput jumps from ~50 inserts/sec (autocommit) to 50,000+ inserts/sec.

Use prepared statements:

```python

stmt = conn.prepare("INSERT INTO logs (ts, level, msg) VALUES (?, ?, ?)")

for row in data:

stmt.execute(row)

```

Optimal batch size: 1,000–10,000 rows balances throughput vs memory.

5. Concurrency Configuration

WAL mode enables concurrent readers + one writer:

• Readers don't block writers
• Writers don't block readers
• Uses .wal and .shm files alongside main DB

Handle contention:

```sql

PRAGMA busy_timeout = 5000; -- Retry for 5 seconds before SQLITE_BUSY

```

Architecture pattern: Single writer thread/process + many reader connections.

6. Maintenance

VACUUM after large deletes:

```sql

VACUUM; -- Rebuilds and defragments DB file

```

• Run during maintenance windows (blocking operation)
• Requires free disk space ≈ DB size

Auto-vacuum for incremental reclaim:

```sql

PRAGMA auto_vacuum = INCREMENTAL;

VACUUM; -- Apply change

-- Then periodically:

PRAGMA incremental_vacuum;

```

Keep statistics fresh:

```sql

PRAGMA optimize; -- Smart, selective ANALYZE

-- Or full:

ANALYZE;

```

• [pragma-reference.md](references/pragma-reference.md) — Complete PRAGMA settings with tradeoffs
• [indexing-guide.md](references/indexing-guide.md) — Advanced indexing patterns and diagnostics
• [architecture.md](references/architecture.md) — SQLite internals: B-trees, VDBE, Pager, concurrency mechanics

| Problem | Solution |

|---------|----------|

| SELECT * | List only needed columns |

| Loop with single inserts | Batch in transaction |

| Correlated subquery per row | Rewrite as JOIN |

| Index on every column | Index only queried columns |

| No busy_timeout | Set appropriate timeout |

| Rollback journal mode | Switch to WAL |

| Never running ANALYZE | Run PRAGMA optimize periodically |

Mobile (iOS/Android):

• WAL + synchronous = NORMAL
• Background maintenance jobs for VACUUM
• Local storage only (not SD card)

Server/Desktop:

• Centralize writes in single thread if possible
• Set PRAGMAs immediately after connection open
• Consider connection pooling with writer serialization