SQLite is the most deployed database in the world (trillions of instances). Every iPhone has 100+ SQLite databases (Messages, Photos, Contacts, Safari). Every Android phone uses SQLite. Chrome browser uses SQLite for history, cookies, cache. WhatsApp uses SQLite to store 2 billion users' messages locally. Airbnb mobile app uses SQLite for offline mode (browse listings without internet). Dropbox uses SQLite for file metadata sync. SQLite is perfect when you need: (1) Local storage (no network), (2) Embedded in app (no separate server), (3) Zero configuration (no DBA), (4) Cross-platform (same file works on iOS/Android/Windows/Mac). NOT for: High-concurrency web servers (single writer bottleneck), Network database (no client-server), Large teams (no user management).

WhatsApp: 2 Billion Users with SQLite Local Storage

WhatsApp stores all messages locally in SQLite (one database per user). 2 billion users = 2 billion SQLite databases. Each database stores: messages, contacts, media metadata, chat settings. Challenges: (1) UI must stay smooth while syncing messages from server (background writes), (2) Fast message search across 100K+ messages, (3) Efficient storage (mobile devices have limited space), (4) Reliable (no message loss on crash).

WhatsApp uses SQLite with optimizations: (1) WAL mode (PRAGMA journal_mode=WAL) for non-blocking reads during sync. UI reads messages smoothly while background thread writes new messages. (2) Transaction batching: Sync batches 100 messages per transaction (100x faster than individual INSERTs). (3) Indexes on chat_id + timestamp for fast message retrieval. (4) Full-text search (FTS5) for message search. (5) Media stored in filesystem (photos/videos), only metadata in database. (6) Regular VACUUM during app idle time to reclaim space. (7) Backup: Copy .sqlite file to iCloud/Google Drive when app is closed.

-- WhatsApp message schema (simplified)

CREATE TABLE messages (

message_id INTEGER PRIMARY KEY AUTOINCREMENT,

chat_id INTEGER NOT NULL,

sender_id INTEGER NOT NULL,

content TEXT NOT NULL,

timestamp INTEGER NOT NULL,

is_read INTEGER DEFAULT 0,

media_path TEXT, -- Path to photo/video file

media_type TEXT -- 'image', 'video', 'audio'

);

CREATE INDEX idx_chat_timestamp ON messages(chat_id, timestamp);

CREATE INDEX idx_unread ON messages(chat_id, is_read) WHERE is_read = 0;

-- Enable WAL mode (non-blocking reads)

PRAGMA journal_mode=WAL;

-- Enable foreign keys

PRAGMA foreign_keys=ON;

-- UI thread: Load messages for chat (reader)

SELECT message_id, sender_id, content, timestamp, media_path

FROM messages

WHERE chat_id = 123

ORDER BY timestamp DESC

LIMIT 50;

-- Fast: Uses idx_chat_timestamp

-- Not blocked by background sync

-- Background thread: Sync new messages from server (writer, concurrent)

BEGIN TRANSACTION;

-- Batch 100 messages

INSERT INTO messages (chat_id, sender_id, content, timestamp, media_path, media_type)

VALUES

(123, 456, 'Hello!', 1704067200, NULL, NULL),

(123, 789, 'Hi!', 1704067201, '/media/photo1.jpg', 'image'),

-- ... 98 more messages

(123, 456, 'Bye!', 1704067299, NULL, NULL);

COMMIT;

-- Fast: Batched in transaction