SQLite vs PostgreSQL for Production

The same write-heavy workload that SQLite handles at roughly 10,000 transactions per second on a single NVMe drive grinds to a halt the moment you add a second concurrent writer. The Headscale benchmark is the cleanest demonstration of this. Given several hundred concurrent clients creating records, SQLite in WAL mode took hours to finish the job, completed only a fraction of the creations, and threw a large batch of “context deadline exceeded” errors. PostgreSQL, given the exact same workload, finished with zero errors on the same hardware. That single result is the fastest way to understand the real difference between these two databases: it is not raw speed, it is concurrency. They are both fast at what they are architecturally built to do.

This is Part 3 of the series. In Part 1 we broke down how SQLite executes a query through its virtual machine, and in the benchmark-focused Part 2 we looked at real throughput numbers. Here we go head to head: architecture, concurrency, memory, data types, scaling behavior, and the operational work each one demands. The goal is a decision you can defend, not a tribal preference.

Key Takeaways:

• SQLite is an embedded, serverless, single-file library. PostgreSQL is a client-server, multi-user database with a much larger feature set.
• SQLite serializes writes with database-level locking. PostgreSQL uses multi-version concurrency control (MVCC) with row-level locking, so many writers proceed in parallel.
• For read-heavy, single-server, or per-tenant workloads, SQLite in WAL mode is often faster and far cheaper than PostgreSQL. For heavy concurrent writes, PostgreSQL is mandatory.
• PostgreSQL trades a little single-connection latency for genuine write scaling, but inherits real operational costs: connection pooling, autovacuum, monitoring, and query tuning.
• The decision threshold is measured, not assumed: sustained write throughput, multiple app instances writing the same data, and datasets beyond roughly a terabyte.

Two Architectures, Two Jobs: Library Versus Server

The architectural divide is the root of every other difference. SQLite is a C library that links directly into your application process. There is no daemon, no listening socket, no connection pool, no port to open. When your code calls a SQLite function, the database engine runs inside your own process and reads and writes a single file on disk. The GeeksforGeeks comparison puts it plainly: SQLite is a serverless database management system that embeds directly into the application, while PostgreSQL is a client-server system that requires a separate server process.

PostgreSQL is an independent server process. Your application connects over TCP/IP or a Unix socket, sends a query over the wire, and waits for a response. The server holds its own memory, authenticates users, enforces permissions, and coordinates access from many clients at once. That separation is what makes shared access work: a web application, a batch job, and an analytics tool can all query the same database concurrently, and the server keeps them from corrupting each other’s work.

This single fact drives the performance story. Because SQLite runs in-process, a simple SELECT completes in microseconds. There is no network round-trip and no inter-process communication. The devops-daily comparison notes that SQLite’s in-process calls deliver microsecond latency and are often faster than a local PostgreSQL for read-heavy workloads. Postgres pays a connection and round-trip cost on every query, even when the app and the database sit on the same machine.

The trade is concurrency and administration. SQLite gives up network access, user management, and parallel multi-writer coordination because it has no server to perform them. PostgreSQL gives up in-process latency to gain exactly those features. The devops-daily comparison frames it as a category error: the two engines sit at opposite ends of the relational database spectrum and solve different problems, so asking which is “better” without first naming the workload is the wrong question.

The Concurrency Divide: Database-Level Locking Versus MVCC

Concurrency is where the two databases diverge most. SQLite, being file-based, uses database-level locking. A write operation locks the entire database file. In the default rollback journal mode, this locks out readers too. Enabling Write-Ahead Logging (WAL) mode changes things dramatically: it lets multiple readers proceed while a single writer is active, because writers append to a separate WAL file instead of overwriting the main database. But even in WAL mode, only one write transaction can commit at any instant. Readers never block writers and writers never block readers, yet all writes queue up and take turns.

The PostgreSQL MVCC documentation explains the other side: PostgreSQL keeps a history of row versions so each transaction sees a consistent snapshot of the data as it was at a point in time. This means readers are never blocked by writers, and multiple writers can update different rows concurrently because locking happens at the row level, not the database level. Two sessions can modify different products in the same inventory table at the same instant. They only contend when they target the same row.

The difference is visible in code. The Better Stack guide shows a SQLite writer holding a lock for the duration of a transaction, while a PostgreSQL session locks only the specific row it touches:

-- SQLite: a write transaction locks the whole database file
BEGIN IMMEDIATE; -- acquires the write lock up front
INSERT INTO logs VALUES (1, 'entry');
-- another connection cannot write until this commits
COMMIT;

-- PostgreSQL: row-level locking, different rows proceed in parallel
-- Session 1 updates product ABC, Session 2 updates product XYZ
-- both commit without blocking, since they touch different rows
-- Session 3 updating ABC waits only for Session 1, not for XYZ

The Headscale benchmark shows what this difference means under load. The tableone.dev analysis of that test is blunt: with hundreds of concurrent clients, SQLite in WAL mode took hours, completed only a fraction of the requested creations, and produced a large number of errors as the single-writer queue saturated. PostgreSQL ran the same workload to completion with zero errors because its row-level locking let many writers proceed in parallel. Profiling showed SQLite’s CPU and memory spiking while Postgres handled the load gracefully.

A production SQLite deployment can still handle substantial write loads. The Shivek Khurana benchmark shows WAL mode cutting p99 write latency by 30-60% once more than two writers contend, and a busy_timeout between 5 and 10 seconds eliminates the lock errors that appear when concurrent writes collide. That benchmark is honest about the ceiling, though: beyond roughly 20 concurrent writers, latency degrades and the single writer becomes a hard wall.

Memory Footprint: Why Postgres Eats RAM and SQLite Does Not

The memory difference is stark and matters directly on a small VPS. The DEV Community field report gives concrete numbers: an empty PostgreSQL instance reserves tens of megabytes of RAM even with no active connections, and each new client connection forks a process that costs a few more megabytes. Without a connection pool, a hundred concurrent connections can consume several hundred megabytes. That is before any query work begins.

SQLite runs in-process and shares your application’s memory space. Idle, it consumes essentially nothing beyond what your app already uses. On a budget VPS running several small services, that difference decides whether the machine fits in a single gigabyte of RAM.

The memory costs of PostgreSQL are manageable with tooling. A connection pooler like PgBouncer keeps a fixed set of ready connections and avoids the per-request process fork. But that pooler is an additional piece of infrastructure you must run and monitor, which is precisely the kind of operational layer SQLite never needs. If you are weighing a single small service, this is where the calculators diverge.

Data Types and Integrity: Type Affinity Versus Strict Typing

How each database handles data types is a quieter but real production difference. SQLite uses type affinity: column types are suggestions rather than hard contracts. You can insert the string 'twenty-five' into an INTEGER column and SQLite will silently store it, as the Better Stack guide shows. This speeds prototyping, but it means your application must validate data because the database will not reject type mismatches on your behalf.

PostgreSQL enforces declared types strictly. Insert a non-numeric string into a NUMERIC column and the database rejects the transaction with invalid input syntax for type numeric. This catches bugs early, at insert time, instead of letting a malformed value drift downstream into a report or a broken join. The trade-off is that you must design the schema carefully up front.

SQLite has closed much of this gap. As we detailed in our earlier coverage of SQLite strict tables, every production table should use STRICT unless you have a specific reason to rely on flexible typing. Still, PostgreSQL keeps a richer type system overall: native UUID, INET, arrays, ranges, enums, composite types, and a JSONB type that is the standard for SQL-plus-JSON workloads. The devops-daily comparison lists this ecosystem, including PostGIS, pgvector, and TimescaleDB, as an area with no equivalent in the SQLite world.

Scalability: What Each Handles in 2026

Scalability means different things for the two systems, so measure against your actual workload rather than a marketing sheet. On modern NVMe SSDs, a single SQLite database in WAL mode sustains roughly 10,000 to 50,000 write transactions per second and over 100,000 reads per second on a single writer, per the daily.dev production guide. The botmonster benchmark cites around 85,000 point reads per second, 18,000 range queries, and 12,000 inserts per second on a 2026 AMD Ryzen 9 9900X, and it notes that covers apps up to roughly 100,000 daily active users.

Those are single-writer numbers. The andrecasal benchmark shows what happens when concurrency rises: PostgreSQL peaks around 35,000 ops per second with 16 concurrent connections, exceeding SQLite’s roughly 23,000, because the multi-process architecture delivers higher aggregate throughput when many clients write at once. The inflection point is real and it is the reason “SQLite only for tests” became “SQLite for read-heavy production, Postgres for concurrent writes.”

Real-world deployments confirm both claims. Expensify runs its entire backend on SQLite, giving each user their own file. The Tailscale coordination server uses SQLite as its primary store tracking millions of devices with a single writer. Fly.io’s LiteFS reports 200,000+ reads per second per node. On the other side, PostgreSQL is the default for serious multi-tenant and analytic workloads, a dominance the State of PostgreSQL Performance assessment calls established fact: it has ranked as the most-used database in the Stack Overflow Developer Survey for three consecutive years.

PostgreSQL Has Real Operational Costs

PostgreSQL is mature, popular, and genuinely excellent, but its marketing glosses over the operational burden it brings to a first-time operator. The DCAC review of PostgreSQL operational challenges names two scaling drags that recur under load: the lack of an execution plan cache and an I/O-intensive vacuum process. These mostly bite at higher throughput, but they are real.

The State of PostgreSQL Performance assessment is more direct. After inspecting query traffic across hundreds of production deployments, it reports that the same problems repeat with predictable regularity: missing indexes, N+1 query patterns generated by ORMs, and autovacuum configurations untouched since first provisioning. None of these are exotic. They are the default state of a PostgreSQL database that was deployed competently but not tuned deliberately.

That assessment includes a concrete before-and-after. A filtered SELECT on a multi-million-row table without a matching index required a full sequential scan and took hundreds of milliseconds. Adding a single composite index dropped the same query to well under a millisecond, an improvement of several orders of magnitude. The fix is one SQL statement:

-- A filtered query on a large orders table
SELECT * FROM orders
WHERE customer_id = 4217
 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Without an index, this is a Seq Scan touching every row.
-- The fix is one line, no application changes required:
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

-- After: an Index Scan returns the same rows in a fraction of a millisecond.
-- Note: production workloads should also run EXPLAIN ANALYZE to
-- confirm the planner actually uses the new index.

None of this makes PostgreSQL a bad choice. The engine is rarely the bottleneck; the queries you send it are. A team that migrates to PostgreSQL while preserving SQLite-era habits (minimal indexing, no monitoring, no pooling) will get a slow PostgreSQL and blame the wrong thing. The honest framing is a trade: you give up a little single-connection latency to gain the ability to scale writes, and you inherit a server’s worth of operational work: backups, pooling, autovacuum, monitoring, and replication.

The Decision Framework: Which One for Production?

Stop treating “production” as a binary that forces PostgreSQL. The devops-daily decision matrix and the botmonster benchmark both converge on the same thresholds. Migrate to PostgreSQL when you cross two or more of these, not when a blog post tells you SQLite is a toy.

SQLite is the right choice when:

• Your application runs on a single server with local file access.
• Reads dominate writes (roughly 90%+ reads).
• Your write throughput stays below roughly 10,000 transactions per second.
• Your dataset fits under roughly 1 TB in practice (the theoretical maximum is 281 TB).
• You are building a mobile app, desktop tool, CLI, per-tenant SaaS with one file per customer, or an edge-deployed application.
• You want to eliminate an entire class of operational work.

PostgreSQL is the right choice when:

• Multiple application instances must read and write the same data (SQLite does not work safely over network filesystems).
• You need thousands of concurrent write transactions from different processes.
• You need roles, row-level security, or connection pooling across services.
• Your dataset will grow beyond the practical SQLite size ceiling.
• You need advanced types or extensions: PostGIS for geospatial, pgvector for AI embeddings, JSONB at scale.
• A distributed, high-availability architecture is a known requirement.

One warning applies to the SQLite side. The Better Stack guide and the SQLite documentation both stress that file locking is unreliable over network filesystems, so sharing a single SQLite file across servers is a corruption risk, not a scaling strategy. If you see database is locked in your logs more than once a week, begin the migration plan now. The worst time to design a move to PostgreSQL is during an outage.

Neither database is a compromise. SQLite is arguably the best possible storage engine for its niche: embedded, read-heavy, single-host, and per-tenant workloads, with a file format guaranteed stable through 2050. PostgreSQL is the better default for classic server-side applications that expect concurrent writers, advanced data types, and a deep extension ecosystem. Pick the one whose architectural shape matches the system you are actually building, and measure your write concurrency before you decide, not after you are already stuck.

For applications that choose SQLite, the next step is squeezing maximum throughput out of it. In Part 4 of this series we walk through journal modes, cache sizing, and transaction management as practical tuning steps for high-throughput workloads, and how to measure the improvements rather than guess at them. The PRAGMA settings that matter most are worth a preview, since they appear in every production SQLite deployment:

-- The production PRAGMA defaults for SQLite, applied on every connection
PRAGMA journal_mode=WAL; -- concurrent readers during writes
PRAGMA synchronous=NORMAL; -- safe in WAL mode, cuts fsync calls
PRAGMA cache_size=-64000; -- 64 MB page cache
PRAGMA mmap_size=268435456; -- 256 MB memory-mapped region
PRAGMA busy_timeout=5000; -- wait 5 seconds instead of SQLITE_BUSY
PRAGMA foreign_keys=ON; -- not persistent; set on every connection
PRAGMA journal_size_limit=67108864; -- cap the WAL file at 64 MB

Part 4 turns these seven lines into a full tuning playbook, with the measurements that show whether each one is paying off.

Key Takeaways:

• Choose SQLite for lightweight, embedded, read-heavy, or per-tenant production workloads to eliminate an entire class of operational work.
• Choose PostgreSQL for heavy concurrent write loads, multiple app instances, datasets beyond a terabyte, and advanced data types or security.
• The Headscale benchmark is the cleanest proof: under heavy concurrent write load, SQLite took hours and errored on many tasks, while PostgreSQL finished with zero errors.
• SQLite’s WAL mode plus a 5-10 second busy timeout handles 10,000-50,000 write transactions per second on NVMe, but only one writer at a time.
• PostgreSQL’s real cost is operational: connection pooling, autovacuum, monitoring, and query tuning are mandatory, not optional.
• If you see database is locked more than once a week, plan the migration now, before it becomes an emergency.

Related Reading

More in-depth coverage from this blog on closely related topics:

• Best Practices for SQLite to PostgreSQL
• SQLite in Production 2026: Real Benchmarks, Limits, and When to Migrate to Postgres

Sources and References

Sources cited while researching and writing this article:

• Difference between SQLite and PostgreSQL – GeeksforGeeks
• SQLite vs PostgreSQL: Feature Comparison, Pros/Cons, and Verdict
• PostgreSQL MVCC documentation
• PostgreSQL vs SQLite – Better Stack Community
• SQLite vs PostgreSQL Performance An In-Depth Benchmark Analysis
• SQLite in Production – A Real-World Benchmark
• daily.dev production guide
• SQLite scales to production: 10K TPS, WAL mode, real benchmarks
• andrecasal/sqlite-vs-postgres-benchmark – GitHub
• Expensify
• State of PostgreSQL Performance assessment
• DCAC review of PostgreSQL operational challenges
• SQLite documentation