PostgreSQL vs. SQLite for Self-Hosted Apps: Which Should You Actually Use?

A lot of self-hosted apps let you pick between SQLite and PostgreSQL at install time, and the default advice is some version of "use SQLite to try it, switch to Postgres for real." That advice is wrong often enough to be worth unpacking.

SQLite is a genuinely serious database. It runs on aircraft, in browsers, and in phones by the billions. The question is not "which is better" but "which fits how this app is used."

The one difference that drives everything else

SQLite is a library that reads and writes a single file. PostgreSQL is a server that clients connect to over a socket.

Everything else follows from that:

• SQLite has no separate process to run, secure, or back up. The database is a file next to your app.
• SQLite allows many simultaneous readers but effectively one writer at a time. In WAL mode, writes are fast and do not block reads, but they still serialize.
• Postgres handles many concurrent writers, connections from multiple machines, and work that SQLite simply does not do: rich full-text search, a large extension ecosystem, materialized views, replication.

When SQLite is the right choice

Single server, single app process. If your app runs as one container on one machine, SQLite's "one writer" limit is rarely the bottleneck. A modern disk handles thousands of small writes per second.

Read-heavy workloads. A wiki, a bookmark manager, a documentation site, a personal dashboard, a status page. These are 95%+ reads. SQLite is often faster here than Postgres because there is no network round-trip and no connection overhead.

Small teams. A self-hosted issue tracker or notes app for 5, 20, even 50 people who are not all hammering "save" at the same millisecond will be completely fine on SQLite.

When you value operational simplicity. Backup is "copy the file" (using .backup, not cp). There is no connection pool to tune, no pg_hba.conf, no separate service that can go down on its own.

Concrete example: a self-hosted read-it-later app for a household of four. SQLite. Anything else is wasted effort.

When PostgreSQL earns its keep

Multiple app servers. The moment you run two copies of your app for redundancy or scale, they need a shared database over the network. SQLite cannot do that safely. This is the single most common real reason to choose Postgres.

High write concurrency. A chat app, an analytics ingester, a job queue processing hundreds of writes per second from many workers. The single-writer model becomes a real ceiling.

You need Postgres features. Proper full-text search with ranking, PostGIS for geospatial queries, pg_trgm for fuzzy matching, JSON indexing, logical replication to a read replica or a data warehouse.

Large datasets with complex queries. Tens of millions of rows with multi-table joins and analytical queries benefit from Postgres's planner and index types.

Concrete example: a self-hosted CRM used by a 40-person sales team, with a reporting dashboard, a mobile app hitting the same data, and nightly syncs to an analytics tool. Postgres, clearly.

The "I might grow" question

People pick Postgres "just in case." Consider the actual cost of being wrong in each direction:

• Chose SQLite, outgrew it. Most app frameworks and ORMs abstract the database. Migrating SQLite → Postgres is usually an export/import plus a config change, done in an evening. Tools like pgloader automate most of it.
• Chose Postgres, did not need it. You now run, secure, tune, and back up a database server forever, for a workload a file would have handled.

Starting on SQLite and moving later is a smaller regret than the reverse.

Side by side

A rule of thumb

• One machine, one app process, mostly reads, under ~50 active users → SQLite, and enable WAL mode.
• More than one app server, or heavy concurrent writes, or you need a specific Postgres feature → PostgreSQL.
• Genuinely unsure → start with SQLite, keep your schema in migrations, and revisit when you have real numbers.

The self-hosting community has spent years treating SQLite as the training-wheels option. For a large share of the apps people actually run at home or in a small company, it is the correct permanent choice.