SQLite is probably enough for your application

I have a mild affection for SQLite that I think is under-shared.

It is a complete relational database in a single file. It needs no server, almost no configuration, and no network connection between your application and your data. For the workloads most small applications actually have, it is extremely fast.

Meanwhile, the standard advice for a new project is usually:

“Start with Postgres.”

That advice is not wrong. Postgres is an excellent database.

But it is often given as if choosing anything else is irresponsible.

Most projects take the default and never look at it again for years.

I think it is worth asking a simpler question:

What does this application actually need from its database?

For a surprising number of applications, the answer is SQLite.

What SQLite actually is

SQLite is not a toy database and it is not a “lesser” relational database.

It is a mature relational database engine that has been heavily optimized over decades and is embedded directly into the application. There is no database server sitting somewhere waiting for connections. Your application talks directly to the database engine, which reads and writes the database file.

That architecture removes an entire category of complexity.

No database server to provision.

No connection pool to configure.

No database host to monitor.

No network round trip for every query.

No database service to restart at 3am.

The database is a file.

And that turns out to be a remarkably useful property.

The number that actually matters

SQLite’s most important limitation is not database size.

It is concurrent writes.

SQLite allows many readers, but only one writer can modify a database at a time. Writers therefore take turns.

That sounds much worse than it usually is.

A web application does not normally have hundreds of requests simultaneously spending seconds writing to the database.

A typical request might:

begin a transaction,
insert or update a few rows,
commit,
return a response.

If the write transaction takes 5 ms, then one SQLite database can theoretically serialize around:

1 / 0.005 = 200 write transactions per second

before accounting for other work, disk latency, contention, application overhead, and the fact that real transactions are not identical.

At 10 ms, that becomes roughly:

100 writes/second

At 50 ms:

20 writes/second

This is not a benchmark claiming SQLite delivers those numbers. It is the more useful way to think about the constraint: how long does your write transaction actually hold the writer lock?

SQLite’s own documentation makes essentially this point: if each writer does its database work quickly, writers can simply queue and take turns. It recommends a client/server database when you actually have many concurrent writers that cannot queue.

That distinction matters enormously.

A SaaS application with 50,000 users does not necessarily have a high-write database.

If most of those users are reading dashboards, searching records and occasionally updating something, the number of users tells you very little about whether SQLite is appropriate.

Concurrency matters more than user count.

WAL changes the picture

SQLite’s default rollback journal mode is not necessarily what you want for a web application.

With WAL (Write-Ahead Logging) enabled, readers and a writer can operate concurrently. Readers see a consistent snapshot while the writer appends changes to the WAL. There is still only one writer at a time.

So the simplified model becomes:

Many readers + one writer = good fit

Many simultaneous writers = eventually a problem

And the distinction is important because WAL does not magically turn SQLite into a multi-writer database.

The SQLite documentation is explicit: there is still only one writer at a time because there is only one WAL file.

A benchmark that actually tells you something

There is an old but useful SQLite benchmark published by the SQLite project comparing different databases.

One test performs 25,000 INSERTs inside a single transaction. In that test, SQLite completed the workload in 0.914 seconds, compared with 4.900 seconds for PostgreSQL and 2.184 seconds for MySQL.

That does not mean SQLite is “5× faster than Postgres.”

The benchmark is deliberately measuring a particular workload: a large number of inserts batched into one transaction.

Change the workload and you change the result.

And that is precisely the point.

Database benchmarks are not rankings. They tell you how a particular architecture behaves under a particular workload.

For your application, the useful benchmark is therefore not:

“Is SQLite faster than Postgres?”

It is:

“How many database operations does my application perform, how long do my transactions take, and how much concurrent writing do I actually have?”

How far it goes

A single SQLite database can handle considerably more than many developers assume.

SQLite’s documented maximum database size is 281 TB (2⁴⁸ bytes), although the SQLite project recommends considering a client/server database when the database is heading toward the terabyte range.

So “SQLite can’t handle large databases” is not really the right argument.

The more important questions are:

How many concurrent writers do you have?
Does the database need to live on another machine?
Do you need replication?
Do you need multiple application servers?
How complicated is your operational environment?
Can the entire database comfortably live on one machine?

A small SaaS with 500 users and a few writes per second may be a much better SQLite candidate than an internal system with 20 users generating thousands of concurrent writes.

SQLite is particularly attractive when:

Most operations are reads.
Writes are short and relatively infrequent.
Users perform the writes rather than background workers hammering the database.
The application runs on one machine or a small number of processes on the same machine.
The database comfortably fits on local storage.
You value operational simplicity.

That last point is underrated.

My affinity for SQLite is not nostalgia.

It is that it removes an entire category of operational work.

Where it stops being enough

Be clear about the limits, because this is where a recommendation becomes dishonest.

Concurrent writes

This is the big one.

There is only one writer at a time per SQLite database. If many requests need to write simultaneously, they queue.

If transactions are short, that may be completely fine.

If they are long or the application generates sustained write contention, you will eventually encounter locking and SQLITE_BUSY behavior.

WAL and appropriate busy timeouts can improve the experience, but they do not remove the underlying one-writer architecture.

Multiple machines

This is where SQLite becomes much less attractive.

WAL requires all processes accessing the database to be on the same host because it relies on shared memory. SQLite’s documentation specifically recommends a client/server database when the application and database are separated across machines.

Putting a SQLite file on a network filesystem and pointing several application servers at it is not the equivalent of running Postgres.

Don’t do that casually.

Horizontal scaling

Once you genuinely need multiple application servers sharing the same database, Postgres starts making considerably more sense.

You can still build architectures around SQLite with replication or a database proxy, but at that point you are rebuilding infrastructure that a client/server database already provides.

The simplicity advantage starts disappearing.

Replication and operational tooling

Postgres has an enormous ecosystem around it.

Replication, backups, monitoring, connection pooling, failover, managed hosting and years of operational knowledge are all readily available.

SQLite intentionally does much less.

That is part of why it is so simple, but it also means that some problems become your problems.

Very large or write-heavy systems

SQLite can technically store enormous databases, but technical possibility is not the same as architectural suitability.

If you are approaching terabyte-scale data, have sustained high write concurrency, or need several machines writing to the same database, you are already describing the problem that client/server databases were built to solve.

My actual decision rule

Start with SQLite when:

the writes are short, the write concurrency is low, the database lives close to the application, and you don’t need database infrastructure.

Move to Postgres when:

you need sustained concurrent writes, multiple application machines, replication, remote database infrastructure, or the operational ecosystem that comes with a client/server database.

The interesting thing is that neither decision has much to do with the number of users.

A single-user application can need Postgres.

A SaaS with thousands of users can potentially run perfectly well on SQLite.

The workload determines the answer.

And yes, migration is possible

SQLite to Postgres is not some irreversible architectural decision.

There are migration tools and libraries that can move schemas and data between them.

But I would still rather make the decision deliberately at the beginning.

Not because migration is impossible.

Because unnecessary migration is work you didn’t need to create in the first place.

If SQLite comfortably satisfies the application’s requirements, starting with Postgres because “that’s what serious applications use” is not architectural maturity.

It is cargo culting.

Which one am I using?

On client work, it depends on the constraints.

PostgreSQL for most SaaS backends, because multi-instance deployment, concurrent writes and operational requirements are common enough that the infrastructure is worth having.

SQLite for internal tools, single-instance products, desktop applications, small services and anything where the database workload is simple enough that running a database server would be unnecessary complexity.

The important part is not choosing SQLite.

It is choosing based on the workload instead of the default.

If you want to review a database choice you have already made, send me a message and tell me what the access patterns are. Sometimes the answer is “your database is fine and you have an index problem”, which is the answer I most want to give.