SQLite in Production: WAL Mode and Concurrency on the Factory Edge

The first time I pitched SQLite as the database engine for our Manufacturing Execution System, I got the look. You know the one — the raised eyebrow that says that's a toy database. PostgreSQL was the reflex answer. MySQL was the safe answer. SQLite was apparently the answer you give when you haven't thought hard enough about the problem.

Here's what changed my mind: I actually read the SQLite documentation. Not the introductory page that calls it "lightweight." The deep pages — the ones about WAL mode, isolation levels, and write-ahead logging. The ones that made me realize we'd been dismissing the wrong database for the wrong reasons.

Why PostgreSQL Is Wrong for the Factory Floor

This isn't a takedown of PostgreSQL. In a cloud-deployed, horizontally-scaled, multi-tenant environment, PostgreSQL is excellent. But our MES runs on edge hardware — a dedicated machine bolted to the factory floor, adjacent to the SMT line it monitors. The operational constraints are completely different.

PostgreSQL requires a running daemon. It needs port configuration. It requires a superuser account, a cluster initialization step, and continuous background maintenance (vacuuming, WAL archiving, connection pooling). In a cloud environment, your DevOps infrastructure handles all of that invisibly. On a factory-floor machine managed by manufacturing engineers who should never have to think about database administration, it's a maintenance liability waiting to happen.

SQLite, by contrast, is a single .db file. The database "server" is your application itself. Zero configuration, zero daemon, zero network ports. Backup is cp factory.db factory.db.bak. This operational simplicity isn't a consolation prize — it's a genuine engineering advantage for the deployment context.

The Concurrency Problem That Almost Proved Everyone Right

Here's where the SQLite skeptics had a point. In its default configuration, SQLite uses a rollback journal with writer-takes-all locking. When one connection is writing, all readers are blocked. In an MES environment, this is catastrophic by design.

A background Python scheduler polls machine APIs every 30 seconds and writes telemetry to the database. Meanwhile, the foreground UI is rendering a live dashboard from the same database. In default SQLite mode, every background write freezes the UI query. At 30-second intervals, this produces visible hitches in the interface — the kind that makes production engineers distrust the system.

The standard reaction is to conclude that SQLite can't handle concurrent workloads and reach for a heavier database. The correct reaction is to enable WAL mode.

What WAL Mode Actually Does

WAL — Write-Ahead Logging — fundamentally changes SQLite's concurrency model. Instead of locking readers out during writes, the WAL mode maintains a separate log file where changes are first written before being applied to the main database file. Readers can continue querying the last committed state while a writer appends to the WAL. Writes and reads no longer block each other.

Enabling it is one line of Python:

conn.execute("PRAGMA journal_mode=WAL;")

That's it. One pragma, and your SQLite database goes from "unsuitable for concurrent workloads" to "handles multiple simultaneous readers and a single writer gracefully." For the MES use case — one background writer, multiple foreground readers — WAL mode is precisely the right tool.

There's a nuance worth understanding: WAL mode still serializes writers. Only one process can write at a time. But in our architecture, that constraint is actually a feature. A single authoritative writer (the telemetry scheduler) producing a consistent event log, with multiple readers querying it, is exactly the data flow we want. WAL mode makes that model work without friction.

Configuring SQLite for Production Workloads

WAL mode is the headline, but a production SQLite configuration has a few other PRAGMA settings worth knowing:

PRAGMA synchronous = NORMAL — In WAL mode, NORMAL synchronization provides excellent durability without the overhead of FULL sync. The database survives application crashes; only an OS crash during a checkpoint could theoretically cause data loss, and even then the WAL file provides recovery.

PRAGMA cache_size = -64000 — Sets a 64MB page cache in memory. For a database serving frequent dashboard queries over millions of rows, this cache dramatically reduces disk I/O. The negative sign tells SQLite the value is in kilobytes.

PRAGMA foreign_keys = ON — Off by default, which is a strange choice. Turn it on. Every time.

PRAGMA busy_timeout = 5000 — Tells SQLite to wait up to 5 seconds before raising a "database is locked" error when another connection holds a write lock. Without this, concurrent writes raise immediate exceptions that require application-level retry logic. With it, SQLite handles brief contention silently.

Together, these four pragmas transform a default SQLite installation into a robust edge database capable of handling real production workloads.

The Connection Pool Pattern for Python MES Applications

One remaining challenge: Python's sqlite3 module creates connections per thread, and connection objects aren't thread-safe. In an async or multi-threaded MES application, naively sharing a single connection produces race conditions.

The pattern I settled on uses a thread-local connection pool: each thread maintains its own SQLite connection to the same database file, with WAL mode ensuring they don't block each other. The background scheduler thread holds one connection; the Eel WebSocket handler threads hold their own. All connections point to the same WAL-enabled database file and coordinate automatically through SQLite's locking mechanisms.

This is meaningfully simpler than managing a PostgreSQL connection pool with asyncpg or psycopg3. It's also more appropriate for the scale: a factory-floor MES handling a few hundred writes per minute doesn't need the connection pool sophistication of a web application serving thousands of concurrent users.

The Benchmark That Settled It

After implementing the WAL configuration, I ran a simple stress test: 500 concurrent read queries against 10 simultaneous background writes, all on the SQLite database on edge hardware (a mid-range Intel NUC). The results averaged sub-10ms read latency even under write pressure. Dashboard queries that previously hitched now rendered smoothly. The UI thread never blocked.

For comparison, I spun up a local PostgreSQL instance on the same hardware and ran the equivalent workload. PostgreSQL was marginally faster at peak throughput — but only after accounting for daemon startup time, connection establishment overhead, and the fact that it took 45 minutes to configure correctly.

SQLite with WAL mode finished in 5 minutes of configuration and performed within 15% of PostgreSQL on this workload. For a factory-floor edge device, that's not a close call.

When SQLite Is the Wrong Answer

In fairness: SQLite is genuinely wrong for some scenarios. If you need multiple machines writing to the same database simultaneously over a network, SQLite's single-writer model breaks down — you need a client-server database. If you're running complex analytical queries over hundreds of gigabytes, a column-store like DuckDB or a dedicated analytics engine is more appropriate.

But for a single-machine, edge-deployed MES with one writer and several readers? SQLite with WAL mode is not a compromise. It's the right architecture.

The assembly hub's entire data layer is built on this foundation. In subsequent posts I'll cover the schema design — how three cascading tables handle everything from BOM imports to machine telemetry — and the query patterns that power the real-time dashboard. The database choice shapes everything downstream, and WAL mode is where that story starts.