Skip to content
OriginalGuideSoftware

SQLite vs. PostgreSQL: when a single file is the right database

SQLite is a library that lives inside your app; PostgreSQL is a server that lives on the network. The right choice follows from who writes, how much, and where the data lives.

An aerial photograph of terraced farmland: a small walled kitchen garden with neat vegetable beds sits in the lower left, beside a vast system of terraces stretching to the horizon
One small plot, tended by one person, beside a whole system built to serve many.
On this page

In brief

  • SQLite is an in-process library: the whole database is one file your app opens directly. PostgreSQL is a separate server process that clients reach over the network.
  • SQLite allows one writer at a time; write-ahead logging lets readers run alongside it. PostgreSQL's MVCC lets many readers and writers work at once without blocking each other.
  • SQLite's own guidance: if the data is separated from the app by a network, or many writers must write at once, choose a client/server database.
  • PostgreSQL brings what a shared service needs: per-user permissions, replication to standby servers, and extensions. SQLite brings none of that, and needs none of it.
  • Backing up SQLite is copying a file. Backing up PostgreSQL is operating a server. Pick the one whose failure modes you are willing to own.

The question arrives early, before much code exists: which database, SQLite or PostgreSQL? The answers tend to be benchmarks and tribal loyalty, and neither helps much, because the two are not really competing products. They solve different problems, and SQLite's own documentation says so in plain language: SQLite does not compete with client/server databases. It competes with fopen().

That line is the whole comparison.

SQLite is a library, not a service. Your application links it in, opens a file, and reads and writes directly to disk. No server process to install, no port to open, no daemon to keep alive. The entire database, tables, indexes, triggers and views, lives in one cross-platform file. The library itself is under 900 kilobytes with every feature enabled. The code is in the public domain, the project intends to support it through 2050, and by its own account it is the most widely deployed database in the world, inside phones, browsers, set-top boxes, cars and airplanes.

PostgreSQL is the opposite shape: a server, a long-running process that owns the data, takes connections over the network, and coordinates everyone who wants to read or write. It descends from the Berkeley POSTGRES project, it is open source, and it supports a large part of the SQL standard, with foreign keys, triggers and updatable views. Where SQLite is a file your app carries, PostgreSQL is a service your app talks to.

One writer, or many

The difference that decides most real choices is concurrency: how many writers can work at once.

SQLite allows one writer at a time per database file. A write transaction usually takes milliseconds, so several writers can simply take turns, and SQLite's documentation notes it handles more write concurrency than many people suspect. Write-ahead logging, enabled with one setting, improves things: readers no longer block the writer and the writer no longer blocks readers. But the ceiling stays. One writer. If many threads or processes need to write at the same instant and cannot queue up and take turns, SQLite's own guidance says to choose a client/server database.

PostgreSQL was built for the other side of that line. It keeps several versions of each row around, a technique called multiversion concurrency control, so each query sees a consistent snapshot of the data as it was when the query started. Reading never blocks writing and writing never blocks reading. That is the machinery a shared service needs: dozens of clients writing at once, none waiting on a single file lock.

So the first question is not "which is faster" but "how many writers, at the same instant?" A personal app, a small team tool, a content site with mostly reads: one writer taking turns is plenty. A service where users write constantly, from many places at once: that is what the server is for.

Where the data lives

SQLite's second decision rule is about distance. A database engine sits next to the disk, chews through raw data, and hands the application a small answer. SQLite is built into the application, so if the data lives on a different machine from the application, the high-bandwidth engine-to-disk link has to cross the network. It works, but it is the wrong shape. If the data is separated from the application by a network, choose a client/server database. That is the project's own rule.

There is a subtlety worth keeping. "Application" here means the code that issues SQL statements. If that code is an application server, and the data sits on the same physical machine as the application server, SQLite can still be the right call even though the end user is another network hop away. This is the pattern behind a surprising number of small web services: one server, one app, one file, no database server to operate. SQLite's documentation says most websites work fine on it, offering a conservative estimate of fewer than 100,000 hits a day, and noting it has been demonstrated with ten times that. Its own website runs on SQLite.

What the server buys you

PostgreSQL adds specific machinery that SQLite deliberately does without.

The first is access control. PostgreSQL is a shared service, so it has users, roles, and per-object permissions: one account reads a table, another writes it, a third cannot see it at all. SQLite has no users. If your application can open the file, it can do anything to it. That is fine when the application is the only reader, and a real gap when several services or teams share one database.

The second is replication. PostgreSQL can keep standby servers that track the primary, ready to take over if it fails or to serve read-only queries. A standby can be warm, unavailable until promoted, or hot, accepting read queries. Transactions can be synchronous, confirmed on every server before they count as committed, or asynchronous, faster with a small risk of loss on failover. SQLite has nothing equivalent: its answer to a dead disk is the backup you made, a copy of the file.

The third is extensibility. PostgreSQL lets you add data types, functions, operators, index methods, and whole procedural languages, and entire ecosystems live on that hook: PostGIS for geography, pgvector for embeddings, TimescaleDB for time series.

None of this is free. A server needs provisioning, patching, monitoring, backups you have actually tested restoring, and someone who notices when the disk fills up at 3 a.m. SQLite's operations bill is close to zero: no process to watch, no accounts to manage, no replication lag to monitor. Its backup is a file copy, and its failure mode is a file you can move, inspect, and restore with ordinary tools. That simplicity is not a beginner feature. It is the product. The same instinct runs through other self-hosted tools: Stirling PDF does its work on your own machine for the same reason.

The honest trade

SQLite's third decision rule is the bluntest: if your data will grow to a size you are uncomfortable fitting into a single disk file, choose something else. SQLite supports databases up to 281 terabytes in theory, assuming a filesystem that supports files that large. In practice, once content creeps into the terabyte range, a centralized server is the better home. Most projects never get there; the rule exists so the ones that do are not surprised.

The comparison is not a contest with a winner. It is a question about the shape of your project. Is the data yours alone, on one machine, written by one process at a time? Then the library is almost always the better answer: faster to start, simpler to run, easier to back up, and honest about what it is. Is the data shared, written by many clients at once, with different people needing different levels of access? Then you want the server, and operating it is the price of sharing.

Both are open source and free. The costly error is subtler: running SQLite over a network filesystem, where its write-ahead log cannot work, or running PostgreSQL for a single-user desktop app and paying the operations cost for machinery nobody uses. Pick the shape that matches the work, and the choice mostly makes itself.

Related reporting

More from grep.tips

All articles