Database ballooning in size at an alarming rate

[Fawesum]

Got Umami up and running and it's so great! Really a wonderful, wonderful product.

However, I'm watching the table that's storing the pageview stats, and it's completely wild. Our site isn't really that big, but it's still adding 2-3 megabytes every day to this table. In three days it's already over 10MB. I suspect we're going to hit a gigabyte in less than a year, and if our site grows in popularity possibly much faster than that.
On top of that is the sessions table, which is around half the size of the pageview table, so that's also going to become huge.

I don't really want to clear out historical stats every few months here, so I'm wondering if I might have configured things wrong?

[mikecao]

That sounds pretty normal. That's just the nature of analytics. You have to store a lot of data. We can do some table optimizations to reduce the size a bit, but then it's tradeoff between performance and disk size. We'd rather optimize for performance. You can get a $5 a month server with 25GB of space these days.

[joshinat0r]

My umami database is 650mb large, have it running for 7 months now with 2m pageviews / 440k visitors.

Table	Size
event	44mb
pageview	500mb
session	100mb

Nothing unexpected though, just a lot of entries in the tables.

There was another issue where someone was asking for size optimization aswell. By not storing the path of a pageview in the pageview table itself, instead save the paths in another table and reference the path-ID in the pageview table.
Thing is then that every pageview insert first needs to check if it already exists in the reference table, find the ID or create a new one and then insert it into pageview.

Thats the storage vs performance tradeoff Mike is talking about.
Other similar open source trackers wont be significantly better, shynet is storing it pretty much the same way with additional data.
Google btw is doing this by sampling (depending on tier) and aggregating the data (thats also why you wont get seconds with GA).

Aggregating old data and deleting the high resolution data is really the only way to keep the footprint of the database low without performance penalties; but you're still deleting information.

[MonksterFX]

Aggregating old data and deleting the high resolution data is really the only way to keep the footprint of the database low without performance penalties; but you're still deleting information.

If you look at the schema of the database you can easily reduce the size in long term analytics. As mentioned from @joshinat0r

Just throw out all the strings and normalize them. The design of the database is not normalized in a lot of fields what perfectly fits the need because you cant predict all the possibilities.

[Migushthe2nd]

Is there a planned feature to aggregate old data? Deleting information sounds fine to me if it keeps the size of the database compact and queries snappy, and that appears to be the suggested solution anyways. My database has grown to 16gb in just 6 months at 4mil pageviews. This server does not have a lot of storage and it feels like a waste to start paying more a month just for data resolutions I won't ever really care about anyway

[lightningspirit]

You can use ClickHouse that gives that for free and setup some TTLs