When SQLite fits

SQLite is a great default when one application owns the database and the data belongs on one machine.

That covers more ground than most people assume. Command-line tools, desktop and mobile apps, test suites, caches, data analysis scripts, and plenty of web applications all fit that description. A single web server handling thousands of requests per day is comfortably inside SQLite territory.

The write concurrency question

It handles many readers well. Writes are serialized, so applications with many concurrent writers need more care.

Serialized writes mean SQLite applies one write transaction at a time. Each write is fast — often well under a millisecond — so a queue of writers usually drains quickly. But if dozens of processes hammer the database with writes simultaneously, some of them wait, and past a point they start failing with database is locked errors.

A database server is usually a better fit when several machines must write directly to the same database. SQLite has no network protocol of its own, and putting the database file on a network filesystem is a known source of corruption. If your architecture is “three app servers, one database”, that database should be PostgreSQL or MySQL.

A quick decision test

Ask these questions about the project in front of you:

Does more than one machine need to write to this data?   -> server
Do many processes write heavily at the same time?        -> server
Is the data owned by one app on one machine?             -> SQLite
Is this a test suite, CLI tool, or local cache?          -> SQLite

Notice the questions are about writes and machines, not about data size. SQLite handles databases of many gigabytes without trouble.

Don’t buy scale you don’t need

Start from the workload you have. Do not add a database server only because the project might become large one day.

Choosing PostgreSQL “just in case” costs you real money and effort today: a server to run, credentials to manage, a connection pool to tune, one more thing to back up and monitor. SQLite costs you a file.

And migrating later is a well-understood move. Your SQL knowledge transfers, and because SQLite lives behind the same query patterns, swapping the engine is far cheaper than carrying an unneeded server for years.