GitHub generates a constant stream of issues, pull requests, and comments. Those are known as GitHub events. Over time, these billions of events capture the complete lifecycle of technology: how frameworks, libraries, and tools rise and fade. But capturing that immense data stream and turning it into a real-time trends analyzer is a challenge. 

**GitTrends** make this possible. It is a new open-source demo application that works like a **specialized Google Trends for the tech world**, letting you search and compare any topic, technology, or keyword trends across **over 10 billion GitHub events in real time**. 

It is now available at [https://gittrends.clickhouse.com](https://gittrends.clickhouse.com)

> GitTrends is a public demo built to showcase ClickHouse's full-text search capabilities. It is fully functional, but may be missing some features or have the occasional bug. 

## How can you use GitTrends? 

Type a search term into GitTrends and instantly see how many times it was mentioned across issues, pull requests, and comments in every GitHub repository. This gives you an overview of a technology's popularity over time, the repositories driving the conversation, the developers most actively talking about it, and the actual issues and pull requests where the term appears.

![gittrend-1.png](/uploads/gittrend_1_f90a94c48d.png)

But GitTrends gets really interesting when you start comparing.

### Compare tech adoption 

Search and compare mention trends for any keywords, exactly like Google Trends but built on the world's most relevant data source for open-source technology. Compare `ClickHouse vs Druid` to see how two analytics databases have traded momentum over time, or track `Claude vs OpenAI` to watch the AI landscape shift in real developer conversations.

![gittrends-21.png](/uploads/gittrends_21_d1284e2bdc.png)

### Identify Ecosystems

Identify the top repositories driving the conversation around any topic. Is ClickHouse discussed mostly in its own ecosystem, or is it bleeding into data engineering and observability projects? Is OpenAI mentioned across a broad range of repos while Claude is concentrated in a handful? 

Knowing where a technology lives tells you as much as knowing how popular it is.

![gittrends-31.png](/uploads/gittrends_31_cca45cbdc2.png)

### Drill into the Source

Move from a high-level trend to the actual conversations behind it. Select any repository and explore its underlying activity: the most active contributors, and the most mentioned issues and PRs driving the trend.

![gittrends-41.png](/uploads/gittrends_41_81773597ae.png)

## Full-Text Search at Scale

GitTrends is built around a simple idea: search any term in real time, across nearly 10 billion GitHub events with no data transformation. Rather than querying pre-computed answers, you index the raw text and search it directly at query time. That's the all promise behind the [new full-text search feature](https://clickhouse.com/blog/full-text-search-ga-release) recently released in ClickHouse. [Simply build a text index on a text column](https://clickhouse.com/docs/engines/table-engines/mergetree-family/textindexes) and use full-text search.

What makes ClickHouse particularly powerful here is that full-text Search and aggregation live in the same engine. A single query can search raw text and aggregate results in one pass, with no joins across systems, no data movement, and no latency penalty. That combination is what makes the experience feel instant rather than just fast.

The GitHub events dataset, nearly 10 billion rows of issues, pull requests, and comments, is a deliberate stress test. GitTrends still delivers fast search across all of it.

To highlight the performance of the new full-text search index, GitTrends includes a live query performance comparison. For any search, you can toggle between using full-text Search, bloom filter and a full table scan and watch the difference play out in real time. 

It is the clearest demonstration of what the right index buys you at scale.

## Look under the hood

GitTrends is fully open and built to be explored at every layer.

### How is the data ingested?

We have been ingesting the GitHub events dataset for some time and exposing it in our SQL Playground so anyone can explore it with SQL. We also documented several example queries and analyses on this [page](https://clickhouse.com/demos/explore-github-with-clickhouse-powered-real-time-analytics). 

The ingestion script used to load the data into ClickHouse is available [here](https://github.com/ClickHouse/sql.clickhouse.com/blob/main/load_scripts/github_events/ingest.sh).

Note that GitHub introduced changes to the Events API payloads starting in October 2025. As a result, certain fields differ from earlier data, which can affect analyses and make trend detection across the full history less accurate. More details are available in the GitHub [changelog](https://github.blog/changelog/2025-08-08-upcoming-changes-to-github-events-api-payloads/).

### What SQL query is running behind each chart?

Every chart in GitTrends is backed by a real query. Click the SQL button on any chart to open it in the SQL playground, where you can inspect it, edit it, and run it yourself.

<pre><code run='false' type='click-ui' language='sql' runnable='true' view='table'  play_link='https://sql.clickhouse.com/?query_id=TWAMA73L7QFQUY6AKWSVJL' show_statistics='true'>
SELECT
  toStartOfDay(created_at) AS bucket,
  count() AS count
FROM github.github_events
WHERE
  event_type IN ('IssueCommentEvent','IssuesEvent','PullRequestEvent','PullRequestReviewCommentEvent','PullRequestReviewEvent')
  AND hasAllTokens(body, 'clickhouse')
  AND created_at >= (now() - toIntervalMonth(1))
  AND 1=1
GROUP BY bucket
ORDER BY bucket ASC
SETTINGS
    enable_parallel_replicas = 1,
    enable_full_text_index = 1,
    use_skip_indexes = 1,
    query_plan_direct_read_from_text_index = 1,
    use_skip_indexes_on_data_read = 1
</code></pre>

Each search type, full-text search, bloom filter, and full table scan, has its own query so you can see exactly what changes under the hood.

### How is the application built?
Interested in running GitTrends locally or adapting the approach to your own dataset?

Most of the demo was developed with Claude Code. The complete source code and step by step deployment instructions are available on [GitHub](https://github.com/ClickHouse/gitTrends).

Give the demo a try at <https://gittrends.clickhouse.com> and share with us [any feedback](https://github.com/ClickHouse/gitTrends/issues/new).


Search and uncover technology trends across 10 billion GitHub events in real time.

large_gittrends-banner.jpg

medium_gittrends-banner.jpg

small_gittrends-banner.jpg

thumbnail_gittrends-banner.jpg

gittrends-banner.jpg

GitTrends: A Google Trends style view of the GitHub ecosystem

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

実際の課金モデルを使用して、10億〜1000億行規模で主要5大クラウドデータウェアハウスをベンチマークし、1ドルあたりのパフォーマンスを測定しました。データ量が増えるにつれてコストパフォーマンスがどのように変化するかを示しています。

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

主要5大クラウドデータウェアハウスのコストパフォーマンス比較

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.

Lionel Palacin

Blog posts by Lionel Palacin

GitTrends: A Google Trends style view of the GitHub ecosystem

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

主要5大クラウドデータウェアハウスのコストパフォーマンス比較

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

Resources by Lionel Palacin

The definitive guide to ClickHouse query optimization (2026)