Speed on larger datasets.

[plezploz]

I've noticed the application lag quite a lot when loading up all time statistics for websites (ex. umami's own demo https://app.umami.is/share/8rmHaheU/umami.is). Could this be possibly solved by adding some running counters for large sections of data?

[franciscao633]

Our cloud product uses Clickhouse, which doesn't have issues with the large datasets. However we are aware of performance issues in our self-hosting options (postgres/mysql) with larger datasets and explored options like TimescaleDB. We are open to other solutions as well and trying to balance performance with having as little dependencies to run Umami as well.

[franciscao633]

Currently not. Need to make some updates to write directly to CH, roll out some docs, setup, etc. The code is like 90% there to self-host a CH version of Umami.

[marcfowler]

I'd be very keen to see the Clickhouse version. Is there anything we can do to help get that over the line?

[wc-matteo]

same

[otahirs]

looking forward to CH support 🙏

[bgervan]

Is there any update on timescaledb? It would solve most of the issues for a single tenant (CH is more reasonable for the cloud multi tenant approach, but would be overkill for most self hoster)

[icexuick]

Does anybody have more information on better performance on self-hosting?
We have a site which gets between 1-2M pageviews per month, but we can only use the "7 day" overview. On "30 days" most of the data/graphs don't load anymore, and it crashes on "90 days".

We have tried cloud.umami for a month and a half, with over 1.3M page views, and that loads super-fast / instantly.

Any help / feedback / tips on how to get this working on a self-hosted solution is very much welcome!

[CammerJack]

what is considered large dataset? When it will start slowing down?

[jeffdrumgod]

My website_event table has over 19 million rows. The initial dashboard screen took more than 2 minutes to load and exceeded my system’s limits for the site with the highest number of records. Using EXPLAIN on the simple aggregation query, I found that over 251,000 rows were being examined.

(file related: src/queries/sql/getWebsiteStats.ts)

I conducted a test by analyzing the query and noticed several existing indexes, but a key one was missing. I created the following index, and now the screen loads instantly in under 2 seconds:

CREATE INDEX idx_website_event_opt ON website_event (website_id, event_type, created_at, session_id, visit_id);

I plan to continue studying and propose improvements to the indexes in the Prisma schema in the coming days if I can find some time.

[otahirs]

great find 🚀. I will be waiting for more insights 👏