Consolidate old analytics

[ItalyPaleAle]

Currently, looks like Plausible keeps all analytics inside Clickhouse.

On my Plausible node, which isn't even used on websites too trafficked, the size of the Clickhouse DB keeps growing steadily. It's already many GBs in size, and of course all of that comes with a cost.

Can you please look into a way to consolidate months-old analytics? It's probably not necessary to keep each visit in the OLAP database, and could be periodically aggregated and consolidated into the OLTP one.

Otherwise, sooner or later it will become unsustainable!

[ukutaht]

Let's see what's actually taking up the space. Please connect to your Clickhouse DB and paste the output of the following query:

SELECT table,
    formatReadableSize(sum(bytes)) as size,
    FROM system.parts
    WHERE active
GROUP BY table

[ukutaht]

Haha events and session are only 5 megabytes :)

We actually made a change to the default setup to disable query logs for self-hosted setups: plausible/community-edition@1c2004f

Once you have it disabled, you can safely drop all the query log tables.

[ItalyPaleAle]

I deployed my setup before that commit so I missed that.

Thanks for helping, now I feel much more comfortable with Plausible!

[ukutaht]

Sure!

Do you think the whole thing would be easier if we kept stats in Postgres instead? I feel like managing CH requires quite a bit of knowledge and since most people are more comfortable with PG, I've thought about adding an adapter for keeping your stats there.

Clickhouse is designed to be deployed in large clusters with 32GB memory at the minimum. For most self-hosters it's probably massive overkill for what they're trying to achieve.

[ItalyPaleAle]

I don't feel like I know enough about the architecture of Plausible to make a recommendation on what you should do, sorry! (I haven't gotten my head around Elixir quite yet 😄 )

I agree that Clickhouse isn't the most popular database and a majority of people would not be familiar with it. However, I also know you're running a SaaS service that is massively larger than our self-hosted instances, so if you implemented a solution that requires an OLAP database, you probably had good reasons for it!

But what I wrote in my first comment above might be an option? Maybe Clickhouse could contain the most recent data, and then an ETL job periodically consolidates that into Postgres? You've provided good-enough instructions (especially after you updated them to explain how to disable query logging) for how to run Clickhouse, but what might be missing is how to operate it such as running backups or having a DR plan?

PS: The reason why I'm self-hosting Plausible is because I know that SaaS analytics are often blocked by ad-blockers (even those that are privacy-respecting like Plausible). I know there are workarounds, but ad-blockers have workarounds to the workarounds too, and it feels like a never-ending cat-and-mouse game 😢

[bhavin192]

Do you think the whole thing would be easier if we kept stats in Postgres instead?

From self-hosting user perspective, having stats in Postgres makes it easy to manage, backup etc. But don't know how much overhead will it be to maintain both adapters CH and PG. That also means the service will use CH and self-hosted setups will use PG, less parity between both setups, and might have different issues? I'm just putting my thoughts out here, I don't know much about CH and PG.