Farewell to Hypothesis Skip Indexes: ClickHouse® 26.3 Deprecations

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.

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

In this blog, we'll look at what Hypothesis Skip Indexes were, why they were removed, how to identify affected tables, and how to safely migrate.

What are Skip Indexes?

Before diving into the deprecations, a quick recap of what skip indexes are.

ClickHouse® provides a powerful mechanism called data skipping indices that can dramatically reduce the amount of data scanned during query execution, particularly when the primary key isn't helpful for a specific filter condition. Unlike traditional databases that rely on row-based secondary indexes like B-trees, ClickHouse® is a column-store and doesn't store row locations in a way that supports such structures. Instead, it uses skip indexes, which help it avoid reading blocks of data guaranteed not to match a query's filtering conditions.

What Were Hypothesis Skip Indexes?

What Was It?

The hypothesis skip index is a secondary index type in ClickHouse® that stores per-granule boolean results for a given condition expression. When a query's WHERE clause matches the stored expression, ClickHouse® uses the precomputed per-granule truth values to skip granules where the expression is known to be false.

It stored one of three states per granule:

• 0 - the condition is definitely false for all rows in the granule (granule can be skipped)
• 1 - the condition may be true for some rows (granule must be read)
• Unknown - not enough information

Example of what it looked like:

-- Creating a hypothesis skip index
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;

The index would precompute whether is_large = 1 was possible for each granule and skip granules where it was definitely false.

In ClickHouse® 26.3, this definition is no longer valid.

Why Was It Removed?

The hypothesis skip index was an experimental feature that was rarely used in production. It had several known limitations:

• Only worked reliably with specific data types - known bugs with FixedString columns
• Experimental status meant no stability guarantees
• The same granule pruning benefit can be achieved more reliably using materialized columns combined with set or minmax indexes
• Not worth the maintenance burden given its low adoption

What Changed in ClickHouse® 26.3?

Starting with ClickHouse® 26.3:

• INDEX ... TYPE hypothesis is no longer supported.
• Creating new tables with this index type results in an error.
• Existing tables using Hypothesis Skip Indexes should be updated before upgrading.

What Happens After Upgrading?

Attempting to create a table using the removed index type now produces an error because TYPE hypothesis is no longer recognized.

Similarly, existing schemas containing this index must be cleaned up before or during the upgrade process.

How to Find Affected Tables

Before upgrading, identify tables that reference Hypothesis Skip Indexes.

For example, review your table definitions:

SHOW CREATE TABLE default.orders;

or inspect table metadata if you manage multiple databases.

If the output contains:

• TYPE hypothesis

the table should be updated before upgrading.

Removing the Deprecated Index

If the Hypothesis Skip Index is no longer needed, remove it using:

ALTER TABLE default.orders
DROP INDEX idx_amount;

This removes the deprecated index definition while leaving the table data intact.

Choosing an Alternative

If your workload still benefits from data skipping, consider replacing the deprecated index with one of ClickHouse®'s supported skip indexes, depending on your query patterns:

Example: Replacing a Hypothesis Skip Index

Instead of:

INDEX idx_large is_large TYPE hypothesis GRANULARITY 4

Use:

INDEX idx_large is_large TYPE minmax GRANULARITY 4

or

INDEX idx_status status TYPE set(100) GRANULARITY 4

-- Build the new skip index for existing data
ALTER TABLE default.orders MATERIALIZE INDEX idx_large;
ALTER TABLE default.orders MATERIALIZE INDEX idx_status;

Choose the replacement based on the actual filtering behavior of your queries rather than simply replacing the deprecated index one-to-one.

Upgrade Checklist

Before upgrading to ClickHouse® 26.3:

Best Practices

• Avoid relying on experimental features in production environments.
• Review release notes before upgrading to identify deprecated functionality.
• Select skip indexes based on real query workloads and filtering patterns.
• Validate schema changes in a staging environment before applying them to production.

Conclusion

The removal of Hypothesis Skip Indexes in ClickHouse® 26.3 is a small but important change for users upgrading from earlier versions. Although the feature was experimental, any remaining references to TYPE hypothesis should be removed before upgrading.

By reviewing your schemas, replacing deprecated index types with supported alternatives where appropriate, and validating the changes in a test environment, you can ensure a smooth migration to ClickHouse® 26.3.

Note: Don't confuse the deprecated TYPE hypothesis skip index discussed in this article with the newer Hypothetical Skip Indexes feature (CREATE HYPOTHETICAL INDEX). They are different features. This blog focuses only on the removal of the experimental TYPE hypothesis skip index in ClickHouse® 26.3.

References

• ClickHouse® 26.3 Release Blog
• ClickHouse® Documentation – Data Skipping Indexes
• Altinity Stable® Build for ClickHouse® 26.3 Release Notes