SQLite vs PostgreSQL: When to Move to a Database Server

Follow the data boundary. Choose SQLite when one application instance owns a local database file, and choose PostgreSQL when multiple machines, services, administrators, or untrusted users need controlled network access.

SQLite is an embedded relational database, so the application reads and writes the database file through a library rather than sending queries to a separate SQL server. That makes deployment straightforward for desktop applications, mobile applications, command-line tools, device software, test suites, and modest web applications running on one host. There is no database daemon to configure, patch, monitor, authenticate against, or restart.

PostgreSQL is an open-source object-relational database management system with a client-server design. It adds user authentication, user management, network connections, server-side configuration, backup tooling, replication, extensions, and privileges that can separate one application's access from another. Those are operating costs, but they are also the reason PostgreSQL belongs in applications requiring shared or independently administered data.

Do not proxy SQLite casually. Putting its file on NFS or SMB does not turn the embedded engine into a safe database server, because correctness depends on filesystem locking behaving as SQLite expects. The project's own network filesystem warning↗ recommends a client-server database when data is separated from the application by a network.

The practical rule is blunt: if the process opening the file and the storage holding it are no longer part of one failure domain, use PostgreSQL. Database size alone does not decide this. SQLite documents a maximum database size measured in terabytes, while a small PostgreSQL database may still be justified by access control, replication, or deployment topology.

Count writers, not users. SQLite remains credible when writes are brief and serialized, but PostgreSQL is the better choice once several requests must modify data simultaneously without waiting behind one database writer.

SQLite supports many readers, yet its normal locking model permits only one writer at a time. Write-ahead logging improves reader and writer overlap because readers can continue while changes enter the WAL file, but it does not create multiple concurrent writers. The SQLite WAL documentation↗ states that only one writer can operate at once.

That distinction is easy to miss. A site can serve thousands of mostly read-only requests from SQLite and behave perfectly, while a much smaller job queue can produce database is locked errors because workers hold write transactions open during network calls or CPU work. Before migrating, shorten transactions, batch related write operations, set a sensible busy timeout, and move external API calls outside the transaction. These repairs often recover substantial headroom.

Move when serialization is the workload. Warning signs include sustained write queues, unpredictable lock waits, background workers competing with request handlers, or business operations that must update different rows concurrently. PostgreSQL uses multiversion concurrency control and row-level locking, allowing multiple users to change different rows while preserving data integrity; its concurrency control documentation↗ explains the transaction model and isolation behavior.

There is no honest universal writes-per-second threshold because storage latency, transaction duration, indexes, journal mode, and SQL shape alter the result. Measure p95 transaction wait time under production-like contention. If lock waiting consumes more of the latency budget than query execution, PostgreSQL solves the actual bottleneck rather than merely changing the badge on the database.

Choose features deliberately. PostgreSQL earns its administration cost when the application needs granular permissions, strict shared schemas, sophisticated indexing, replication, server-side observability, or operational failover before write contention appears.

SQLite and PostgreSQL both speak SQL, support transactions, provide ACID compliance, and handle ordinary relationships, joins, indexes, constraints, and full-text search. They are not interchangeable dialects, however. PostgreSQL isn't as forgiving as SQLite type-wise: SQLite uses flexible type affinity, while PostgreSQL commonly enforces column types and offers domains, arrays, ranges, JSONB, generated columns, and extension-defined types.

PostgreSQL dominates joins only as a workload-specific shorthand, not a law. Its planner has richer statistics and more join strategies for complicated analytical queries, while SQLite can be faster for a small indexed lookup because an in-process call avoids network and protocol overhead. Port the real query set and inspect execution plans. Synthetic single-query races conceal connection pooling, concurrent connections, cache state, and write operations.

Administration is the dividing bill. PostgreSQL needs upgrades, credentials, backups, connection limits, monitoring, vacuum behavior, and a recovery plan; SQLite usually needs correct file permissions plus a tested backup procedure. SQLite's backup API↗ can copy a live database incrementally, but copying an active database file with an ordinary filesystem command can produce an inconsistent backup when related journal files are missed.

Pick PostgreSQL early for multi-tenant services with database-enforced permissions, independent services sharing records, read replicas, logical replication, or teams that need auditable database roles. Keep SQLite for embedded applications and single-node services where restoring one file is an advantage. Flexibility includes declining infrastructure you do not need.