SQLite in Production: The 2026 Perspective

The Big Shift: SQLite in prod 2026

In 2025 and 2026, something shifted. SQLite, long dismissed as the database you use for unit tests and mobile apps and then replace “when things get real,” started showing up in production. Rails 8 made SQLite its default database in 2024. Independent developers proved you could run substantial traffic on a single .db file. PropFirm Key, a trading comparison site, handled over 50,000 daily visitors within three months using a single 47 MB SQLite database with a 98/2 read-write ratio, as the developer published in a detailed account on dev.to. Companies like Expensify and Fly.io showed that SQLite could process billions of transactions with an embedded architecture that eliminates network latency, as covered in the daily.dev production guide.

But here is the catch that gets glossed over in hype posts: SQLite’s operational constraints did not disappear. The single-writer limit, file-level locking, lack of native replication, and practical dataset ceiling around 1 TB, these are architectural realities baked into the design of a serverless, embedded database engine.

This article is for backend engineers evaluating whether SQLite can handle their production workload in 2026. It covers real tradeoffs (concurrency, write throughput, backups, replication, scaling limits) with specific numbers from independent sources and community reports. If you are building a small-scale web app where infrastructure costs are tight and every dollar counts, this is the decision framework you need.

Architectural Reality: What SQLite Can and Cannot Do

SQLite is a C library that you link directly into your app process. When your app runs a query, SQLite reads and writes a single file on disk. There is no network hop, no connection pool, no separate server process to monitor. The entire database (tables, indexes, schema, data) lives in one cross-platform file.

This architecture explains both strengths and hard limits.

# Python: SQLite connects to local file, no server required
import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

cursor.execute('''
 CREATE TABLE IF NOT EXISTS orders (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 user_id INTEGER NOT NULL,
 product_id INTEGER NOT NULL,
 quantity INTEGER NOT NULL DEFAULT 1,
 status TEXT DEFAULT 'pending',
 created_at TEXT DEFAULT CURRENT_TIMESTAMP
 )
''')
conn.commit()
conn.close()
# That is the entire setup. No install, no config, no users.

The official SQLite documentation on appropriate uses states that the database engine is appropriate for “any site that gets fewer than 100K hits per day.” The SQLite website itself handles about 400K to 500K HTTP requests per day on a single VM that shares a physical server with 23 others and yet still keeps its load average below 0.1 most of the time, according to the same documentation (noting this data was current as of 2015 when the page was written).

But those numbers come from a read-heavy workload. The picture changes dramatically when writes enter the picture.

Practical Dataset Limits

SQLite’s theoretical maximum database size is approximately 281 terabytes (256 tebibytes) at a maximum page size of 65,536 bytes, as documented in SQLite implementation limits. The developers note this upper bound is untested since they do not have access to hardware capable of reaching this limit. In practice, performance degrades significantly once databases exceed 1 TB, especially under write-heavy workloads. The daily.dev production guide puts the practical production limit at roughly 1 TB.

Concurrency Bottlenecks and Write Throughput

The single most important operational constraint of SQLite is its writer lock. Even in WAL (Write-Ahead Logging) mode, only one write transaction can commit at any instant. Multiple readers can proceed concurrently, but writers queue up and take turns.

# Simulating concurrent writes — this is where SQLite shows its limits
import sqlite3
import threading
import time

def update_inventory(product_id, delta):
 conn = sqlite3.connect('store.db')
 conn.execute('PRAGMA busy_timeout = 3000') # Wait up to 3 seconds
 cursor = conn.cursor()
 try:
 cursor.execute(
 'UPDATE inventory SET stock = stock + ? WHERE product_id = ?',
 (delta, product_id)
 )
 conn.commit()
 except sqlite3.OperationalError as e:
 print(f"Write failed: {e}")
 conn.rollback()
 finally:
 conn.close()

# 50 concurrent stock updates — contention is likely
threads = []
for i in range(50):
 t = threading.Thread(target=update_inventory, args=(101, -1))
 threads.append(t)
 t.start()

for t in threads:
 t.join()

print("All update attempts completed.")
# Note: In production, expect SQLITE_BUSY errors above ~10-20 concurrent writers.

Independent benchmarks provide consistent numbers. According to the daily.dev production guide, on modern NVMe SSDs with WAL mode enabled, SQLite can handle between 10,000 and 50,000 write transactions per second. Read throughput can exceed approximately 100,000 reads per second on a single server. But these are sequential single-writer numbers, not concurrent multi-writer throughput. The real-world benchmark by Shivek Khurana confirms that with WAL mode enabled, lock errors become negligible for workloads under 20 concurrent writers, but p99 latency degrades as concurrency increases beyond that threshold.

The SQLite Renaissance analysis on dev.to puts it bluntly: “If your app genuinely needs thousands of concurrent write transactions per second from different processes, use PostgreSQL or MySQL. Financial trading platforms, real-time analytics ingest, and IoT sensor data pipelines are examples.”

The default busy timeout in SQLite is effectively 0, if no busy handler is set via sqlite3_busy_timeout() or sqlite3_busy_handler(), a write attempt that cannot acquire the lock returns SQLITE_BUSY immediately, as documented in the SQLite busy timeout API. Setting PRAGMA busy_timeout = 5000 makes SQLite retry for up to 5 seconds, a value recommended by the Shivek Khurana benchmark which suggests setting it between 5 and 10 seconds, but this masks underlying contention rather than solving it.

WAL Mode: Helpful but Not a Cure

WAL mode is essential for any production SQLite deployment. It allows readers to proceed without blocking during writes, and it reduces write amplification. But it does not change the single-writer constraint. Two processes that attempt to write simultaneously will still serialize, one succeeds, the other waits or errors.

-- Essential PRAGMA settings for production SQLite
PRAGMA journal_mode = WAL; -- Enable Write-Ahead Logging
PRAGMA busy_timeout = 5000; -- Retry for 5 seconds on lock
PRAGMA synchronous = NORMAL; -- Balance safety vs speed (with WAL)
PRAGMA cache_size = -64000; -- 64 MB page cache
PRAGMA foreign_keys = ON; -- Enforce referential integrity
PRAGMA temp_store = MEMORY; -- Temp tables in RAM
PRAGMA mmap_size = 268435456; -- 256 MB memory-map for faster reads

When SQLite Genuinely Wins Over Postgres

Despite concurrency constraints, there are clear production scenarios where SQLite is the better choice in 2026.

Read-Heavy Single-Server Apps

The most common success pattern is a read-heavy web app running on a single server. With approximately 98/2 read-write ratio, SQLite delivers sub-millisecond query times because there is no network round trip. The PropFirm Key developer published a detailed account of how a single .db file served 50,000+ daily visitors with zero operational overhead. The database was 47 MB (small enough to live entirely in the OS page cache) and the site used WAL mode with a 5-second busy timeout to handle occasional write contention.

Database-Per-Tenant SaaS

Instead of one large Postgres instance with row-level security, some teams give each customer their own SQLite file. This isolates tenants completely, simplifies GDPR compliance (just delete the file), and avoids the need for tenant_id columns in every query. Turso’s platform formalizes this pattern with embedded replicas that sync across 30+ locations worldwide, as documented in the Turso embedded replicas documentation. The SQLite Renaissance analysis notes that Turso supports up to 100 monthly active databases on the free tier with 5 GB of storage, scaling to thousands of databases on paid plans.

Edge Computing and Local-First Apps

SQLite’s zero-config design makes it natural for edge deployments where you cannot run a Postgres server. Cloudflare D1 and Turso both build on SQLite to provide globally distributed read replicas with local writes. For apps that need to work offline and sync later, SQLite is the only practical choice. The local-first pattern (where the database lives on the client device and syncs to a server) is described in detail in the SQLite Renaissance article, which shows how LibSQL’s embedded replicas enable offline writes that sync when connectivity returns.

Prototyping and MVPs

If you are building an MVP to validate a market, SQLite eliminates an entire category of infrastructure complexity. There is no database server to provision, no connection pooling to configure, no backups to schedule. You can go from zero to deployed with a single file. If the product fails, you delete the file. If it succeeds, you plan a migration to Postgres.

Where SQLite Quietly Fails: Real Burn Scenarios

The teams that get burned by SQLite are the ones who underestimate how quickly a small app’s write profile changes.

The Write Contention Trap

A common story: a team launches a booking app for a small market. Traffic starts light, a few hundred bookings per day. SQLite handles it effortlessly. Then they add real-time availability checking, which means every page load triggers a write. Then they add a mobile app that polls frequently. Suddenly, dozens of concurrent writers are hitting the same database file. Lock contention spikes. Users see “database is locked” errors. The team scrambles to migrate to Postgres while customers are angry. The daily.dev production guide explicitly warns that SQLite “struggles with write-heavy workloads” and recommends Postgres for apps that “need thousands of concurrent write transactions per second.”

The Multi-Server Ambush

SQLite does not work over network filesystems. The official documentation warns that “file locking logic is buggy in many network filesystem implementations (on both Unix and Windows).” Despite this, teams sometimes try to share a SQLite database over NFS or SMB to support multiple app servers. The result is almost always data corruption or mysterious lock errors.

The Schema Migration Wall

SQLite’s ALTER TABLE support is limited. For apps that evolve rapidly (adding indexes, changing data types, splitting tables) this becomes a significant operational burden. The workaround (create new table, copy data, drop old table, rename) requires downtime or careful migration scripting. LibSQL, the open-source fork maintained by Turso, addresses some of these limitations with improved ALTER TABLE operations, but upstream SQLite remains constrained.

Operational Playbook for SQLite in 2026

If you decide SQLite is the right choice for your workload, here is how to run it safely in production based on current best practices.

Backup and Replication

SQLite has no built-in replication. For production deployments, you need an external tool. Litestream streams WAL changes to S3-compatible storage continuously. It runs as a background process and safely replicates changes incrementally. The Litestream project provides continuous streaming backup of SQLite to S3, GCS, or Azure with point-in-time recovery from object storage.

LiteFS (by Fly.io) uses a FUSE-based filesystem to replicate WAL segments from a primary write node to multiple read replicas across regions. This enables global read scaling while keeping a single writer. However, the SQLite Renaissance analysis notes that LiteFS Cloud was sunset in October 2024 and Fly.io has deprioritized active development. LiteFS remains stable and usable in production, but it is in a pre-1.0 beta state with no guaranteed roadmap.

Turso and libSQL provide managed multi-region SQLite with embedded replicas that sync automatically across 30+ locations worldwide. According to the Turso documentation, embedded replicas keep a local read replica of the Turso Cloud database: reads run locally from the file in microseconds, and writes are sent to the cloud primary and then reflected back to the replica.

Monitoring

Watch for these signals that indicate you are approaching SQLite’s operational limits:

• SQLITE_BUSY errors in app logs
• Query latency spikes during peak write hours
• Database file size approaching the practical limit
• Growing number of concurrent write operations
• WAL file growing unusually large (indicates checkpoint lag)

Migration Planning

Every team using SQLite in production should have a migration plan to Postgres or MySQL before they need it. The worst time to design a migration is during an outage. Key steps:

• Use a database abstraction layer (ORM) that supports both SQLite and Postgres
• Avoid SQLite-specific SQL features (GLOB, type affinity tricks)
• Test the migration process regularly, not just when you hit the wall
• Budget for the migration: expect several weeks of engineering time for a production app

Decision Framework: SQLite vs Postgres

Here is a practical decision tree based on real workloads, synthesized from the SQLite official guidelines, the daily.dev production guide, and the SQLite Renaissance analysis.

Choose SQLite when:

• Your app runs on a single server with local file access
• Daily active users are under 500 (or under 5,000 with mostly reads)
• Read-write ratio is 90%+ reads
• Dataset fits comfortably under the practical limit
• You can tolerate brief downtime for schema migrations
• You are building an MVP, internal tool, or edge-deployed app

Choose Postgres when:

• Your app requires multiple app servers
• You need thousands of concurrent writes per second
• Dataset will exceed 1 TB
• You need built-in replication and point-in-time recovery
• Schema will evolve frequently with complex migrations
• You are building multi-tenant SaaS with shared infrastructure

Key Takeaways:

• SQLite in 2026 can handle 10,000-50,000 writes/sec and over 100,000 reads/sec on modern NVMe hardware, but only one writer at a time.
• The practical production dataset limit is around 1 TB, per the daily.dev production guide.
• SQLite genuinely wins for read-heavy single-server apps, edge computing, local-first apps, and database-per-tenant architectures.
• Teams get burned by write contention, multi-server assumptions, schema migration friction, and lack of native replication.
• Always have a migration plan to Postgres before you need it, the worst time to design one is during an outage.
• For small web apps with under 500 daily users and read-heavy workloads, SQLite is often the right call in 2026.

Sources and References

Sources cited while researching and writing this article:

• Appropriate Uses For SQLite
• Implementation Limits For SQLite
• SQLite busy timeout API
• Turso embedded replicas documentation
• Litestream project