 |
VOOZH | about |
|
VOOZH | about |
We’re so glad you’re here. You can expect all the best TNS content to arrive Monday through Friday to keep you on top of the news and at the top of your game.
Check your inbox for a confirmation email where you can adjust your preferences and even join additional groups.
Follow TNS on your favorite social media networks.
Become a TNS follower on LinkedIn.
Check out the latest featured and trending stories while you wait for your first TNS newsletter.
Postgres is a common pick to bootstrap an application because it’s well known, flexible and dependable. Its flexibility means it can handle most things you throw at it, for a time. As an application scales, Postgres is often pushed to its limits by workloads it wasn’t built for.
The point at which an app reaches these limits hasn’t changed, but the time taken to reach that point has dramatically reduced thanks to AI.
One pattern that has emerged to address this is combining Postgres with ClickHouse. In this architecture, Postgres continues to serve transactional workloads, while ClickHouse handles analytics. Both databases are open source, and an ecosystem has developed that brings them closer together.
In the AI era, growth that used to unfold over years now happens in months. Developers are reaching the limits of Postgres much sooner because AI-driven workloads accelerate product development, data creation and analytical demand.
This trend isn’t limited to internal dashboards or offline reporting. More often, it’s affecting user-facing applications. Real time dashboards, recommendation systems and search over large datasets all depend on fast analytical queries. Once these features become part of the user experience, the architecture has to support low-latency access to high-volume data, and Postgres alone is not enough.
Building an application that uses both Postgres and ClickHouse usually involves two main challenges. The first is data integration, meaning how to move the right data into the right database. The second is application integration, meaning how to ensure the application knows which database to query for each operation.
There are two common patterns for integrating ClickHouse with PostgreSQL.
Split or dual-write: Applications write data directly to PostgreSQL and ClickHouse based on the specific use case. The split-write pattern writes data only to the database that needs it, while the dual-write pattern sends all data to both systems simultaneously. This approach works well when there is clear delineation in what data is used for. For example, it’s unlikely that telemetry or user tracking events need to be sent to Postgres when they are likely only used for analysis. Supporting this pattern means updating the application to send data to the right database.
Change data capture (CDC): All writes occur in PostgreSQL, which remains the source of truth. A CDC process streams inserts, updates and deletes into ClickHouse so analytical queries always reflect the latest state without placing extra load on the transactional database. This pattern fits operational analytics use cases, where consistency is essential but analytical performance remains a priority. It allows teams to maintain transactional guarantees in PostgreSQL while scaling analytical queries independently in ClickHouse.
The goal of integrating Postgres and ClickHouse is to use each database for the workloads it is strongest at. This means that some queries will remain on Postgres, and some will be moved to ClickHouse.
Many apps use object relational mappers (ORMs) with Postgres, but this is less common with analytical databases. There are some open source projects like MooseStack, which can provide an ORM-like experience for ClickHouse. More commonly, the integration uses ClickHouse native language clients.
An integration will begin by identifying the queries that will move, such as any queries that are doing large aggregate queries. The API routes for these queries will need to be updated to send the SQL to ClickHouse. It’s possible to use a backward-compatible pattern that allows for these routes to be swapped to and from Postgres or ClickHouse during testing. This pattern is used by clickhouse.build, an agentic CLI that can automatically migrate TypeScript codebases to use Postgres and ClickHouse for prototyping.
An alternative approach can be to use a foreign data wrapper (FDW) inside Postgres, which allows queries to be sent to Postgres as-is and pushed down to ClickHouse transparently. This reduces the amount of work needed to start using Postgres and ClickHouse together, though can sacrifice some control over the integration.
The Postgres and ClickHouse ecosystem has grown into a well-established stack. Many teams now pair the two databases by default, and a set of mature open source and commercial tools make this architecture straightforward to operate at production scale. The focus of these tools is narrow and intentional: reliable Postgres replication, fast ingestion into ClickHouse and smooth integration with existing Postgres workflows.
PeerDB is an open source project that delivers high-throughput PostgreSQL CDC and reliable replication into ClickHouse. It supports large update streams, handles schema changes and avoids putting load on the transactional database. PeerDB also underpins managed services like ClickPipes for ClickHouse Cloud.
The PostgreSQL extension model helps teams shift analytical workloads to ClickHouse without changing their application code. FDWs make this possible by exposing external systems as regular PostgreSQL tables. Supabase’s ClickHouse FDW, the open source clickhouse_fdw, and similar extensions let applications continue issuing familiar SQL through Postgres while the heavy analytical queries run in ClickHouse. This keeps the application layer untouched and provides a smooth path for moving analytics off Postgres as workloads grow.
Projects like MooseStack show that developer tooling is keeping pace. They make it easier to use ClickHouse in environments where ORMs or schema-first development patterns are standard.
Overall, the ecosystem around Postgres and ClickHouse is not just a collection of tools. It is a focused, well-adopted stack designed for teams that outgrow a single online transaction processing (OLTP) database and need a fast analytical engine without losing the familiar Postgres development workflow.
Today, many applications start with Postgres and then adopt ClickHouse after the cracks appear. As this timeline shrinks, adopting this architecture makes more sense from the beginning of the product life cycle. Developers should be able to start with Postgres + ClickHouse out of the box with minimal impact to product velocity.
Managed services, hosted replication and deeper integrations across tools are already moving in this direction. The goal is a seamless experience where transactional and analytical systems work together by default.
The core principle remains unchanged: Postgres and ClickHouse are not competing technologies. They complement each other and together form the foundation of a modern open source data architecture that is flexible, transparent and ready for production.
