从 SQLite 到 DuckDB:查询快 5 倍，存储减少 80%

作者丨 Trace

译者丨明知山

策划丨李冬梅

Trace 从一开始就使用 SQLite 将所有数据存储在用户设备上。这是一个非常不错的选择—— SQLite 高度可靠，并且多种编程语言都提供了广泛支持。毕竟，它是全球部署最广泛的数据库之一。

Trace 是一款 macOS 时间追踪应用，可以监控你在 Mac 上的时间使用情况。鉴于它的功能，Trace 需要处理大量的数据，因此是一款数据密集型应用。尽管 SQLite 表现不错，但我们仍然面临两个关键挑战：

随着时间的推移，SQLite 数据库文件体积会显著增大。
随着数据集的增长，聚合查询响应速度开始变慢。

我们尝试了许多优化 SQLite 的方法，最好发现了 DuckDB：一个类似于 SQLite 的可嵌入式数据库系统，专为分析工作负载进行了优化。经过一段时间的测试，证明 DuckDB 比 SQLite 更适合我们的时间追踪应用。

为什么选择 DuckDB

我们需要一个能够嵌入到应用中，同时又快速且可靠的数据库系统。SQLite 和 DuckDB 都可以满足这些要求，但我们发现 DuckDB 在执行分析查询时比 SQLite 快 3 到 5 倍，而且随着数据量的增加，这种优势更加明显。

我们最复杂的查询涉及多个大表和视图的连接，并且大量依赖聚合 SQL 函数。在这种工作负载下，DuckDB 在这方面超越了 SQLite 也就不足为奇了。

DuckDB 的文档写道：

DuckDB 是为支持分析查询工作负载（即在线分析处理，OLAP）而设计的。

为了高效地支持这种工作负载，缩短每个单独值所消耗的 CPU 时间至关重要。在数据管理领域，实现这一目标的最新技术是矢量化或即时查询执行引擎。DuckDB 使用了列存储矢量化查询执行引擎。虽然查询仍为解释执行，但会一次性处理大量值（一个“向量”）。这大大降低了传统系统（如 PostgreSQL、MySQL 或 SQLite）中逐行处理数据的开销。矢量化查询执行在 OLAP 查询中表现出更好的性能。

SQLite 逐行处理数据，而 DuckDB 使用列存储矢量化执行引擎，同时处理大量值。这种处理方式特别适合我们的分析工作负载。

ClickBench 基准测试显示，在某些分析查询中，DuckDB 的性能比 SQLite 快了多达 1200 倍。

尽管 DuckDB 在我们的工作流程中读取性能显著优于 SQLite，但在单行操作方面，它不如 SQLite。在我们的测试中，SQLite 在这些场景下比 DuckDB 快大约两倍。

尽管如此，另一个促使我们选择 DuckDB 的关键因素是它的自动磁盘压缩功能，这个功能大大减少了存储空间。

我们创建了一个张表，与我们应用中使用的表类似，包含了两个时间戳列、一个主键整数 ID 和一个布尔列。插入 100 万行数据后，SQLite 数据库文件大小为 101.6MB，而 DuckDB 仅需 23.1MB，体积减少了 77%。

迁移过程

迁移过程并非一帆风顺。SQLite 的编程语言生态系统支持比 DuckDB 要好得多。Trace 的大部分代码是用 Rust 编写的，我们使用 Diesel ORM 来处理数据库交互。

DuckDB 也有自己的 Rust 包，即 duckdb-rs，但它并不是 ORM。因此，我们不得不重写代码库的核心部分，并自己实现了一个数据库迁移系统。

在重写过程中，我们还遇到了 DuckDB 的一些并发限制。以前使用 SQLite 时，我们可以打开多个并发连接，对同一张数据库表执行操作。而使用 DuckDB 时，这种情况会导致大量数据丢失——一个连接的数据会被保存，另一个连接的数据则完全丢失。解决这个问题并不难，我们只需要确保在任何给定时间只有一个线程可以修改一张表。

另外需要注意的是，将 DuckDB 包含在我们的应用中几乎使应用包的体积翻倍，从 26MB 增加到 44MB。虽然体积变大了，但对我们来说绝对值得。

我们是否完全放弃了 SQLite

没有。尽管 DuckDB 在分析工作负载方面表现出色，但我们仍然依赖 SQLite 来执行一些核心操作。SQLite 更适合处理频繁的小事务，因为它轻量，对简单查询速度更快，并且更有效地支持并发连接。

总结

自从迁移到 DuckDB 以来：