编程与软件工程unixsheikh.com
SQLite：大多数情况下你唯一需要的数据库
SQLite the only database you will ever need in most cases
2021 — 2023，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
这篇文章主张在绝大多数应用场景下，SQLite 就是你唯一需要的数据库：它零部署、零运维、性能出色，只有当多台物理机需要通过网络共享同一个数据库时才需要考虑客户端-服务器型数据库。文章在 2021 和 2023 两次登上 HN 首页，引发了 SQLite 与 Postgres 的经典之争。
为什么经久不衰：它代表了一场持续多年的实用主义之争——SQLite 够用还是 Postgres 更稳妥——每次重提都能引发高质量讨论，对今天选型仍有参考价值。
HN 评论精选
反方代表：Postgres 在远程访问、多租户、数据类型和多核利用上全面胜出
Postgres 是我什么都用的数据库。我试过各种类型的数据库，最后总是回到 Postgres。它强大又轻量，几乎没有做不到的事。为什么不用 SQLite？比如我想从 IDE 远程访问数据库，据我所知 SQLite 不支持远程访问；再比如 Postgres 的多租户非常简单，数据类型也极其丰富（包括数组和 JSON），而且它是为对称多处理设计的，这在多核时代很重要。
andrewstuart查看英文原文
Postgres is the database I use for everything. I find databases of all sorts extremely interesting and I've tried many of them, of all flavors. In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do. Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite. Another example - multitenancy is dead easy in postgres, makes things more secure and makes coding easier. Another is that Postgres had an extremely rich set of data types, including arrays and json. Also Postgres is designed for symmetric multiprocessing which matters in today's multicore world.
正方实证：6 年生产环境重度使用 SQLite 零事故，并给出具体最佳实践
我们把 SQLite 当主数据存储用了 6 年，服务数百个并发用户，每人每秒向磁盘读写 1-15MB 的业务状态，期间没有出过一次性能或数据完整性事故。诀窍是：每个数据库文件只用一个连接实例并在应用内共享；启用 WAL（快照或迁移时要拷贝全部 3 个文件）；尽量批量写入；需要备份时直接对整个虚拟机做快照，简单可靠。
bob1029查看英文原文
We've been using SQLite as our principal data store for 6 years. Our application services potentially hundreds of simultaneous users at once, each pushing 1-15 megabytes of business state to/from disk 1-2 times per second. We have not had a single incident involving performance or data integrity issues throughout this time. The trick to this success is as follows: - Use a single SqliteConnection instance per physical database file and share it responsibly within your application. I have seen some incorrect comments in this thread already regarding the best way to extract performance from SQLite using multiple connections. SQLite (by default for most distributions) is built with serialized mode enabled, so it would be very counterproductive to throw a Parallel.ForEach against one of these. - Use WAL. Make sure you copy all 3 files if you are grabbing a snapshot of a running system, or moving databases around after an unclean shutdown. - Batch operations if feasible. Leverage application-level primitives for this. Investigate techniques like LMAX Disruptor and other fancy ring-buffer-like abstractions if you are worried about millions of things per second on a single machine. You can insert many orders of magnitude faster if you have an array of contiguous items you want to put to disk. - Just snapshot the whole VM if you need a backup. This is dead simple. We've never had a snapshot that wouldn't restore to a perfectly-functional application, and we test it all the time. This is a huge advantage of going all-in with SQLite. One app, one machine, one snapshot, etc...
SQLite 与文件系统同处一机，可做原子快照，这是远程数据库做不到的独特优势
我在本地用 Gitea（GitHub 克隆）+ SQLite，跑在 ZFS 上，可以对 SQLite 数据库和 git 仓库做原子快照。如果用远程数据库就不可能了，因为无法对另一个文件系统上的数据库和 git 仓库做原子快照。希望各种服务的开发者支持 SQLite，不要把自己锁死在 PostgreSQL 或 MySQL 上。
olavgg查看英文原文
I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories. This would not be possible if I used a remote database, as there is no way to do a atomic snapshot of both the remote database on another filesystem(even if its ZFS) and the git repository. Developers of multiple services, please support SQLite and don't lock yourself to a single database like PostgreSQL and MySQL. Even if the service is embedded in a Docker Container it is still painful to manage.
质疑核心论点：单机存储不满足持久性，SQLite 缺乏可靠的复制方案
文章说只有多台物理机通过网络访问同一数据库时才需要客户端-服务器架构——但这个例外恰恰覆盖了'大多数情况'：如果数据只存在单机上，就不算持久。另外据我所知 SQLite 除了异步复制没有真正的持久性方案，虽然多数应用能容忍，但我宁愿用半同步复制的 MySQL，省得自己推演各种数据丢失的边缘情况。
adamkf查看英文原文
>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchronous replication. Arguably, most applications can tolerate this, but I'd rather just use MySQL with semi-sync replication, and not have to think through all of the edge cases about data loss.
历次讨论
同类还值得读
开源应用架构
The Architecture of Open Source Applications
《开源软件架构》（AOSA）是一本在线免费书，邀请知名开源项目的作者亲自剖析其内部设计，涵盖 SQLAlchemy、LLVM、Audacity、Apache、Bash 等系统。
编程与软件工程上首页 9 次 · 2011–2024aosabook.org编辑精选编程语言简史：简短、不完整且多半错误
A Brief, Incomplete, and Mostly Wrong History of Programming Languages
一篇以戏谑、故意不准确的方式回顾编程语言史的博客文章，用夸张轶事和讽刺定义串起 Lisp、C、C++、Java、JavaScript、PHP 等语言。
编程与软件工程上首页 8 次 · 2009–2021james-iry.blogspot.comSQLite 是如何测试的
How SQLite is tested
SQLite 官方文档页，系统介绍 SQLite 的测试体系：多种测试方法（TCL 测试、模糊测试、回归测试、静态分析等）和海量测试用例，展示了这个小型开源项目如何靠极致测试获得…
编程与软件工程上首页 8 次 · 2009–2025sqlite.org编辑精选高级编程语言（2009）
Advanced programming languages (2009)
Matt Might 的经典文章，按用途推荐“高级编程语言”：如果追求就业就选主流语言，如果追求表达力、学术研究或创业，则推荐 Haskell、OCaml、Scala 等函数式语…
编程与软件工程上首页 8 次 · 2009–2022matt.might.net我的 Forth 与栈机器经历
Yosefk - My history with Forth & stack machines
作者以亲身经历回顾了使用 Forth 语言和栈式机器的历程：既赞叹其简洁优雅、代码量小、可自举的优点，也坦率指出栈操作在实际编程中的痛苦，以及 Forth 社区文化中的问题。
编程与软件工程上首页 8 次 · 2010–2024yosefk.com编辑精选