When should you use SQLite vs PostgreSQL/MySQL? Explain the trade-offs and give specific use cases.

Use SQLite when: (1) Embedded in application (mobile app, desktop app, embedded system), (2) Single user or low concurrency (<10 concurrent writes), (3) Local storage (no network access needed), (4) Zero configuration (no DBA, no server setup), (5) Cross-platform (same file works on iOS/Android/Windows/Mac). Examples: WhatsApp (local messages), Chrome (history/cookies), VS Code (settings). Use PostgreSQL/MySQL when: (1) Client-server architecture (multiple apps connect to shared database), (2) High concurrency (>10 concurrent writes, 100+ concurrent users), (3) Network access (remote connections), (4) Multi-user (user authentication, permissions), (5) Large datasets (>100GB with partitioning/sharding). Examples: E-commerce website, SaaS application, analytics platform. Trade-offs: SQLite pros: Zero config, embedded, cross-platform, fast reads. SQLite cons: Single writer, no network, limited ALTER TABLE. PostgreSQL pros: Multi-writer, MVCC, advanced features, network access. PostgreSQL cons: Requires server setup, more complex, higher resource usage. Key decision: Single user = SQLite, Multiple users = PostgreSQL/MySQL.

Explain SQLite WAL mode. How does it improve concurrency compared to default rollback journal mode?

SQLite has two journal modes: (1) Rollback journal (default): Writers block readers, readers block writers. Low concurrency. (2) WAL (Write-Ahead Logging): One writer + multiple readers simultaneously. Writers don't block readers, readers don't block writers. Higher concurrency. How WAL works: Writes go to separate WAL file (not main database file). Readers see data from main file + WAL file. Checkpoint process periodically moves WAL data to main file. Example: Mobile app with UI thread (reader) and background sync thread (writer). Default mode: Sync blocks UI = UI freeze. WAL mode: Sync doesn't block UI = smooth UX. Enable: PRAGMA journal_mode=WAL (persists in database file). Trade-offs: WAL pros: Non-blocking reads, 2-3x faster writes. WAL cons: Slightly more disk space (WAL file), requires checkpoint. When to use: Always use WAL for mobile/desktop apps. Default mode only for: Single-threaded apps, embedded systems with limited resources. Real-world: WhatsApp, Telegram, Signal, Chrome all use WAL mode.

Why is transaction batching critical for SQLite performance? Explain the fsync bottleneck.

SQLite writes are slow because of fsync (flush to disk) for durability. Each fsync takes 10ms (HDD) or 1ms (SSD). Without transactions: Each INSERT triggers fsync = 10ms per INSERT = 100 inserts/sec. With transactions: Multiple INSERTs + single fsync = 1ms per INSERT + 10ms fsync = 1000 inserts/sec = 10x faster. Example: Insert 1000 products. Without transaction: 1000 INSERTs × 10ms fsync = 10 seconds. With transaction: BEGIN; 1000 INSERTs (1ms each); COMMIT (10ms fsync); = 1 second. 10x speedup. Why fsync is slow: Disk write cache must flush to physical disk (magnetic platter or SSD flash). OS can't guarantee durability without fsync. SQLite is ACID-compliant, so it must fsync for durability. Best practices: (1) Batch 100-1000 operations per transaction. (2) Use multi-row INSERT for even better performance. (3) For non-critical data (cache), use PRAGMA synchronous=NORMAL (skips some fsyncs, 2x faster, small data loss risk). Real-world: WhatsApp batches 100 messages per transaction during sync. Dropbox batches 1000 file metadata updates per transaction. Always use transactions for bulk operations.

You're building a mobile messaging app. Messages are stored in SQLite. Users report UI freezes when receiving new messages. How do you diagnose and fix this?

-- Problem: UI freezes when background sync writes new messages

-- Likely cause: Default rollback journal mode blocks readers during writes

-- Step 1: Check current journal mode

PRAGMA journal_mode;

-- If result is "delete" or "persist" = rollback journal mode (blocking)

-- If result is "wal" = WAL mode (non-blocking)

-- Step 2: Enable WAL mode

PRAGMA journal_mode=WAL;

-- Result: wal

-- This enables non-blocking reads during writes

-- Step 3: Verify WAL mode is active

PRAGMA journal_mode;

-- Result: wal (persists across app restarts)

-- Step 4: Test concurrent read/write

-- Thread 1 (UI): Read messages

BEGIN;

SELECT * FROM messages WHERE chat_id = 123 ORDER BY timestamp DESC LIMIT 50;

-- Should not block

-- Thread 2 (Background sync): Write new messages (concurrent)

BEGIN;

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

VALUES (123, 456, 'New message', 1704067200);

COMMIT;

-- Should not block Thread 1

-- Thread 1: Continue reading

SELECT COUNT(*) FROM messages WHERE chat_id = 123;

-- Should see new message from Thread 2

COMMIT;

-- Step 5: Monitor WAL file size

PRAGMA wal_checkpoint(PASSIVE);

-- Moves WAL data to main database file

-- Run during app idle time (user not active)

-- Step 6: Verify fix