Experiences with ClickHouse hot/cold storage for Plausible CE?

[RazerPie]

Hi,

I'm planning a self-hosted Plausible CE deployment and I'm looking into long-term CLickHouse storage options.

Traffic will be somewhat seasonal, potentially around 10M+ pageviews/moth durin busier periods, while much lower during quieter months. I'd like to keep historical analytics indefinitely, but I don'n necessarily want severl years of rarely accessed data occupying the fast local storage.

I'm considerin setup along these lines:

• Recent data on local NVMe
• Older data moved to slover/cheaper storage
• CLickHouse storage policy with TTL .. TO VOLUME

So conceptually:
Local NVMe - recent data, 0-12 months
↓ TTL MOVE
Slower storage - historical data +12 months

I'm mainly interested in whether anyone is already doing something similar with Plausible CE in production.

A few specific questions:

1. Has anyone configured events_v2 ans sessions_v2 with hot/cold ClickHouse sotrage policies?
2. If so, what storage backed are you using for the cold tier: another local disk, network/block storage, etc?
3. Have you encountered any issues with Plausible upgrades or ClickHouse migrations after adding custom storage policies or TTL rules?
4. Does Plausible expect these tables to use the default storage policy, or is using a custom policy considered reasonably safe?
5. Is CLICKHOUSE_DEFAULT_STORAGE_POLICY intended to support setups like this?
6. Are there any Plausible-specific reasons why moving older parts to slower storage would be a bad idea?
7. For people running larger self-hosted instances, how do you normally manage long-term historical data?

I'm not mainly looking to delete old analytics. The goal is to keep them queryable while allowing older, rarely accessed data to live on cheaper/slower storage.

Any production experiences, configuration approaches, or upgrade-related caveats would be very useful. : )

[Protemir]

I haven't run tiered storage on Plausible CE in production, so I can't answer 1, 2 and 7 from experience. But I read how Plausible creates and migrates these tables, which answers part of 3–5, and a few ClickHouse details are worth knowing before you set this up.

About CLICKHOUSE_DEFAULT_STORAGE_POLICY (5): config/runtime.exs turns it into storage_policy in the table settings, and migrations add SETTINGS storage_policy = '...' when they create tables (Plausible.MigrationUtils.table_settings_expr). So it only picks the policy new tables start on. It doesn't move anything by itself. The move rule is a TTL you add yourself, for example:

ALTER TABLE events_v2 MODIFY TTL timestamp + INTERVAL 12 MONTH TO VOLUME 'cold';
ALTER TABLE sessions_v2 MODIFY TTL start + INTERVAL 12 MONTH TO VOLUME 'cold';

I'd set the variable before the first start, so both tables are created on your policy. Switching an existing table later with ALTER TABLE ... MODIFY SETTING storage_policy = '...' only works if the new policy still has all of the old one's disks and volumes. And add the TTL while the tables are still small: by default MODIFY TTL materializes it over the whole table right away (materialize_ttl_after_modify).

On upgrades (3, 4): Plausible has rebuilt these tables in data migrations before. VersionedSessions, for example, creates sessions_v2_tmp_versioned with its own engine and ORDER BY, copies only the SETTINGS part of the old table (extract(engine_full, 'SETTINGS .+')), attaches every partition and swaps the tables. The storage policy survives that, since ATTACH PARTITION FROM needs the same policy on both tables anyway. A hand-added TTL ... TO VOLUME wouldn't. That migration has already run on current installs, but if a future one works the same way, your move rule would just disappear without an error. So after each upgrade I'd check:

SELECT name, engine_full FROM system.tables
WHERE database = currentDatabase() AND name IN ('events_v2', 'sessions_v2');

For sizing (7): after a few weeks of real traffic, this shows how many bytes a row really takes, so you can see whether 12 months even outgrow the NVMe:

SELECT table, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       round(sum(bytes_on_disk) / sum(rows)) AS bytes_per_row
FROM system.parts
WHERE active AND database = currentDatabase() AND table IN ('events_v2', 'sessions_v2')
GROUP BY table;

One more thing grows on the fast disk no matter how you tier. CE's clickhouse/logs.xml turns off most of ClickHouse's own logs and keeps query_log for 30 days, but it leaves processors_profile_log on, and in the 24.12 image CE uses, that log has no TTL (ClickHouse's default config added a 30-day one in 25.2). plausible/community-edition#275 is about exactly this. You can give it a TTL or turn it off in the same file.