--- title: SQLite in Production: Optimizing WAL Mode, Concurrency,... description: Running SQLite in production requires moving beyond default settings. WAL mode enables concurrent reads and writes by appending transactions to a separate file... canonical: https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers | daily.dev og:description: Running SQLite in production requires moving beyond default settings. WAL mode enables concurrent reads and writes by appending transactions to a separate file... og:url: https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es og:image: https://api.daily.dev/og/posts/wedvUe3es.png og:image:alt: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers **[Hacker News](https://daily.dev/sources/hn)** · 8 min read · 0 upvotes · 0 comments ## Summary Running SQLite in production requires moving beyond default settings. WAL mode enables concurrent reads and writes by appending transactions to a separate file instead of modifying the main database directly. Key tuning areas include checkpointing strategies (PASSIVE, FULL, RESTART, TRUNCATE), busy timeout configuration to handle SQLITE_BUSY errors gracefully, using BEGIN IMMEDIATE transactions to prevent deadlocks, and scaling cache size to keep working sets in memory. Memory-mapped I/O via mmap_size can turn disk reads into pointer arithmetic. The VFS abstraction layer enables tools like Litestream and LiteFS for replication and distributed deployments. A complete production pragma blueprint covers WAL, synchronous=NORMAL, busy_timeout, cache_size, mmap_size, foreign keys, journal_size_limit, and auto_vacuum settings. SQLite suits read-heavy, single-region workloads under a few hundred gigabytes demanding ultra-low latency. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Community take How the wider developer community reacted, aggregated from 1 discussion and 77 comments across hackernews (as of 2026-07-29). **TL;DR:** The community is skeptical of this article (suspected AI-generated) and uses it as a springboard to debate SQLite's real-world production viability, surfacing genuine limitations around schema migrations, column alterations, durability trade-offs, and concurrency, while acknowledging it can work well in the right contexts. **Sentiment:** 20% positive · 45% mixed · 35% skeptical **The case for** - SQLite with Litestream can offer a smaller data-loss window than typical Postgres RDF defaults (around 1s vs 5 minutes). - A single-writer pattern at the application level effectively eliminates sqlite_busy contention. - Per-tenant SQLite databases provide strong isolation and are a well-proven model for mobile/desktop apps. - Tools like sqlite-utils provide practical solutions for alter-table limitations and schema migrations. - Check constraints can compensate for SQLite's limited column type enforcement. **The pushback** - The article is widely suspected to be AI-generated, undermining trust in its claims. - No production benchmarks are provided to back up the optimization recommendations. - PRAGMA synchronous=NORMAL can cause loss of committed transactions on crash, not just uncommitted ones. - Schema migrations are genuinely painful in SQLite (limited ALTER TABLE, complex table-rebuild workarounds). - Concurrent multi-process writes can corrupt a SQLite WAL database, as one commenter experienced firsthand. - Adding replication (LiteFS/Litestream) for HA negates much of SQLite's operational simplicity advantage over Postgres. - busy_timeout + BEGIN IMMEDIATE is insufficient for embedded multi-writer scenarios; an application-level lock is often needed. **By community** - hackernews (skeptical): Commenters are largely critical, questioning whether the author has real production SQLite experience, flagging AI generation, and debating concrete limitations around migrations, durability, and concurrency. **Hottest debate:** Whether PRAGMA synchronous=NORMAL is 'safe' in WAL mode — commenters disagree on whether it risks only uncommitted or also committed transactions. **Open questions** - What do production benchmarks actually look like comparing these optimizations to a default SQLite setup? - At what scale or workload does SQLite in production genuinely outperform or justify the trade-offs versus Postgres? - How should teams handle schema migrations cleanly in a SQLite-backed production service without bundling migrations into the app binary? **Highlights** > I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and I think it's great that it's getting hyped because it truly is a capable database, but after trying myself I think I'd never reach for it in production because it lacks a lot of power that a database like Postgres has, and some of that power is actually relevant to a real production setting: - Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database. - Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times. - You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky. All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant. > — [yladiz on hackernews · 11 comments](https://news.ycombinator.com/item?id=49095113) > > PRAGMA synchronous = NORMAL; > In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. No, this is not safe. You can lose the latest committed transaction with this pragma. > — [tnodir on hackernews · 2 comments](https://news.ycombinator.com/item?id=49097135) > I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down. The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres. > — [mikeocool on hackernews · 1 comments](https://news.ycombinator.com/item?id=49095741) > My sqlite-utils CLI tool and Python library offers solutions to both the alter table limitations and the need for schema migrations. For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... sqlite-utils transform fixtures.db roadside_attractions \ --rename pk id \ --default name Untitled \ --column-order id \ --column-order longitude \ --column-order latitude \ --drop address And for migrations there's a new-in-v4 "migrate" command which lets you create and execute an ordered sequence of migrations: https://sqlite-utils.datasette.io/en/stable/cli.html#running... sqlite-utils migrate creatures.db path/to/migrations.py Migrations files look like this: https://sqlite-utils.datasette.io/en/stable/migrations.html#... from sqlite_utils import Migrations migrations = Migrations("creatures") @migrations() def create_table(db): db["creatures"].create( {"id": int, "name": str, "species": str}, pk="id", ) @migrations() def add_weight(db): db["creatures"].add_column("weight", float) > — [simonw on hackernews · 1 comments](https://news.ycombinator.com/item?id=49096257) > Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process. > To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions). > — [andersmurphy on hackernews](https://news.ycombinator.com/item?id=49095141) **Source threads** - [hackernews](https://news.ycombinator.com/item?id=49094346) · 65 points · 77 comments ## Similar posts on daily.dev - [Write-Ahead Logging](https://daily.dev/posts/write-ahead-logging-emwv33ikr) · Lobsters · 0 upvotes · 0 comments - [SQLite in Production: Lessons from Running a Store on a Single File](https://daily.dev/posts/sqlite-in-production-lessons-from-running-a-store-on-a-single-file-kr6r2zrhi) · Hacker News · 2 upvotes · 0 comments - [Learning a few things about running SQLite](https://daily.dev/posts/learning-a-few-things-about-running-sqlite-lnbwnhclc) · Julia Evans · 2 upvotes · 0 comments - [SQLite concurrency and why you should care about it](https://daily.dev/posts/sqlite-concurrency-and-why-you-should-care-about-it-eju9k2khu) · Hacker News · 3 upvotes · 0 comments --- Tags: [#database](https://daily.dev/tags/database), [#sqlite](https://daily.dev/tags/sqlite) [View this post on daily.dev](https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers","url":"https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es"},"datePublished":"2026-07-29T09:47:58.408Z","dateModified":"2026-07-29T14:19:32.400Z","description":"Running SQLite in production requires moving beyond default settings. WAL mode enables concurrent reads and writes by appending transactions to a separate file...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/1e7a45163f1b00f015cfef8383a7e169?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/1e7a45163f1b00f015cfef8383a7e169?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Hacker News","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Hacker News","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/hn","url":"https://daily.dev/sources/hn"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/sqlite-in-production-optimizing-wal-mode-concurrency-and-vfs-layers-for-low-latency-app-servers-wedvue3es","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"database,sqlite","timeRequired":"PT8M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Hacker News","item":"https://daily.dev/sources/hn"},{"@type":"ListItem","position":3,"name":"SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers"}]} ```