HN
Today

SQLite in Production: Lessons from Running a Store on a Single File

Ultrathink runs a production e-commerce store on SQLite, showcasing its surprising capabilities for a real business while highlighting its unique deployment pitfalls. The post details how rapid blue-green deploys exposed filesystem-level contention, leading to lost orders despite WAL mode. It's a deep dive into the hidden complexities and critical lessons learned when pushing a single-file database to its limits.

10
Score
1
Comments
#13
Highest Rank
7h
on Front Page
First Seen
Apr 4, 11:00 AM
Last Seen
Apr 7, 7:00 PM
Rank Over Time
28181316202524

The Lowdown

Ultrathink details their experience running a production e-commerce store using SQLite, leveraging Rails 8's first-class support. While acknowledging the significant benefits of operational simplicity and reduced infrastructure complexity, the authors candidly share the unexpected challenges and critical lessons learned from this unconventional setup.

  • Four Databases, One Volume: Their setup involves four distinct SQLite databases (primary, cache, queue, cable) all residing on a single, shared Docker volume, managed via Kamal for deploys.
  • WAL Mode's Role: Write-Ahead Logging (WAL) is essential for handling concurrency, allowing multiple readers and a single writer, which comfortably manages their read-heavy e-commerce traffic under normal operations.
  • The Lost Orders Incident: A series of 11 rapid-fire deploys within two hours caused overlapping blue-green container instances to contend for the same WAL file, leading to the silent loss of two order records despite successful Stripe payments.
  • The Deployment Pipeline Fix: The issue was attributed to deployment pacing rather than SQLite itself; slower, batched deploys resolved the contention, contrasting with how client-server databases like Postgres handle concurrent writes.
  • sqlite_sequence as a Forensic Tool: The sqlite_sequence table, which tracks the highest auto-incremented ID, proved invaluable for diagnosing the lost orders and is used for general auditing of historical records.
  • SQLite-Specific Gotchas: The authors highlight several peculiarities: the absence of ILIKE (requiring LOWER() LIKE), json_extract returning native types (necessitating CAST), and the high memory cost of kamal app exec spawning new containers on resource-constrained hosts.
  • Concurrency Limitations: They stress that ActiveRecord timeout is a safety net, not a solution for fundamental concurrency issues, advocating for application-level writer contention reduction if SQLITE_BUSY errors are frequent.

Ultimately, Ultrathink would choose SQLite again for single-server, moderate-write workloads due to its simplicity, but recognizes its limitations for horizontal scaling or high multi-writer concurrency, at which point a migration to Postgres would be necessary.