How to Make SQLite Grind Millions of Vectors on a $5 VPS with 2GB RAM (and Not Die from Out-of-Memory)

The article discusses how to effectively manage SQLite databases on a small virtual server with limited resources. The main points are:

1. A common mistake is opening separate database connections inside loops or functions, leading to file descriptor leaks and Out-Of-Memory crashes on systems like a 2GB RAM VPS.

2. To fix this, refactor the code to use context managers (`with sqlite3.connect(...) as conn:`) that automatically close connections, even on crashes. Also, use native Python functions (`time.time()`) for timestamps instead of running separate SQL queries.

3. SQLite configuration can be tuned for better performance on low-memory servers. Recommended settings include:

- Write-Ahead Logging (WAL) mode for concurrent reads/writes

- A modest 256MB mmap_size to limit in-memory mapping

- A cache_size of -131072 KiB to cap RAM usage

- A 5000ms busy_timeout to handle concurrent writes

- temp_store=MEMORY to keep temporary tables in RAM

- Normal synchronous mode for durability

4. When multiple workers write to the database concurrently, SQLite locks can be triggered. Implementing proper locking mechanisms and handling errors gracefully prevents deadlocks and data corruption.

By following these techniques, the author managed to run vector search on a resource-constrained VPS without memory issues, demonstrating that SQLite can be an effective choice for these environments with some careful tuning and best practices.