Using SQLite for high-concurrency web server (e-commerce site with 1000 concurrent users placing orders)

Use PostgreSQL or MySQL for web servers (multi-writer concurrency, network access, user management)

SQLite has single-writer concurrency: only ONE write transaction at a time. With 1000 concurrent users, writes queue up and timeout. Even with WAL mode, only one writer allowed. Example: E-commerce site with 100 orders/sec needs 100 concurrent writers, but SQLite allows only 1 writer = 99 orders wait = timeout. PostgreSQL/MySQL allow multiple concurrent writers with MVCC or row-level locking. Check concurrent writers: If >10 concurrent writes/sec, use server database. SQLite is for: mobile apps (single user), desktop apps (single user), embedded systems (low concurrency).

Rule of thumb: Single user (mobile/desktop app) = SQLite. Multiple users (web server) = PostgreSQL/MySQL. High concurrency (>10 writes/sec) = server database.

Critical

Database locked errors, slow writes, timeout errors

Not enabling WAL mode in mobile app (using default rollback journal mode)

Enable WAL mode on app first launch: PRAGMA journal_mode=WAL (persists across restarts)

Default rollback journal mode: writers block readers, readers block writers. Example: User scrolls messages (reader) while background sync writes new messages (writer). In default mode: sync blocks UI = UI freeze. In WAL mode: sync doesn't block UI = smooth scrolling. WhatsApp, Telegram, Signal all use WAL mode. Enable once: PRAGMA journal_mode=WAL (setting persists in database file). Verify: PRAGMA journal_mode; should return "wal". Trade-off: Slightly more disk space (WAL file), but massive UX improvement. Always use WAL mode for mobile/desktop apps.

Enable WAL mode on app first launch or database creation. Add to initialization code: db.execSQL("PRAGMA journal_mode=WAL"). Verify in logs: "journal_mode=wal".

High

UI freezes during background sync, janky scrolling, poor user experience

Individual INSERTs without transactions (inserting 1000 products one by one)

Batch INSERTs in transaction: BEGIN; INSERT x1000; COMMIT; (100x faster)

Each INSERT without transaction triggers fsync (flush to disk) = 10ms. 1000 INSERTs = 1000 fsyncs = 10 seconds wasted. With transaction: 1000 INSERTs + 1 fsync = 1 second. Example: App initial setup loads 10K products from server. Without transaction: 110 seconds (user waits, bad UX). With transaction: 5 seconds (acceptable). Batch size: 100-1000 operations per transaction. Too small (10) = not enough speedup. Too large (100K) = long transaction, large rollback on error. Monitor: If bulk insert takes >1 second per 1000 rows, you're not using transactions.

Always wrap bulk operations in transactions. Pattern: BEGIN; for (item in items) { INSERT; } COMMIT;. For very large datasets, batch in chunks: for (chunk in chunks) { BEGIN; INSERT chunk; COMMIT; }.

High

Slow bulk inserts (11 seconds instead of 1 second for 1000 rows), poor app startup performance

Storing large images/videos in SQLite database as BLOBs (10MB photos, 100MB videos)

Store files in filesystem, store file path in database (path TEXT, size INTEGER, created_at INTEGER)

SQLite loads entire row into memory. Large BLOB = large memory usage. Example: Query 100 photos (10MB each) = 1GB memory = OOM crash on mobile. Also: Large database file = slow backup (copy 10GB file), slow VACUUM (rewrite 10GB file), slow queries (scan large pages). Best practice: Store files separately (app documents directory), store metadata in database (file_path TEXT, file_size INTEGER, thumbnail_path TEXT). Query returns paths, app loads files on demand. Example: Instagram stores photos in filesystem, metadata (likes, comments, timestamp) in SQLite. Dropbox stores files in filesystem, sync metadata in SQLite.

Never store files >1MB in database. Store file path instead. Pattern: Save file to disk, INSERT path into database. Query returns path, load file from disk. Thumbnail: Store small thumbnail (<100KB) in database for fast preview, full image in filesystem.

Medium

Huge database file (10GB+), slow queries, slow backups, out of memory errors

Not enabling foreign key constraints (PRAGMA foreign_keys=OFF, the default)

Enable foreign keys on every database connection: PRAGMA foreign_keys=ON

SQLite disables foreign key constraints by default for backward compatibility. Without FK constraints: Can DELETE customer while orders exist = orphaned orders. Can INSERT order with invalid customer_id = broken reference. Example: User deletes account, but orders remain = data inconsistency. With FK constraints: DELETE customer fails if orders exist (or CASCADE deletes orders). INSERT order fails if customer doesn't exist. Enable on every connection: PRAGMA foreign_keys=ON (doesn't persist, must run on each connection). Verify: PRAGMA foreign_keys; should return 1. Add to connection initialization code.

Always enable foreign keys: PRAGMA foreign_keys=ON. Add to database helper initialization. Verify in tests: Try to INSERT invalid FK, should fail. Try to DELETE parent with children, should fail (or CASCADE).

Medium

Orphaned rows (orders without customers), data integrity issues, hard-to-debug bugs