OLTP is expensive, you can ClickHouse it.

Summary

PostgreSQL’s ACID guarantees cause substantial write amplification: each write flushes WAL for durability, copies rows via MVCC for isolation, and updates every affected index, amplifying 100 bytes of row change into tens of kilobytes of I/O. Costs scale linearly with data size, and even sharded Postgres offers only linear scaling with added coordination overhead, so growing datasets drive rising bills. The post proposes offloading settled, rarely-changing data to ClickHouse, whose columnar compression shrinks datasets 10-30x and whose cloud offering costs about 5x less than comparable Postgres infrastructure while also reducing Postgres compute load through fewer indexes. ClickHouse builds indexes in background batches and runs aggregations at roughly 100x lower cost than Postgres. ClickHouse Cloud provides WalShadow for millisecond-latency CDC and pg_clickhouse FDW, enabling an application pattern that cuts the cost of serving settled data by an order of magnitude.

Takeaways

1. 1

   Postgres’s ACID guarantees cause significant write amplification through WAL, MVCC, and index updates, making it costly to store data that has stopped changing.

2. 2

   ClickHouse’s columnar compression typically reduces dataset size by 10-30x, and its cloud offering costs roughly 5x less than comparable Postgres infrastructure for storage and compute.

3. 3

   ClickHouse Cloud provides WalShadow for millisecond-latency CDC and pg_clickhouse FDW, enabling transparent queries of historical data via the Postgres protocol.

FAQ