PostgreSQL vs SQLite: Picking the Right Database for Your Project

Choosing between PostgreSQL and SQLite is one of the most common early-stage technical decisions developers face. Both are relational databases, both speak SQL, both are free and open source, and both are exceptionally well-engineered pieces of software. The confusion is understandable. But they are designed for entirely different contexts, and using SQLite when you need PostgreSQL — or adding PostgreSQL when SQLite would do — creates unnecessary pain. Understanding the distinction clearly makes the decision straightforward. **What Is SQLite?** SQLite is an embedded relational database. Unlike most databases, it does not run as a separate server process — it is a C library that your application links against, and your entire database lives in a single file on disk. There is no installation, no configuration, no network connection, no authentication setup. You give SQLite a file path and start reading and writing. SQLite is the most widely deployed database in the world by a significant margin. Every Android device, every iPhone, every macOS installation, every Firefox and Chrome browser installation has SQLite embedded in it. It powers billions of applications because it is small (the compiled binary is around 600KB), reliable, and requires nothing from the environment except a filesystem. SQLite is fully ACID-compliant. Transactions work correctly. Data does not get corrupted when your application crashes mid-write. The SQL dialect is mostly standard with some quirks (type affinity is permissive, foreign key constraints must be enabled explicitly). For read-heavy workloads, SQLite performs impressively — it can serve tens of thousands of reads per second from a local file. The fundamental limitation of SQLite is concurrency. Only one writer can access the database at a time. Concurrent writes are serialised. In WAL (Write-Ahead Logging) mode, concurrent reads work alongside writes, but the write bottleneck remains. This is by design: SQLite is built for embedded use, not for serving many simultaneous users. **What Is PostgreSQL?** PostgreSQL is a full-featured, client-server relational database. It runs as a background service, listens on a network port, manages connection pools, and handles hundreds or thousands of simultaneous clients with full ACID transactions and multi-version concurrency control (MVCC). PostgreSQL has been developed continuously since 1986 and is widely considered the most advanced open-source relational database available. Beyond standard SQL, PostgreSQL offers: JSONB for semi-structured document storage with indexing, full-text search, geospatial queries via the PostGIS extension, row-level security for multi-tenant applications, logical replication, table partitioning, and a rich extension ecosystem. It powers some of the most data-intensive applications in the world, including large portions of Instagram, Reddit, and Spotify's infrastructure. PostgreSQL requires a server to run. You install it, configure authentication, create databases and users, and manage the server process. In production, this typically means a managed database service (Amazon RDS, Neon, Supabase, Render Postgres) rather than a self-hosted installation. **Key Differences** | Dimension | PostgreSQL | SQLite | |---|---|---| | Architecture | Client-server | Embedded (library) | | Storage | Multiple files, server-managed | Single file | | Setup | Requires installation and configuration | Zero configuration | | Concurrent writes | Full MVCC, hundreds of writers | Single writer at a time | | Concurrent reads | Unlimited concurrent readers | Excellent in WAL mode | | Network access | Yes — accessible from multiple clients | No — local only by default | | Replication | Full streaming and logical replication | Via Litestream (third party) | | Extensions | Rich ecosystem (PostGIS, pgvector, etc.) | Limited | | Hosting cost | Managed services from ~$5-25/month | Typically free (just a file) | | Ideal for | Production web apps, APIs, data systems | Mobile, embedded, local tools, dev | **When to Choose SQLite** SQLite is the correct choice for: mobile applications (it is the default on iOS and Android), desktop applications, command-line tools that need to persist data, browser extensions, embedded devices and IoT applications, local development databases, and applications where the database will only ever be accessed by a single process at a time. An interesting emerging use case: SQLite with Litestream (which streams the database file to S3 as a continuous backup) is used by teams building small-scale web applications where the simplicity of a single-file database outweighs the concurrency limitations. DHH and Basecamp have publicly advocated for this pattern. For any web application that will serve more than one concurrent user, SQLite's single-writer limitation becomes a constraint. The application server typically handles this by queuing writes, but this adds complexity that a proper client-server database eliminates. **When to Choose PostgreSQL** PostgreSQL is the right choice for any production web application, API, SaaS product, or data system that serves multiple users or needs to be accessed by multiple services. If you are building anything that will run in the cloud, be accessed by more than one process, or need to scale beyond a single machine, PostgreSQL is the foundation. PostgreSQL is also the right choice when you need features that SQLite does not support: row-level security for multi-tenant data isolation, JSONB with indexing, full-text search, geospatial queries, streaming replication, or connection pooling via PgBouncer. **The Verdict** If you are building a web application or API that will be accessed by multiple users, use PostgreSQL. The managed hosting cost is low, the setup with services like Neon or Supabase is almost as simple as using a file, and you will not outgrow it for years. If you are building a mobile app, desktop tool, local utility, embedded system, or anything that does not require network access or concurrent writes, use SQLite. Its simplicity and zero-configuration nature are genuine advantages that PostgreSQL cannot match for these use cases. Both databases are excellent. The decision is about fit, not quality.