We [recently launched a managed service that brings PostgreSQL and ClickHouse](https://clickhouse.com/blog/postgres-managed-by-clickhouse) together as a single, unified data stack. The goal is simple: let teams run transactional and analytical workloads side by side without stitching together multiple systems or maintaining complex pipelines. This launch reflects a pattern we have seen repeatedly in production. PostgreSQL remains the system of record, while ClickHouse handles analytics at scale.

> Start with PostgreSQL, scale with ClickHouse.

In this post, we step back from the managed service and focus on its open source foundations. We show how to build the same unified data stack using open source components, how the integration works in practice, and how to offload analytics to ClickHouse without rewriting applications or rebuilding pipelines.

<iframe width="768" height="432" src="https://www.youtube.com/embed/4hM1nKtXn04?si=MOzYhg6bmH649okI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Introducing an open source data stack

The Open Source unified data stack, [available on Github](https://github.com/ClickHouse/postgres-clickhouse-stack), is simple and built on top of four open source components:

-   [Postgres](https://github.com/postgres/postgres) is the primary database of the data stack acting as the system of records and handling all transactional workload.

-   [ClickHouse](https://github.com/ClickHouse/ClickHouse) is a purpose-built open source analytical database of the data stack. As data volume grows, this is the preferred database to handle analytical workload.

-   [PeerDB](https://github.com/PeerDB-io/peerdb) is an open source tool to stream data from PostgreSQL to Data warehouse, including ClickHouse. It uses change data capture (CDC) to replicate inserts, updates, and deletes into ClickHouse in near real-time.

-   [pg_clickhouse](https://github.com/ClickHouse/pg_clickhouse) is an open source postgres extension that runs analytics queries on ClickHouse directly from PostgreSQL without rewriting any SQL.

These four components come together to form the open source unified data stack. Below is a high-level overview of the stack.

![postgres-oss-diagram-1.jpg](/uploads/postgres_oss_diagram_1_e72845a9a9.jpg)

### Implementing the stack 

Running PostgreSQL and ClickHouse side by side is a well established pattern. Many teams use this architecture in production, and GitLab [described it publicly](https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-and-clickhouse/) as early as 2022. Depending on the workload, the implementation falls into two main patterns, Change data capture or split-writes.

#### Change data capture (CDC)

**Components**: Postgres, ClickHouse, PeerDB and pg_clickhouse (optional).

This approach is well suited for operational, real-time analytical workloads where analytics run directly on application data. Common use cases include retail platforms, financial systems, and CSM or CRM applications.

PostgreSQL remains the system of record. All writes go to PostgreSQL, while PeerDB streams inserts, updates, and deletes into ClickHouse using CDC. ClickHouse maintains a near real-time copy of the data, allowing analytical queries to run on the latest state without adding additional load to the transactional database.

Applications can continue to send both transactional and analytical queries to PostgreSQL thanks to pg_clickhouse which transparently offloads analytical queries to ClickHouse. This keeps application changes minimal. Alternatively, applications can query ClickHouse directly if needed.

**Customer examples**:  [Seemplicity](https://clickhouse.com/blog/seemplicity-scaled-real-time-security-analytics-with-postgres-cdc-and-clickhouse), [Sewer AI](https://clickhouse.com/blog/sewerai-sewer-management-at-scale)

![postgres-oss-diagram-2.jpg](/uploads/postgres_oss_diagram_2_9a4ab8724a.jpg)

### Split-writes

**Components**: PostgreSQL, ClickHouse, and pg_clickhouse (optional).

This pattern is commonly used for observability or event-based workloads, where analytical data consists of logs, metrics, or events. These datasets do not require transaction support and are written at high volume.

In this case, analytical data can be written directly to ClickHouse, or routed through PostgreSQL using pg_clickhouse when minimal application change is preferred. PostgreSQL is not the system of record for this data and does not need to store the full analytical dataset.

Querying follows the same model as the CDC approach. Analytical queries run on ClickHouse, either transparently offloaded from PostgreSQL via pg_clickhouse or issued directly to ClickHouse.

**Customer examples**: [Langfuse](https://clickhouse.com/blog/langfuse-and-clickhouse-a-new-data-stack-for-modern-llm-applications), [Langchain](https://clickhouse.com/blog/langchain-why-we-choose-clickhouse-to-power-langchain)

![postgre-oss-diagram-3.jpg](
/uploads/postgre_oss_diagram_3_4452977ed6.jpg)

## Get started locally

Whether you're implementing a new application or extending an existing PostgreSQL application, getting started locally is straightforward. The [Getting started guide](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#getting-started) explains how to run the stack locally. Once you have it running locally, you can simply connect your application to the [exposed PostgreSQL database instance](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#connect-to-postgresql).

At that point, it is no different than running it with your own PostgreSQL instance. You might stop here while your application is running just fine on PostgreSQL only. Then once you need to improve the analytical workload performance, it is very simple to do:

1.  [Create a database in ClickHouse](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#create-the-clickhouse-database) for replicated tables.
2.  [Replicate data](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#replicate-data-from-postgresql-to-clickhouse) from PostgreSQL using PeerDB.
3.  [Configure](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#configure-clickhouse-foreign-data-wrapper) the pg_clickhouse extension.
4.  Direct queries to the pg_clickhouse tables via  your [PostgreSQL connection string](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#configure-application-to-use-clickhouse-for-analytical-queries).

The application still relies on PostgreSQL as the primary database, making the required changes to the application code very minimal, just a new connection option. Note that you can also connect your application [directly to ClickHouse](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#connect-directly-to-clickhouse) if that works better for you.

The project provides an [end to end example](https://github.com/ClickHouse/postgres-clickhouse-stack?tab=readme-ov-file#sample-application) with a sample application that initially runs the entire workload in PostgreSQL. A script migrates the analytical workload in just a few steps.

## When to start with the stack

Starting with this setup makes sense when:

-   PostgreSQL is your primary database
-   Analytical queries are part of the product, not just offline reporting
-   You expect data volume or query complexity to grow 

As growth timelines shrink, waiting to "outgrow" PostgreSQL often means reacting under pressure. Starting with PostgreSQL and ClickHouse together avoids a disruptive transition. But even if you adopt the stack later, PostgreSQL remains the main interface, keeping changes contained.

## From open source to production-ready

The open source data stack provides a strong foundation, but many production environments require managed services with clear operational boundaries and predictable behavior. Running and upgrading databases, operating replication pipelines, and handling failures often become the limiting factors rather than the architecture itself.

We recently [launched a managed version of the unified data stack](https://clickhouse.com/blog/postgres-managed-by-clickhouse) that delivers the same architecture as a single, integrated experience under one ClickHouse Cloud account. 

Deployment, scaling, upgrades, and reliability are handled by the platform, removing the need to operate clusters or data pipelines manually. PostgreSQL by ClickHouse connects directly to ClickHouse through [ClickPipes](https://clickhouse.com/cloud/clickpipes), the managed alternative to PeerDB. Managed PostgreSQL also comes with the pg_clickhouse extension preinstalled and configured, allowing analytical queries to be offloaded to ClickHouse without rewriting applications.



To try ClickHouse's native Postgres service, sign up for Private Preview using this link.



Get started with our native Postgres Service

## A new baseline

PostgreSQL and ClickHouse are not competing databases. They are complementary systems designed for different workloads.

With mature CDC and query offload tooling, using both from the start no longer requires complex pipelines or duplicated application logic. PostgreSQL remains the system of record for transactions, while ClickHouse handles analytical queries efficiently as data volume and query complexity grow.

**The default open data stack is no longer a single database. It is PostgreSQL for transactions, ClickHouse for analytics, and a clean, well-defined bridge between the two.**

A ready-to-use open source data stack that combines PostgreSQL and ClickHouse to handle transactional and analytical workloads.

large_postgres-oss-banner.jpg

medium_postgres-oss-banner.jpg

small_postgres-oss-banner.jpg

thumbnail_postgres-oss-banner.jpg

postgres-oss-banner.jpg

PostgreSQL + ClickHouse as the Open Source unified data stack

[ClickPy, a free analytical Python download statistics platform](https://clickpy.clickhouse.com/), recently crossed an impressive milestone: the main dataset now contains more than 2 trillion rows. Each row in this dataset represents a single Python package download, and the historical data traces back to 2011. 

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
SELECT count() FROM pypi.pypi

┌───────count()─┐
│ 2214017475506 │ -- 2.21 trillion
└───────────────┘
</code></pre>

ClickHouse is the analytical database powering ClickPy, making it possible for Python enthusiasts to check their favorite Python package popularity or understand new trends in the Python ecosystem.

Reaching this scale with minimal ongoing maintenance highlights why ClickHouse works well as a long-term store for high-volume analytical data. Hitting the 2 trillion row mark gave us a reason to review the ingestion pipeline and make it more robust. 

Evolving a data ingestion pipeline at this scale is not a trivial task. The system needs to remain available, ingestion must continue uninterrupted, and changes have to be introduced carefully. As part of this work, we also looked closer at the historical data, which led us to uncover some discrepancies.

The rest of this post explains how we redesigned the ingestion pipeline and, later on, how we fixed historical data while keeping ClickPy running throughout the process.

## Replacing the legacy ingestion pipeline

The initial pipeline was built at a time when [ClickPipes](https://clickhouse.com/cloud/clickpipes) did not exist yet, therefore it required quite a bit of scripting. We described the original ingestion pipeline in this [blog post](https://clickhouse.com/blog/clickpy-one-trillion-rows#ingesting-the-data). 

Pypi download statistics [are publicly available](https://packaging.python.org/en/latest/guides/analyzing-pypi-package-downloads/#public-dataset) on a BigQuery table, and we have an automated BigQuery job that exports daily data to a Google Cloud Storage bucket. Finally, a custom script runs every day a ClickHouse insert statement to ingest data directly from the GCS bucket to ClickPy's main table, `pypi`.

![clickpy-2-trillion-rows-1.jpg](/uploads/clickpy_2_trillion_rows_1_2167e28828.jpg)

This approach worked, but at this scale, it left room for improvement. The main opportunity was to replace the custom ingestion script, shown as ClickLoad in the diagram, with ClickPipes.

Moving to ClickPipes removes several operational constraints introduced by a cron-based script:

-   Retries, backoff, and failure handling are built in instead of being hand-rolled
-   Pipeline state and progress are visible, which makes it easier to detect stalled or partial ingestions
-   Delegate ingestion pipeline maintenance to the ClickHouse team  
-   Changes to ingestion logic require fewer moving parts and less custom code

With ClickPipes in place, ingestion becomes a first-class part of the system rather than a separate process that needs to be monitored and maintained alongside it.

![clickpy-2-trillion-rows-2.jpg](/uploads/clickpy_2_trillion_rows_2_e9e6d6b383.jpg)

The challenge of replacing the custom script with ClickPipes is the scale at which we're operating. We can't afford to corrupt the data. The main table `pypi` contains more than 2 trillion rows, and we also have several materialized views built from the `pypi` table, which makes re-ingesting the data from scratch not an option.

## Hot swap with ClickPipes at scale

We adopted a staged approach to replace the custom ingestion script with ClickPipes while keeping risk low.

The first step was to isolate the new pipeline from production. We cloned all table schemas and materialized views from the `pypi` database into a separate database called `pypi_clickpipes`. This allowed us to validate ingestion, transformations, and aggregations without affecting existing queries or dashboards.

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
-- Create the database
CREATE DATABASE pypi_clickpipes;

-- Clone the table schemas
CREATE TABLE pypi_clickpipes.pypi AS pypi.pypi;
CREATE TABLE pypi_clickpipes.pypi_downloads AS pypi.pypi_downloads;
CREATE TABLE pypi_clickpipes.pypi_downloads_by_version AS pypi.pypi_downloads_by_version;
CREATE TABLE pypi_clickpipes.pypi_downloads_max_min AS pypi.pypi_downloads_max_min;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day AS pypi.pypi_downloads_per_day;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_installer AS pypi.pypi_downloads_per_day_by_installer;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version AS pypi.pypi_downloads_per_day_by_version;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_country AS pypi.pypi_downloads_per_day_by_version_by_country;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_file_type AS pypi.pypi_downloads_per_day_by_version_by_file_type;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_installer_by_type AS pypi.pypi_downloads_per_day_by_version_by_installer_by_type;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_installer_by_type_by_country AS pypi.pypi_downloads_per_day_by_version_by_installer_by_type_by_country;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_python AS pypi.pypi_downloads_per_day_by_version_by_python;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_python_by_country AS pypi.pypi_downloads_per_day_by_version_by_python_by_country;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_system AS pypi.pypi_downloads_per_day_by_version_by_system;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_day_by_version_by_system_by_country;
CREATE TABLE pypi_clickpipes.pypi_downloads_per_month AS pypi.pypi_downloads_per_day_by_version_by_system_by_country;
</code></pre>

With the clone table in place, we configured ClickPipes to ingest data from GCS to a new target table `pypi_raw`. ClickPipes automatically creates the table and infer the schema at the first ingestion. This table acts as a transient staging layer.

![CleanShot 2026-01-21 at 11.54.40.png](/uploads/Clean_Shot_2026_01_21_at_11_54_40_4c69f7d87b.png)

In the configuration, we intentionally use the `Null` engine for the target table. We do not need to persist raw rows in ClickHouse. Instead, a materialized view performs the transformation and writes directly to the `pypi` table, which contains the main dataset. 

<iframe width="768" height="432" src="https://www.youtube.com/embed/r-QQ4VEJN68?si=j8T2JGpTOs0vmJYg" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

A new materialized view encapsulates all transformation logic that previously lived in the custom script: field normalization, type conversions, and schema alignment. Moving this logic into ClickHouse makes it easier to reason about and evolve over time.

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
CREATE MATERIALIZED VIEW pypi_clickpipes.pypi_mv TO pypi.pypi
(
    `date` Date,
    `country_code` LowCardinality(String),
    `project` String,
    `type` LowCardinality(String),
    `installer` LowCardinality(String),
    `python_minor` LowCardinality(String),
    `system` LowCardinality(String),
    `version` String,
    `ci` Enum8('false' = 0, 'true' = 1, 'unknown' = 2),
    `filename` String,
    `libc` Tuple(
        lib LowCardinality(String),
        version LowCardinality(String))
)
AS SELECT
    toDate(timestamp) AS date,
    ifNull(country_code, '') AS country_code,
    ifNull(project, '') AS project,
    ifNull(tupleElement(file, 'type'), '') AS type,
    ifNull(tupleElement(installer, 'name'), '') AS installer,
    arrayStringConcat(arraySlice(splitByChar('.', ifNull(python, '')), 1, 2), '.') AS python_minor,
    ifNull(tupleElement(system, 'name'), '') AS system,
    ifNull(tupleElement(file, 'version'), '') AS version,
    CAST(ifNull(ci, 0), 'Enum8(\'false\' = 0, \'true\' = 1, \'unknown\' = 2)') AS ci,
    ifNull(tupleElement(file, 'filename'), '') AS filename,
    tuple(ifNull(tupleElement(tupleElement(distro, 'libc'), 'lib'), ''), ifNull(tupleElement(tupleElement(distro, 'libc'), 'version'), '')) AS libc
FROM pypi_clickpipes.pypi_raw
</code></pre>



Below is a high-level view of the data flow from BigQuery to ClickHouse using ClickPipes.

![clickpy-2-trillion-rows-3.jpg](/uploads/clickpy_2_trillion_rows_3_59c2c805bb.jpg)

While reworking the ingestion pipeline, we also took the opportunity to extend the dataset with new fields to address recent community feature requests [[1](https://github.com/ClickHouse/clickpy/issues/136),[2](https://github.com/ClickHouse/clickpy/issues/140)].

Once the staging table and materialized view were in place, configuring ClickPipes was straightforward. ClickPipes supports [continuous ingestion](https://clickhouse.com/docs/integrations/clickpipes/object-storage/gcs/overview#continuous-ingestion-lexicographical-order) and keeps track of which files have already been processed, but it does not support starting ingestion from an arbitrary point in time. To avoid re-ingesting the full historical dataset, we updated the BigQuery export job to write new data to a fresh GCS bucket. ClickPipes was then configured to read from this new location, allowing the new pipeline to start from the migration point.

We let the new ingestion pipeline run in parallel for several days and compared daily row counts between the production database pypi and the staging database pypi_clickpipes. Once the data matched, switching over was simple. We disabled the cron job that ran the custom script and updated the materialized view to write directly to `pypi.pypi`.

After the switch, we removed the cloned tables from the `pypi_clickpipes` database and kept only `pypi_raw`, `pypi_mv`, and the tables created and managed by ClickPipes. This results in a clean separation of concerns: the `pypi` database continues to serve ClickPy, while `pypi_clickpipes` is dedicated to ingestion and transformation logic.

## Discovering historical ingestion gaps

While validating the new ingestion pipeline, we noticed discrepancies between BigQuery (the source of data) and ClickHouse. When comparing daily row counts, some historical dates in ClickHouse were missing data.

At this scale, issues like this are easy to miss. Queries continue to work, and dashboards still render, but the results are quietly incorrect. Once identified, the challenge was to correct the data without rebuilding the full dataset or interrupting ongoing ingestion.

## Fix the past

The most direct way to fix the past data is to delete the affected days and re-ingest them from the source. However, column-oriented databases are optimized for fast inserts and analytical queries, not for modifying existing data. ClickHouse is no exception. That said, ClickHouse recently introduced [lightweight delete and update operations](https://clickhouse.com/blog/updates-in-clickhouse-1-purpose-built-engines), which made it possible to safely delete large ranges of data even on a multi-trillion-row table.

To fix a single day of data, we followed this sequence:

1.  Delete the affected day from all tables that aggregate data by day.
2.  Delete the affected day from the main pypi table.
3.  Temporarily drop materialized views that do not aggregate by day.
4.  Re-ingest the source data for the affected day into the pypi table.
5.  Recreate the materialized views that do not aggregate by day.
6.  Rebuild the tables populated by those materialized views.

The distinction between daily and non-daily aggregations is important. For tables that group data by day, such as pypi_downloads_per_day, we can delete a specific date and rely on materialized views to repopulate it during re-ingestion.

For tables that do not group by day, such as monthly aggregates, it is not possible to isolate rows belonging to a single day. In those cases, materialized views must be dropped before re-ingestion to avoid duplicate data, then recreated and rebuilt once the backfill is complete.

The full script used for this process is available [here](https://github.com/ClickHouse/clickpy/blob/main/scripts/day-fix.sh).

## Where this leaves ClickPy

[ClickPy](https://clickpy.clickhouse.com/) reaching 2 trillion rows is more than a numerical milestone. It reflects both the growing activity of the Python ecosystem and the ability of ClickHouse to support analytical workloads at this scale. We offer ClickPy as a free open source service because we enjoy building applications on top of large datasets, want to support the open source community, and, thanks to ClickHouse, can operate it [cost-efficiently](https://clickhouse.com/blog/cloud-data-warehouses-cost-performance-comparison) even at multi-trillion-row scale.

The work described in this post, migrating the ingestion pipeline to ClickPipes and correcting historical data, is part of our ongoing effort to keep the platform healthy and relevant as it grows. Community engagement plays an important role in shaping ClickPy, and we encourage [feature requests and issue](https://github.com/ClickHouse/clickpy/issues) reports as they directly drive improvements. A recent example is support for exporting charts through Metabase.

With these changes in place, ClickPy is more accurate, easier to operate, and ready for continued growth.


A look at how ClickPy handles over 2 trillion Python package downloads, from ingestion redesign to fixing historical data at scale.

large_clickpy-2-trillion-rows-banner.jpg

medium_clickpy-2-trillion-rows-banner.jpg

small_clickpy-2-trillion-rows-banner.jpg

thumbnail_clickpy-2-trillion-rows-banner.jpg

clickpy-2-trillion-rows-banner.jpg

ClickPy at 2 Trillion rows: Scaling ingestion and fixing the past

As the year comes to an end and things slow down a bit, it feels like a good moment to zoom out and look back at the work we did as a team. I spend a lot of my time at ClickHouse working on demos, something I really enjoy, and I thought I would do a review of the most significant demos we built this year.

That idea was reinforced by a conversation I had with my colleague Tom Schreiber before the holidays. He [spends a lot of his time benchmarking ClickHouse](https://clickhouse.com/blog/what-really-matters-for-performance-lessons-from-a-year-of-benchmarks) and he shared a reflection that stuck with me:

> Benchmarks are great for measuring and understanding how fast ClickHouse is, but demos help people actually feel and experience that speed.

That feels very true. Nothing beats a real-world example that shows how ClickHouse performance makes it possible to build impressive applications at scale. And it's not just us saying this. Customers like Tesla, which has ingested [over a quadrillion rows into ClickHouse](https://clickhouse.com/blog/how-tesla-built-quadrillion-scale-observability-platform-on-clickhouse), or GitLab, [which serves sub-second queries to tens of millions of users](https://clickhouse.com/blog/how-gitlab-uses-clickhouse-to-scale-analytical-workloads), would likely agree.

That's a common theme across the demos featured in this post, and a good place to start looking at what we built this year.

## StockHouse: Real-time market analytics

[StockHouse](https://stockhouse.clickhouse.com/) is a real-time market analytics application built on live stock and crypto data from [Massive APIs](https://massive.com/). It streams ticks over WebSocket APIs, ingests them into ClickHouse, and renders a dashboard built using [Perspective](https://perspective-dev.github.io/) that updates within milliseconds.


stockhouse-video.mp4

> What I love about this demo is how tangible ClickHouse speed becomes. When prices move, charts move with them, without buffering or visible lag.

From an engineering perspective, StockHouse shows how little configuration is needed to ingest real-time data into ClickHouse. [The Go ingester](https://clickhouse.com/blog/building-stockhouse#ingester-in-go) connects directly to Massive's WebSocket APIs and writes batches using the native ClickHouse interface. There is no complex pipeline or external stream processor involved.

On the query side, [the demo relies on materialized views](https://clickhouse.com/blog/building-stockhouse#pre-aggregation-with-materialized-views). Raw ticks are stored as-is, but the queries that feed the live UI read from pre-aggregated tables. Daily open, close, volume, and last update timestamps are computed at insert time, so dashboard queries stay simple and fast.

The pattern is straightforward: raw events for flexibility, materialized views for speed, and queries that consistently run in a few milliseconds. This is a common ClickHouse design, but seeing it applied to live market data makes the tradeoffs very clear.

StockHouse is a good reference for anyone building real-time dashboards, whether the data comes from financial markets, sensors, or observability pipelines. And it's [open source](https://github.com/ClickHouse/stockhouse/).

## ClickGems: open analytics for the RubyGems ecosystem

[ClickGems](https://clickgems.clickhouse.com/) started as a simple request from the [Ruby Central team](https://rubycentral.org/). They wanted an easier way for the community to explore RubyGems download statistics using SQL. [After loading the data](https://clickhouse.com/blog/announcing-ruby-gem-analytics-powered-by-clickhouse) into our [SQL Playground](https://sql.clickhouse.com/?query_id=HVMKR3JXFT4DA8NMAPGXKM), it quickly became clear that it deserved a dedicated interface.

ClickGems video

[ClickGems is essentially a RubyGems-focused version of ClickPy.](https://clickhouse.com/blog/announcing-clickgems) It uses the same codebase and the same approach: expose large public datasets, back them with ClickHouse, and let users explore them through fast, transparent SQL-powered visualizations. This approach has proven to scale well. [ClickPy](https://clickpy.clickhouse.com/) crossed the **two trillion row mark this year**, and ClickGems benefits from the same design choices.

> What I love about this demo is the collaboration. Ruby Central helped with data ingestion and validation, Metabase enabled shareable charts, and together they made ClickGems useful in practice.

From a technical standpoint, ClickGems reinforces a pattern we use across demos. Data is ingested in raw form, then materialized views aggregate it into shapes that match common queries. Each chart is backed by a SQL query that users can inspect and run themselves. There is no hidden logic between the visualization and the database.

ClickGems is now a free analytics site for more than 200,000 gems, covering years of download history. It also serves as a [practical example](https://github.com/ClickHouse/clickpy/tree/clickgems) of how ClickHouse can power public, read-heavy analytics workloads at scale.

## AgentHouse: querying data with natural language

[AgentHouse](https://accounts.google.com/) explores a different angle. Instead of building another dashboard, we wanted to show how ClickHouse can act as a backend for [agentic applications](https://clickhouse.com/ai).

agenthouse_v3.mp4

Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Get started today

AgentHouse is built on top of [LibreChat](https://www.librechat.ai/) and ClickHouse, using [ClickHouse MCP server](https://github.com/ClickHouse/mcp-clickhouse) to query real datasets hosted in ClickHouse Cloud. Users can ask questions using natural language, see the generated SQL, and get instant answers based on real data.

> What I love about this demo is how clearly it shows what agentic applications can look like in practice. There is no abstraction or mock setup. The LLM generates SQL, runs it against live data in ClickHouse, and reasons over real results, which makes the architecture easy to understand.

AgentHouse uses the same datasets available in our [SQL Playground](https://sql.clickhouse.com/), including RubyGems, PyPI, GitHub, and Hacker News. This makes it a safe environment to explore how natural language interfaces behave on large, real-world datasets.

For us, AgentHouse is less about replacing SQL and more about expanding how people interact with data. It shows how ClickHouse fits into LLM-driven systems without giving up transparency, performance, or control.

## ClickStack demo: Observability on ClickHouse, end to end

We built the [ClickStack demo](https://play-clickstack.clickhouse.com/search) to give users an easy way to experience what observability on top of ClickHouse actually looks like. The idea is to put yourself in the role of an SRE and use the stack to [investigate a real application incident](https://clickhouse.com/docs/use-cases/observability/clickstack/getting-started/remote-demo-data), using the same workflows you would rely on in production.

The demo [is based on the official OpenTelemetry demo](https://github.com/ClickHouse/opentelemetry-demo), which simulates a microservices-based e-commerce application with traffic generated by a load generator. All services are instrumented with OpenTelemetry and emit logs, traces, metrics, and session data through a standard OTel Collector into ClickHouse.

ClickStack video

The dataset contains roughly 40 hours of telemetry and is replayed daily with timestamps shifted into the current time window. All signals are stored as wide events in ClickHouse and explored through HyperDX, without separate backends for logs, metrics, or traces.

> What I love about this demo is that it mirrors how an SRE actually works: starting from a vague symptom, narrowing it down through logs, confirming behavior with traces and metrics, and validating impact from the user’s perspective.


## Special mention to Alexey

Alexey Milovidov, ClickHouse CTO and co-founder, loves adopting new datasets and building demos to make it easy to explore them, using ClickHouse, always, obviously.

This year, he extended a visualization tool he originally built for air traffic data and used it for several new demos. [One of the first ](https://clickhouse.com/blog/fsq)was based on Foursquare data, showing places such as shops, restaurants, parks, playgrounds, and monuments on a map.

![fsq-screenshot.png](/uploads/fsq_screenshot_f3955a7908.png)

Later, Alexey explored an unusual dataset derived from airplane telemetry to infer weather conditions. The data was ingested into ClickHouse and the [experiments were reproduced end to end](https://clickhouse.com/blog/planes-weather).

![weather-screenshots.png](/uploads/weather_screenshots_450ade09a4.png)

[He then moved on to a dataset focused on bird observations](https://clickhouse.com/blog/birds), which was again ingested into ClickHouse and visualized on [adbs.exposed](https://adsb.exposed/?dataset=Birds&zoom=5&lat=52.2278&lng=5.0977).

![birds-screenshot.png](/uploads/birds_screenshot_66eab44304.png)

And that still was not enough. A few weeks before the end of the year, Alexey [launched a new website](https://clickhouse.com/blog/velocity) to track GitHub activity across the ClickHouse team.

![velocity-screenshot.png](/uploads/velocity_screenshot_ad779dfae3.png)

Thanks, Alexey, for all the demos this year.

## Why these demos matter

Looking back, these demos cover very different use cases, from real-time dashboards to public analytics sites to agent-driven interfaces. But they all rely on the same core ideas. Store raw data, shape it with materialized views, keep queries explicit, and let performance simplify the rest of the system.

All of these demos are built in the open. The code, schemas, and queries are there to be read, reused, and adapted. They are not reference architectures on slides, but working systems that developers can take apart and build on for their own applications.
That openness matters, especially when combined with real data and real scale. As I discussed with Tom in the introduction, demos make it possible to feel the speed and scale that ClickHouse allows us to operate at. You can see queries return in milliseconds, watch dashboards update live, and explore datasets that would be impractical to handle with many other systems.

For me, this is where demos complement benchmarks. 

We will keep building more of them in 2026.

Have a look at some of the most notable demos we built this year, and how they help people feel the speed of ClickHouse through real, hands-on examples.

large_demos-review-banner.jpg

medium_demos-review-banner.jpg

small_demos-review-banner.jpg

thumbnail_demos-review-banner.jpg

demos-review-banner.jpg

A look back at 2025 demo development at ClickHouse

We benchmarked the five major cloud data warehouses at 1B–100B rows using their real billing models to measure performance per dollar. Results show how cost-performance shifts as data grows.

large_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

medium_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

small_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

thumbnail_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

How the 5 major cloud data warehouses compare on cost-performance

Financial data never stops moving. Prices tick up and down, trades execute in milliseconds, and quotes update thousands of times per second. Capturing that stream and turning it into something usable in real time is a tough engineering problem, but with the right tools, it becomes approachable.

StockHouse is a complete demo showing how to build a streaming market analytics app using [ClickHouse](https://clickhouse.com), [Massive](https://massive.com), and [Perspective](https://perspective.finos.org).

You can explore the live version at [stockhouse.clickhouse.com](https://stockhouse.clickhouse.com).

![stockhouse-landing.png](/uploads/stockhouse_landing_3195032938.png)

StockHouse ingests live market data from Massive's WebSocket APIs, stores it efficiently in ClickHouse, and visualizes it in a web dashboard that updates within milliseconds. The goal is to demonstrate how to handle high-frequency, high-volume data end-to-end: ingestion, storage, aggregation, and real-time visualization.

## Why build it

Market data is the perfect test case for real-time analytics. It's fast, continuous, and always changing. Traditional systems often fall behind when asked to process millions of tiny updates per second.

ClickHouse was designed for that kind of load: fast ingestion, low-latency queries, and efficient compression for time-series data.

StockHouse shows this in action:

-   **Streaming ingestion** from Massive's real-time stock and crypto feeds
-   **Efficient time-series schema** optimized for inserts and queries
-   **Low-latency analytics** with ClickHouse materialized views
-   **Interactive visualizations** that update as fast as the data arrives

The system is simple enough to run locally but powerful enough to handle millions of events per second, which is a solid starting point for anyone building trading dashboards, analytics tools, or observability systems.

## Architecture overview

StockHouse consists of five components that form a straightforward streaming pipeline:

1.  **Data Source** -- Massive WebSocket APIs stream live stock and crypto market data.
2.  **Ingester** -- A Go service that consumes the stream and writes to ClickHouse.
3.  **Database** -- ClickHouse tables and materialized views optimized for real-time analytics.
4.  **Backend** -- A Node.js API that connects the frontend to ClickHouse efficiently.
5. **Frontend** -- A Vue.js dashboard powered by Perspective for high-performance charts.

![stockhouse-diagram.jpg](/uploads/stockhouse_diagram_e38424f51f.jpg)

Let's look at some of the key technical decisions behind this setup.

## Ingester in Go

The ingestion layer is written in Go to handle high event rates while keeping CPU and memory usage low. Go's concurrency model (goroutines and channels) allows the ingester to process multiple WebSocket streams in parallel without blocking.

It uses ClickHouse's Native interface, which sends data in the database's binary columnar format. This reduces parsing overhead and achieves the highest throughput for inserts, confirmed in the [ClickHouse input format benchmark](https://clickhouse.com/blog/clickhouse-input-format-matchup-which-is-fastest-most-efficient#support-matrix). Recently, Netflix [came to the same conclusion](https://clickhouse.com/blog/netflix-petabyte-scale-logging#optimization-2-hub---serialization) while building its ingestion pipeline.

In practice, this lets the ingester push millions of rows per second into ClickHouse while maintaining low latency and predictable resource usage.

Each record (trade, quote, or crypto tick) is batched before insertion, balancing throughput and freshness. The result is a streaming pipeline that can sustain a constant firehose of updates without falling behind.

## Real-Time Visualization with Perspective

On the frontend, StockHouse uses [Perspective](https://perspective.finos.org), an open-source visualization library originally developed at J.P. Morgan and open source through the FINOS community. It's designed for large, continuously updating datasets like financial ticks. This isn't our [first time](https://clickhouse.com/blog/streaming-real-time-visualizations-clickhouse-apache-arrow-perpsective) featuring Perspective, it's no surprise we're drawn to tools built for performance.

Perspective's core engine is written in C++ and compiled to WebAssembly, so most computations (aggregations, pivots, filtering) happen off the main thread. This keeps the UI responsive even when thousands of updates arrive each second.

A key advantage is its native support for [Apache Arrow](https://perspective-dev.github.io/guide/explanation/table/loading_data.html?highlight=arrow#apache-arrow), a high-performance columnar format for in-memory data exchange.

ClickHouse also supports [Arrow as an output format](https://clickhouse.com/docs/interfaces/formats/Arrow), which means query results can be streamed directly from ClickHouse to the browser in a binary, zero-copy format. This avoids JSON parsing overhead and makes transferring data between the database and the visualization layer extremely efficient.

In practice, this allows the StockHouse dashboard to pull live data from ClickHouse, feed it straight into Perspective, and render updates immediately, all while keeping latency and CPU load low. The result is a smooth, interactive dashboard that stays in sync with the market in real time.

## Pool connection with Node.js

Between the frontend and ClickHouse sits a lightweight Node.js backend. This layer is small but crucial for performance.

By managing a persistent connection pool to ClickHouse, the backend avoids the overhead of opening new database sessions for every query. It can reuse prepared contexts and execute requests with minimal latency.

In a real-time dashboard, where queries run frequently and continuously, these savings add up as even a few milliseconds per query can make the difference between smooth streaming and lag.

## Pre-Aggregation with materialized views

ClickHouse can aggregate raw data quickly, but for live dashboards, every millisecond counts. 

To minimize query time, StockHouse uses materialized views to pre-aggregate data as it's ingested.

![blog-stockhouse.gif](/uploads/blog_stockhouse_fbbce14048.gif)

For example, the crypto live table keeps updating its content by querying ClickHouse for the latest daily price information for each crypto pair. 

To optimize query performance, it is a good idea to pre-aggregate the data at insert time therefore reducing the query operation complexity. See below the Materialized view SQL statement that aggregates the data at insert time. 

<pre><code type='click-ui' language='sql' runnable='false'>
-- Create table to store daily crypto price information
CREATE TABLE agg_crypto_trades_daily
(
    `event_date` Date,
    `pair` LowCardinality(String),
    `open_price_state` AggregateFunction(argMin, Float64, UInt64),
    `last_price_state` AggregateFunction(argMax, Float64, UInt64),
    `volume_state` AggregateFunction(sum, Float64),
    `latest_t_state` AggregateFunction(max, UInt64)
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, pair);

-- MV to keep daily price information up to date
CREATE MATERIALIZED VIEW mv_crypto_trades_daily TO agg_crypto_trades_daily
(
    `event_date` Date,
    `pair` String,
    `open_price_state` AggregateFunction(argMin, Float64, UInt64),
    `last_price_state` AggregateFunction(argMax, Float64, UInt64),
    `volume_state` AggregateFunction(sum, Float64),
    `latest_t_state` AggregateFunction(max, UInt64)
)
AS SELECT
    toDate(fromUnixTimestamp64Milli(t, 'UTC')) AS event_date,
    pair,
    argMinState(p, t) AS open_price_state,
    argMaxState(p, t) AS last_price_state,
    sumState(s) AS volume_state,
    maxState(t) AS latest_t_state
FROM crypto_trades
GROUP BY
    event_date,
    pair;
</code></pre>

Then, to query the data, the SQL statement is very simple and can be executed in a few milliseconds. 

<pre><code  type='click-ui' language='sql' runnable='true' play_link='https://sql.clickhouse.com/?query_id=KNHZ65RQLNWZFFBME8ZJW9' show_statistics='true'>WITH toDate(now('UTC')) AS curr_day
    SELECT
        t.pair AS pair,
        argMaxMerge(t.last_price_state) AS last,
        argMinMerge(t.open_price_state) AS open,
        argMaxMerge(q.bid_state)        AS bid,
        argMaxMerge(q.ask_state)        AS ask,
        round(((last - open) / open) * 100, 2) AS change,
        sumMerge(t.volume_state)                AS volume,
        toUnixTimestamp64Milli(now64()) -
        greatest(maxMerge(t.latest_t_state), maxMerge(q.latest_t_state)) AS last_update
    FROM stockhouse.agg_crypto_trades_daily AS t
    LEFT JOIN stockhouse.agg_crypto_quotes_daily AS q
        USING (event_date, pair)
    WHERE event_date = curr_day
    AND pair in ('BTC-USD','ETH-USD','XRP-USD','ZEC-USD','ALEO-USD','SOL-USD','DASH-USD','SUI-USD','ICP-USD','NEAR-USD','TAO-USD','DOGE-USD','HBAR-USD','LINK-USD','ZK-USD','LTC-USD','XLM-USD','ADA-USD','ZEN-USD','ALCX-USD','APT-USD','USDT-USD','SEI-USD','SYRUP-USD','ONDO-USD','AERO-USD','DOT-USD','USDC-USD','XTZ-USD','MINA-USD','FIL-USD','AAVE-USD')
    GROUP BY pair
    ORDER BY pair ASC
</code></pre>

The pattern raw data → aggregated → visualization is a standard way to balance flexibility and speed in ClickHouse. The raw data remains available for historical or analytical queries, while the pre-aggregated tables keep the live UI fast.

## Putting it all together

Here's how it flows:

1.  Massive streams live market data over WebSocket.
2.  The Go ingester parses and inserts events into ClickHouse via the native interface.
3.  Materialized views aggregate data in real time.\
    The Node.js backend serves fast queries through pooled connections.
4.  The Vue + Perspective frontend visualizes updates within milliseconds.

The result is a responsive dashboard that stays current with live market activity.

## Try it yourself

You can explore the running demo at [stockhouse.clickhouse.com](https://stockhouse.clickhouse.com) or deploy it locally using the setup instructions in the [GitHub repository](https://github.com/ClickHouse/stockhouse).

All you need is Node.js, Docker, and a Massive API key to start streaming live data into your own ClickHouse instance. Massive has a free tier for basic use and paid plans that include unlimited API calls and full real-time data access.

StockHouse raw data is also available on our [SQL playground](https://sql.clickhouse.com/?query_id=VTSFBTGQ8C51F15Z1HCZBK) if you want to explore it using SQL.

## Final thoughts

StockHouse isn't just a demo; it's a reference architecture for anyone building real-time analytics systems.

By combining a streaming data source (Massive), a fast analytical database (ClickHouse), and a high-performance visualization layer (Perspective), you can handle massive event streams and deliver insights instantly.

Whether you're tracking financial markets, monitoring infrastructure metrics, or analyzing sensor data, the same principles apply: ingest fast, store efficiently, and query faster.




Learn more how we built StockHouse, a real-time financial analytics application with Massive, ClickHouse and Perspective that scales to thousands of events per second.

large_stockhouse-banner.jpg

medium_stockhouse-banner.jpg

small_stockhouse-banner.jpg

thumbnail_stockhouse-banner.jpg

stockhouse-banner.jpg

Building StockHouse: Real-time market analytics with ClickHouse

This post shows how to use log clustering with Drain3 and ClickHouse UDFs to automatically structure raw application logs. By identifying log templates and extracting key fields into columns, we achieved nearly 50x compression while keeping logs queryable and fully reconstructable.

Summary

Learn how to implement log clustering with Drain3 and ClickHouse UDFs to automatically structure raw application logs. 

Lionel Palacin

Blog posts by Lionel Palacin

PostgreSQL + ClickHouse as the Open Source unified data stack

ClickPy at 2 Trillion rows: Scaling ingestion and fixing the past

A look back at 2025 demo development at ClickHouse

How the 5 major cloud data warehouses compare on cost-performance

Building StockHouse: Real-time market analytics with ClickHouse

Improve logs compression with log clustering

Resources by Lionel Palacin

The definitive guide to ClickHouse query optimization (2026)