This is a extremely common issue that happens in growing firms.

You start off with everything in Postgres, it makes the most sense. Soon you realize some tables are growing really huge - usually some sort of time-series or log data reaching 10TB+. You can no longer fit it in one node. You can try you luck with some sharding extensions, but they add complexity to upgrades.

In that case it makes total sense to move these large tables off Postgres, and I think Clickhouse is a straight up replacement here. You can still keep your relational heavy tables in Postgres.

Yes it affects you ability to cleanly join data, and guarantee 100% consistency. With some smart application code, and schema design, you can replace parts of Postgres with Clickhouse for the big data problem.