We've used PeerDB's hosted offering for sync'ing data from Postgres to Clickhouse both pre and post acquisition by Clickhouse Inc. We've also helped test the integrated sync features in Clickhouse Cloud built on top of PeerDB. We're using it to power customer facing features within our product.
It works well. Their team is great. I feel a bit spoiled having had as much access to the engineering team during the private beta as we've experienced.
It's great for use cases where it makes sense to sync postgres tables across to clickhouse without denormalizing them. PeerDB can transform rows in a single table sent via CDC using a lua scripting language, but it can't (yet!) denormalize data into clickhouse that is stored in 3NF on Postgres across multiple tables.
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. Most of the PeerDB sync'd tables end up as Clickhouse Dictionaries which we then use in our queries.
PeerDB works well and I like it for what it is. Just don't expect to be satisfied with querying in Clickhouse against the same table structure as you've got in Postgres unless your data size is tiny.
Curious to know about how others are using it and the architectures you've developed.
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.
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.
We launched our company on Render. It was great for going from zero to one very quickly but we had many problems and ended up migrating to AWS as we scaled.
* Poor visibility into detailed metrics, especially when problems happened in the render load balancer / routing mesh. We had a specific issue where a small number of requests were failing somewhere in render's infrastructure before reaching our application, and at the time there was no visibility to allow customers to know about requests that timed out or failed within render's infrastructure rather than our application. We collaborated with your team to surface and replicate the issue, but it was frustrating. I had a very good set of conversations with a product manager on your team about what we needed and why it was important in early 2024.
* At the time, the hosted postgres implementation was immature. I think this is an area you've already improved dramatically.
* Maybe you could add support for something like AWS PrivateLink so customers can run parts of their workloads on AWS securely over a private network. This would be a neat way to allow customers to stay on Render longer as their needs grow.
We launched HTTP request logs early in 2024, which would have made things easier to debug. Similarly, we're launching full OpenTelemetry exports in a few weeks.
Deep observability is critical for more complex environments, and things are improving dramatically on this front just as they have on Postgres.
I'm currently building on Render and I concur with the third point. Render is great right now but I know I'm going to need a more sophisticated backend data environment and analytics workloads in the future.
As much as DuckDB is cute I've mostly come to believe that Clickhouse is the perfect thing to pair Postgres with. This is especially true now that they've acquired PeerDB and are integrating it into the Clickpipes cloud product.
DuckDB is neat, and I understand why a company like BemiDB would build their product on top of it, but as a prospective customer embedded databases are a weird choice for serious workloads when there are other good open-source solutions like Clickhouse available.
Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).
I can imagine this product is a very elegant solution for many types of companies/teams/workloads.
curl https://clickhouse.com/ | sh
./clickhouse local
Run the query above to attach the table.
The table is updated in real time. For example, here is your comment:
:) SELECT * FROM hackernews_history WHERE text LIKE '%Clickhouse is amazing%' ORDER BY update_time \G
Row 1:
──────
update_time: 2024-04-06 16:35:28
id: 39785472
deleted: 0
type: comment
by: mightybyte
time: 2024-03-21 22:59:20
text: I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested:<p><pre><code> clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty"
</code></pre>
Way easier than opening in Excel and creating a pivot table which was my previous workflow.<p>Here's a list of the different input and output formats that it supports.<p><a href="https://clickhouse.com/docs/en/interfaces/formats" rel="nofollow">https://clickhouse.com/docs/en/interfaces/formats</a>
dead: 0
parent: 39784942
poll: 0
kids: [39788575]
url:
score: 0
title:
parts: []
descendants: 0
Row 2:
──────
update_time: 2024-04-06 18:07:34
id: 31334599
deleted: 0
type: comment
by: richieartoul
time: 2022-05-11 00:54:31
text: Not really. Clickhouse is amazing, but if you want to run it at massive scale you’ll have to invest a lot into sharding and clustering and all that. Druid is more distributed by default, but doesn’t support as sophisticated of queries as Clickhouse does.<p>Neither Clickhouse nor Druid can hold a candle to what Snowflake can do in terms of query capabilities, as well as the flexibility and richness of their product.<p>That’s just scratching the surface. They’re completely different product categories IMO, although they have a lot of technical / architectural overlap depending on how much you squint.<p>Devil is in the details basically.
dead: 0
parent: 31334527
poll: 0
kids: [31334736]
url:
score: 0
title:
parts: []
descendants: 0
Row 3:
──────
update_time: 2024-11-07 22:29:09
id: 42081672
deleted: 0
type: comment
by: maxmcd
time: 2024-11-07 22:13:12
text: Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).<p>I can imagine this product is a very elegant solution for many types of companies/teams/workloads.
dead: 0
parent: 42080385
poll: 0
kids: []
url:
score: 0
title:
parts: []
descendants: 0
3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.)
Peak memory usage: 579.26 MiB.
Received exception:
Code: 499. DB::Exception: The AWS Access Key Id you provided does not exist in our records. (Code: 23, S3 exception: 'InvalidAccessKeyId'): While processing disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false). (S3_ERROR)
It can be a wrong AWS profile in your configuration. In that case, you can correct the configuration, or use an empty server, docker container, or even CloudShell.
Very interesting, can you give more info on how this could be used for instance in my IoT case where I want to keep the last 3 months (say) of data in Postgres, and dump old data in parquet/iceberg on S3, and be able to do analytical queries on the past data? Would that be hard to do?
And how does the real-time update work? Could I make it so that my latest data is incrementally sync'd on S3 (eg "the last 3-months block" is incrementally updated efficiently each time there is new data) ?
> 3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.)
Since you were running $(./clickhouse local) does that mean the query downloaded 14.45GB out of S3 to your machine? The 3.981s seems to imply "no," but I struggle to think what meaning that output would otherwise try to convey
This is the amount of processed data after decompression. The amount of compressed data is less. You can press whitespace during the query run to see the detailed metrics.
ReadBufferFromS3Bytes 8.95 GB
The amount of compressed data read from S3 is 8.95 GB. Note: it sounds quite large, interesting why compression is less than 2x on this dataset. Most likely, it uses just lz4.
I recommend trying it because it works everywhere. You can run ClickHouse on your laptop, on a VM, or even in Cloudshell in AWS.
The setup in my example uses a table updated by one ClickHouse server and observed by an infinite amount of read-only replicas, which could run anywhere.
ClickHouse is definitely a popular choice nowadays. I'm curious whether you self-host ClickHouse or use their Cloud? We wanted to make BemiDB as simple to run as possible with a single binary and object storage (vs large machines, big disks, clustering, running Temporal for CDC, etc.)
Clickhouse has an embedded version (https://github.com/chdb-io/chdb), the issue with duck is that it's too buggy for production loads. You can see a nice list of the issues here:
Daisychain.app | Software Engineer | Full-time or Contract | Remote
Daisychain is building a modern platform for effective organizing. Our customers include progressive political campaigns, grassroots organizations, and ambitious non-profits.
We’re seeking a software engineer who is excited about using their talents to deepen the impact of the causes we work with (we're particularly focused on impacting the 2024 US elections) while having an opportunity to shape the technical direction of our product.
What would actually be interesting to me is info on how local data protection authorities across the EU are now interpreting the principles articulated by the ECJ when applied to standard contractual clauses which are what most data transfers actually happen under rather than Privacy Shield.
How all this will play out and be interpreted by regulators is interesting and currently hard to see how everything will be reconciled between trade in digital services, the US national security state, and the fundemental rights guaranteed to EU citizens.
Tim in this piece cites the climate pledge as being an admirable Amazon policy.
Unfortunately, it's all lofty language and Amazon lags behind its peers on real action.
For the AWS cloud business Amazon lags far behind its peers at Microsoft and Google. Of the three main public clouds AWS is the only one still using coal power (coal is a big part of the power mix for the grid used by their largest point of presence in Northern Virgina). Microsoft and Google have run their data centers completely on renewables and have done so for years.
We're stuck on us-east-1 in Northern Virginia for legacy reasons and to make up for the dirty way that Amazon runs its cloud we buy feed-in RECs for the grid where our AWS instances run. AWS could be doing this themselves (there are RECs available! we're buying them!) to help jumpstart the transition to renewables in the energy markets where they operate but they're simply choosing not to spend the money.
Microsoft and Google deserve credit for their work in this area and they're doing a much better job. It's just too bad that AWS is a better technical product for our workload.
This feels a bit misleading. Both Microsoft and Google still use fossil fuels for their data centers. I understand the "value" of RECs, but it definitely doesn't mean your data center is actually powered by renewable or "clean" energy, despite it allowing you to claim that. I really wish these companies had to publish the real numbers. That would demotivate them to buy RECs though...so here we are.
These might be puff pieces, but at least from reading them, it seems like Amazon is actually moving towards doing things. Not only that, but they are getting other companies to sign the pledge and take action.
Google has been running on 100% renewable electricity since 2017. Microsoft has reached that milestone as well, but I can not find a date for when they achieved it.
AWS aspires to eventually reach that goal by 2025. One of the clouds is dirtier than the others.
They don't run 100% on renewable electricity. They offset their non-renewable by buying RECs, which allows them to claim they are 100% renewable. RECs work by allowing you to use non-renewable energy (from the coal power plant that is local to your data center/business). Then, you buy an equivalent number of RECs from somewhere else (could be an entirely different state) and you are allowed to claim you run on 100% renewable energy.
See: https://www.google.com/about/datacenters/renewable/
"In 2019, for the third year in a row, Google purchased enough renewable energy to match 100 percent of our annual global electricity consumption". They are not running on renewable energy, they are simply buying RECs. Albeit, one can argue that is better than not buying RECs, but it is misleading to claim they are "running on 100% renewable energy"
We're already running separate US/EU points of presence on AWS.
The trouble is that as a small business we can't afford to have two separate operations teams for the US instance vs. the EU instance. We're all based in North America too and it is not practical for us to hire a whole separate devops team for Europe.
Our US based engineers could in theory be compelled to hand over the data stored at rest in the EU. They could also in theory see PII like names or email addresses in the course of administering the application on their laptops in the US which counts as data export, so would still need Privacy Shield or now SCC to allow engineers to do their everyday work keeping the product up and working.
In this particular case all of the people without premium cards who pay with cash or debit cards with no rewards are paying for our free lunch and flight to Hawaii. It's a tax on basically every transaction, paid to those participating in the rewards scheme
It works well. Their team is great. I feel a bit spoiled having had as much access to the engineering team during the private beta as we've experienced.
It's great for use cases where it makes sense to sync postgres tables across to clickhouse without denormalizing them. PeerDB can transform rows in a single table sent via CDC using a lua scripting language, but it can't (yet!) denormalize data into clickhouse that is stored in 3NF on Postgres across multiple tables.
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. Most of the PeerDB sync'd tables end up as Clickhouse Dictionaries which we then use in our queries.
PeerDB works well and I like it for what it is. Just don't expect to be satisfied with querying in Clickhouse against the same table structure as you've got in Postgres unless your data size is tiny.
Curious to know about how others are using it and the architectures you've developed.