For a small web app, the right database is usually the one that matches where your app runs and how it writes data—not the one with the longest feature list. SQLite is often a sensible choice when the application and database live together and writes are brief. PostgreSQL is usually a better fit when several app instances need shared data or the workload needs more simultaneous writes.
Neither choice is a temporary toy by default. SQLite can serve real websites, and PostgreSQL can be a reasonable starting point for a small project. The trade-off is between a simple, embedded database and a separate database server designed for shared access.
Quick comparison
| Question | SQLite | PostgreSQL |
|---|---|---|
| How does the app connect? | The database engine runs inside the application and reads a local database file. | The application connects to a separate database server. |
| Where should the data live? | On durable storage accessible to the application on the same host. | On a database host or managed service reachable by one or more app instances. |
| What does concurrent writing look like? | One write transaction at a time per database file; short writes can take turns. | Built for concurrent sessions, with multiversion concurrency control and explicit locking features. |
| What do you operate? | The application, its database file, storage, and a tested backup process. | The database service, connections, access, upgrades, storage, and backups—or the managed service that handles some of them. |
| Best starting point | A single-instance app with modest write contention and local storage. | Multiple app instances, frequent concurrent writes, or a need for a shared database service. |
These are architectural differences, not a ranking. SQLite’s own when-to-use guidance recommends thinking about whether the database is separated from the application by a network and whether many writers must work at once.
How the two databases are deployed
SQLite is an embedded database: the application uses a library that reads and writes a database file. You do not need to deploy a separate database server or manage a database service account. That can make development, local tools, prototypes, and a single-host web app straightforward.
The important constraint is that the database file belongs on storage that works correctly for local file access. If you run multiple copies of the web app on different machines, putting the same SQLite file on a network share is not a safe substitute for a database server. SQLite’s write-ahead log documentation specifically says that WAL mode requires processes to share a host and does not work over a network filesystem.
PostgreSQL uses a client/server model. A server process owns the database and accepts connections from application clients. That makes a central PostgreSQL service a natural fit when multiple app instances need to read and write the same data. The server can run on your own host or through a managed database provider.
A managed PostgreSQL service can reduce the work of installing and maintaining the server, but it does not remove every operational decision. You still need to understand connection limits, credentials, network access, storage, backups, and the provider’s recovery options. PostgreSQL’s connection settings documentation explains that concurrent connections are limited and that increasing the limit uses additional server resources.
Compare write patterns, not just visitor counts
SQLite supports multiple simultaneous readers but only one simultaneous write transaction per database file. For many apps, writes are short and can take turns. The issue appears when writes overlap often, remain open for a long time, or cannot wait: an application may encounter a busy response and need to retry or queue work. The SQLite transaction guide describes its read and write transaction behavior.
Write-ahead logging can let SQLite readers and a writer proceed at the same time. It does not create multiple simultaneous writers. WAL also has deployment constraints, including keeping all database processes on the same host.
PostgreSQL uses multiversion concurrency control (MVCC) to support concurrent access. Its concurrency documentation explains how statements see consistent snapshots while other transactions make changes. This makes PostgreSQL a stronger fit for apps with overlapping writes, jobs that update shared records, or multiple application instances.
That does not mean PostgreSQL makes every write conflict disappear. Transactions can still contend for the same rows or resources, and the server has finite connections and capacity. The practical question is whether your app’s actual write patterns fit SQLite’s single-writer model—not how many accounts or page views the app has in theory.
Do not treat a requests-per-day threshold as a database selection rule. A mostly read-only site and an app that writes a row on every request can have very different database loads at the same traffic level. Start from the operations your app performs, then observe write waits, errors, latency, and resource use as real usage grows.
Consider the work you want to own
SQLite keeps the operational surface small: there is no separate database service to provision, secure, patch, or monitor. You are still responsible for durable storage and recovery. If your host uses ephemeral disks or replaces instances during deployment, verify that the database file survives those events and that the app can restore it.
PostgreSQL adds a service and connection path. If you self-host it, you own server maintenance, access rules, upgrades, backups, and recovery. If you use a managed provider, check which of those tasks it performs, what the backup retention and recovery windows are, and whether the plan fits your budget. A managed service can be worth paying for when it saves you from operating a database, but “managed” does not guarantee that a backup is restorable or that recovery meets your needs.
Both databases need a backup that you have actually restored in a separate environment. SQLite’s file-based design does not make copying a live database file automatically safe in every configuration, especially when WAL is active. PostgreSQL has several backup approaches with different recovery properties. See our guide to database backup and restore plans for small web apps before choosing a production setup.
Think about migration before you need one
Moving from SQLite to PostgreSQL is possible, but it is not just copying a file to a server. You may need to change connection configuration, provisioning, data types, SQL statements, schema migrations, and the way the application handles transactions. Different database features and defaults can expose assumptions that were invisible while the app used one database.
Before a migration, inventory the schema and queries, test the conversion with a copy of production-shaped data, and verify important reads, writes, constraints, and background jobs. Plan how you will stop or reconcile writes during the final data transfer, how you will switch the application, and how you will recover if validation fails. Keep a restorable backup and do not assume that changing an ORM’s connection string completes the migration.
If you already know the app will run on several instances or needs frequent concurrent writes, starting with PostgreSQL can avoid an early database move. If those needs are uncertain and your first deployment is a single app instance with local persistent storage, SQLite can keep the first version simpler. Choose deliberately rather than treating either option as a universal default.
A practical decision checklist
Choose SQLite when most of these are true:
- The application and database can live on the same host.
- One app instance can safely access the database file.
- Writes are short and can take turns.
- You value a small operational footprint and have a reliable persistent-storage and backup plan.
Choose PostgreSQL when one or more of these are true:
- Multiple app instances need to share a database over the network.
- The app has frequent overlapping writes or background jobs that update shared data.
- You want a dedicated database service with server-side access and concurrency controls.
- Your expected deployment, data model, or growth path already depends on PostgreSQL features.
If you are unsure, prototype the application’s busiest write path and confirm that the deployment architecture supports the database you chose. A measured workload and a tested restore plan are more useful than switching databases based on a generic traffic number.
