Hacker News Reader

Thanks, Nathan, for chiming in and for all the support during the private beta! <3

Overall, what you shared makes sense for use cases like yours. However, there are other scenarios—such as multi-tenant SaaS analytics running large-scale workloads with PeerDB/PostgreSQL CDC. In these cases there are 100s of tables across different schemas that are synced using CDC. Some customers denormalize tables using materialized views (MVs), which is a powerful feature in ClickHouse, while others power dashboards directly with JOINs using the recent JOIN improvements in ClickHouse and suitable/optimized order keys (tenant_id,id).

When dealing with 100s to 1000s of tables and a heavily relational schema, building dual-write pipelines with denormalization becomes extremely difficult—especially when the workload involves UPDATEs.

We have many customers falling in the above bucket, replicating multiple petabytes of data to ClickHouse. A few customer deep dives on this are coming soon! :)

Side note: We are tracking support for in-transit transformations as a future feature. However, MVs are the way to go—more of an ELT approach.

Disclaimer: I work for Timeplus in the field team.

This is exactly the kind of problem we've been solving with a few of our customers. With Timeplus, we can listen to Kafka and then do streaming joins to create denormalized records to send downstream to ClickHouse. Traditionally we did this with stream processing and this would build up really large join state in memory for when cardinality on the join keys would get very large (think 100s of millions of keys).

This has recently been improved with two additional enhancements: 1. You can setup the join states to use hybrid memory/disk based hash tables in Timeplus Enterprise if you still want to keep the join happening locally (assume all data in the join is still coming in via Kafka) and maintaining high throughput

2. Alternatively, where you have slow changing data on the right hand side(s), we can use a Kafka topic on the left hand side and do direct lookups against MySQL/Postgres/etc on each change on the LHS. This takes a hit throughput but may be ok for say 100s of records per second per join. There's an additional caching capability with TTL here to allow for the most frequently accessed reference data to be kept locally so that future joins are faster.

On additional benefit from using Timeplus to send data downstream to ClickHouse is being able to batch appropriately so that it is not emitting lots of small writes to ClickHouse.

I also agree with most of your comments and conclusions.

In our setup, we use app ingestion to send all the denormalised data into Clickhouse using async inserts and Debezium/Kafka/Kafka engine and materialized views to sync a few Postgres tables into Clickhouse. 2 of the replicated tables are in the order of billions of rows, and are used in 20% of the queries (usually directly and less frequently with no more than 1-2 joins). Everything else queries the denormalised tables (usually no joins there, only some dictionary usage). Overall query performance is great, although it would have been even better since we use replacing merge trees and final.

The 2 main issues that we are facing are:

- we need to periodically cleanup the deleted rows from the replacing merge trees, since the application does lots of upserts and deleted rows just stay there.

- there is not much flexibility in the ordering keys of the replicated Postgres tables, unless you enable full replica identity. We took that performance hit (although nothing really noticeable in Postgres side) in order to have some flexibility and better query performance in the replicated tables in Clickhouse.

Great points! We're making progress on improving both of the issues you mentioned.

1. For deleted rows, you can create policies to simplify querying. However, periodic deletions are still necessary. We've been optimizing lightweight deletes/updates to improve performance, which should help with automatic deletions.

2. For the second issue, refreshable materialized views with different order keys than raw tables are an option worth considering. However, having it in real time for tables with billions of rows might not be viable. That said, processing within tens of minutes to a few hours could work. We're tracking that the order key serves a dual role—as both a deduplication key and a skip index—which is the root cause of this issue of enabling REPLICA IDENTITY on Postgres side.

Separately, working on a guide covering best practices for Postgres to ClickHouse data modeling, detailing these concepts further. More on this coming soon!

> On the clickhouse query side, we end up wanting denormalized data for query performance and to avoid JOINs. It's frequently not a great idea to query in clickhouse using the same table structure as you're using in your transactional db.

> In our experience we sync a few tables with PeerDB but mostly end up using app-level custom code to sync denormalized data into Clickhouse for our core use-cases.

Have you explored dbt? You may find that using custom code is not scalable, and that dbt solves this exact problem.

Our use case requires real-time inserts as events happen.

dbt is as I understand it for batch processing transformations on a set schedule.

This is exactly a use case a streaming processing like Timeplus excels, which help users do 20s streaming to dynamic table joins (imagine the hand side tables are dynamically updates) to denormalize data before the data lands in ClickHouse. This helps a lot for warehouse like ClickHouse to save the joins in them. Disclaimer, I am one of the Timepluer core engine engineer.