Long term data retention

[Tristan971]

Hello,

I was strolling through our storage metrics and my attention was brought to the persistent volume bound to the Clickhouse instance we use for Plausible.

We run a fairly large website with somewhat consistent activity, so we see the space used by Clickhouse ever increasing (~12GB/month)

I should preface this by saying that, all things considered, it's really not a significant amount of space and it will be a while before it is a serious issue for us, but I was still a bit curious about how long term data storage is (or should be) handled with Plausible.

Am I right in assuming the following:

• the space used will grow essentially forever
• there is no particular "optimisation" for old data, like reducing granularity by some sort of merging behavior, at the cost of perhaps some necessary individual event/session data loss (like UAs, Referer, etc)

Similarly, if it ever becomes a problem to us, what kind of options would we have?
Would everything be fine if we manually pruned old events/sessions tables data from Clickhouse (let's say more than 2 years old) ?

Thank you!

N.B.: I know "deleting data" from Clickhouse is not really a thing, but bear with me here :-)

[ukutaht]

Your assumptions are correct. There is no optimization for long-term usage.

You can prune old data like you said. Clickhouse has a TTL directive that you could use as well to make Clickhouse automatically delete data at a certain age.

[teebu]

How do you do that?

[l4rm4nd]

# exec into the running docker container
docker compose exec plausible_events_db clickhouse-client

# select the correct database
\c plausible_events_db

# alter events_v2 table and add TTL with retention time
ALTER TABLE events_v2 MODIFY TTL timestamp + INTERVAL 365 DAY;

# alter sessions_v2 table and add TTL with retention time
ALTER TABLE sessions_v2 MODIFY TTL timestamp + INTERVAL 365 DAY;

# optimization
OPTIMIZE TABLE events_v2 FINAL;
OPTIMIZE TABLE sessions_v2 FINAL;

I've tested the above temporarily with a TTL retention of 10 seconds to validate whether events and sessions are automatically pruned:

ALTER TABLE events_v2 MODIFY TTL timestamp + INTERVAL 10 SECOND;
ALTER TABLE sessions_v2 MODIFY TTL timestamp + INTERVAL 10 SECOND;

This worked flawlessly and events + sessions older than 10s were automatically pruned. I switched back to the 365 DAY and assume this will also work flawlessly and prune events older than 1 year only.