PostgreSQL vs SQLite — When to Use a Tank vs a Swiss Army Knife

SQLite is perfect for local apps, but PostgreSQL dominates when you need real databases with concurrency, security, and scale.

The Framing: Server vs Library, Not Apples to Apples

These aren't direct competitors—they're different tools for wildly different jobs. PostgreSQL is a client-server database management system that runs as a separate process, handling multiple connections, transactions, and complex queries simultaneously. SQLite is a self-contained, serverless, zero-configuration SQL database engine that lives as a library inside your application. Comparing them is like comparing a commercial kitchen to a microwave: one's built for volume and complexity, the other for convenience and simplicity.

PostgreSQL is what you use when you need a real database: think web apps, enterprise systems, or anything with more than one user hitting it at once. SQLite is for embedding—mobile apps, local tools, or prototyping where you don't want the overhead of a server. Most debates here miss the point: it's not about which is 'better,' but which is less wrong for your specific mess.

Where PostgreSQL Wins: Concurrency, Features, and Not Crashing

PostgreSQL's killer feature is MVCC (Multi-Version Concurrency Control), which lets multiple users read and write without locking the whole database. SQLite uses a global write lock—one writer at a time, which means your app grinds to a halt under any real load. PostgreSQL also supports JSONB for semi-structured data, full-text search, geospatial extensions with PostGIS, and stored procedures in multiple languages like PL/pgSQL, Python, and JavaScript. It's ACID-compliant with WAL (Write-Ahead Logging) for crash recovery, and scales vertically without fuss.

For anything beyond a toy project, PostgreSQL is the only sane choice. SQLite's lack of real user management and network access means you're stuck with file permissions and local-only use—fine for a personal diary app, embarrassing for anything public.

Where SQLite Holds Its Own: Zero Fuss, Perfect for Embedding

SQLite's strength is its simplicity and portability. It's a single C library with no server to configure—just include it and go. This makes it ideal for mobile apps (iOS/Android use it heavily), local desktop applications, or prototyping where you don't want to set up a database server. It's ACID-compliant for single-user scenarios, supports most standard SQL, and stores everything in a single cross-platform file.

For low-traffic websites (think <100k hits/day), SQLite can work—but you'll regret it the first time two users try to write at once. Its small footprint (under 1MB) and public domain license make it unbeatable for embedded systems or environments where installing PostgreSQL is overkill.

The Gotcha: Switching Costs and Hidden Limits

Moving from SQLite to PostgreSQL isn't just a migration—it's a rewrite. SQLite's lax type system (e.g., storing strings in integer columns) and limited SQL dialect (no RIGHT OUTER JOIN until version 3.39.0) mean your queries might break. PostgreSQL requires proper user roles, schemas, and connection pooling, which adds operational complexity. Conversely, SQLite's lack of network access means you can't scale horizontally without hacks like read-only replicas.

The real surprise? SQLite's performance can beat PostgreSQL for simple, single-user reads—because there's no network overhead. But try any concurrent writes, and it falls apart. PostgreSQL's extensions like TimescaleDB for time-series or Citus for sharding show how it scales; SQLite just gets slower.

If You're Starting Today: Pick Based on Users, Not Hype

Ask one question: how many concurrent users will this have? If it's a mobile app or a local tool with one user, use SQLite—it's faster to set up and avoids server costs. For anything with a web interface or multiple users, use PostgreSQL from day one. Don't 'start with SQLite and migrate later'—that's a recipe for pain when you hit locking issues or need features like row-level security.

For a concrete example: building a personal finance tracker? SQLite is fine. Building a SaaS platform for budgeting? PostgreSQL is non-negotiable. The cost difference is minimal (PostgreSQL is free and open-source, with cloud hosting from $15/month on services like DigitalOcean), so don't cheap out on the wrong tool.

What Most Comparisons Get Wrong: It's Not About Size

Many guides frame this as 'small vs big' databases, but that's misleading. SQLite can handle multi-terabyte databases (theoretical limit is 281 TB), and PostgreSQL can run on a Raspberry Pi. The real divide is architecture: serverless vs client-server. SQLite's lack of a separate process means it's tightly coupled to your app—great for embedding, terrible for scaling. PostgreSQL's server model adds overhead but enables replication, backups, and monitoring tools that SQLite can't match.

Stop worrying about file size or raw speed. Focus on whether you need multiple writers, user permissions, or network access. If yes, PostgreSQL. If no, SQLite might work—but test with real concurrency before committing.

Quick Comparison

Factor	PostgreSQL	SQLite
Architecture	Client-server, separate process	Serverless, embedded library
Concurrency Model	MVCC, multiple readers/writers	Global write lock, one writer at a time
Network Access	Native over TCP/IP	Local file only, no network
Setup Complexity	Requires server install and config	Zero-config, single file
Typical Use Case	Web apps, multi-user systems	Mobile apps, local tools
Cost for Cloud Hosting	From $15/month (e.g., DigitalOcean)	Free (embedded, no server needed)
Advanced Features	JSONB, full-text search, PostGIS, extensions	Basic SQL, limited to core library
License	PostgreSQL License (BSD-like)	Public domain

The Verdict

Use PostgreSQL if: You're building a web app, SaaS, or anything with more than one concurrent user—PostgreSQL's concurrency and features are non-negotiable.

Use SQLite if: You're embedding a database in a mobile app, desktop tool, or prototype where simplicity and zero-config matter more than scale.

Consider: MySQL if you need a simpler alternative to PostgreSQL for basic web apps—it's faster for reads but less feature-rich, and Oracle's ownership is a risk.

The Bottom Line

PostgreSQL wins

PostgreSQL wins because it's a full-featured database server that handles multiple users and complex queries without breaking a sweat. SQLite is just a library—great for embedding, but laughably inadequate for anything beyond single-user apps.

Related Comparisons