HNHacker News
TopNewBestAskShowJobs

saisrirampur

1,428 karma · joined September 22, 2016

Postgres @ ClickHouse. Lead efforts for https://clickhouse.com/cloud/postgres
submissionscomments
saisrirampur··on ClickHouse vs PostgreSQL UPDATE performance comparison
Great question, exactly CDC from Postgres to ClickHouse and adapting the application to start using ClickHouse for analytics. Through the PeerDB acquisition, ClickHouse now has native CDC capabilities that work at any scale (few 10s of GB to 10s of TB Postgres databases). You can use ClickPipes if you’re on ClickHouse Cloud, or PeerDB if you’re using ClickHouse OSS.

Sharing a few links for reference: https://clickhouse.com/docs/integrations/clickpipes/postgres https://github.com/PeerDB-io/peerdb https://clickhouse.com/cloud/clickpipes/postgres-cdc-connect... https://clickhouse.com/blog/clickhouse-acquires-peerdb-to-bo...

Here is a short demo/talk that we did at our annual conferemce Open House that talks about this reference architecture https://clickhouse.com/videos/postgres-and-clickhouse-the-de...

saisrirampur··on ClickHouse vs PostgreSQL UPDATE performance comparison
Very interesting take — I see where you’re coming from. Yes, there are caveats and differences between ClickHouse and Postgres. Much of this stems from the nature of the workloads they are built for: Postgres for OLTP and ClickHouse for OLAP.

We’ve been doing our best to address and clarify these differences, whether through product features like this one or by publishing content to educate users. For example: https://clickhouse.com/blog/postgres-to-clickhouse-data-mode...; https://www.youtube.com/watch?v=9ipwqfuBEbc.

From what we’ve observed, the learning curve typically ranges from a few weeks for smaller to medium migrations to 1–2 months for larger ones moving real-time OLAP workloads from Postgres to ClickHouse. Still, customers are making the switch and finding value — hundreds (or more) are using both technologies together to scale their real-time applications: Postgres for low-latency, high-throughput transactions and ClickHouse for blazing-fast (100x faster) analytics.

We’re actively working to bridge the gap between the two systems, with features like faster UPDATEs, enhanced JOINs and more. That’s why I’m not sure your comment is fully generalizable — the differences largely stem from the distinct workloads they support, and we’re making steady progress in narrowing that gap.

- Sai from the ClickHouse team here.

saisrirampur··on Show HN: GlassFlow – OSS streaming dedup and joins from Kafka to ClickHouse
Got it. Thanks for the clarification. That might not work if the ingested row represents an UPDATE. We do this in Postgres CDC by replicating an UPDATE as a new version of the row and that is what you want to retain. For most customers using FINAL (with the correct ORDER KEY as needed) works well for deduplication and query performance is still great. But in cases where it isn't, customers typically resort to tuning faster merges with ReplacingMergeTree or Materialized Views (either aggregating or refreshable) to manage deduplication.

Anyway, great work so far! I like how well you articulated the problem. Best wishes.

saisrirampur··on Show HN: GlassFlow – OSS streaming dedup and joins from Kafka to ClickHouse
Neat project! Quick question, will this work only if the entire row is a duplicate? Or even if just a set of columns (ex: primary key) conflict and you guarantee only presence of the latest version of the conflict? I’m assuming former because you are deduping before data is ingested into ClickHouse. I could be missing something, wanted to confirm.

- Sai from ClickHouse

saisrirampur··on Making Postgres scale
Interesting technology. Similar to Citus but not built as an extension. The Citus coordinator, which is a Postgres database with the Citus extension, is replaced by a proxy layer written in Rust. That might provide more flexibility and velocity implementing distributed planning and execution than being tied to the extension ecosystem. It would indeed be a journey to catch up with Postgres on compatibility, but it's a good start.
saisrirampur··on Postgres Just Cracked the Top Fastest Databases for Analytics
Sounds good. I'm still a bit confused. But will wait for your next version. :) ETL problems still aren't avoided — replicating from Postgres sources using logical replication is still ETL. One topic we didn't chat much is, be careful about what you're signing up for with logical replication — we built an entire company just to solve the logical replication/decoding problem. ;)
saisrirampur··on Postgres Just Cracked the Top Fastest Databases for Analytics
I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.
saisrirampur··on Postgres Just Cracked the Top Fastest Databases for Analytics
Thank you for the kind words! :)

1, makes sense.

On 2, I understand your thinking around purpose-built — but you're retrofitting an analytical database into a transactional database without fully supporting all the features (both in terms of functionality and performance) of either. It's really hard to be truly purpose-built this way. As a result, users might not get the best of both worlds.

PeerDB is different. We keep Postgres and ClickHouse separate and just move data reliably between them. Users get to query Postgres and ClickHouse in isolation and make the best of each of them.

Anyway, keep up the good work! Just wanted to share some challenges we've seen before when building an analytics extension (Citus), particularly around chasing both Postgres compatibility and performance.

saisrirampur··on Postgres Just Cracked the Top Fastest Databases for Analytics
Sai from PeerDB/ClickHouse here. Nice to see the progress on this project! I wanted to leave a few notes:

For analytics on transactional data, it looks like you'd still need to use logical replication (https://github.com/Mooncake-Labs/pg_mooncake/issues/90). Logical replication is somewhat similar to an ETL/CDC experience, though it's more Postgres-native. Managing logical replication at a production grade isn't trivial — it's quite common for customers to use PeerDB for homogeneous replication due to performance, manageability and observability issues with logical replication.

One potential value of extensions is Postgres compatibility for your queries. However, I'm curious about the SQL coverage on columnar tables — for example, advanced constructs like partitioned tables, prepared statements, correlated subqueries, RCTEs, triggers, and more. While it seems there’s ongoing work to improve compatibility, achieving full Postgres compatibility is challenging. In some cases, you might need to fall back to Postgres (instead of DuckDB), sacrificing performance for certain queries.

The biggest challenge we faced at Citus was the constant trade-off between Postgres compatibility and performance — both are complex and constantly moving targets. This was the key reason why Citus couldn't compete with purpose-built databases like Snowflake, ClickHouse, SingleStore. While DuckDB didn’t exist when we built Citus, it's still fundamentally different from Postgres. Even though the DuckDB dialect is somewhat similar to Postgres (as is Snowflake’s), retrofitting two distinct databases — each evolving independently for world's best performance — isn't trivial.

In short, relying on ETL (logical replication) without providing full Postgres compatibility raises the question: is it better to lean on a purpose-built database with a laser focus on performance, or adopt a more generalized approach?

Anyway, I come from a different school of thought — using the right tool for the right job. That said, I love seeing all the progress and evolution in the Postgres community — Postgres will always be my first love!

saisrirampur··on Reliably replicating data between Postgres and 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!

saisrirampur··on Reliably replicating data between Postgres and ClickHouse
Planned failovers can be managed through maintenance windows by recreating replication slots before maintenance ends. Unplanned failovers require resyncs, which are heavily optimized with PeerDB (https://blog.peerdb.io/parallelized-initial-load-for-cdc-bas...).

With PG17+, this shouldn’t be an issue due to failover slots.

Another important observation: Aurora Postgres, which is used by many customers, persists slots during failover, so this isn’t a problem at all. PeerDB has built-in retries that resume from the last committed source LSN.

saisrirampur··on Reliably replicating data between Postgres and ClickHouse
I think it's more about convenience / operational simplicity — managing multiple ingestion pipelines with potentially 100s of tables at a production grade isn’t trivial and adds significant overhead. This also includes ensuring data consistency across both targets, monitoring lag, and so on.

Secondly, OLAP/DWH systems aren’t as friendly as OLTP databases when it comes to UPDATEs/DELETEs. You can’t just perform point UPDATEs or DELETEs and call it a day. So why not let a replication tool handle this for you in the most efficient way.

saisrirampur··on Reliably replicating data between Postgres and ClickHouse
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.

saisrirampur··on Reliably Replicating Data Between PostgreSQL and ClickHouse
Great summary and spot on! Once you reach that TBs scale Postgres is hard to scale. Yes, you could make Postgres scale to larger scales but it would need deep expertise and architecting and the experience wouldn’t be “it just works”. Ex-Citus here, we had PB scale deployments which needed multiple months of effort to implement and an expert team to manage. Eventually many (ex: CloudFlare, Heap) migrated to purpose built stores like ClickHouse, Singlestore. And not to forget storage costs. Sure there was compression/columnar in Postgres/Citus too - but it didn’t fare well compared to pure columnar stores.

(Disclaimer: This is Sai from ClickHouse/PeerDB team)

saisrirampur··on How to use Postgres for everything
TL;DR from many comments: just don’t do it! I’m glad this is becoming mainstream, and people are realizing that Postgres for everything doesn’t work. Don’t get me wrong—I’m a huge Postgres proponent and have spent 10 years helping customers implement it. However, I’m a strong believer in using Postgres for what it’s designed for in first-place.

Postgres was designed as a row-based OLTP database, with over 30 years of effort dedicated to making it robust for that use case.I know there are many extensions attempting to make Postgres support other use cases, such as analytics, queues, and more. Keep in mind that these extensions are relatively recent and aim to retrofit new capabilities onto a database primarily designed for transactional workloads. It’s like adding an F1 car engine to a Toyota Camry — will that work?

Extensions also have many issues—they are not fully Postgres-compatible. In Citus, for example, we added support for the COPY command four years into the company, and chasing SQL coverage was a daily challenge for 10 years. Being unable to use the full capabilities of Postgres and having to work around numerous unsupported features defeats the purpose of being a Postgres extension.

On the other hand, you have purpose-built alternatives like ClickHouse and Snowflake for analytics, Redis for caching, and Kafka for queues. These technologies have benefited from decades of development, laser-focused on supporting specific use cases. As a result, they are robust and highly efficient for their intended purposes.

I often hear that these Postgres extensions are expanding the boundaries of what Postgres can do. While I partly agree, I also question the extent to which these boundaries are truly being expanded. In this era of AI, where data is growing exponentially, handling scale is critical for any technology. These boundaries will likely be broken very quickly.

Take queues as an example: you have a purpose-built technology like Kafka or a Postgres extension that supports queues. For an early-stage startup, adopting a less optimized Postgres-based solution may (not a guarantee) save a few weeks of initial CapEx costs compared to using an optimized solution like Kafka. However, 6 to 12 months later, you may find yourself back to square one when the Postgres-based queue fails to scale. At that point, migrating to a purpose-built technology becomes an arduous task—your system has grown, and now it may take months of effort and a larger team to make the switch.

Ultimately, this approach can cost more time and money than starting with a purpose-built solution from the beginning, which might have only required a few extra weeks of CapEx. I’ve seen this firsthand at Citus, where customers like Cloudflare and Heap eventually migrated to purpose-built databases like ClickHouse and SingleStore respectively. While these migrations happened a few years later, times have changed — data grows faster now, and the need for a purpose-built database arises much sooner. It’s also worth noting that Citus was an incredible piece of technology that required years of development before it could start making a real impact.

TL;DR: Please think carefully before choosing the right technology as you scale. Cramming everything into Postgres might not be the best approach for scaling your business.

saisrirampur··on 7 Databases in 7 Weeks for 2025
Sai from ClickHouse here. Have been living and breathing past year helping customers integrating Postgres and ClickHouse together. Totally agreed with this statement - there are numerous production grade workloads solving most of their data problems using these 2 purpose-built Open Source databases.

My team at ClickHouse has been working hard to make the integration even seamless. We work on PeerDB, an open source tool enabling seamless Postgres replication to ClickHouse https://github.com/PeerDB-io/peerdb/ This integration is now also natively available in the Cloud through ClickPipes. The private preview was released just last week https://clickhouse.com/cloud/clickpipes/postgres-cdc-connect...

saisrirampur··on Ask HN: How did you scale your analytics workloads (Postgres)?
It depends on the scale - If you expect to scale to at least a few hundred GBs a (to multiple TBs), ClickHouse would be a safe and proven option. The Postgres + ClickHouse duo is a common pattern adopted by production-grade customers as their default data stack to solve transactional (OLTP) and analytical (OLAP) use cases.

ClickHouse also recently released Postgres CDC connector in ClickPipes for a seamless integration of Postgres. Now you can stream Postgres data into ClickHouse within a few minutes. https://clickhouse.com/cloud/clickpipes/postgres-cdc-connect... This was a result of the acquisition of PeerDB, an open-source Postgres CDC product - https://clickhouse.com/blog/clickhouse-welcomes-peerdb-addin...

Disclaimer: This is Sai from ClickHouse/PeerDB here. However, the answer above is based on multiple years of customer experiences. :)

saisrirampur··on pg_flo – Stream, transform, and re-route PostgreSQL data in real-time
Great question! PeerDB can be just run on a single EC2 instance (using either Docker or Helm charts). A typical production-grade setup could use 4 vCores with 16GB RAM, You can scale up or down based on resource usage for your workload.

To elaborate more on the architecture (https://docs.peerdb.io/architecture), the flow-worker does most of the heavy lifting (actual data movement during initial load and CDC), while the other components are fairly lightweight. Allocating around 70-80% of provisioned resources to the flow-worker is a good estimate. For Temporal, you could allocate 20-25% of resources and distribute the rest to other components.

Our open-source offering (https://github.com/PeerDB-io/peerdb) supports multiple connectors (CH and non-CH). Currently, there aren’t any plans to make changes on that front!

saisrirampur··on pg_flo – Stream, transform, and re-route PostgreSQL data in real-time
Sai from PeerDB here. Temporal has been very impactful for us and a major factor in our ability to build a production-grade product that supports large-scale workloads.

At a high level, CDC is a complex state machine. Temporal helps building the state machine taking care of auto-retries/idempotency at different failure points and also aids in managing and observing it. This is very useful to identify root causes when issues arise.

Managing Temporal shouldn’t be complex. They offer a well-maintained, mature Docker container. From a user standpoint, the software is intuitive and easy to understand. We package the temporal docker container in our own Docker setup and have it integrated into our Helm charts. We’ve quite a few users smoothly using Enterprise (that we open sourced recently) and standard OSS!

https://github.com/PeerDB-io/peerdb/blob/main/docker-compose...

https://github.com/PeerDB-io/peerdb-enterprise

Let me know if there are any questions!

saisrirampur··on ClickHouse Data Modeling for Postgres Users
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.
saisrirampur··on ClickHouse Data Modeling for Postgres Users
Great feedback, thanks for chiming in rnavi. We will add this (contributing to AirByte) as a potential item in our roadmap.
saisrirampur··on Change Data Capture (CDC) Tools should be database specialized not generalized
Ack, true VC-backed startup optimize for TAM and therefore go more generalized. At PeerDB, we were still focused on just Postgres and were expanding from CDC to other ETL/data-movement use cases for Postgres, such as Active<>Active, Database migrations etc. With the pace at which Postgres is growing, I believe that ETL for Postgres can meet that billion-dollar TAM. Anyway, we were recently acquired by ClickHouse and are now doubling down on providing a world-class CDC experience from Postgres to ClickHouse. :)
saisrirampur··on pg_duckdb: Splicing Duck and Elephant DNA
Excited about the work here. However, my 2 cents - for this to become a reality (serious production use at scale), I don’t think it’s just based on the choice of the analytical engine (here, DuckDB), but rather on how well the Postgres extension is built. The Postgres extension framework is complex, still maturing, and doesn’t offer full flexibility to implement features. We saw this closely at Citus. It was a deterrent to competing with native analytical databases like ClickHouse and Snowflake. A bunch of customers, including CloudFlare and Heap, switched from Citus to ClickHouse and SingleStore, respectively. This was one of the inspirations to start PeerDB , to make it magical for customers to move data from Postgres to native and purpose-built analytical databases like ClickHouse.

Being a Postgres fan, Good luck and best wishes with the effort here!

saisrirampur··on Postgres.new: In-browser Postgres with an AI interface
Super cool. Great work team! Love the deploy feature to deploy the entire playground to the cloud and get a connection string. Helps devs get started with Postgres projects very quickly.
saisrirampur··on ClickHouse acquires PeerDB to expand its Postgres support
Great question! I'm expecting it to support parent/child tables too as the way we implemented partitioned table support is querying the pg_inherits metadata table - https://github.com/PeerDB-io/peerdb/blob/2d30e5fae887552f93c... However, inheritance (old way of partitioning) isn't a common thing with Postgres. Out of 100s of workloads I've seen in the past decade, it came up a couple of times...
saisrirampur··on ClickHouse Acquires PeerDB
Thanks for posting this question! I previously worked at Citus for 8 years, where we tried to bring real-time analytical capabilities to Postgres. It was common to see POCs go sideways, and several customers (including CloudFlare and Heap) moved from Citus to an analytics-specialized database. For example, CloudFlare moved from Citus to ClickHouse. This was one of the inspirations for me to build a company (PeerDB) that brings specialized OLTP and OLAP databases together.

This is not to say that Postgres cannot support larger-scale analytical workloads, but it will take time. ClickHouse has taken 10 years of effort and development to get where it is now.

I would love to understand how tablespace performs at scale in production workloads. Are there any references that you could share. :)

saisrirampur··on Speedup of deletes on PostgreSQL
Nice post! Love the unique insight here. TL;DR Create indexes on the foreign key columns of the table on which the foreign keys are defined.
saisrirampur··on Show HN: Porter Cloud – PaaS with an eject button
Crunchy Bridge seems to make the data migration simple too - https://docs.crunchybridge.com/migrate/heroku_dump_restore
saisrirampur··on Show HN: Peerdb Streams – Simple, native Postgres change data capture
Yes, that is doable through a very simple Lua-based row-level transformation. :) Here is an example transformation that adds an additional field: https://github.com/PeerDB-io/examples/blob/main/usdcad.lua . More details on row-level transformations are planned to be released later this week. :)
saisrirampur··on Show HN: Peerdb Streams – Simple, native Postgres change data capture
Quite a bit of work is going on to support failover slots in upstream - https://github.com/search?q=repo%3Apostgres%2Fpostgres+failo... From what I understood it could get into Postgres 17 (released later this year) if there are no big issues. :)
← PreviousPage 3 of 6Next →