SQLite performance is excellent for reads (100K reads/sec) but limited for writes (single writer = 1K-10K writes/sec). Optimization strategies: (1) Use WAL mode (PRAGMA journal_mode=WAL) for 2-3x faster writes and non-blocking reads. (2) Batch writes in transactions (BEGIN; INSERT x1000; COMMIT;) for 100x speedup - single INSERT = 1ms + fsync 10ms = 11ms, batched 1000 INSERTs = 1000ms + fsync 10ms = 1010ms = 1ms per INSERT. (3) Use indexes on WHERE/JOIN columns (same as server databases). (4) Use PRAGMA synchronous=NORMAL (faster writes, small data loss risk on crash). (5) Use in-memory database for testing (:memory: or file::memory:?cache=shared). (6) Avoid large BLOBs in database (store files separately, store path in database). (7) Regular VACUUM to reclaim space (DELETE doesn't shrink file). (8) Use prepared statements (sqlite3_prepare_v2) to avoid SQL injection and improve performance. Example: WhatsApp batches message inserts (100 messages per transaction) for fast local storage. Chrome uses WAL mode for non-blocking history writes while browsing.

WAL mode: 2-3x faster writes, non-blocking reads (PRAGMA journal_mode=WAL)

Transaction batching: 100x faster bulk inserts (BEGIN; INSERT x1000; COMMIT;)

Multi-row INSERT: 2x faster than single-row (INSERT INTO table VALUES (1), (2), (3);)

PRAGMA synchronous=NORMAL: 2x faster writes, small data loss risk on crash (acceptable for cache, not for critical data)

Indexes: 100-1000x faster queries on WHERE/JOIN columns (same as server databases)

PRAGMA cache_size=10000: More memory cache (default 2000 pages = 8MB, increase for large databases)

Prepared statements: 10x faster repeated queries (compile once, execute many times)

VACUUM: Reclaims space after DELETE, improves query performance (defragments database)

Using SQLite for high-concurrency web servers (single writer bottleneck, use PostgreSQL/MySQL instead)

Not enabling WAL mode (default mode blocks readers during writes, use PRAGMA journal_mode=WAL)

Individual INSERTs without transactions (100x slower, always batch in transactions)

Storing large BLOBs in database (slow queries, large file, store files separately)

Not enabling foreign keys (PRAGMA foreign_keys=ON, disabled by default)