When SQLite Is the Right Database — and When to Move to PostgreSQL / MySQL / DuckDB / etc.

Learning outcomes

“Can SQLite hold this many rows?” is rarely the right architecture question. The more important questions are where the data lives relative to the application, how many writers need to make progress simultaneously, who must authenticate/authorize clients, what availability/replication model is required, and whether the dominant workload is transactional point access or analytical scanning. This final architecture lesson turns those requirements into migration triggers.

SQLite solves a different problem from a database server

SQLite runs in the application process and reads/writes its local database file. PostgreSQL/MySQL-class systems run a server that owns database files and accepts client connections. SQLite’s official guidance emphasizes local storage simplicity and reliability; client/server systems emphasize centralized coordination, authorization, and higher write concurrency. Neither architecture is universally superior.

Strong-fit SQLite cases

SQLite is particularly compelling when keeping the engine with the application removes operational machinery rather than creating a concurrency bottleneck.

Warning case 1: many concurrent writers

SQLite can queue writers and often handles more write activity than teams expect when transactions are short. The migration signal is not “two writers exist”; it is that the required concurrent write throughput/latency cannot be met with short transactions, sensible batching, busy handling, and a topology that keeps the file local.

Record over representative peak windows:  writes/sec and commits/sec  p50/p95/p99 write transaction duration  SQLITE_BUSY rate and total retry wait  writer queue/backlog age  WAL checkpoint behavior  CPU + storage latency  transaction batch size  percent of time writer slot is occupiedMigration trigger example:  "p99 durable write latency > 250 ms for 15 min while writer   occupancy > 85% after documented query/batch fixes"# Replace thresholds with your product SLOs; these are an example shape.

Warning case 2: direct network clients and server authorization

If many machines need to query the same authoritative data directly, a client/server database normally places the engine next to the data and lets clients send compact SQL/protocol messages. If the product also needs central roles, credential rotation, connection admission, auditing, row/tenant authorization, or DB-level network policies, a server system may be the natural security boundary rather than trying to recreate it around a shared SQLite file.

Warning case 3: HA, replicas, failover, and multi-service ownership

SQLite does not include a built-in distributed consensus/replication service or automatic multi-node failover system in core. Products can build replication around SQLite, and specialized SQLite-based systems exist, but that becomes an additional architecture with its own consistency/failure semantics. If the core product requirement is a centrally operated, multi-node transactional database with standard replication/failover tooling, evaluate a client/server system directly.

SQLite versus DuckDB: embedded does not mean same workload

DuckDB also emphasizes in-process simplicity, but its official design targets analytical/OLAP workloads with vectorized execution and column-oriented analytical behavior. SQLite is primarily a transactional/general-purpose embedded relational database with row-oriented B-tree storage. A product may even use both: SQLite for application state and DuckDB for local analytical scans over large columnar files/data extracts.

Migration is a system project, not a file conversion

Moving from SQLite to PostgreSQL/MySQL/etc. changes transaction/concurrency behavior, type semantics, SQL dialect details, connection failures, authentication, migrations, deployment, backups, monitoring, and operational ownership. Do not migrate solely because a database file “feels large.” Build a requirements-driven plan and run representative workload tests on the target.

Trigger category             Evidence / threshold           Status---------------------------  -----------------------------  ------Concurrent writer SLO        _____________________________  _____Remote direct client need    _____________________________  _____DB-server authorization      _____________________________  _____Multi-node HA/RTO/RPO        _____________________________  _____Central multi-service writes _____________________________  _____Dataset/storage management   _____________________________  _____Analytical scan workload     _____________________________  _____Operational staffing/tools   _____________________________  _____Regulatory/audit controls    _____________________________  _____Decision: keep SQLite / complement it / migrateOwner: _____________________  Review date: _______________

Five architecture review scenarios

Production architecture checklist

[ ] SQLite engine and DB file are on the same appropriate host/filesystem.[ ] Required peak write concurrency fits one-writer-at-a-time behavior.[ ] Busy/retry metrics show bounded, understood contention.[ ] Application, not SQLite roles, owns end-user authorization.[ ] OS file/directory/backup permissions match least privilege.[ ] Encryption need (if any) has an explicit product/key-management design.[ ] trusted_schema/defensive/extension policies match the threat model.[ ] Backups and restore drills satisfy RPO/RTO.[ ] HA/replication requirements are either absent or explicitly engineered.[ ] Runtime SQLite version/build is patched and capability-tested.[ ] Dataset growth fits operational storage/backup/maintenance, not a folklore threshold.[ ] Analytical workloads are evaluated separately from OLTP state.[ ] Migration triggers are measurable and owned, not emotional guesses.

Chapter synthesis

Bridge to the production capstone

Chapter 20 now asks you to apply the whole course. You will define FieldNotes requirements and workload, defend SQLite fit, implement schema and migrations, build safe application access and concurrency handling, test/benchmark/backup/secure the database, and finish with an operational architecture that can explain both what SQLite guarantees and what the surrounding system must guarantee.

Authoritative references

• SQLite appropriate uses
• SQLite over a network
• SQLite security guidance
• DuckDB: why DuckDB / OLAP design
• PostgreSQL architectural fundamentals