ClickHouse Sizing: Data Types, Capacity Planning and Zero Data Loss in 8 Essential Decisions (26.8 LTS)

ClickHouse sizing is usually done backwards. A team picks instance sizes from a vendor calculator, loads a year of data, and discovers twelve months later that half the disk is spent on columns typed as String that should have been UInt32, and that the cluster was never sized for three replicas, merge headroom or the log that underwrites a zero data loss commitment. By then the schema is in production and the fix is a migration.

This guide reverses the order. It treats data types as the first capacity decision, because they set the bytes per row that every downstream number multiplies, and it then builds a sizing model that includes the replicas, Keeper, Kafka and backup capacity a zero-loss estate needs. It is written against ClickHouse 26.8 LTS and the current 26.10 stable release, with every version-sensitive statement pinned. The intended reader is the executive who owns the platform budget and the principal engineer who has to defend it.

Part I. ClickHouse data types as a capacity decision

ClickHouse stores each column in its own file, compressed in blocks. The uncompressed width of the type determines how much the compressor has to work with, how many rows fit in a granule’s worth of cache, and how much memory a GROUP BY on that column needs. Two columns holding identical information can differ by an order of magnitude on disk depending on the type chosen, which is why we start every ClickHouse sizing exercise at the schema.

Figure 1. A column-by-column decision path for ClickHouse data types. Each choice fixes the uncompressed bytes per row that ClickHouse sizing multiplies.

Fixed-width types and their ClickHouse sizing cost

The table below gives the in-memory width of the types we see most often in enterprise schemas. These are uncompressed sizes; on disk the codec reduces them, often dramatically for sorted or repetitive data.

The ClickHouse sizing rule we apply is “smallest type that fits the projected maximum, with explicit headroom”. A UInt32 holds just over four billion values; if a key can plausibly pass that within the retention period, use UInt64 now rather than ALTER a multi-terabyte column later. ClickHouse can change a column type online, but on large tables it rewrites every part of that column, which is a capacity event in its own right.

Strings, LowCardinality and Enum

String is variable length: each value carries a length prefix plus its bytes. It is the right type for genuinely free text and the most common source of avoidable bytes everywhere else. For repetitive values, LowCardinality(String) stores a per-part dictionary plus a narrow index per row, and the ClickHouse documentation positions it as effective when a column has fewer than roughly ten thousand distinct values. Enum8 and Enum16 are tighter still, at one or two bytes, but every new value is a schema change.

LowCardinality over fixed-width types of eight bytes or less is blocked by default through allow_suspicious_low_cardinality_types = 0, and for good reason: a dictionary over a UInt32 rarely saves anything. We leave that guard on.

Nullable: a capacity cost, not a free annotation

Every Nullable(T) column carries a separate null map of one byte per row before compression, and every read has to consult it. Where a default (0, empty string, epoch) carries the same meaning for the business, a non-nullable column is smaller and faster. We reserve Nullable for columns where “unknown” and “zero” must be distinguished in reporting, and we document that decision in the data contract.

Separately, columns that are mostly default values are stored with sparse serialization when the share of defaults crosses ratio_of_defaults_for_sparse_serialization (0.9375 by default on 26.10). That is one more reason to prefer defaults over NULL for optional attributes: sparse columns cost almost nothing.

Semi-structured data: JSON, Dynamic and Variant

The JSON, Dynamic and Variant types have been production-ready since ClickHouse 25.3. JSON stores frequently seen paths as typed sub-columns and spills the long tail into a shared structure, which makes it a sound choice for payloads whose keys evolve. It is not a substitute for modelling: paths that every query filters or groups on should be promoted to typed top-level columns, where they compress better and can participate in the sort key. Nullable(Tuple(...)) became generally available in 26.9.

Codecs and the 26.9 compression default

Since 26.9 the default on-disk compression for MergeTree moved from LZ4 to ZSTD(3), and the default client and server network compression moved to ZSTD in the same release. For ClickHouse sizing this matters twice: estates upgrading from 26.8 LTS will see newly written parts shrink as merges rewrite them, and CPU spent on decompression rises. Re-baseline both after the upgrade rather than reusing pre-upgrade ratios.

Specialised codecs pay off on specific shapes of data: Delta or DoubleDelta on monotonic timestamps and counters, Gorilla on slowly changing floats, T64 on integers that use few of their bits. We set them per column only after measuring, as shown below.

CREATE TABLE telemetry.device_metrics_local ON CLUSTER prod_zdl
(
    event_date      Date,
    event_time      DateTime64(3, 'UTC')    CODEC(DoubleDelta, ZSTD(3)),
    device_id       UInt32,
    tenant_id       UInt16,
    region          LowCardinality(String),
    firmware        LowCardinality(String),
    status          Enum8('ok' = 1, 'degraded' = 2, 'offline' = 3),
    reading_count   UInt32                  CODEC(T64, ZSTD(3)),
    temperature_c   Float32                 CODEC(Gorilla, ZSTD(3)),
    payload         JSON(max_dynamic_paths = 256)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{cluster}/{shard}/telemetry/device_metrics_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, device_id, event_time)
TTL event_date + INTERVAL 400 DAY DELETE
SETTINGS
    index_granularity = 8192;

Measure before you decide: per-column footprint

Every type, codec and ClickHouse sizing recommendation we make to a client is anchored in this query. It shows, per column, the compressed and uncompressed bytes and the resulting ratio, which identifies the columns that dominate disk and the ones where a type change will repay the migration effort.

SELECT
    table,
    name                                                AS column_name,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes)           AS compressed,
    formatReadableSize(data_uncompressed_bytes)         AS uncompressed,
    round(data_uncompressed_bytes / nullIf(data_compressed_bytes, 0), 2) AS ratio,
    round(100 * data_compressed_bytes / sum(data_compressed_bytes) OVER (PARTITION BY table), 1) AS pct_of_table
FROM system.columns
WHERE database = 'telemetry'
ORDER BY data_compressed_bytes DESC
LIMIT 30;

To test an alternative type for ClickHouse sizing without touching production, load a representative sample into two scratch tables that differ only in the candidate column and compare the same query. One day of real data is usually enough to show which choice wins.

Part II. A ClickHouse sizing model built on measured multipliers

With the schema settled, sizing becomes arithmetic over a short chain of multipliers. The discipline is that each multiplier comes from a system table on the client’s own data, not from a generic ratio. Figure 2 shows the chain; the sections after it show where each number comes from.

Figure 2. The ClickHouse sizing stack. Schematic only: the compression ratio, replica count, headroom and growth are each measured, not assumed.

Compressed bytes per day, from the system tables

The single most useful ClickHouse sizing number is compressed bytes written per day per table. On an existing cluster it comes straight from the active parts:

SELECT
    database,
    table,
    toStartOfMonth(min_date)                         AS month,
    sum(rows)                                        AS row_count,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts
WHERE active
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY database, table, month
ORDER BY database, table, month;

For a new workload we run the same query against a staging table loaded with at least a full day of production-shaped data. Synthetic data compresses differently from real data, usually better, and produces plans that are too small.

Merge headroom in ClickHouse sizing

Background merges write a new part before removing the parts they replace, so a disk needs free space at least as large as the biggest merge it will run. On 26.10 max_bytes_to_merge_at_max_space_in_pool defaults to 150 GiB, which bounds the largest automatic merge per table. Mutations, OPTIMIZE ... FINAL and detached parts consume more. We plan disks to stay below roughly 70 to 80 percent of capacity at the end of the horizon and alert well before; treat those figures as a starting policy to tune against system.merges and system.disks on your workload, not as a published limit.

A sizing calculator you can audit

We hand clients the model as code rather than a spreadsheet, so every assumption is visible and reviewable. The inputs below are illustrative placeholders, not measurements from any client; replace each with the value from the queries above.

"""ClickHouse sizing model (illustrative inputs; replace with measured values)."""
from dataclasses import dataclass


@dataclass
class Workload:
    rows_per_day: float            # system.parts trend, or forecast
    uncompressed_row_bytes: float  # system.columns uncompressed / rows
    compression_ratio: float       # system.columns, re-measured after 26.9
    retention_days: int            # TTL on the table
    annual_growth: float           # 0.40 = 40 % more rows per year
    horizon_years: float           # planning horizon


@dataclass
class Topology:
    shards: int
    replicas: int                  # 3 for quorum writes across three sites
    max_disk_utilisation: float    # policy, e.g. 0.75


@dataclass
class ZeroLoss:
    kafka_replication_factor: int
    replay_window_days: float      # must exceed the backup interval
    backup_full_copies: int        # full backups retained
    incremental_fraction: float    # daily change as share of compressed size


def plan(w: Workload, t: Topology, z: ZeroLoss) -> dict:
    growth = (1 + w.annual_growth) ** w.horizon_years
    rows_retained = w.rows_per_day * w.retention_days * growth
    compressed = rows_retained * w.uncompressed_row_bytes / w.compression_ratio
    per_replica_set = compressed / t.shards
    per_node_needed = per_replica_set / t.max_disk_utilisation
    cluster_raw_disk = per_node_needed * t.shards * t.replicas

    daily_raw = w.rows_per_day * growth * w.uncompressed_row_bytes
    kafka = daily_raw * z.replay_window_days * z.kafka_replication_factor
    backups = compressed * (z.backup_full_copies + z.incremental_fraction * 7)

    tib = 1024 ** 4
    return {
        "compressed_dataset_tib": round(compressed / tib, 2),
        "disk_per_node_tib": round(per_node_needed / tib, 2),
        "cluster_disk_tib": round(cluster_raw_disk / tib, 2),
        "kafka_log_tib": round(kafka / tib, 2),
        "backup_store_tib": round(backups / tib, 2),
    }


if __name__ == "__main__":
    print(plan(
        Workload(rows_per_day=2e9, uncompressed_row_bytes=120, compression_ratio=8.0,
                 retention_days=400, annual_growth=0.40, horizon_years=1.5),
        Topology(shards=4, replicas=3, max_disk_utilisation=0.75),
        ZeroLoss(kafka_replication_factor=3, replay_window_days=3,
                 backup_full_copies=2, incremental_fraction=0.02),
    ))

Two modelling choices are deliberate. Kafka is sized on uncompressed bytes because producer-side compression varies by client; substitute the measured topic size if you have it. Backups are sized as one logical copy, not per replica, because native BACKUP ON CLUSTER writes each shard’s data once.

Memory, CPU and Keeper: the dimensions disk models miss

Disk is the easiest dimension of ClickHouse sizing and the least likely to cause an outage. Memory and Keeper are where under-sized clusters fail. The server caps itself at max_server_memory_usage_to_ram_ratio (0.9 of RAM by default), and inside that budget sit the mark cache, the primary index cache, the uncompressed cache when enabled, insert buffers and the working memory of every running query.

For ClickHouse sizing, we plan memory from the peak of real queries, not the average. The 99th percentile of per-query memory, multiplied by the concurrency the business expects, plus cache allowances, is the floor.

SELECT
    normalized_query_hash,
    any(query)                                          AS sample_query,
    count()                                             AS executions,
    formatReadableSize(quantile(0.99)(memory_usage))    AS p99_memory,
    formatReadableSize(max(memory_usage))               AS max_memory,
    round(quantile(0.99)(query_duration_ms))            AS p99_ms
FROM system.query_log
WHERE event_date >= today() - 7
  AND type = 'QueryFinish'
  AND query_kind = 'Select'
GROUP BY normalized_query_hash
ORDER BY quantile(0.99)(memory_usage) DESC
LIMIT 20;

SELECT
    metric,
    formatReadableSize(value) AS size
FROM system.asynchronous_metrics
WHERE metric IN ('MarkCacheBytes', 'PrimaryIndexCacheBytes', 'UncompressedCacheBytes', 'MemoryResident')
ORDER BY metric;

Type decisions show up here too. A GROUP BY over a String key holds every distinct string in a hash table; the same key as UInt32 or LowCardinality uses a fraction of the memory. Primary key bytes held in memory are visible per part in system.parts.primary_key_bytes_in_memory, and since primary_key_lazy_load is on by default they are loaded on first use rather than at startup.

For CPU, the useful ClickHouse sizing unit is concurrent heavy queries per node, because a single query will by default use as many threads as the node has cores. Keeper deserves its own line in ClickHouse sizing: its data size grows with the number of parts and replicated tables and with the deduplication hashes kept per table, and it needs low-latency storage because every change is fsynced. Upstream publishes no formal Keeper sizing guide as of November 2026, so we size it from mntr output on a staging cluster and keep it on dedicated hosts.

How zero data loss changes the capacity plan

A zero data loss commitment is a ClickHouse sizing commitment. Our ClickHouse zero data loss design uses quorum writes across three sites, a replicated Kafka log, an independent standby cluster and immutable backups. Each one adds a pool that must appear in ClickHouse sizing from the start.

Figure 3. Zero data loss adds three capacity pools outside the database. A ClickHouse sizing exercise that covers only the cluster disk under-provisions the estate.

One coupling deserves emphasis. If the Kafka replay window is shorter than the interval between backups, a restore-and-replay after a logical error cannot reach the moment before the error, and the RPO is no longer zero. That constraint links the log, backup and database plans, and it is the line item we see cut most often when budgets tighten.

Version notes for 26.8 LTS and 26.10

We verified the following against the upstream source and changelog on 13 November 2026.

Estates still on 25.8 LTS are out of upstream security support, and 26.3 LTS is supported until March 2027. ClickHouse sizing should assume at least one LTS upgrade inside the horizon, with its own re-measurement.

A ClickHouse sizing checklist for the steering committee

When we present a ClickHouse sizing to a client’s steering committee, these are the items we expect to be signed off, each with the evidence behind it.

For estates growing through several orders of magnitude, our guide to scaling ClickHouse from gigabytes to petabytes covers the sharding decisions that follow from this ClickHouse sizing model. The upstream ClickHouse data types reference remains the definitive list of types and their semantics.

Questions we are asked about ClickHouse sizing

Is there a standard compression ratio to use for ClickHouse sizing?

No. Ratios depend on the data, the sort key, the types and the codec, and they changed with the 26.9 default. Measure on a day of production-shaped data and base ClickHouse sizing on that number.

Should every ClickHouse sizing exercise assume three replicas?

Only if the estate needs zero data loss through a site failure. Two replicas protect against a node failure; quorum writes across three sites are what give RPO zero for acknowledged inserts when a site is lost.

How often should the capacity plan be revisited?

Quarterly against the growth trend in system.parts, and after every LTS upgrade, schema migration or change in ingest shape such as a move to smaller, more frequent inserts.

Working with ChistaDATA

ChistaDATA runs 24x7x365 ClickHouse operations from the San Francisco Bay Area plus eleven global offices, on 100% open-source ClickHouse with no vendor lock-in. A ClickHouse sizing engagement starts with a schema and type review, moves to a measured sizing model of the kind shown here, and ends with a plan the steering committee can approve and our ClickHouse managed services team can run against. If your current plan cannot point to the system table behind each number, our ClickHouse consulting team can help.