DEV Community

Cover image for Day 99 - Efficient Random Sampling with system.numbers_mt: Parallel Number Generation in ClickHouse® 26.3
Kanishga Subramani
Kanishga Subramani

Posted on

Day 99 - Efficient Random Sampling with system.numbers_mt: Parallel Number Generation in ClickHouse® 26.3

#clickhouse #analytics #database #devops

Introduction

Every major ClickHouse® release introduces new features and performance improvements, but occasionally older experimental features are removed to simplify maintenance and improve long-term stability.

One such change in ClickHouse® 26.3 is the removal of the experimental Hypothesis Skip Index (TYPE hypothesis).

If you experimented with this index type in earlier versions, you'll need to update your schema before upgrading to ClickHouse® 26.3. Otherwise, table creation or schema restoration involving this index type will fail.

In this article, we'll explore what Hypothesis Skip Indexes were, why they were removed, how to identify affected tables, and the recommended migration path.

Understanding Data Skipping Indexes

Before discussing the deprecation, it's useful to understand how skip indexes work.

Unlike traditional relational databases that rely on B-tree secondary indexes, ClickHouse® is a column-oriented database optimized for analytical workloads. Instead of locating individual rows, ClickHouse® stores data in granules (blocks of rows).

A data skipping index stores metadata about each granule, allowing the query engine to determine whether an entire granule can be skipped during query execution.

When a query contains filtering conditions, ClickHouse® evaluates the skip index before reading data. If a granule cannot possibly satisfy the filter, it is skipped entirely, reducing disk I/O and improving query performance.

Depending on the workload, skip indexes can significantly reduce the amount of data scanned.

What Was the Hypothesis Skip Index?

The Hypothesis Skip Index (TYPE hypothesis) was an experimental skip index designed to precompute whether a particular boolean expression could evaluate to true within each granule.

Instead of storing values themselves, it stored one of three states for every granule:

Stored Value Meaning
0 Expression is definitely false for all rows (granule can be skipped)
1 Expression may be true (granule must be scanned)
Unknown Insufficient information

For queries using the same expression, ClickHouse® could immediately eliminate granules where the condition was guaranteed to be false.

Example 

CREATE TABLE default.orders
(
    order_id UInt32,
    amount Float64,
    is_large UInt8 MATERIALIZED (amount > 1000),
    order_date Date,

    INDEX idx_large is_large TYPE hypothesis GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY order_id;
Enter fullscreen mode Exit fullscreen mode

In earlier releases, ClickHouse® would precompute whether is_large could ever be true within each granule.

During execution of:

SELECT *
FROM orders
WHERE is_large = 1;
Enter fullscreen mode Exit fullscreen mode

granules known to contain only is_large = 0 could be skipped.

Why Was It Removed?

Although technically interesting, the feature never matured beyond experimental status.

Some of its limitations included:

  • Limited production adoption
  • Known issues with certain data types such as FixedString
  • Experimental behavior without long-term compatibility guarantees
  • Similar optimization could be achieved using supported skip indexes together with materialized columns
  • Additional maintenance burden for the ClickHouse® developers

Because of these reasons, the feature has been removed in ClickHouse® 26.3.

What Changed in ClickHouse® 26.3?

Starting with ClickHouse® 26.3:

  • INDEX ... TYPE hypothesis is no longer recognized.
  • Creating new tables using this index type fails.
  • Schemas containing this index must be updated before upgrading.
  • Existing metadata referencing the deprecated index should be cleaned up.

What Happens After Upgrading?

Attempting to create a table with the removed index now results in an error similar to:

Unknown skip index type: hypothesis
Enter fullscreen mode Exit fullscreen mode

Similarly, restoring backups or executing old DDL statements containing TYPE hypothesis will fail.

Finding Affected Tables

Before upgrading, review your table definitions.

SHOW CREATE TABLE default.orders;
Enter fullscreen mode Exit fullscreen mode

If the output contains:

TYPE hypothesis
Enter fullscreen mode Exit fullscreen mode

that table requires modification before upgrading.

For larger environments, searching exported DDL files or schema repositories for TYPE hypothesis is also recommended.

Removing the Deprecated Index

If the index is no longer required:

ALTER TABLE default.orders
DROP INDEX idx_large;
Enter fullscreen mode Exit fullscreen mode

This removes the deprecated index definition without affecting the table's data.

Recommended Replacement Indexes

Depending on your workload, ClickHouse® offers several supported skip indexes.

Skip Index Best Use Case
minmax Numeric and date range filtering
set Low-cardinality equality filters
bloom_filter String equality and IN predicates
ngrambf_v1 Substring search
tokenbf_v1 Token-based full-text search

The replacement should be selected based on actual query patterns rather than simply replacing TYPE hypothesis with another index.

Example Migration

Old definition:

INDEX idx_large is_large TYPE hypothesis GRANULARITY 4
Enter fullscreen mode Exit fullscreen mode

Possible replacement:

INDEX idx_large is_large TYPE minmax GRANULARITY 4
Enter fullscreen mode Exit fullscreen mode

or

INDEX idx_status status TYPE set(100) GRANULARITY 4
Enter fullscreen mode Exit fullscreen mode

For existing data, materialize the new index:

ALTER TABLE default.orders
MATERIALIZE INDEX idx_large;

ALTER TABLE default.orders
MATERIALIZE INDEX idx_status;
Enter fullscreen mode Exit fullscreen mode

Materialization builds the new skip index for all previously stored parts.

Upgrade Checklist

Before moving to ClickHouse® 26.3:

Step Action
1 Search schemas for TYPE hypothesis
2 Remove deprecated indexes
3 Replace with supported skip indexes where appropriate
4 Materialize new indexes
5 Validate changes in a staging environment
6 Proceed with the production upgrade

Best Practices

To avoid similar upgrade surprises in the future:

  • Avoid using experimental features in production systems.
  • Review release notes before every major upgrade.
  • Choose skip indexes based on observed query workloads.
  • Benchmark performance after index changes.
  • Validate schema migrations in a staging environment before production deployment.

Important Clarification

One common point of confusion is the similarity between two different features.

The deprecated feature discussed in this article is:

TYPE hypothesis
Enter fullscreen mode Exit fullscreen mode

This is not the same as the newer Hypothetical Indexes feature introduced through:

CREATE HYPOTHETICAL INDEX
Enter fullscreen mode Exit fullscreen mode

These are entirely different features with different purposes.

This article focuses only on the removal of the experimental TYPE hypothesis skip index in ClickHouse® 26.3.

Conclusion

The removal of the experimental Hypothesis Skip Index is a relatively small but important breaking change in ClickHouse® 26.3. Organizations upgrading from earlier releases should review their schemas for any remaining TYPE hypothesis definitions before upgrading.

Fortunately, modern skip indexes such as minmax, set, and Bloom filter variants provide reliable, production-ready alternatives for most workloads. By auditing existing tables, replacing deprecated indexes where necessary, and validating the changes in a staging environment, you can ensure a smooth upgrade with no unexpected schema failures.

As ClickHouse® continues to evolve, keeping schemas aligned with supported features is one of the simplest ways to maintain long-term performance, stability, and compatibility.

Top comments (0)