db size over time about goatcounter HOT 5 CLOSED

This is in the 2.5 release.

from goatcounter.

This sounds about right; you can usually vacuum things to reclaim some space, but probably not too much.

This is the trade-off you get with SQL: the upshot is that it's easy to run and "standard", and that people don't need to spend a lot of time managing specialized databases, but the downside is that it's not the most optimum format, and you end up duplicating some things, which results in more disk space being used. Usually it's not a problem, although at scale it does add up (goatcounter.com is approaching 250G).

That said, I did spend some time optimizing all of this, and it reduced the database for stats.arp242.net by about half (the exact savings will depend on a few factors); stats.arp242.net is already running well on that, I just need to migrate goatcounter.com which I haven't gotten around to.

from goatcounter.

Thanks for confirming. The numbers I report are after vacuum-ing.

Are your optimizations in the source code of GoatCounter or somewhere else? Would this need a database migration to preserve all historic stats?

from goatcounter.

It's mostly just an optimisation of the database schema to avoid duplication – you do need to run a fairly expensive migration which rewrites quite a bit, but of course all data will be retained.

It's this branch: https://github.com/arp242/goatcounter/tree/refs – see the latest commit on that (I make NO guarantees on the stability of that branch, I don't recommend running it).

from goatcounter.

That's great to know. I'll keep an eye on upcoming releases to see when this change makes it. It's not urgent right now, but would be nice to keep the DB size a bit smaller if possible. The ~half reduction you mention sounds very good.

from goatcounter.