• Searchcode.com's SQLite Database: It is likely the largest public-facing SQLite database, with a size of 6.4 TB. The author has no evidence of a larger one and couldn't find anyone talking about a bigger one during internet searches.
• Project History: Searchcode.com has gone through multiple rewrites using different technologies like PHP, CodeIgniter, MySQL, Memcached, Sphinx search, Python, Django, Java, Go, MySQL, Redis, Caddy, and Manticore search. The constant was using MySQL as the storage layer initially, but it was later replaced with SQLite.
• Reasons for Switching to SQLite: The main reason was to have a single binary deploy without the need to install dependencies. SQLite can be compiled directly into the binary. The author also considered other embedded Go databases but found them not suitable for the expected scale.
• SQLite Issues and Solutions: Previously, the author faced issues like the "database is locked" error in SQLite, which was solved by having two connections - one for reading with the number of CPUs and one for writing with a single connection. Cross-compiling with CGO was a pain, but pure Go versions of SQLite like https://modernc.org/sqlite resolved the issue at the expense of some performance.
• Converting to SQLite: The conversion was made using SQLC, which converts SQL queries into type-safe code. It was a simple process with a proof of concept working in a week or two. However, storing code in SQLite was problematic due to the large quantity of content. The author used MySQL's compress and uncompress functions before. After considering other options like https://phiresky.github.io/blog/2022/sqlite-zstd/ and filesystem-level compression (like ZFS with zstd compression), the author chose ZFS and found a drastic compression ratio.
• Migration and Server Upgrade: The author exported the entire database and converted it to SQLite. After setting up a new server (Intel(R) Xeon(R) Gold 5412U with 256 GB of RAM and BTRFS partition), the migration was completed over the network. The data was less compressible than expected but still fit nicely with room to grow.
• Current Status: SQLite is working fine so far, with everything being faster compared to the previous MySQL instance. Indexing is still happening, and the author has plans to expand searchcode.com and focus on other code hosting services.