You can amortize the write speed significantly by not committing often, either in the sense of a SQL `COMMIT` or in the sense of doing a _synchronous_ `COMMIT`. You could commit every N seconds, say, for some sufficiently large N, or you could commit after N seconds of idle time and no more than M seconds since the last commit. You can also disable `fsync()`, commit often, and re-enable `fsync()` once every N seconds. There are many tactics you can use for data where some loss due to power failure is tolerable.

I.e., you can probably get pretty close to the storage device's max sustained write throughput, though with some losses for write magnification due, e.g., to B-tree write magnification and also indices you might want to maintain.

Write magnification due to B-tree write magnification can be amortized by committing infrequently (which is why I listed that _first_ above). Though there should be no need because SQLite3 already amortizes B-tree write magnification by using a write-ahead log (WAL), so be sure to enable the WAL for this sort of application.

Write magnification due to indices can be amortized by partitioning your tables by time ranges, and then use a VIEW to unify your tables, and then you can create an index on any partition only when it closes to new log entries. This approach causes reads to be slow when searching newer log entries, but those probably will all fit in memory, so it's not a problem if you have a large enough page cache.

Now I've not built anything like this so I can't say for sure, but I suspect that one could get very aggressive with these techniques and reach a sustained write rate of around 75% of the storage device(s)' sustained write rate.

I don't think this is going to be an issue as Linux has a built-in rate limiter.

The advantage of SQLite is being able to perform queries like “logs for service X and Y yesterday between 15hs and 16hs”.

You're sort of right in that a B-tree is not a good data structure for logs given that append-only files are perfect for logs. But the point of using an RDBMS for logs is to be able to a) index the logs, b) provide a great search facility for logs. Perhaps a better design would be a virtual table plugin for SQLite3 that allows one to use log files as tables, then index and search them with SQLite3, but if one lacks the time to investigate that approach then oe can't be faulted for using SQLite3 directly.

https://news.ycombinator.com/item?id=27897427

Maybe autocommit mode is set? I'd expect that.

I stopped trying to swim against the current and switched to OpenBSD/FreeBSD. You might be surprised how viable it really is.

That's the spirit!

https://www.antixforum.com/forums/topic/antix-23-1_init-dive...

Seems that it even saves power, in spite of using that gaming/latency-optimized kernel from Pika-OS.