
OLTP is expensive, you can ClickHouse it
In this article we explore a new application pattern - offloading more of your Postgres data to ClickHouse. Leading to an order of magnitude better unit economics for your applications, as the data size increases.
以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。
In this article we explore a new application pattern - offloading more of your Postgres data to ClickHouse. Leading to an order of magnitude better unit economics for your applications, as the data size increases.
We start with what an Online Transactional Processing (OLTP) database like Postgres does on every write and read, and the costs it takes on to keep transactions ACID-compliant. We then show how to stop paying those costs for the data and queries that don’t need them.
Back in the 2000s Web and Mobile booms exponentially increased the dataset sizes the systems needed to process. At the time, many fast growing companies turned to NoSQL databases for scalability and efficiency, which SQL databases couldn’t provide at the time.
Over the following few years the question of operational databases' scalability was resolved. With the cloud advancements, the latest systems offer tens of terabytes on single-writer OSS PostgreSQL deployments. With some closed source forks scaling beyond 100 TB of storage without compromising OLTP guarantees. While systems like Spanner and sharded MySQL/Postgres allow web-scale companies to achieve transactional consistency at global scale.
The increased scalability comes with a big cloud infrastructure bill. The systems based on OLTP engines scale linearly at best. You pay N times more for Postgres, you get up to N times more storage and processing power. With ClickHouse Managed Postgres NVMe based architecture you can get better multipliers on transactional throughput.
Still though as your dataset sizes explode, the compute and storage costs grow linearly.
In 2026, thanks to the agentic AI boom, the data sizes and query volumes are rising faster than ever before, reaching tens of terabytes of transactional data hit earlier in the cycle, demanding a better economy for fast-moving companies.
We can look outside of the traditional OLTP box for a solution, just like the web companies in the 2000s did.
PostgreSQL here is used as the most popular OLTP database for new applications. Other operational database systems work with very similar mechanics.
PostgreSQL guarantees four fundamental transactional properties for all data it writes and reads: Atomicity, Consistency, Isolation, and Durability (ACID):
Guaranteeing ACID for complex, multi-row transactions while thousands run concurrently is a hard problem. Postgres solves it with a few mechanisms, and each one adds work to every write:
For durability, it flushes the transaction’s write-ahead log (WAL) record to disk before confirming each COMMIT. For isolation, it uses MVCC: an update never changes a row in place. Instead, it writes a complete new physical copy of the row and leaves the old copy behind as a dead tuple until VACUUM reclaims it. And so that a read right after a write sees the new data, every insert or update also modifies every affected index entry.
Add it all up, and the amplification is steep: to change ~100 bytes of row data, Postgres modifies several 8 KB heap and index pages in the buffer pool and eventually writes tens of kilobytes to disk as WAL, full-page images and page flushes
That is not just the write path. When the system fully supports ACID, data formats are built for that too, and the reads involve additional costs:
Here is a simplified view of and write and read amplifications in PostgreSQL database, when the dataset exceeds memory buffers:
Loading video...
Distributed Postgres won’t help #
Shared storage and sharded systems are designed to allow linear scale with the data size and workload.
For a sharded PostgreSQL system, with some investment in schema, you can achieve near linear scalability of data size and transactional capability as you throw more dollars.
In the real world, most applications get diminishing returns due to costs of additional coordination and occasional multi-phase cross shard commits.
Shared storage solutions incur more infrastructure costs to process the workload as more parts of the distributed system need redundancy. The additional costs then amortised with aggressive multi-tenant packing on the storage side.
Even when additional optimizations and cheaper object storage are taken into account, those systems generally cost more than simple OSS deployments for production workloads.
Many applications across different business domains treat actively changed and settled data differently. Active state and recent rows frequently used to write new transactions, read by users and integration systems. After certain window or activity criteria passed - the data stays mostly read-only, accessed infrequently while still needs to be preserved.
Here are some examples across different business application domains:
In all those examples, once data moved from mutate-heavy to settled state, the need for ACID subsided. Though, the data incurs associated costs, as long as maintained in the OLTP storage.
ClickHouse is a SQL database engine. It can process any SQL workload your application needs a database for.
ClickHouse isn’t optimized for high-frequency ACID transactions, and that changes the economics completely:
Few applications accept that for data that’s actively changing. But once the data settles, why pay 5–10x to keep it ready for updates that never come?
Bonus: Fewer indexes, lighter maintenance, means less PostgreSQL compute costs.
(Janitor AI CEO shares a success story on offloading settled data to ClickHouse at CH South Bay Meetup)
Building an application using 2 databases, keeping data in sync and system complexity under control is a challenging task. ClickHouse cloud is built to make this easy.
Our mission at ClickHouse is to deliver best in class price-performance for both analytical and transactional workload.
Unified OLTP+OLAP stack on ClickHouse Cloud is optimized to get most out of the strengths of Postgres and ClickHouse, and provide a seamless integration experience:
You can build this pattern on ClickHouse Cloud today and cut the cost of serving settled data by an order of magnitude. Next on our roadmap is a fully managed experience that offloads settled data to ClickHouse for you.
Interested in seeing how ClickHouse Managed Postgres works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.
正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。