ClickHouse Data Modeling for Postgres Users

https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips

by saisrirampur • 2 years ago

80 9 2 years ago

`ORDER BY (enabled, ts, id) POPULATE AS` `SELECT * FROM peerdb.public_goals;`

With a materialized view like that, a simple "final" for deduplication won't work anymore, right? And the other two deduplication methods will probably cause performance problems on large-ish tables.

deisteve• 2 years ago

ur absolutely right that a simple FINAL clause won't work as expected with a materialized view like this, since the FINAL clause relies on the underlying table's ordering to eliminate duplicates. With a materialized view, the ordering is based on the enabled, ts, and id columns, which might not be the same as the underlying table's ordering.

As for the other two deduplication methods, you're also correct that they might cause performance problems on large-ish tables. The DISTINCT ON method can be slow due to the need to sort the entire table, while the ROW_NUMBER() method can be resource-intensive due to the need to assign a unique row number to each row.

one possible solution to this problem is to use a combination of a materialized view and a secondary deduplication method. For example, you could create a materialized view that includes a ROW_NUMBER() or RANK() function to assign a unique identifier to each row, and then use a secondary query to eliminate duplicates based on this identifier.

you could consider using a different data modeling approach, such as using a separate table to store the deduplicated data, or using a data warehousing tool that supports more advanced deduplication techniques.

Elucalidavah3• 2 years ago

materialized view that includes a ROW_NUMBER() or RANK() function

That won't work, as the materialized view's query is applied per inserted chunk, i.e. to each row separately in the most extreme cases.

rnavi2• 2 years ago

Love everything about CH. However, we were at wits end syncing things to it from postgres. Airbyte was a let down with no normalisation (they had it but removed it). Meltano was seamless but with no ui. However, we have had issues with it as well. Peerdb is a welcome addition - will give it a go soon.

rnavi2• 2 years ago

Adding to above, CH should take ownership of CH airbyte connector. There are so so many users waiting for it. Have a look at that issue/discussion in airbyte github repo.

saisrirampur2• 2 years ago

Great feedback, thanks for chiming in rnavi. We will add this (contributing to AirByte) as a potential item in our roadmap.

greatgib• 2 years ago

In Postgresql, having an append only table like that and using a "window" for queries would lead to very bad performance as the whole table would have to be scanned for the window function to work. And that could be terrible when you have hundred of thousands of rows.

I'm wondering if the situation is different with clickhouse? Or if it is just that no one cares as your requests to clickhouse are supposed to be less frequent and so it is not a problem if the request takes several minutes to complete?

Elucalidavah3• 2 years ago

if the situation is different with clickhouse

Somewhat. Unlike postgres, clickhouse lets you throw more CPU resources at the problem. And there shouldn't be a need to scan the whole table.

saisrirampur2• 2 years ago

OP here. It depends on the workload/query - number of columns in the query, filters, presence of aggregates, etc. Overall, I've seen ClickHouse perform well with window functions. This is a common strategy customers use to deduplicate data.

ClickHouse Data Modeling for Postgres Users

Related Stories