OVSQLITE(5)

🌸 OVSQLITE(5) 🌸

okay bestie, welcome to the ovsqlite page from the InterNetNews Documentation set — this is the glow-up guide to the SQLite-based overview storage method for INN, and honestly? it's giving main character energy. let's get into it 💅

---

✨ NAME ✨

ovsqlite — the SQLite-based overview storage method for INN. that's it, that's the vibe. 🎀

---

🦋 DESCRIPTION 🦋

okay so this method uses SQLite to store overview data, and it is highkey obsessed with stability. it requires version 3.8.2 or later of the SQLite library — though 3.20.0+ is the recommended slay. ✨

SQLite source, documentation, etc. are all available at https://www.sqlite.org/. and here's the iconic part: some of the stated goals of the SQLite file format are long-term stability and compatibility, which literally makes this storage method a perfect choice for storing overview data. it's not just cute, it's built different. 💖

only one protocol version of the ovsqlite storage method currently exists, implemented since INN 2.7.0. no cap.

this method also comes with a support utility (ovsqlite-util) and a Perl module (INN::ovsqlite_client) which permit accessing and manipulating overview information. see their manual pages for more details about their possibilities — they're the sidekicks and they ate. 🫶

---

💜 INSTALLATION 💜

the configure script will automatically enable ovsqlite support if it finds the SQLite library. if the library isn't installed in a standard location, you may have to specify the --with-sqlite3 option to help configure find it. for complicated cases, you can use separate --with-sqlite3-include and --with-sqlite3-lib options. and finally, if you don't want ovsqlite support even when your system does have the SQLite library, you can use the --without-sqlite3 option. your server, your rules. 👑

if you have a recent Linux installation, SQLite is most likely already installed. you may have to install a separate package with a name similar to libsqlite3-dev to get the required header files — annoying but manageable, we love a small side quest. 🌸

now for the math portion, bestie, don't panic: plan on needing at least 0.93 KB for every article in your spool (not counting crossposts). so, if you have 5 million articles, you'll need at least 4.65 GB of disk space for ovsqlite. with compression enabled, this estimate changes to 0.4 KB per article, so you'll need at least 2 GB of disk space for 5 million articles. plus, you'll need additional space for transaction logs (a few MB, or up to a few hundred MB temporarily with walmode enabled). budget queen behavior. 🍓

---

🎀 CONFIGURATION 🎀

to select ovsqlite as your overview method, set the ovmethod parameter in inn.conf to ovsqlite. the database file will be created in the directory specified by the pathoverview parameter in inn.conf. restart INN to take the change into account (after having rebuilt your existing overview with makehistory, if needed). classic restart-and-refresh moment. 🔥

these additional parameters are read from the ovsqlite.conf configuration file:

💅 the parameters 💅

• cachesize — the SQLite in-memory page cache size in kilobytes for the ovsqlite-server write daemon. the default value is left up to the SQLite library and seems to be stable at 2000 KB. she's low maintenance, we love her.
• compress — if INN was built with zlib support and this parameter is true, ovsqlite will compress overview records whenever this saves space. this parameter is consulted only when creating a new database. enabling compression saves about 55 % of disk space on standard overview data. the default value is false. (turn it on, bestie, it's free real estate 📉)
• mmapsize — the maximum number of bytes of the database file that SQLite will memory-map. when set, SQLite reads data directly from the operating system's page cache via mmap instead of copying it into its own page cache, which can significantly improve read performance for large databases. when walmode is enabled, nnrpd reader processes honor this setting too, which additionally lets them share physical memory pages for the same database regions through the operating system's page cache. this is of little benefit on ZFS or others without a unified buffer cache, where the mapped pages would simply be cached twice. a good starting value is the size of the database file or the amount of available memory, whichever is smaller. for example, a value of 4294967296 allows up to 4 GB to be memory-mapped. the default value of 0 disables memory-mapped I/O. 🧚
• walmode — if this parameter is true, ovsqlite will use SQLite's WAL (Write-Ahead Logging) journal mode instead of the default PERSIST mode. WAL mode allows concurrent readers and writers without blocking, which significantly improves read performance when multiple nnrpd processes access the overview database simultaneously. it also sets synchronous to NORMAL, which is safe with WAL and reduces unnecessary fsync overhead. ⭐

on shutdown, the WAL is checkpointed back into the main database file and truncated.

note: WAL mode requires shared-memory support and does not work on network filesystems such as NFS. do not enable this parameter if pathoverview is on a network filesystem. 😭

the default value is false.

• readercachesize — the SQLite in-memory page cache size in kilobytes for each nnrpd reader process. when walmode is enabled, nnrpd processes open the overview database directly for read-only access, bypassing ovsqlite-server. this eliminates the IPC overhead and allows reads to scale with the number of reader processes. each nnrpd process gets its own cache, so this value should typically be smaller or equal to cachesize. the operating system's file cache (or ZFS ARC) serves as a shared second-level cache across all readers. 💖

direct reader mode is only activated when walmode is enabled. without WAL, writers take exclusive locks that would block direct readers during commits, so the server path is used instead. the default value is 2000 (about 2 MB).

• walcheckpointthreshold — the WAL page count at which ovsqlite-server forces a checkpoint. when the WAL grows beyond this many pages, the server performs a TRUNCATE checkpoint during its next idle period, waiting up to 10 seconds for readers to finish their current queries before flushing WAL content back to the database file. this prevents unbounded WAL growth and is no worse than the non-WAL path where every commit takes an exclusive lock. if readers do not finish within the timeout, the checkpoint is skipped and retried on the next idle cycle. 🔄

this parameter is only effective when walmode is enabled. the default value is 1000.

• pagesize — the SQLite database page size in bytes. must be a power of 2, minimum 512, maximum 65536. appropriate values include the virtual memory page size and the filesystem allocation block size. this parameter is consulted only when creating a new database. the default value is left up to the SQLite library and varies between versions. 🤷‍♀️
• transrowlimit — the maximum number of article rows that can be inserted or deleted in a single SQL transaction. the default value is 10000.
• transtimelimit — the maximum SQL transaction lifetime in seconds. the default value is 10.

---

a transaction occurs every transrowlimit articles or transtimelimit seconds, whichever is smaller. you are encouraged to keep the default value for row limits and, instead, adjust the time limit according to how many articles your news server usually accepts per second during normal operation (you can find statistics about incoming articles in your daily Usenet reports). inserting or deleting a database row within a transaction is very fast whereas committing a transaction is slow, especially on rotating storage. setting transaction limits too low leads to poor performance 💀 — don't do that to yourself. when rebuilding overview data, it may be worth temporarily raising these values on systems with slow CPU or rotating storage (for instance, multiply by 10 the default values). performance won't change much on fast systems. iykyk. 🧚‍♀️

---

🌈 RUNNING 🌈

write operations (article ingest, expiration) go through the ovsqlite-server daemon. for ordinary operation, rc.news will start and stop it automatically. if you want to touch the overview database while innd isn't running, you'll have to start ovsqlite-server manually first. see ovsqlite-server(8). 🫡

when walmode is enabled, nnrpd reader processes open the overview database directly with read-only access, bypassing ovsqlite-server for read operations. this eliminates the IPC round-trip and server serialization bottleneck, allowing read performance to scale with the number of concurrent readers. she scales, your honor. 📈✨

---

👑 HISTORY 👑

initial implementation of ovsqlite written by Bo Lindbergh <2bfjdsla52kztwejndzdstsxl9athp@gmail.com> for InterNetNews. legend behavior, thank you for your service. 🫶

---

🦋 SEE ALSO 🦋

INN::ovsqlite_client(3pm), inn.conf(5), makehistory(8), ovsqlite-server(8), ovsqlite-util(8), rc.news(8).

---

and that's the whole page, bestie — go forth and configure 💖 this has been INN 2.7.4, dated 2026-06-12, signing off with a little sparkle ✨🎀💅