Commit

Here's how I built Fusionaly's internals on a single SQLite file. SQLite allows one writer at a time, so I split the connections into two pools. The write pool has one connection. Ingestion does one tiny INSERT into a queue table and returns 202. Every write waits its turn for that connection, so SQLite never returns "database is locked". Every minute a job turns the queue into pre-aggregated metrics, 100 events at a time. Each visit adds 1 to a counter in its 30-minute bucket. A day of page views for one site is never more than 48 rows, whether it got 1,000 visits or 1,000,000. The read pool has 10 connections. The dashboard runs its 31 queries 12 at a time, and with WAL none of them waits for a write. The cost is that stats can run behind on high traffic. A small VPS processes a few thousand events a minute, so a site needs millions of events a day before you the stats start falling behind and if yours gets there, who cares? You decide on days and weeks of data. Pre-aggregated data it's an important piece of fusionaly and works pretty good for small-medium sized websites.