Preventing storage to balloon

[ItalyPaleAle]

I am self-hosting Plausible (great app, by the way!!!) on a server with a few other low-usage apps, and today one of them started misbehaving. As I began to look into the issue, I found out that it was because I ran out of disk space, primarily caused by Clickhouse.

I understand the need for Clickhouse, as it's an OLAP database that you use to generate analytics from. But is there a way to prevent that from keeping all the history, forever, and ballooning in disk space? Some way to automatically prune old data and keep the most-recent logs only?

I understand that you run the SaaS service on a large infrastructure, but I assume that sooner or later you'd be facing issues like these too?

-- EDIT
I found another thread that referenced https://theorangeone.net/posts/calming-down-clickhouse/ This recommended cleaning up the Clickhouse logs. In my case, looks like the bulk of the data is in /var/log/clickhouse/store and not in the log files

[taobojlen]

At least with the official Docker image, it seems that Clickhouse's query logs are stored in Clickhouse itself, not just in .log files. By running the truncate query in the linked blog post (clickhouse-client -q "SELECT name FROM system.tables WHERE name LIKE '%log%';" | xargs -I{} clickhouse-client -q "TRUNCATE TABLE system.{};"), I reduced my Clickhouse disk usage from about 26GB to 440kB (!)

I ensured that the log tables won't fill up again by using the config files in that post, too. With docker-compose that might look something like this:

  plausible_events_db:
    image: yandex/clickhouse-server:latest
    restart: always
    volumes:
      - event-data:/var/lib/clickhouse
      - ./clickhouse-config.xml:/etc/clickhouse-server/config.d/custom.xml
      - ./clickhouse-users.xml:/etc/clickhouse-server/users.d/custom.xml
    ulimits:
      nofile:
        soft: 262144
        hard: 262144