What determines whether a database is suitable for a production environment is not the fancy buzzwords in marketing brochures; it is the locking mechanisms on the disk, its stability in memory management, and its behavior under concurrent requests. For years, we have heard the rote memorization of "SQLite is only for testing, you must use PostgreSQL in production." However, the truth of the matter lies in making decisions by knowing the limits and architectural approaches of both databases. As someone who has pushed both databases to their limits in my own side projects and in the systems I manage in the field, I want to dissect the real-world production performance, advantages, and critical thresholds where these two beasts might let you down.
In this article, we will steer clear of theoretical definitions and focus directly on disk blocks, lock logs, RAM consumption, and operational costs. If you want to optimize server resources and get maximum efficiency with minimum maintenance costs, you are in the right place.
Concurrency and Locking Mechanisms
The most fundamental architectural distinction between SQLite and PostgreSQL is how they manage concurrent write operations. PostgreSQL uses the MVCC (Multi-Version Concurrency Control) architecture. Thanks to this, while a row is being updated or written, other operations performing reads are never blocked (non-blocking reads). PostgreSQL can successfully coordinate thousands of concurrent write requests by performing row-level locking.
SQLite, on the other hand, is inherently a file-based database. When operating in standard DELETE or TRUNCATE journal modes, it locks the entire database file to perform a write operation. However, when you enable WAL (Write-Ahead Logging) mode in the modern SQLite architecture, this situation changes drastically. WAL mode prevents readers from blocking writers and writers from blocking readers. Still, only a single write operation (single-writer) can be performed at any given time.
If you have an application that receives hundreds of concurrent INSERT or UPDATE requests per second, it is inevitable that you will encounter the SQLITE_BUSY error on SQLite. When background services of one of my side projects started receiving an average of 120 write requests per second, I began seeing this error in my SQLite logs:
sqlite3.OperationalError: database is locked
To resolve this issue, configuring journal_mode=WAL and synchronous=NORMAL in the SQLite connection parameters, and also pulling the busy_timeout value to a reasonable level like 5000 ms, provides temporary relief. However, when the concurrent write volume exceeds this threshold, PostgreSQL's row-based lock management and advanced lock manager structure become the only real solution.
Memory and CPU Consumption: Bare-metal Performance Metrics
In a production environment, the cost of server resources directly impacts your wallet. Especially if you are hosting microservices or side projects on your own VPS servers, how much RAM the database consumes when idle is a critical parameter.
PostgreSQL comes with a significant memory overhead due to its rich feature set and connection management. An empty instance running PostgreSQL 16 reserves about 35-50 MB of RAM from the operating system even when there are no active connections. For each new client connection, a new process is forked in the operating system, which means an average of 2-5 MB of additional memory consumption per connection. In cases where you do not use connection pooling, 100 concurrent connections instantly lead to over 400 MB of RAM consumption.
SQLite, on the other hand, runs in-process within your application. Since there is no separate service or process, it does not consume even an extra 1 KB of RAM when idle. It shares the memory space of your application code. You can manage memory optimization directly with the SQLite cache_size pragma:
-- Setting the SQLite cache to 10000 pages (approximately 40MB)
PRAGMA cache_size = -10000;
PRAGMA page_size = 4096;
As I previously mentioned in my [related: PostgreSQL connection pool tuning] post, while you have to use external tools like PgBouncer to alleviate this memory load on the PostgreSQL side, you do not need such additional operational layers at all with SQLite. On the CPU side, since there are no network protocol operations (context switching, TCP packaging) in SQLite, it consumes up to 30% fewer CPU cycles in simple queries compared to PostgreSQL.
Network Latency and IPC
PostgreSQL is a database with a client-server architecture. If your application server and database server are on different machines, every SQL query is converted into a network packet, passes through the TCP/IP protocol stack, travels over the network, and is processed on the target server and returned. This adds a minimum network latency (network round-trip time - RTT) of 1-2 milliseconds per query.
If your application executes 15 consecutive SQL queries to process a single HTTP request (which is a very common scenario in systems using ORMs), the response time of the request increases by 30 milliseconds just because of network latency. Even if you install PostgreSQL on the same server as the application and connect via a Unix domain socket, you still cannot escape the IPC (Inter-Process Communication) overhead.
With SQLite, however, there is no such thing as a network. The database engine is part of your application's compiled code. Running a query is no different from calling a C function in memory. The latency is at the microsecond level.
In the table below, you can see the average latency times of simple SELECT queries for SQLite running in WAL mode versus PostgreSQL running on the same server and on a local network (LAN):
| Measurement Metric | SQLite (WAL Mode) | PostgreSQL (Unix Socket) | PostgreSQL (LAN - 1ms RTT) |
|---|---|---|---|
| Single Row Read | 0.02 ms | 0.25 ms | 1.20 ms |
| 1000 Row Bulk Read | 1.10 ms | 3.40 ms | 5.80 ms |
| Simple Write (INSERT) | 0.45 ms | 1.80 ms | 3.10 ms |
| Memory Consumption (Idle) | ~0 MB | ~45 MB | ~45 MB |
ℹ️ N+1 Query Problem
If you are using ORM (Object-Relational Mapping) tools in your application and forgot to do eager loading, SQLite can tolerate this mistake because there is no network latency. However, when you connect the same code to a remote PostgreSQL database, you will see your page load times climb to the level of seconds.
Data Types, Schema Flexibility, and Strict Rules
PostgreSQL is a monument of discipline when it comes to data consistency and adherence to standards. Thanks to strong data types, CHECK constraints, custom enum types, and JSONB support, it guarantees data quality at the database level. If you accidentally try to send a string to an integer field in PostgreSQL, the database immediately rejects the transaction and throws an error.
SQLite, on the other hand, traditionally uses a flexible data type system called "manifest typing." That is, the data type is associated with the value itself, not the column. In SQLite, you can go ahead and write a string value like "mustafa" into a column you defined as INTEGER, and SQLite will silently accept it.
However, the STRICT tables introduced with SQLite version 3.37.0 (November 2021) have fundamentally changed this situation. Now, if you do not want to compromise on data consistency while using SQLite in production, you can define your tables like this:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
age INTEGER,
created_at TEXT
) STRICT;
When the STRICT keyword is added, SQLite behaves just like PostgreSQL and returns an error on type mismatches. Still, PostgreSQL's rich data types (e.g., UUID, INET, native JSONB indexing capabilities, and advanced date-time functions) make PostgreSQL indispensable in complex enterprise applications, especially when developing a production ERP or financial systems.
Backup, Replication, and High Availability (HA)
The biggest fear in a production environment is data loss. PostgreSQL is one of the industry's gold standards in this regard. You can take backups with pg_dump from a live system with zero downtime, and stream WAL segments to a remote storage area (like S3) to set up a Point-in-Time Recovery (PITR) installation. This means you can restore your database to exactly 14:15:02 yesterday. You can also distribute the read load with a master-replica architecture and set up automatic failover mechanisms.
SQLite, being just a single file, seems easy to backup at first glance: "Just copy the file." However, if you try to copy the file with the cp command at the operating system level while live write operations are ongoing, you run a very high risk of getting a corrupted backup file.
To backup SQLite safely, you need to use SQLite's own online backup API or CLI tool:
sqlite3 /var/lib/data/prod.db ".backup /backup/backup-$(date +%F).db"
In recent years, thanks to tools like litestream that have revolutionized the SQLite ecosystem, real-time replication has also become possible for SQLite. Litestream listens to SQLite's WAL files in the background and streams changes to AWS S3 or a similar object storage service within seconds after each transaction.
Still, if you need a high-availability (HA) architecture that runs active-active across multiple geographic regions (multi-region) or performs thousands of write operations per second, the SQLite + Litestream duo is not yet close to PostgreSQL's mature replication tools.
Decision Matrix: Which One Did I Choose in Which Scenario?
When designing application architecture, there is no "best database"; there is the "right database for the right scenario." Here is the simple but effective decision matrix I use when making this choice in my own projects and field installations.
Scenarios Where I Prefer SQLite:
- Low-Resource VPS Deployments: If I am running multiple small services on a VPS with 1 GB of RAM costing $5/month, I definitely prefer SQLite (in WAL mode) to avoid dealing with PostgreSQL's memory overhead.
- Read-Heavy Applications: In scenarios where the write rate is below 5%, such as content management systems (CMS), APIs serving static data, or personal blogs, SQLite's speed is unrivaled.
- Edge and IoT Devices: SQLite is the only choice in field operations where the internet connection might drop and data needs to accumulate locally and synchronize later.
Scenarios Where I Prefer PostgreSQL:
- High Write Concurrency: Systems where hundreds of different users or background workers (Celery/Sidekiq workers) are constantly writing to the database simultaneously.
-
Complex Analytical Queries and Reporting: Where
WINDOWfunctions are heavily used, complexJOINoperations run on large datasets, such as the planning modules of a production ERP, PostgreSQL's query planner works wonders. - Strict Data Schema and Security: Multi-tenant enterprise SaaS applications that require Row-Level Security (RLS) and user-based data access restrictions enforced at the database level.
As I discussed in my previous post [related: Yazılım mimarisi ve organizasyonel akış], you should make technology choices based on your organization's operational capabilities and budget, not popular trends. PostgreSQL is a great database, but managing, backing up, configuring connection limits, and optimizing it requires serious effort. SQLite, on the other hand, is an engineering marvel closest to the "set and forget" philosophy, with near-zero operational overhead. As long as you know their limits, both will work like beasts in a production environment. When making your choice, honestly evaluate your application's write load and your team's operational experience.
Top comments (0)