SQLite vs PostgreSQL

SQLite and PostgreSQL are both SQL databases but designed for entirely different deployment models. SQLite is an embedded database; PostgreSQL is a client-server database.

Architecture Differences

SQLite runs as a library linked directly into your application process. The entire database is a single file on disk. There is no separate server process, no network communication, and no configuration. This makes it the most widely deployed database in the world, present in every smartphone, web browser, and most operating systems.

PostgreSQL runs as a separate server process accepting connections over TCP. It manages shared memory, background workers, and provides sophisticated multi-user access control.

Performance Characteristics

SQLite is remarkably fast for local reads because there is zero network overhead: queries execute in the same process as your application. For read-heavy workloads with a single writer, SQLite with WAL mode can handle millions of reads per second.

PostgreSQL handles concurrent writes from thousands of clients simultaneously, something SQLite cannot do due to its single-writer limitation. For multi-user system designs, PostgreSQL is the only choice.

Trade-offs

SQLite's simplicity is unmatched. Deploy a database by copying a single file. Back it up with a file copy. Test with an in-memory database. This makes it perfect for mobile applications, desktop software, and as an application file format.

The emerging trend of SQLite at the edge with tools like Litestream (streaming replication), LiteFS (distributed SQLite), and Turso (libSQL cloud) is blurring the boundaries. These tools bring SQLite closer to server use cases.

When SQLite Surprises You

SQLite can handle more than most developers expect. It supports databases up to 281 terabytes, handles hundreds of thousands of queries per second, and is ACID-compliant. For single-tenant applications and edge computing, it is increasingly a viable production database.

Real-World Usage

SQLite is embedded in Android, iOS, Chrome, Firefox, and macOS. Expensify uses SQLite as their server database. PostgreSQL powers Instagram, Reddit, and enterprise applications worldwide.

Explore database fundamentals and edge architectures. Check our interview preparation resources and pricing.