How bloom filters made SQLite 10x faster (avi.im)

Article summary

Researchers used Bloom filters to optimize SQLite for analytical queries, resulting in a 7-10x speed improvement. The optimization involved adding two new opcodes, Filter and FilterAdd, to reduce the number of B-tree probes. This improvement has been applied to SQLite and released in version 3.38.0. The article explains the background, cause, and optimization of the improvement.

Main themes

• SQLite optimization
• Bloom filters
• Analytical queries
• Database performance
• Query planning
• Concurrency

What commenters say

• SQLite's concurrency model can be a limitation, but it can be worked around with proper configuration and application-level code.
• The database's performance is sufficient for many use cases, and its limitations are often overstated.
• Using SQLite for concurrent writes can lead to locking issues, but this can be mitigated with techniques like message passing or queuing.
• Enabling WAL mode can improve concurrency and performance, but it requires careful consideration of its caveats.
• SQLite is not a replacement for a fully featured database, but it can be a suitable choice for certain applications.
• The use of heuristics for query planning can help find efficient solutions, even for NP-hard problems.
• Proper understanding and configuration of SQLite's features, such as busy_timeout and WAL mode, are crucial for optimal performance.
• SQLite's limitations can be addressed by using it within a single process scope, where it can achieve good performance outcomes.