Chapter 24: From SQLite to PostgreSQL
1. When SQLite isn't enough
SQLite gives an application persistence with almost no operational overhead. PostgreSQL gives a deployed application a shared database service built for concurrent web traffic. This page shows the core difference between them and when a deployed application should move from one to the other.
SQLite is a strong database for local applications, scripts, prototypes, and plenty of modest deployed workloads. It is embedded: the database engine runs inside your application's process and reads from and writes to a file on disk. PostgreSQL is a client-server database. It runs as its own service, and your application connects to it over the network. That single distinction is the source of many of the practical differences between them.
Running a database as its own service creates a production operating model: application connections can be pooled, access can be controlled with database roles, and hosted PostgreSQL services can provide backups, point-in-time recovery, replication, and monitoring. Those capabilities become useful when an API must remain dependable through deploys, traffic spikes, and growing load.
Picture a small Python app running locally against SQLite. It runs as a single process, reads and writes to a single file on your laptop, and rarely competes with another writer. Now imagine that same app deployed to production, with three web workers and a background job all using the same stored data. The database is now a shared resource that separate operating system processes may need to update at nearly the same time.
The pressure usually appears as a write problem, not a read problem. Web workers are separate copies of your application handling different incoming requests. One worker may be saving a new record while another updates existing data and a background job writes its own result. SQLite supports concurrent access, but writes to one database are serialised. Under light load, the wait may be invisible. As write traffic grows, lock waits, timeouts, and database is locked errors become a sign that a server-based database may be the better operational fit.
PostgreSQL uses client-server architecture. It runs as a separate server that application workers connect to over the network. PostgreSQL is designed to coordinate concurrent transactions without making several processes compete to write through one local database file. For a deployed API with shared state, that is often the more practical model.
SQLite is still the right choice for plenty of cases. The trade-off is real and worth seeing side by side:
| Use SQLite when… | Use PostgreSQL when… |
|---|---|
| You have a single app or script on one machine. | You have multiple web workers, services, or machines. |
| You want the simplest possible setup: ship one file with your app. | You are happy to run a database server or use a managed Postgres service. |
| Your workload is mostly reads with modest writes. | You expect lots of concurrent writes or heavier queries. |
| You are prototyping, teaching, or building a small internal tool. | You are building a production web app or shared internal platform. |
| Only your app needs to touch the data. | Many tools and services should share the same database. |
Five signals to migrate
Migrate when you see any of these signals:
- Write concurrency errors. "Database is locked" appearing in production logs.
- Network access needed. The database must live on a different server from the application.
- Team collaboration friction. Multiple developers need direct database access.
- Advanced features required. JSONB, full-text search, PostGIS.
- Growing database size. Approaching 100 GB with performance degradation.
PostgreSQL is not a replacement for a bad decision. It is the database model that becomes useful when an application needs shared access, concurrent writes, and production-style operations. SQLite remains a good choice when its simpler embedded model matches the job.
Next, in section 2, you will see what a migration actually involves: the four parts of the work and why each one matters.