---
title: "ClickHouse scalable storage for large datasets"
excerpt: "ClickHouse storage scales with columnar compression, TTL, partitions, and tiered lifecycle rules from GB to petabytes."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-07-14 00:00:00"
publishedOn: "2026-07-14 00:00:00"
updatedOn: "2026-07-14 00:00:00"
status: "published"
---

Storage is where most analytics databases become expensive. Row-oriented systems store every column for every row, compression is limited, and old data accumulates until someone runs a manual cleanup job. ClickHouse® inverts this: columnar storage compresses each column independently, MergeTree engines organize data into parts that merge in the background, and TTL rules delete or move data automatically. The result is storage that grows sub-linearly with event volume and query performance that stays stable as datasets reach billions of rows.

This post covers the storage architecture, compression strategies, tiered storage, partition design, and lifecycle management patterns that keep ClickHouse storage efficient at scale.

## How ClickHouse stores data

ClickHouse organizes data in tables backed by MergeTree-family engines. Each table splits into partitions (typically by month), and each partition contains data parts written by inserts and merged in the background.

The storage layout for a typical events table:

```text
/data/events/
  202607_1_1_0/     ← July 2026 partition, part 1
  202607_2_2_0/     ← July 2026 partition, part 2 (after merge)
  202606_1_3_1/     ← June 2026 partition
```

Columnar storage means each column is stored separately on disk. A query that reads `event_time` and `user_id` skips every other column entirely. This is the primary reason ClickHouse scans billions of rows in seconds where row-oriented databases scan minutes.

For [when to use a columnar database](https://www.tinybird.co/blog/when-to-use-columnar-database), the storage model is the foundational decision. Columnar storage trades write complexity for read speed and compression efficiency.

## Compression codecs and storage efficiency

ClickHouse applies compression per column. The [ClickHouse compression documentation](https://clickhouse.com/docs/en/sql-reference/statements/create/table#column_compression_codec) supports codecs including LZ4, ZSTD, Delta, and Gorilla depending on column data patterns:

```sql
CREATE TABLE events
(
    event_time      DateTime64(3) CODEC(Delta, ZSTD(3)),
    user_id         String CODEC(ZSTD(3)),
    event_type      LowCardinality(String),
    amount          Float64 CODEC(Gorilla, ZSTD(3)),
    properties      String CODEC(ZSTD(3))
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);
```

Common codec choices from ClickHouse documentation:

| Codec | Typical use |
| --- | --- |
| ZSTD | General-purpose compression |
| Delta + ZSTD | Monotonic timestamps and ordered integers |
| Gorilla + ZSTD | Floating-point time series |
| LowCardinality | String columns with relatively few unique values |

`LowCardinality` stores a dictionary of unique values and replaces the column with integer keys, which reduces storage when cardinality is low relative to row count.

For [ClickHouse large datasets](https://www.tinybird.co/blog/clickhouse-large-datasets), columnar compression is the primary mechanism that keeps on-disk size manageable as event volume grows.

## Partition design for storage management

Partitions define the unit of data lifecycle operations. Monthly partitions are the default for time-series data:

```sql
PARTITION BY toYYYYMM(event_time)
```

Monthly partitions enable:

- **Fast drops.** `ALTER TABLE events DROP PARTITION '202601'` removes an entire month instantly without scanning rows.
- **Targeted TTL.** TTL rules operate within partitions, deleting expired parts efficiently.
- **Query pruning.** Queries with time filters skip irrelevant partitions entirely.

Daily partitions suit very high-volume tables where monthly partitions grow large. Yearly partitions suit low-volume reference data.

Avoid over-partitioning. ClickHouse documentation warns that too many small partitions increase metadata overhead and merge cost.

## TTL for automatic data lifecycle

TTL rules delete or move data based on age, column values, or custom expressions:

```sql
ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 90 DAY;
```

TTL runs during background merges. Expired parts are dropped without manual intervention, per [ClickHouse TTL documentation](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl).

```sql
ALTER TABLE events
    MODIFY TTL
        event_time + INTERVAL 365 DAY,
        properties + INTERVAL 30 DAY;
```

Raw event properties expire after 30 days while aggregated columns persist for a year.

Group TTL moves data between storage tiers:

```sql
ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 30 DAY TO VOLUME 'hot',
               event_time + INTERVAL 90 DAY TO VOLUME 'cold',
               event_time + INTERVAL 365 DAY DELETE;
```

Recent data stays on fast NVMe storage. Older data moves to object storage. Data older than a year is deleted automatically.

## Tiered storage with hot and cold volumes

ClickHouse supports multiple storage volumes within a storage policy, documented under [MergeTree multi-volume storage](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes):

```sql
CREATE TABLE events
(
    event_time      DateTime64(3),
    user_id         String,
    event_type      LowCardinality(String),
    amount          Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time)
SETTINGS storage_policy = 'tiered';
```

A tiered storage policy might define:

- **Hot volume:** Local NVMe SSD for data accessed in the last 30 days.
- **Cold volume:** S3 or GCS for data accessed occasionally but retained for compliance.

Queries transparently read from both volumes. The query engine fetches hot data from local disk and cold data from object storage without application changes.

For [S3 analytics the easy way](https://www.tinybird.co/blog/s3-analytics-the-easy-way-tinybird-connector), object storage as a cold tier keeps historical data queryable without paying NVMe prices for data nobody reads daily.

## MergeTree engines for different storage patterns

Different MergeTree variants optimize storage for specific access patterns:

| Engine | Storage behavior | Use case |
| --- | --- | --- |
| MergeTree | Append-only, background merges | Raw events |
| ReplacingMergeTree | Deduplicates on version column | CDC upserts |
| SummingMergeTree | Sums numeric columns on merge | Counters and totals |
| AggregatingMergeTree | Stores aggregate states | Pre-computed rollups |
| CollapsingMergeTree | Collapses sign column pairs | Mutable rows via insert/delete pairs |

Pre-aggregated tables reduce storage by storing rollups instead of raw events:

```sql
CREATE TABLE events_daily
(
    day             Date,
    event_type      LowCardinality(String),
    event_count     SimpleAggregateFunction(sum, UInt64),
    unique_users    AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(day)
ORDER BY (event_type, day);
```

A daily rollup table stores one row per event type per day instead of billions of raw event rows for dashboard queries.

## Storage at billion-row scale

At billion-row scale, storage management becomes an operational discipline:

**Monitor part count.** Too many small parts slow merges and queries. ClickHouse documentation recommends batching inserts rather than sending many tiny batches.

**Monitor compression ratios.** `SELECT formatReadableSize(sum(data_compressed_bytes)), formatReadableSize(sum(data_uncompressed_bytes)) FROM system.parts WHERE active` shows compressed versus uncompressed size in `system.parts`.

**Monitor merge queue.** Backlogged merges indicate insert pressure exceeding merge capacity.

**Use projections for query-specific storage layouts.** Projections store an alternate sort order within the same table, avoiding duplicate storage for common query patterns:

```sql
ALTER TABLE events ADD PROJECTION events_by_user
(
    SELECT *
    ORDER BY user_id, event_time
);
```

For [how to count 100B events comparing architectures](https://www.tinybird.co/blog/how-to-count-100b-events-comparing-architectures), storage efficiency at 100 billion rows is the difference between a manageable cluster and an infrastructure budget crisis.

## Deployment options and storage costs

Self-hosted ClickHouse gives full control over storage hardware, tiered policies, and compression tuning. You provision NVMe for hot data, attach S3 for cold tiers, and manage merge performance yourself.

Managed ClickHouse services handle storage provisioning automatically but charge per compressed GB stored. [Best cloud managed ClickHouse](https://www.tinybird.co/blog/best-cloud-managed-clickhouse) options differ in how they price storage, compute, and data transfer.

For [ClickHouse deployment options](https://www.tinybird.co/blog/clickhouse-deployment-options), the storage cost model depends on whether you self-host with your own hardware, use ClickHouse Cloud with consumption-based billing, or use Tinybird with storage included in the platform tier.

The tradeoff is operational control versus managed convenience. Self-hosted teams optimize compression and tiering manually. Managed platforms automate lifecycle rules but charge a premium for the convenience.

Another option for teams that want the flexibility of dedicated infrastructure without the operational burden of managing every server component is [ScalaHosting managed cloud hosting](https://www.scalahosting.com/managed-cloud-hosting.html). It provides dedicated cloud resources, NVMe storage, free website migration, and the company's proprietary SPanel control panel, while SShield Security is designed to block 99.998% of web attacks before they reach your applications. With a 99.9% uptime guarantee and 24/7 expert support, it's a practical choice for organizations running data-intensive applications that need predictable performance without building their own infrastructure from scratch.

## Tinybird for storage at scale

Tinybird is managed ClickHouse where storage management, merge tuning, and tiered lifecycle policies are handled by the platform. You define datasources with sort keys and TTL rules in `.datasource` files. Tinybird handles part merges, compression, and storage provisioning.

The workflow:

1. Define datasource schema with partition key and sort key aligned to query patterns.
2. Set TTL in the datasource definition for automatic data expiration.
3. Stream events via the Events API or Kafka connector.
4. Query through SQL Pipes without managing storage infrastructure.

Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Storage at that volume runs on Tinybird's managed infrastructure without Resend operating ClickHouse clusters directly. Tinybird is SOC 2 Type II certified.

Branch environments let teams test schema changes and TTL adjustments without affecting production storage. When a datasource schema changes, Tinybird handles the migration path without manual partition management.

## Storage as a solved problem

Most analytics teams treat storage as a growing cost center: data accumulates, disks fill up, someone runs a manual cleanup, queries slow down until an engineer adds indexes or partitions. ClickHouse treats storage as a managed lifecycle: compress aggressively, partition by time, expire old data automatically, move cold data to cheaper tiers.

The result is storage that grows with your business without growing your infrastructure team. Billions of rows stay queryable in milliseconds. Old data disappears on schedule. Compression keeps disk usage predictable.

That is what efficient storage means for ClickHouse: not infinite capacity, but compression and lifecycle management that keep disk usage predictable as data grows from gigabytes to petabytes.

{% cta
  title="Storage that keeps up with your data"
  text: "Tinybird is managed ClickHouse with automatic storage management, TTL policies, and sub-second queries over billions of rows."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}