Explore the potential of SQLite for building scalable, production-ready applications on a single node in this GopherCon 2021 talk by Ben Johnson. Learn about recent advances in SQLite that make it a viable alternative to distributed computing, and discover how to leverage its capabilities for read-heavy workloads and moderate request loads. Dive into essential SQLite configurations, including journal mode, busy timeout, and foreign key enforcement. Examine the SQLite type system, benchmarking techniques, and methods for parallelizing in-memory SQLite. Investigate the data durability spectrum, exploring trade-offs between cost, complexity, and performance. Gain insights into improving data durability, regular backup strategies, and the use of Litestream for clustered SQLite setups. Compare SQLite performance with other SQL databases and learn how to benchmark network latency for point query performance. Finally, consider the possibilities of horizontally scaling SQLite for enhanced production experiences.
Building Production Applications Using Go & SQLite
Overview
Syllabus
Intro
About me Ben Johnson
Client/server & cloud databases
What applications are good for SQLite? • Originally used by embedded software • Improvements in concurrency have made it more general purpose . Great for read-heavy workloads • Small to moderate request loads (100's of requests/sec) . Honestly, most applications are good candidates
Connecting to SQLite import
Configuring SQLite: Journal Mode • Journal mode configures how SQLite writes transactions . You almost always want it as "WAL" (write ahead log)
Configuring SQLite: Busy timeout • Busy timeout sets how long write transactions will wait to start . If unset, writes will fail immediately if another write is running . I find that 5 seconds is a good amount of time.
Configuring SQLite: Foreign keys • For historical reasons, foreign keys are not enforced by default. . Please use foreign keys.
SQLite Type System
Benchmarking SQLite tests
Parallelizing in-memory SQLite
The data durability spectrum How safe is your data?
Improving data durability Trade-off of cost, complexity, & performance
No data loss is good ...but not all data loss is catastrophic . GitLab lost 6 hours of data in 2017 .Postgres primary replica configuration • Data deleted by operator error
Option #1: Regular backups Data durability in SQLite
Litestream
Clustered SQLite
Performance between SQL databases How to choose?
Benchmarking network latency point query performance 1000
Horizontally scaling SQLite Thinking the unthinkable
Conclusion Improved production experience
Taught by
Gopher Academy
