Schema Design Advisor

Schema Design Advisor

Replaces the removed recommend_table_design tool. Follow the sections below in order: inspect first, then recommend.

Inspect First

Gather evidence before making any recommendation. Run all three in parallel.

1. Schema — call get_table_schema for the target table.

2. Parts summary — call get_table_parts, or:

SELECT
    partition,
    count()                                                 AS part_count,
    sum(rows)                                               AS total_rows,
    formatReadableSize(sum(data_compressed_bytes))          AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes))        AS uncompressed,
    round(sum(data_uncompressed_bytes) /
          nullIf(sum(data_compressed_bytes), 0), 2)        AS ratio
FROM system.parts
WHERE active AND database = 'db' AND table = 't'
GROUP BY partition
ORDER BY partition DESC
LIMIT 20

3. Per-column compression + cardinality — run via query:

SELECT
    name,
    type,
    formatReadableSize(data_compressed_bytes)               AS compressed,
    formatReadableSize(data_uncompressed_bytes)             AS uncompressed,
    round(data_uncompressed_bytes /
          nullIf(data_compressed_bytes, 0), 2)              AS ratio,
    compression_codec
FROM system.columns
WHERE database = 'db' AND table = 't'
ORDER BY data_uncompressed_bytes DESC

4. Cardinality probe (spot-check suspicious columns):

SELECT
    uniq(col_a)  AS card_a,
    uniq(col_b)  AS card_b,
    count()      AS total
FROM db.t

A ratio < 2× on per-column compression usually means the wrong codec or wrong type. Cardinality drives type and codec choices below.

ORDER BY / Primary Key Design

• Rule: list columns low-cardinality → high-cardinality in the ORDER BY clause.
• Common ordering: (tenant_id, date, entity_id, event_id).
• Only the first N columns of ORDER BY form the sparse index (granule size 8192 rows by default). Columns later in the key still sort data but don't speed up point lookups.
• Filter alignment: include in ORDER BY every column that appears in WHERE clauses of your most frequent queries — leftmost first.
• PRIMARY KEY can be a prefix of ORDER BY if you want to deduplicate on a broader key (ReplacingMergeTree / CollapsingMergeTree).
• Avoid putting high-cardinality UUIDs first — they destroy index locality.
• When two columns have similar cardinality, put the one queried with = before the one queried with BETWEEN/range.

Partition Key Choices

Too-many-partitions warning: ClickHouse merges within a partition, not across. More than ~1000 active partitions per table degrades insert and merge performance (each insert touches one partition; too many partitions = many small parts). Daily partitioning on a high-volume table quickly exceeds safe limits.

Check current partition count:

SELECT count(DISTINCT partition) FROM system.parts
WHERE active AND database = 'db' AND table = 't'

If > 500, recommend coarser partitioning.

Column Data-Type Right-Sizing

Integer width

Use the narrowest type that fits the actual data range (query max(col), min(col)). Narrower types compress better and fit more values per granule.

Strings

LowCardinality stores a dictionary per column chunk; high-cardinality columns with LC can use more memory than String.

Dates and timestamps

Avoid String for timestamps — they block time-based pruning and sort incorrectly.

Nullable

Avoid Nullable(T) unless the column genuinely contains NULLs that have semantic meaning. Nullable adds a hidden bitmask column and disables some optimizations (skip indexes, certain codecs). Use a sentinel value (0, empty string, epoch) and document the convention instead.

How to recommend a type change

1. Query max(col), min(col), uniq(col), countIf(col IS NULL) to measure range, cardinality, and null rate.
2. Pick the narrowest correct type from the tables above.
3. Check if existing queries rely on implicit casting (e.g. comparing String column to integer literal).
4. Emit the ALTER:

ALTER TABLE db.t MODIFY COLUMN col_name NewType;

For large tables this is a background mutation — monitor via system.mutations.

Compression Codecs

Apply per column:

ALTER TABLE db.t MODIFY COLUMN ts DateTime CODEC(DoubleDelta, ZSTD(3));
ALTER TABLE db.t MODIFY COLUMN value Float64 CODEC(Gorilla, ZSTD(3));
ALTER TABLE db.t MODIFY COLUMN status LowCardinality(String) CODEC(ZSTD(3));

ZSTD(3) is a safe default when unsure. LZ4 is faster to decompress at the cost of compression ratio. Benchmark with SELECT formatReadableSize(data_compressed_bytes), formatReadableSize(data_uncompressed_bytes) FROM system.columns WHERE ... before and after.

Skip Indexes

Add skip indexes to columns that appear in WHERE but are not in the ORDER BY prefix.

-- minmax on a numeric column
ALTER TABLE db.t ADD INDEX idx_code error_code TYPE minmax GRANULARITY 4;

-- bloom filter for string equality
ALTER TABLE db.t ADD INDEX idx_trace trace_id TYPE bloom_filter(0.01) GRANULARITY 1;

MATERIALIZE INDEX idx_trace IN PARTITION ID 'all';

Skip indexes only help when they skip whole granules (8192 rows). They are useless on columns with near-random values per granule. Verify with EXPLAIN indexes = 1 SELECT ....

Projections and Materialized Views

Projections — embedded alternative sort orders stored inside the table. Use when you have a second common query shape with a different leading ORDER BY column:

ALTER TABLE db.t ADD PROJECTION proj_by_user (
    SELECT * ORDER BY user_id, event_date
);
ALTER TABLE db.t MATERIALIZE PROJECTION proj_by_user;

Projections double storage for covered columns. Only add if the query pattern is frequent enough to justify the write amplification.

Materialized views — pre-aggregate or transform data into a separate table at insert time. Use for:

• Pre-computed aggregates queried repeatedly (SUM, COUNT by dimension)
• Different granularity (raw events → hourly rollups)
• Derived columns computed at write time to avoid runtime cost

Prefer projections over MVs when the source schema and the query shape are stable. Prefer MVs when you need a different engine (AggregatingMergeTree), different TTL, or cross-table joins at insert time.

Cross-references

• storage-optimization — TTL policies, tiered storage, part management, codec benchmarking workflow
• query-tuning-advisor — EXPLAIN output, PREWHERE, JOIN ordering, index effectiveness
• migration-patterns — ALTER TABLE procedures, mutation monitoring, zero-downtime schema changes
• hardware-tuning — memory limits, merge thread tuning, MergeTree settings that affect schema trade-offs