saisrirampur

(Most) CRUD/OLTP applications don't delete data by timestamp; they delete by primary key. For those workloads, DROP TABLE (or dropping a partition) isn't a palatable option.

The entire premise here is really about time-series workloads where most operations are based on a timestamp. In those apps partition dropping has been a standard and recommended retention strategy for years. That's precisely why extensions like pg_partman and TimescaleDB exist. Given that context, the title feels more clickbaity, and could easily mislead readers into thinking this applies broadly to OLTP systems when it doesn't;

Partially true but too much of a blanket statement and clickbaity.

DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows.

DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statements following it, because of how lock queues work in Postgres. And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app.

It depends on the use case. For real-time, customer-facing analytics, ClickHouse’s MergeTree engine is a natural fit, so a Postgres → ClickHouse CDC setup with low latencies (single-digit seconds) is better.

Replication to Iceberg/S3 is better suited for offline analytics and data warehousing use cases. You can use the same ClickHouse engine to query layer Iceberg data in S3.

Great progress, guys! It’s impressive to see all the enhancements - more types, more aggregate functions, cross-node DML, resharding, and reliability-focused connection pooling and more. Very cool! These were really hard problems and took multiple years to build at Citus. Kudos to the shipping velocity.

Ack, makes sense. When making public posts that reach a large audience, it’s better to be clear with the messaging rather than risk it being blurry for even a subset of the audience, especially if that could lead to misguidance and affect their day-to-day work.

Ack, agreed. But there’s a better way to communicate than making blanket statements like “just use Postgres.” For example, you could say “Postgres is the default database,” etc.

Don’t get me wrong—I’m a huge huge fan of Postgres. I’ve worked at Postgres companies for a decade, started a Postgres company, and now lead a Postgres service within a company! But I prefer being real rather than doing marketing hype and blanket love.

That wasn’t my intention though to mention my workplace multiple times for the sake of PR. Try to avoid being that, atleast on hn. :)

Agree to disagree here. I see a world where developers need to think about (reasonable) scale from day one, or at least very early. We’ve been seeing this play out at ClickHouse - the need for purpose-built OLAP is reducing from years to months. Also integration with ClickHouse is few weeks of effort for potentially significantly faster performance for analytics.

I personally see a difference between “just use Postgres” and “make Postgres your default choice.” The latter leaves room to evaluate alternatives when the workload calls for it, while the former does not. When that nuance gets lost, it can become misleading for teams that are hitting or even close to hitting—the limits of Postgres, who may continue tuning Postgres spending not only time but also significant $$. IMO a better world is one where developers can have a mindset of using best-in-class where needed. This is where embracing integrations with Postgres will be helpful!

I’m a huge Postgres fan. That said, I don’t agree with the blanket advice of “just use Postgres.” That stance often comes from folks who haven’t been exposed enough to (newer) purpose-built technologies and the tremendous value they can create

The argument, as in this blog, is that a single Postgres stack is simpler and reduces complexity. What’s often overlooked is the CAPEX and OPEX required to make Postgres work well for workloads it wasn’t designed for, at even reasonable scale. At Citus Data, we saw many customers with solid-sized teams of Postgres experts whose primary job was constant tuning, operating, and essentially babysitting the system to keep it performing at scale.

Side note, we’re seeing purpose-built technologies show up much earlier in a company’s lifecycle, likely accelerated by AI-driven use cases. At ClickHouse, many customers using Postgres replication are seed-stage companies that have grown extremely quickly. We pulled together some data on these trends here: https://clickhouse.com/blog/postgres-cdc-year-in-review-2025...

A better approach would be to embrace the integration of purpose-built technologies with Postgres, making it easier for users to get the best of both worlds, rather than making overgeneralized claims like “Postgres for everything” or “Just use Postgres.”

Great question! It really depends on the workload. We already support NVMe instances as small as 4 GB RAM / 2 vCPUs. For HA setups, you could go with one standby (with configurable synchronous replication) or two standbys (cross-AZ, with quorum-based replication). So yes, there is some additional cost from a hardware perspective due to the standbys, but depending on the workload, NVMe performance could offset those costs. On top of this, there’s a separate topic around the reliability/availability promises of separating storage and compute for an OLTP Postgres database.

Very interesting question. Depends on the use-case, have seen quite a few workloads where logical replication gets throttled on I/O (reorder buffer) where NVMe based disk access should help a lot. This happens specifically when there are larger or interleaved transactions. We plan to test this at production scale soon. Stay tuned for more learnings!

Thanks for posting this question! Compared to Snowflake and Databricks, a few key differences in our approach are:

(a) An initial focus on real-time, customer-facing applications rather than trying to boil the ocean. This also aligns with where the Postgres + ClickHouse combination has really shined for our users. Both Postgres and ClickHouse are designed primarily with developers building their system of record applications.

(b) Every component in the stack is open source—Postgres, ClickHouse, PeerDB for native CDC, pg_clickhouse, and Ubicloud Postgres (our data plane component). We plan to keep it that way as much as possible, as this strongly aligns with our ethos.

(c)Third, as you noted, Postgres is NVMe-backed and the focus is on performance and scalability, while maintaining top-notch reliability. We think that this more meaningful to fast-growing (AI-driven) workloads than instant provisioning and forking. I talk about this a bit more here - https://clickhouse.com/blog/postgres-managed-by-clickhouse#p...

Thank you for chiming in. Pricing is still TBD and will be finalized in the coming months before the service goes to GA. At a high level we plan to keep competitive also try to make it inclusive of the integration features too (native CDC + pg_clickhouse). Stay tuned!

Lovely article! Thanks for writing this one. Really love your motivation for the move from Timescale to ClickHouse - basically wanted to give ClickHouse a try! :) And later found it easy and useful.

This is what happened to me too when I spent past decade working for to Postgres companies and later started working for ClickHouse. Realized that there is another amazing database technology which is equally cooler and useful to the world and for a different use-case (analytics).

You should try PeerDB, it was acquired by ClickHouse for exactly this use-case - Fast, simple Postgres replication to ClickHouse. https://github.com/PeerDB-io/peerdb

In ClickHouse Cloud, you have ClickPipes which is a simpler/managed manifesation of PeerDB https://clickhouse.com/cloud/clickpipes/postgres-cdc-connect...

Great question! I believe this behavior is by design in logical decoding. Based on my reading and previous chats with committers, this is my understanding: logical decoding is not stateless, and on reconnection it loses the current transaction state (open transactions, subtransactions, snapshots, catalog state, etc.) that is required for decoding. As a result, a reconnection triggers reading WAL from restart_lsn in order to reconstruct that state.

There may be room for improvement in PostgreSQL, by persisting this state to help these reconnections, but I think this is non-trivial and complex than I think, because of the guarantees PostgreSQL must provide around correctness, consistency and reliability.

Also, based on what I’ve read, physical replication does not have this issue because it directly ships WAL files (instead of contructing a txn) and reconstructs state on the standby.

I’ll let PostgreSQL committers/contributors chime in too on this for a more precise analysis. :)

Appreciate the feedback here. This wasn’t marketing — just an unintentional miss on my part. https://news.ycombinator.com/item?id=46392372

(this is Sai, the author of the post and also PeerDB co-founder)

The wording in that post was an unintentional miss on my part. Apologies for that. We’ve just fixed it. Thanks for flagging it!

To add some context, PeerDB was originally released under ELv2 well before the acquisition. During the acquisition we made a choice to keep the project as-is rather than change its license, so this wasn’t a new decision made at that time — just continuity with how the project already existed.

We appreciate the feedback, around integration and downstream OSS adoption. That overall makes sense. We’ll take it into account as we think about licensing going forward.

Separately, I really wish you tried PeerDB out. The ease-of-use and performance around larger Postgres datasets (TBs to 10s of TB) would’ve been something you would have probably appreciated. That is something we optimized a lot on over that last few years. May be sometime in the future! :)

You should just give it a shot in 10-15 min and see how it looks with ClickHouse. We made it that simple with ClickPipes :). Don’t intend to sell here, but it is as simple as signing up for trial on ClickHouse Cloud and clicking a few buttons and start seeing PG data getting synced.

In regards to footguns with replication, totally understand you being cautious. Last 2 years at PeerDB/ClickPipes was laser focused on just Postgres CDC to provide a dead simple yet highly reliable experience. The product has 100s of features, addresses 100s of footguns and actively being enhanced. Sharing some customers using this production https://clickhouse.com/blog/postgres-cdc-year-in-review-2025... You should give it a shot to see how easy it is. :)

In regards to sample apication, here is one, https://github.com/ClickHouse/HouseClick It showcases PG + CH stack. We just merged a PR to integrate pg_clickhouse too. The good news is that, there is a blog planned in a couple of weeks which showcases a tightly integrated experience PG +CH with CDC and pg_clickhouse, all in OSS. It will have docker-compose too. Your question adds up to what we are thinking next, I couldn’t resist myself to reveal it. ;) :)

Great questions! ClickHouse is a purpose-built analytical database with thousands of optimizations for analytics, which is why it’s typically faster and more scalable than TimescaleDB. Here’s a post that covers real scenarios where users have moved workloads from Timescale to ClickHouse: https://clickhouse.com/blog/timescale-to-clickhouse-clickpip...

If your operational (OLTP) tables are reasonably big, the recommended approach is to replicate them into ClickHouse and let ClickHouse handle the joins. This avoids cross-database joins and lets the execution be pushed fully into ClickHouse. You can use ClickPipes/PeerDB to make that super easy. https://clickhouse.com/docs/integrations/clickpipes/postgres... https://clickhouse.com/docs/integrations/clickpipes/postgres

Where pg_clickhouse fits: If you’re already using Postgres for OLTP and want to offload analytics to ClickHouse without rewriting your app, the pg_clickhouse extension helps. It lets you run OLTP and OLAP queries from Postgres, while pushing the analytical queries—and their joins—down to ClickHouse, where the replicated data lives. Going native i.e. querying ClickHouse directly for OLAP will be the most optimal and is recommended if your analytics is advanced/complex. We will be evolving pg_clickhouse over the coming months to support pushdown for more and more complex/advanced queries :)

Thank you, Paul! Great to see Supabase wrappers evolve. I really love the async streaming feature. It helps address use cases involving (reliably) moving larger datasets from ClickHouse to Postgres for supporting (stricter) transactional workloads.

Very excited to continue working closely to further integrate these amazing open source database technologies and make it easier for users. :)

Totally! Making things way easier on the app and query side is very important, which is why we plan to invest heavily in this going forward.

With respect to data replication, it gets really hard and has its challenges as data sizes grow - reliably moving tens of terabytes at speed, handling intricate quirks around replication slots, enterprise-grade observability etc. PeerDB/ClickPipes is designed to solve these problems. I wrote a blog post covering this in more detail here: https://clickhouse.com/blog/postgres-cdc-year-in-review-2025

That said, point taken - we will ensure query and app migration is seamless as well and reduce friction in integrating Postgres and ClickHouse. pg_clickhouse is a step in that direction! :)

More on use-cases involving TimescaleDB replication/migration to ClickHouse https://clickhouse.com/blog/timescale-to-clickhouse-clickpip...

I love DuckDB from a product perspective and appreciate the engineering excellence behind it. However, DuckDB was primarily built for seamless for in-process analytics, data science, data-preparation/ETL workloads than real-time customer facing analytics.

ClickHouse’s bread and butter is real-time analytics for customer-facing applications, which often come with demanding concurrency and latency requirements.

Ack, totally makes sense that both are amazing technologies - you could try both and test them at the scale your real-time application may reach, and then choose the technology that best fits your needs. :)

Great question! If you’re starting a greenfield application, pg_clickhouse makes a lot of sense since you’ll be using a unified query layer for your application.

Now, coming to your question about replication: you can use PeerDB (acquired by ClickHouse https://github.com/PeerDB-io/peerdb), which is laser-focused and battle-tested at scale for Postgres-to-ClickHouse replication. Once the data is replicated into ClickHouse, you can start querying those tables from within Postgres using pg_clickhouse. In ClickHouse Cloud, we offer ClickPipes for Postgres CDC/replication, which is a managed service version of PeerDB and is tightly integrated with ClickHouse. Now there could be non-transcational tables that you can directly ingest to ClickHouse and still query using pg_clickhouse.

So TL;DR: Postgres for OLTP; ClickHouse for OLAP; PeerDB/ClickPipes for data replication; pg_clickhouse as the unified query layer. We are actively working on making this entire stack tightly integrated so that building real-time apps becomes seamless. More on that soon! :)

Appreciate you chiming in! We evaluated almost all the FDWs and landed on clickhouse_fdw (built by Ildus) as the most mature option. However, it hadn’t been maintained since 2020. We used it as the base, and the goal is to take it to the next level.

Our main focus is comprehensive pushdown capabilities. It was very surprising to see how much the Postgres FDW framework has evolved over the years and the number and types of hooks it now provides for push down. This is why we decided to lean into FDW than build an extension bottoms up. But we may still do that within pg_clickhouse for a few features, wherever FDW framework becomes a restriction.

We’ve made notable progress over the last few months, including support for pushdown of custom aggregations and SEMI JOINs/basic subqueries. Fourteen of twenty-two TPCH queries are now fully pushdownable.

We’ll be doubling down to add pushdown support for much more complex queries, CTEs, window functions, and more. More on the future here - https://github.com/ClickHouse/pg_clickhouse?tab=readme-ov-fi... All with the goal of enabling users to build fast analytics from the Postgres layer itself but still using the power of ClickHouse!

Good idea! Btw, ClickHouse does provide a HTTP interface directly, too! https://clickhouse.com/docs/interfaces/http

Sai from ClickHouse here. Very compelling story! Really love your emphasis on using the right tool for the right job - power of row vs column stores.

We recently added a MySQL/MariaDB CDC connector in ClickPipes on ClickHouse Cloud. This would have simplified your migration from MariaDB.

https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql/so...

Sai from ClickHouse here. Adding to above, we just released a blog that presents JOIN benchmarks of ClickHouse against Snowflake and Databricks. This is after the recent enhancements made to the ClickHouse core. https://clickhouse.com/blog/join-me-if-you-can-clickhouse-vs.... The benchmarks is around 2 dimensions of both speed and cost.