In short
Use SQLite when one application server talks to its own local database: small and medium websites, internal tools, desktop and mobile apps, or local-first software. Use Postgres when several servers need the same data, when many users write at once, or when you need its richer types, roles and extensions. SQLite allows only one writer at a time, though writes usually take only milliseconds.
The basic difference
PostgreSQL is a database server. It runs as its own process, your application connects to it over a network, and it manages many connections at once.
SQLite is a library. There is no server. Your application reads and writes a single file directly through function calls. As its own guidance puts it, it “requires no administration”.
This one difference explains nearly everything else.
| SQLite | Postgres | |
|---|---|---|
| Runs as | A library inside your app | A separate server |
| Setup | None; it's one file | Install or use a managed service |
| Writers | One at a time | Many at once |
| Readers | Unlimited | Many, limited by connections |
| Multiple app servers | Not directly | Yes |
| User roles and permissions | None; uses file access | Rich, including Row Level Security |
| Extensions | Some | Extensive, such as PostGIS and pgvector |
| Backup | Copy safely, or use streaming tools | pg_dump, point-in-time recovery |
What SQLite handles better than people expect
The SQLite project says that sites under 100,000 hits a day “should work fine”, and that's a conservative figure. Because there's no network, a query is just a function call, and reads can be extremely quick. Many database calls per page aren't a problem as they would be over a network.
It's also a great fit for:
- Internal tools and small business apps on a single server
- Desktop and mobile apps, where it's the standard local storage
- Application file formats, instead of a custom format
- Tests and development, as it needs no setup, although test against your real production database too
- Read-heavy content sites, which write rarely and read often
And you avoid an entire category of operational work: connection pools, version upgrades and server management.
The writer limit, in practice
SQLite “will only allow one writer at any instant in time”. That sounds alarming. But write transactions usually take milliseconds, so several writers can simply take turns. Using WAL mode, readers don't block writers, or the reverse.
It becomes a real problem when:
- Long-running write transactions block everyone else
- You've got sustained, heavy, concurrent writes
- Lots of processes all need to write simultaneously, such as many background workers
The project's advice is that, if many writers must write at the same instant with no queuing, you should use a client/server engine.
When you need Postgres
More than one application server needs the data. This is the most common reason. SQLite's guidance warns against many computers accessing one database directly over a network, because of filesystem latency and locking bugs. If you're horizontally scaling, or running on a platform with several instances, you'll need a server.
Your host has no persistent disk. Serverless functions and many container platforms have ephemeral filesystems. An SQLite file there can vanish on redeploy, unless you use a hosted variant. See what serverless is.
You need its features. Row Level Security, rich JSON support, full-text search, geospatial data, vector search, stored procedures and strict typing by default.
You have many writers, such as a busy SaaS app with background jobs.
You need several roles, such as giving an analyst read-only access to live data.
You're using a backend platform such as Supabase or Neon, which are built on Postgres.
Modern tools blur the line
A newer set of tools is extending SQLite's reach:
- Litestream replicates a database continuously to object storage, which addresses a major backup concern.
- Hosted SQLite and libSQL services provide replicas and network access.
- Cloudflare D1 offers SQLite-based databases at the edge.
They're worth a look, although each has its own limits and trade-offs. Once you add replication and multiple regions, you've taken on some of the complexity you were trying to avoid, so compare again with a managed Postgres service.
Migration
Moving from SQLite to Postgres is manageable, but not automatic. SQLite is flexible on types by default, whereas Postgres is strict, so bad data can surface. SQL dialects differ in places such as dates, auto-increment and upserts. An ORM reduces this work, but doesn't remove it. Use tests, and follow the approach in how to write a safe database migration.
If you think that you'll definitely need Postgres soon, starting with it is often simpler. Managed services have small free tiers, so the operational gap is narrower than it once was.
How to choose
- One server, modest writes, want simplicity: SQLite.
- Desktop, mobile or local-first: SQLite.
- SaaS with multiple users writing constantly: Postgres.
- Serverless or several instances: Postgres, or a hosted SQLite service.
- You need extensions or Row Level Security: Postgres.
- You aren't sure: if it's a commercial multi-user product, then Postgres is the safer default. For a small tool on one server, SQLite is hard to beat.
For the wider picture, see SQL vs NoSQL for a new product and Postgres or MySQL. Remember to protect your data either way: test your backups.
If you'd like a review of your database choice, book a call.
Common questions
Is SQLite good enough for production?
Yes, for many workloads. It suits a single server with modest writes, and its project says sites under 100,000 hits a day should work fine. It isn't suited to several application servers sharing a database, or to heavy simultaneous writing.
How many users can SQLite handle?
There's no simple user limit, because it depends on how much they write. Reads scale very well, and short writes take turns. A read-heavy site can serve a lot of traffic, while a write-heavy app reaches its limit much sooner.
Can I use SQLite on Vercel or serverless?
Not with a local file in the usual way, since serverless filesystems are ephemeral. Use a hosted SQLite-compatible service, or a managed Postgres service. Check how your platform handles storage before building on it.
Should I start with SQLite and move to Postgres later?
It can work, especially for small tools, but migration isn't automatic, as types and SQL differ. If you already expect multi-server growth or many writers, then starting on Postgres, using a free managed tier, may save you work.