SQLite is fundamentally different from MySQL/PostgreSQL/SQL Server. Those are client-server databases: separate server process, network connections, user management, high concurrency. SQLite is an embedded database: runs in-process with your app, no server, no network, no configuration. The entire database is a single file (mydb.sqlite). Your app links SQLite library (300KB), calls functions (sqlite3_open, sqlite3_exec), and SQLite reads/writes the file directly. No installation, no setup, no DBA. This makes SQLite perfect for mobile apps (every iPhone app can have its own database), desktop apps (Chrome stores history in SQLite), and embedded systems (IoT devices, cars, airplanes). But SQLite has limitations: single writer at a time (write bottleneck), no network access (can't connect remotely), limited ALTER TABLE (can't drop columns in old versions). Understanding when to use SQLite vs server database is critical for system design interviews.

**SQLite Architecture - Serverless Embedded Database:**

**Traditional Client-Server Database (MySQL/PostgreSQL):**

- Separate server process (mysqld, postgres) runs 24/7

- Clients connect over network (TCP/IP, localhost)

- Server manages connections, authentication, concurrency

- Multiple clients can write simultaneously (with locking/MVCC)

- Requires installation, configuration, DBA

**SQLite Embedded Database:**

- No server process, library runs in your app process

- No network, direct file I/O (read/write .sqlite file)

- No authentication, file permissions control access

- Single writer at a time (write lock on entire database)

- Zero configuration, no installation, no DBA

- Database is a single file (portable, easy backup)

**SQLite Concurrency Model - Single Writer, Multiple Readers:**

**Default Mode (Rollback Journal):**

- Only ONE writer at a time (exclusive lock on database)

- Writers block readers (readers wait for write to complete)

- Readers block writers (writer waits for all readers to finish)

- Low concurrency, but simple and reliable

- Good for: Single-user apps, low-traffic scenarios

**WAL Mode (Write-Ahead Logging) - Better Concurrency:**

- One writer + multiple readers simultaneously

- Writers DON'T block readers (readers see old data)

- Readers DON'T block writers (writer creates new data)

- Similar to MVCC but simpler (no vacuum needed)

- Enable with: PRAGMA journal_mode=WAL;

- Good for: Multi-threaded apps, higher read concurrency

- Trade-off: Slightly more complex, requires WAL file cleanup

**SQLite Performance Characteristics:**

**Reads (Fast):**

- 100,000 reads/sec on modern hardware

- All data in single file = good locality

- No network overhead = 1000x faster than remote database

- Memory-mapped I/O = near-RAM speed for hot data

**Writes (Bottleneck):**

- Single writer = 1,000-10,000 writes/sec

- Each write requires fsync (flush to disk) = slow

- Batch writes in transactions = 100x faster