Self-hosted replicated Clickhouse

[Tristan971]

Hello,

I'm migrating our old Plausible setup to a newer one, where we use Clickhouse replication. It's mostly simple-ish, but I have a few questions for that.

---

First: Can I disable automatic DB migrations on start by Plausible?

I plan to do them by hand before updates, but I'm a bit concerned about forgetting one day, and the auto-migrations wreaking havoc (for example converting from ReplicatedMergeTree to non-replicated MergeTree). I use the Distributed database engine, so DDL queries get replicated with the benefits (automatic sync) and downsides (wreaking a replica wreaks all of them).

---

Second: What is the purpose of the "domains_lookup" table exactly?

Seems like the goal is some sort of domain lookup cache from the name and its structure, but since it's using the Memory engine (presumably on purpose), I especially want to know what happens if has different content in 2 subsequent queries by Plausible?

Will just cause a slightly slower event handling when querying different replicas with different table content, or will it actually cause data inconsistency somehow?

---

Third: Is it still unsafe to run multiple instances of Plausible for the same domain?

I remember trying that a while ago and running into issues (can't remember exactly, it was 2.5 years ago by now), so I was curious if that was definitely-safe nowadays (obviously, I mean assuming the same PSQL and Clickhouse databases)

---

(This is all assuming latest versions of Plausible, Clickhouse, and PGSQL)

Thanks!

[ruslandoga]

👋 @Tristan971

Can I disable automatic DB migrations on start by Plausible?

Yes.

What is the purpose of the "domains_lookup" table exactly?

It's used in the v1 -> v2 data migration to lookup site ids for domain names.

Is it still unsafe to run multiple instances of Plausible for the same domain?

I personally haven't tried it yet so can't say for sure.

[Tristan971]

Can I disable automatic DB migrations on start by Plausible?

Yes.

Ehm... any hint as to how I can go about and do that? 🙂

Unless the easiest is to prepopulate the migrations table with the relevant rows so that they get skipped.

Is it still unsafe to run multiple instances of Plausible for the same domain?

I personally haven't tried it yet so can't say for sure.

Well, guess I'll report back when I give it a try. A single instance of Plausible is light enough CPU-wise that I don't have a ridiculously big need for it, but it's nice to have HA going on.

[ruslandoga]

Ehm... any hint as to how I can go about and do that? 🙂

You just remove it from command

[Tristan971]

Ah, right yes I'm silly... Thanks.

[arnaudbriche]

Hi @Tristan971

Can I ask if you managed to create use Clickhouse replication ?
If so, what was your workflow with the migrations ? Translating from elixir to SQL manually ?
Or is there a better way ?

[Tristan971]

Yes, and it works as-expected numbers-wise.

I then:

• Created the database with the Replicated engine, which ensures that DDL statements are applied on all nodes
• Ensured the replicated table engine arguments have defaults (ie that you don’t need to supply these in table DDL queries)

This means that I can now manually run DDL updates just by replacing the table engines Xyz with their ReplicatedXyz version (there were a few tables using non-mergetree engines, but they’re largely unused so not relevant and I picked replicatedmergetree for these)

I think there’s probably some way to have clickhouse itself translate queries from non-replicated to replicated table engines in DDL queries, but I think I’ll just do things manually instead since plausible is infrequently released anyway.

[doctorpangloss]

Plausible isn't really usable without replicated ClickHouse support. Is this something you guys are considering to support? I am plagued by issues because the self-hosted code assumes that ClickHouse isn't replicated. Surely this is a bad way to use a database?

I previously sponsored but stopped when you said earlier it wasn't something you wanted to do.

[doctorpangloss]

looking at the posthog source, they default to replicated. it should be the only option.

[ruslandoga]

This would complicate self-hosted setups significantly. Right now it can be run on a single machine, with that change at least three machines would be needed.

[Tristan971]

Technically you can use replicated table engines on a single-node ClickHouse deployment without issues. Tis what we do for CI runs for our software that uses it, and it avoids the burden of managing templating in SQL migration files

[ruslandoga]

Oh, I didn't know that. Thank you for the information!

[Tristan971]

If it helps here’s a minimal config we use, since it requires also having a 1-node zookeeper cluster* (or clickhouse keeper in this case)

https://github.com/mangadex-pub/clickhouse-ci

*: as the replicated engines parameters are not optional